SELECT MAX(QRT_BENCH_DATE) PREVIOUS_DATE FROM admlxa.MUT_EXCHANGE_NORM_LINK, admlxa.QRT_BENCH, admlxa.QRT_COMMON_PARAMS_ID_LNS WHERE ENL_EXCHANGE_NORM_ID IN (50002, 50003) AND QRT_BENCH_ELIOT_ID = ENL_NORM_CODE AND QRT_BENCH_NAME LIKE LNS_PORTFOLIO_TRANSCOI9QM || '%' AND LNS_PORTFOLIO_TRANSCOI9QM IS NOT NULL AND NOT QRT_BENCH_NAME LIKE '%TESTARVAL' AND QRT_BENCH_PREMIUM <> 0 AND ENL_OBJECT_ID = :a1 AND LNS_ID_EUR = :a2 AND QRT_BENCH_DATE < :a3 ;Cette requête était exécutée plusieurs fois dans une boucle et mettait à chaque fois plus de 2 minutes pour s'exécuter. Le plan d'exécution choisi par le CBO est le suivant:
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:02:13.08 | 285K| 260K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:02:13.08 | 285K| 260K|
|* 2 | TABLE ACCESS BY INDEX ROWID | QRT_BENCH | 1 | 2 | 0 |00:02:13.08 | 285K| 260K|
| 3 | NESTED LOOPS | | 1 | 1 | 8048K|00:00:34.38 | 28503 | 28515 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 1 | 1 |00:00:00.05 | 40 | 38 |
|* 5 | TABLE ACCESS FULL | MUT_EXCHANGE_NORM_LINK | 1 | 1 | 1 |00:00:00.04 | 38 | 37 |
| 6 | BUFFER SORT | | 1 | 1 | 1 |00:00:00.01 | 2 | 1 |
|* 7 | TABLE ACCESS BY INDEX ROWID| QRT_COMMON_PARAMS_ID_LNS | 1 | 1 | 1 |00:00:00.01 | 2 | 1 |
|* 8 | INDEX RANGE SCAN | PK_QRT_COMMON_PARAMS_ID_LNS | 1 | 1 | 1 |00:00:00.01 | 1 | 1 |
|* 9 | INDEX RANGE SCAN | IDX_QRT_BENCH_1 | 1 | 78876 | 8048K|00:00:20.25 | 28463 | 28477 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("QRT_BENCH_ELIOT_ID" IS NOT NULL AND "QRT_BENCH_NAME" NOT LIKE '%TESTARVAL' AND "QRT_BENCH_PREMIUM"<>0 AND
"QRT_BENCH_NAME" IS NOT NULL AND "QRT_BENCH_ELIOT_ID"="ENL_NORM_CODE" AND "QRT_BENCH_NAME" LIKE
"LNS_PORTFOLIO_TRANSCOI9QM"||'%'))
5 - filter(("ENL_OBJECT_ID"=:A1 AND INTERNAL_FUNCTION("ENL_EXCHANGE_NORM_ID")))
7 - filter("LNS_PORTFOLIO_TRANSCOI9QM" IS NOT NULL)
8 - access("LNS_ID_EUR"=:A2)
9 - access("QRT_BENCH_DATE"<:A3)
Le plan ci-dessus a été généré avec les stats d'exécution (paramètre STATISTICS_LEVEL=ALL au niveau de la session).
On voit que la requête met plus de 2 minutes à s'exécuter(colonne A-TIME) et que l'essentiel du temps d'exécution concerne la récupération des lignes dans la table QRT_BENCH à partir des ROWIDs de l'index IDX_QRT_BENCH_1. Le problème ici est que l'optimiseur a décidé d'utiliser un accès indexé pour récupérer plus de 8 millions de lignes dans la table QRT_BENCH (soit la quasi totalité de la table).
On devine la cause de l'erreur en comparant les colonnes E-ROWS et A-ROWS pour l'opération 9 du plan (INDEX RANGE SCAN). La colonne E-ROWS indique le nombre de lignes estimées par l'optimiseur pendant la phase de parsing de la requête alors que la colonne A-ROWS indique le nombre de lignes réellement traitées pendant l'exécution de la requête. On constate ici que l'optimiseur a estimé que le parcours de l'index retournerait 78 876 lignes alors que le nombre réel est de 8 048 000.
Pourquoi l'optimiseur se trompe t-il à ce point?
En regardant la trace 10053 j'ai pu m'apercevoir que le paramètre caché _OPTIM_PEEK_USER_BINDS avait été (pour des raisons encore obscures) désactivé sur cette base . Ce paramètre à FALSE indique simplement que la fonctionnalité du BIND VARIABLE PEEKING permettant à l'optimiseur de connaitre la valeur des variables bindées au moment de la phase de hard parse est désactivé.
(Pour plus d'informations sur le BIND VARIABLE PEEKING vous pouvez voir ici mon poste précédent sur ce sujet).
Si le Bind Peeking est désactivé le CBO utilise alors un pourcentage fixe de la table comme estimation de la cardinalité retournée. Ce que j'ai appris comme tout le monde dans le livre de Jonathan Lewis c'est que pour les clauses de type "<" ou ">" une sélectivité de 5% est utlisée par le CBO. Or ici 8763975*0.05 = 438198.
Dans le plan on a une cardinalité de 78876.
NOTE: le nombre 8763975 correspond à la colonne NUM_ROWS dans USER_INDEXES pour l'index IDX_QRT_BENCH_1.
Voici le plan qu'on obtient pour une requête n'impliquant que la table QRT_BENCH:
SQL> explain plan for select * from admlxa.QRT_BENCH where QRT_BENCH_DATE < :a3;
Explained.
Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2896103184
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost> (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 424K| 81M| 3170 (4)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| QRT_BENCH | 424K| 81M| 3170> (4)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | IDX_QRT_BENCH_1 | 78876 | | 303> (5)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("QRT_BENCH_DATE"<:A3)
La cardinalité de l'opération 1 (accès aux données de la table) est de 424K ce qui correspond bien à une sélectivité de 5%:
8480798*0.05 = 424039
NOTE: le nombre 8480798 correspond à la colonne NUM_ROWS dans USER_TAB_STATISTICS pour la table QRT_BENCH.
Par contre pour l'INDEX RANGE SCAN on retrouve nos 78 876.
Alors d'où vient ce nombre?
Après avoir reparcouru en vain le livre de Jonathan LEWIS j'ai décidé de lui envoyer un email directement pour connaitre quelle sélectivité le CBO utilisait pour les INDEX RANGE SCAN. A peine le mail parti que mon collègue avait déjà mis le doigt sur la réponse sur le site de Jonathan :
http://www.jlcomp.demon.co.uk/cbo_book/ch_04.html#_Index_Selectivity_(14th_Dec_2005)
On peut y lire que pour la sélectivité des index (pour les clauses "<" ou ">") le pourcentage appliqué est de 0.9%.
Jonathan LEWIS avec sa grande gentillesse et l'humilité qui le caractérise m'a quand même répondu. Le plus drôle dans l'histoire c'est qu'il avait oublié qu'il connaissait la réponse. Il a d'ailleurs écrit un article le soir même sur son blog sur ce sujet:
http://jonathanlewis.wordpress.com/2011/02/10/cboddity/
Pour en revenir à notre problème initial la solution consiste donc à réactiver le BIND VARIABLE PEEKING via le paramètre caché:
alter session set "_optim_peek_user_binds"=TRUE;Le plan qu'on obtient désormais indique une bonne cardinalité pour la table QRT_BENCH et on voit que l'INDEX RANGE SCAN a laissé place à un Full Table Scan plus approprié.
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:17.91 | 246K| 197K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:17.91 | 246K| 197K|
|* 2 | HASH JOIN | | 1 | 22 | 0 |00:00:17.91 | 246K| 197K|
| 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 1 |00:00:00.04 | 40 | 35 |
|* 4 | TABLE ACCESS FULL | MUT_EXCHANGE_NORM_LINK | 1 | 1 | 1 |00:00:00.03 | 38 | 34 |
| 5 | BUFFER SORT | | 1 | 1 | 1 |00:00:00.01 | 2 | 1 |
|* 6 | TABLE ACCESS BY INDEX ROWID| QRT_COMMON_PARAMS_ID_LNS | 1 | 1 | 1 |00:00:00.01 | 2 | 1 |
|* 7 | INDEX RANGE SCAN | PK_QRT_COMMON_PARAMS_ID_LNS | 1 | 1 | 1 |00:00:00.01 | 1 | 1 |
|* 8 | TABLE ACCESS FULL | QRT_BENCH | 1 | 4136K| 645K|00:00:17.20 | 246K| 197K|
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("QRT_BENCH_ELIOT_ID"="ENL_NORM_CODE")
filter("QRT_BENCH_NAME" LIKE "LNS_PORTFOLIO_TRANSCOI9QM"||'%')
4 - filter(("ENL_OBJECT_ID"=:A1 AND INTERNAL_FUNCTION("ENL_EXCHANGE_NORM_ID")))
6 - filter("LNS_PORTFOLIO_TRANSCOI9QM" IS NOT NULL)
7 - access("LNS_ID_EUR"=:A2)
8 - filter(("QRT_BENCH_ELIOT_ID" IS NOT NULL AND "QRT_BENCH_NAME" NOT LIKE '%TESTARVAL' AND "QRT_BENCH_DATE"<:A3 AND
"QRT_BENCH_PREMIUM"<>0 AND "QRT_BENCH_NAME" IS NOT NULL))
Bien sûr la requête peut être encore améliorée notamment en travaillant sur les clauses de jointure avec la table QRT_BENCH mais au moins avec le BIND PEEKING, le CBO arrive à estimer de bonnes cardinalités.
Aucun commentaire:
Enregistrer un commentaire