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