Discussion :

[jypee76]

Bonjour à toutes et tous,

Je me permets de vous solliciter en l’absence de notre DBA absent pendant 3 semaines, alors que nous avons un projet critique à livrer semaine prochaine...

Nous rencontrons de gros problème de performance BDD après migration de Oracle9 vers Oracle10.
Les mêmes requêtes/package SQL prennent beaucoup plus de temps sur notre nouvel environnement que sur l’ancien. Voici un exemple détaillé :
- Serveur actuelle : Oracle10, requête SQL en pièce jointe ‘query.sql‘ --> la requête rend la main après quelques minutes et a un plan d’exécution très correct (coût 945).
- Serveur nouveau : Oracle11, même requête SQL ‘query.sql‘ --> ne rend jamais la main, sature le TBS TEMP, et a un plan d’exécution catastrophique (4868297072831149)
- Les indexes sont identiques entre les 2 serveurs, ils ont été rebuildés/recréés et les statistiques recalculées sur le nouveau serveur. Et pourtant la situation est toujours la même...
- Vous trouverez en attachement les 2 plans d’exécutions totalement différents pour la même requête

Nous ne comprenons pas comment la même requête, avec les mêmes indexes, peut se comporter de façon si différente entre ces 2 environnements. Cela est totalement bloquant pour notre livraison bien évidemment...

Merci d’avance pour votre avis, cela pourrait nous aider grandement.

Jypee !

[pachot]

Bonjour,

Ces plans sont illisibles.
Il peut y avoir plein de raisons pour qu'un plan d'exécution soit différent. Et ce n'st peut être pas lié à la version mais le serveur est peut-être paramétré différemment.
Est-ce que tu peux faire un plan d'exécution de la manière suivante sous sqlplus:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
set pagesize 10000 linesize 300 trimspool on serveroutput off

EXPLAIN PLAN FOR select [...]
spool plan.txt
select * from table(dbms_xplan.display(format=>'advanced'));
spool offet le poster?

Cordialement,
Franck.

[jypee76]

Bonjour Frank,

Je viens de demander le résulat de tes commandes à mon collègue, je poste ça demain !
(Je ne comprends pas pourquoi les plans sont illisibles, ce sont des HTML qui une fois dézippés s'affichent bien chez moi.)

En tous les cas merci pour ta réponse, j'espère que les infos que je posterai demain t'aideront à diagnostiquer.

Je te confirme qu'on a également "joué" avec pas mal de paramètres de config Oracle du serveur (optimizer-xxx, sga, ...) mais sans résultat pour le moment.

JP

[jypee76]

bonjour Franck,
sous oracle10 on a pas pu passer ta commande pour obtenir le plan :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SQL> @queryPlan.sql
Explained.
select * from table(dbms_xplan.display(format=>'advanced'))
*
ERROR at line 1:
ORA-00907: missing right parenthesis

Par contre sur oracle11, base sur laquelle on a les problèmes de perf, ça a marché et je t'ai attaché le résultat (bien visible avec notepad++).

Egalement pour info les paramètres de la BDD ci-dessous.
Merci d'avance pour ton aide, on a pas progressé en 2 jour

compatible 11.2.0
cursor_sharing FORCE
db_cache_size 2 097 152 000
db_file_multiblock_read_count 16
db_writer_processes 1
disk_asynch_io TRUE
filesystemio_options asynch
java_pool_size 167 772 160
job_queue_processes 10
large_pool_size 67 108 864
log_checkpoints_to_alert FALSE
open_cursors 5 000
pga_aggregate_target 5 368 709 120
processes 2 000
recyclebin off
session_cached_cursors 150
sga_max_size 5 301 600 256
shared_pool_reserved_size 78 852 915
shared_pool_size 1 577 058 304
statistics_level TYPICAL
timed_statistics TRUE
undo_management AUTO
undo_retention 10 800
undo_tablespace UNDO_RRC01
workarea_size_policy AUTO
optimizer_capture_sql_plan_baselines FALSE
_optimizer_cost_based_transformation OFF
optimizer_dynamic_sampling 2
optimizer_features_enable 11.2.0.3
optimizer_index_caching 50
optimizer_index_cost_adj 50
optimizer_mode choose
optimizer_secure_view_merging FALSE
_optimizer_skip_scan_enabled FALSE
optimizer_use_invisible_indexes FALSE
optimizer_use_pending_statistics FALSE
optimizer_use_sql_plan_baselines FALSE

[pachot]

Whaou, un plan avec une estimation de 2 mille milliards de lignes... et l'optimiseur choisit un NESTED LOOP là dessus !

Il faudrait commencer par enlever tous les paramètres qui font faire n'importe quoi à l'optimiseur:

db_file_multiblock_read_count	16
_optimizer_cost_based_transformation	OFF
optimizer_index_caching	50
optimizer_index_cost_adj	50
optimizer_mode	choose
_optimizer_skip_scan_enabled	FALSE

et voir ce que ça donne en les enlevant.

Ca ne veut pas dire que tout marchera mieux après, mais au moins le tuning peut partir sur de bonnes bases.
C'est votre DBA qui a mis tout ça ? Il faudra lui demander pourquoi lorsqu'il rentrera de vacances

[jypee76]

Whaou, un plan avec une estimation de 2 mille milliards de lignes... et l'optimiseur choisit un NESTED LOOP là dessus !

Il faudrait commencer par enlever tous les paramètres qui font faire n'importe quoi à l'optimiseur:
db_file_multiblock_read_count 16
_optimizer_cost_based_transformation OFF
optimizer_index_caching 50
optimizer_index_cost_adj 50
optimizer_mode choose
_optimizer_skip_scan_enabled FALSE
et voir ce que ça donne en les enlevant.

Ca ne veut pas dire que tout marchera mieux après, mais au moins le tuning peut partir sur de bonnes bases.
C'est votre DBA qui a mis tout ça ? Il faudra lui demander pourquoi lorsqu'il rentrera de vacances

Je lui dirai au DBA je pense que c'était par défaut au moment de la construction de la base.

- Quand tu dis enlever, il faut bien mettre une valeur quand même? C'est pas un delete du paramètre j'imagine?
- une fois qu'on a "enlevé" ces valeurs là, faut il redémarrer la base, recalculer les stats, ...?

Merci pour tes réponses, et je lance les actions direct.

JP

JP

[pachot]

C'est une base de test, c'est bien ça ?

Oui, on enlève le paramètre pour garder la valeur par défaut. Avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER SYSTEM RESET "parametre";

et redémarrage.

[jypee76]

ok je te tiens au courant demain matin, merci.
On a également recréé les tables cet après midi, là dessus on rebuilde les indexes, on resete les paramètres que tu m'as indiqués, on stop/relance la base et je verrai ce que ça donne.

Faut il qu'on recalcule les stats aussi, et si oui à quel moment de la séquence ci-dessus.

JP

[pachot]

Faut il qu'on recalcule les stats aussi, et si oui à quel moment de la séquence ci-dessus.

Après avoir rebuildé les index.

Est-ce que tu peux faire aussi:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from sys.aux_stats$;

et montrer le résultat pour être sûr qu'il n'y a pas des choses abérrentes.

[jypee76]

bonjour Franck,

Désolé pour le retard dans ma communication mais comme tu l'as deviné dans ton post d'avant, c'est une base de test et j'ai pas mal de développeurs offshores qui l'utilisent. Du coup je ne peux pas arrêter/relancer la base comme ça.

Quoiqu'il en soit, j'ai des bonnes news !

Voici ce qui a été fait :
1) table recréées
2) index rebuildés
3) stats recalculées

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC DBMS_STATS.gather_schema_stats('DIMENSION', estimate_percent => 10, cascade => TRUE);

