SQL Plan d'exécution catastrophique après migration de Oracle10 à Oracle11

Version imprimable

13/08/2014, 19h13
jypee76

3 pièce(s) jointe(s)

SQL Plan d'exécution catastrophique après migration de Oracle10 à Oracle11

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 !
14/08/2014, 20h58
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:

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.
18/08/2014, 19h37
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
19/08/2014, 14h10
jypee76

1 pièce(s) jointe(s)
bonjour Franck,
sous oracle10 on a pas pu passer ta commande pour obtenir le plan :
Code:

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 :(

Citation:

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
19/08/2014, 14h26
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 ;)
19/08/2014, 16h00
jypee76

Citation:

Envoyé par 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 ;)

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
19/08/2014, 17h29
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:

ALTER SYSTEM RESET "parametre";

et redémarrage.
19/08/2014, 19h59
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
19/08/2014, 20h51
pachot

Citation:

Envoyé par jypee76

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:

select * from sys.aux_stats$;

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

1 pièce(s) jointe(s)

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:

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:

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

20/08/2014, 20h54
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
25/09/2014, 18h09
mickybzh

Même soucis en PROD ...

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