4) resultat de SELECT * FROM sys.aux_stats$

SELECT * FROM sys.aux_stats$
SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    08-14-2014 13:06
SYSSTATS_INFO                  DSTOP                                     08-14-2014 14:15
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                     1468.71708
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM                            6.657
SYSSTATS_MAIN                  MREADTIM                            6.507
SYSSTATS_MAIN                  CPUSPEED                             1440
SYSSTATS_MAIN                  MBRC                                    7
SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

5) - modification des paramètres comme indiqué par Franck :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SQL>  alter system reset "db_file_multiblock_read_count";
System altered.
SQL> alter system reset "_optimizer_cost_based_transformation";
System altered.
SQL>  alter system reset "optimizer_index_caching";
System altered.
SQL> alter system reset "optimizer_index_cost_adj";
SQL>  alter system reset "optimizer_mode";
System altered.
SQL> alter system reset "_optimizer_skip_scan_enabled";
System altered.

6) stop/start de la base
7) Nouvelle valeur des paramètres DB :

SQL> show parameter optimi
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_cartesian_enabled         boolean     FALSE
_optimizer_mjc_enabled               boolean     FALSE
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.3
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     FALSE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_sql_plan_baselines     boolean     TRUE
plsql_optimize_level                 integer     2
SQL> show parameter db_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128
db_file_name_convert                 string
db_files                             integer     200

8) resultat de SELECT * FROM sys.aux_stats$ après changement des paramètres --> IDENTIQUE au résultat du 4) (est ce normal?)
9) re-excution de la requête et du plan d'exécution :
-COUT ~ 3400 (nette amélioration !!)
-la requête n'explose plus le TBS TEMP
-la requête rend la main après 1000secondes avec les bons résultats !

En attachement le nouveau plan d'exécution.

Du coup la situation est nettement meilleure, on regarde en ce moment si d'autres requêtes posent encore problème.
Quel est ton avis sur cette nouvelle situation, et merci beaucoup déjà ! Car il est fort probable que ce RESET des variables de l’optimiseur ait sauvé la situation. Je suis déjà bien soulagé

JP

[pachot]

Bonjour,
C'est une bonne nouvelle. Comme quoi l'optimiseur marche bien lorsqu'on ne cherche pas à le bidouiller. Pourtant la requête a l'air assez tordue aussi. On va voir plus de 100 fois la même table!
Les sys.aux_stats$ oui c'est normal qu'ils montrent la même chose. C'était juste pour voir si les valeurs étaient cohérente. Il semble qu'un calcule de statistiques systèmes ait été fait le 14 aout en mode 'workload' oendant 1 heure. C'est ok s'il y avait de l'activité représentative pendant ce temps là.
Pour aller plus loin, maintenant que la requête arrive au bout, tu peux faire un plan d'exécution avec les stats d'exécution (http://www.dbi-services.com/index.ph...execution-plan).
Cordialement,
Franck

[mickybzh]

Bonjour Franck, je suis un collègue de Jypee.

On se retrouve de nouveau avec un plan d'exécution ayant un coût énorme.
Les paramètres de l'optimizer ont tous été resetés.

Pour info, voici les stats.

Stats:
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 09-25-2014 11:52
SYSSTATS_INFO DSTOP 09-25-2014 12:52
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 1290,87206072949
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 0,695
SYSSTATS_MAIN MREADTIM 17,16
SYSSTATS_MAIN CPUSPEED 1395
SYSSTATS_MAIN MBRC 84
SYSSTATS_MAIN MAXTHR 68637696
SYSSTATS_MAIN SLAVETHR 24576

Si jamais tu as une idée, elle serait la bienvenue

Merci d'avance