petit souci d'Optimisation

Cette requête est optimisée par le hint leading . Il s’agit donc de trouver une autre astuce de d'optimisation sans passer par ce hint.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

SELECT /*+ leading(transmitlog) */ orders.pitsequence_s FROM orders, of_x, transmitlog WHERE orders.orderkey = of_x.orderkey AND ( transmitlog.tablename = 'INVRPT_MVTCREATEOVAEXP_X' OR transmitlog.tablename = 'RECAP_INVRPT_MVTCREATEOVAEXP_X' ) AND transmitlog.transmitflag = '5' AND transmitlog.key1 = to_char(of_x.workorderid) AND of_x.storerkey = :vstorerkey ORDER BY orders.orderkey;

---

Merci.

Que est-ce que ça donne quand vous supprimez le hint ?

Avec le hint ça donne un temps de reponse correcte, sinon elle lente.

Attention, la requête fait appel à une bind variable (:vstorerkey).

Citation:

---

Envoyé par assahdo

Attention, la requête fait appel à une bind variable (:vstorerkey).

---

Ce n'est pas grave tant que vous faite des tests avec la variable de binding et que vous avez une idée du mechanisme de "bind variable peeking".
En supposant que les statistiques sont à jour (est-ce que c'est le cas ?);
est-ce que votre problème n'est pas du au

Code:

---

1 2	AND transmitlog.key1 = to_char(of_x.workorderid)

---

Avez vous testé avec un index basé sur la fonction to_char ?

Bonjour mnitu,
D'abord merci pour ton coup d'oeuil sur ma requete, Oui pour les index, bien sur y'a tous les index sur les predicats y compris celui sur la fonction To_char.
Parcontre si tu peux m'expliquer 'bind variable peeking' et comment faire des test en presence des bind variable! faut-il leur donner des valeurs au moment du test, est ce pertinent ?
Merci.

Une variable de binding ne permet pas à l’optimiseur de connaître la valeur effective qui est utilisée pendant l’exécution de la requête. Dans certaines cases ce fait implique des mauvaise estimations dans les cardinalités ce qui implique toujours des mauvais plan d’exécution.

Avant la version 9, Oracle utilise des sélectivités prédéfinies (par exemple 5%) pour les variables de liaison. A partir de la version 9 pour calculer des meilleures sélectivités Oracle utilise le mécanisme d’introspection des valeurs au moment de l’élaboration du plan d’exécution (bind variable peeking). C’est-à-dire qu’il va utiliser la valeur de la variable de liaison au moment dé l’analyse (hard parsing) de la de la requête comme si la requête avait été écrit avec la valeur littérale et non pas la variable de liaison. Ce mécanisme permet une meilleure estimation de la cardinalité mais il pose un autre problème. Si la valeur utilisée pour l’élaboration du plan d’exécution est atypique, le plan d’exécution n’est plus le meilleur quand des autres valeurs sont utilisée ensuite pour la même requête. De plus dans certaines cases, une même requête n’est pas stable, par exemple une semaine elle fait que du full table scan et la semaine d’après, elle fait que d’utiliser un index.

A partir de la version 11 Oracle a ajouté un autre mécanisme « adaptive cursor sharing », qui consiste en gros, à surveiller l’exécution de la requête et dans les cases où il y a une différence significative entre les estimations des cardinalités et la réalité du plan d’exécution de forcer le re parsing de la requête en, question.
Donc il est vital dans l’optimisation des requêtes d’optimiser ces requêtes telle qu’ils sont écrite et non pas en remplaçant les variables de liaison par des valeurs en dur. Sqlplus permet de définir facilement des variables, de le valoriser et d’analyser ces requêtes.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

SQL> variable empno number SQL> exec :empno := 10   ProcÚdure PL/SQL terminÚe avec succÞs.   SQL> set autotrace traceonly SQL> select ename from emp where empno > :empno;   14 ligne(s) sÚlectionnÚe(s).     Plan d'exÚcution ---------------------------------------------------------- Plan hash value: 3956160932   -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 10 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter("EMPNO">TO_NUMBER(:EMPNO))     Statistiques ---------------------------------------------------------- 386 recursive calls 0 db block gets 79 consistent gets 9 physical reads 0 redo size 593 bytes sent via SQL*Net to client 380 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 14 rows processed

---

Mille merci mnitu pour vos precisions, j'en ai vraiment besoin.
:ccool:

bonjour,

quel est le plan de la requête avec et sans le hint ?

Combien de temps dure la requête ?

L'idéal serait d'avoir les plans avec les stats d'execution en utilisant le hint GATHER_PLAN_STATISTICS:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT /*+ gather_plan_statistics */ orders.pitsequence_s FROM orders, of_x, transmitlog WHERE orders.orderkey = of_x.orderkey AND ( transmitlog.tablename = 'INVRPT_MVTCREATEOVAEXP_X' OR transmitlog.tablename = 'RECAP_INVRPT_MVTCREATEOVAEXP_X' ) AND transmitlog.transmitflag = '5' AND transmitlog.key1 = to_char(of_x.workorderid) AND of_x.storerkey = :vstorerkey ORDER BY orders.orderkey;   SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats_last'));

---

Avec ça on pourra comparer les cardinalités estimées par le CBO et les lignes réellement processées lors de l'execution.
On pourra voir rapidement quelle est l'opération du plan qui pose problème.

Peux tu également nous donner la version d'oracle utilisée ainsi que les valeurs des paramètres du CBO?

Merci bcp pour ton aide, la base est une 10g R2, parcontre j'ai l'impression que ça marche des fois, mais pas tout le temps, je ne comprend plus rien là.

le plan sans le hint est mieux en ce moment qu'avec le hint:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

Elapsed: 00:00:00.84   Execution Plan ---------------------------------------------------------- Plan hash value: 719206502   --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50884 | 3528K| | 7096 (1)| 00:01:26 | | 1 | SORT ORDER BY | | 50884 | 3528K| 7992K| 7096 (1)| 00:01:26 | | 2 | NESTED LOOPS | | 50884 | 3528K| | 6148 (1)| 00:01:14 | |* 3 | HASH JOIN | | 50884 | 2782K| 2112K| 1056 (1)| 00:00:13 | | 4 | TABLE ACCESS BY INDEX ROWID| OF_X | 53969 | 1475K| | 309 (1)| 00:00:04 | |* 5 | INDEX RANGE SCAN | IDX_FK_OF_X_STORER | 53969 | | | 17 (0)| 00:00:01 | | 6 | INLIST ITERATOR | | | | | | | |* 7 | INDEX RANGE SCAN | IDX_TRANSMITLOG_SB01 | 159K| 4353K| | 41 (3)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 15 | | 1 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | PK_ORDERS | 1 | | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   3 - access("TRANSMITLOG"."KEY1"=TO_CHAR("OF_X"."WORKORDERID")) 5 - access("OF_X"."STORERKEY"=:VSTORERKEY) 7 - access(("TRANSMITLOG"."TABLENAME"='INVRPT_MVTCREATEOVAEXP_X' OR "TRANSMITLOG"."TABLENAME"='RECAP_INVRPT_MVTCREATEOVAEXP_X') AND "TRANSMITLOG"."TRANSMITFLAG"='5') filter("TRANSMITLOG"."TRANSMITFLAG"='5') 9 - access("ORDERS"."ORDERKEY"="OF_X"."ORDERKEY")     Statistics ---------------------------------------------------------- 3686 recursive calls 0 db block gets 1251 consistent gets 105 physical reads 0 redo size 282 bytes sent via SQL*Net to client 374 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 29 sorts (memory) 0 sorts (disk) 0 rows processed

---

Tu peux toujours jeter un cp d'oeuil, peut-être que tu verra ça mieux que mieux.
Encore merci.

j'ai peut-etre oublié qq lignes:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

no rows selected   Elapsed: 00:00:00.84   Execution Plan ---------------------------------------------------------- Plan hash value: 719206502   --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50884 | 3528K| | 7096 (1)| 00:01:26 | | 1 | SORT ORDER BY | | 50884 | 3528K| 7992K| 7096 (1)| 00:01:26 | | 2 | NESTED LOOPS | | 50884 | 3528K| | 6148 (1)| 00:01:14 | |* 3 | HASH JOIN | | 50884 | 2782K| 2112K| 1056 (1)| 00:00:13 | | 4 | TABLE ACCESS BY INDEX ROWID| OF_X | 53969 | 1475K| | 309 (1)| 00:00:04 | |* 5 | INDEX RANGE SCAN | IDX_FK_OF_X_STORER | 53969 | | | 17 (0)| 00:00:01 | | 6 | INLIST ITERATOR | | | | | | | |* 7 | INDEX RANGE SCAN | IDX_TRANSMITLOG_SB01 | 159K| 4353K| | 41 (3)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 15 | | 1 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | PK_ORDERS | 1 | | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   3 - access("TRANSMITLOG"."KEY1"=TO_CHAR("OF_X"."WORKORDERID")) 5 - access("OF_X"."STORERKEY"=:VSTORERKEY) 7 - access(("TRANSMITLOG"."TABLENAME"='INVRPT_MVTCREATEOVAEXP_X' OR "TRANSMITLOG"."TABLENAME"='RECAP_INVRPT_MVTCREATEOVAEXP_X') AND "TRANSMITLOG"."TRANSMITFLAG"='5') filter("TRANSMITLOG"."TRANSMITFLAG"='5') 9 - access("ORDERS"."ORDERKEY"="OF_X"."ORDERKEY")     Statistics ---------------------------------------------------------- 3686 recursive calls 0 db block gets 1251 consistent gets 105 physical reads 0 redo size 282 bytes sent via SQL*Net to client 374 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 29 sorts (memory) 0 sorts (disk) 0 rows processed     Elapsed: 00:00:00.03   Execution Plan ---------------------------------------------------------- Plan hash value: 3713220770   ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8168 | 16336 | 19 (0)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | | | | | ----------------------------------------------------------------------------------------------------

---

as tu utilisé le hint GATHER_PLAN_STATISTICS?

si oui tu peux récupérer le plan avec les stats d'exécution en récupérant le SQL_ID de ta requête.
Une fois le SQL_ID récupéré tu peux lancer la commande suivante:

Code:

---

1 2	SELECT * FROM table(dbms_xplan.display_cursor('&&sql_id',NULL,'allstats last'));

---

replace &&sql_id par le SQL_ID de ta requête.

si tu ne sais pas comment récupérer le SQL_ID tu peux l'avoir en exécutant la commande suivante:

Code:

---

1 2	select * from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%';

---

Citation:

---

Envoyé par assahdo

Merci bcp pour ton aide, la base est une 10g R2, parcontre j'ai l'impression que ça marche des fois, mais pas tout le temps, je ne comprend plus rien là.

le plan sans le hint est mieux en ce moment qu'avec le hint:
...

---

C'est normal, il arrive très rarement qu'un hint est vraiment nécessaire. Le plan d'exécution montre que la requête a fait l'objet d'un hard parse et a priori c'est un bon plan.
L'index sur la table transmitlog n'est pas très sélectiv donc commencer avec la table of_x semble à être une bon solution.

OK mnitu c'est vrai Transmitlog n'est pas tres selective mais ça n'a marché qu'avec ça (grace au hint) il y'a 2 semaines.
Parcontre Ahmed ce que j'ai mis là est le resultat avec :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT /*+ gather_plan_statistics */ orders.pitsequence_s FROM orders, of_x, transmitlog WHERE orders.orderkey = of_x.orderkey AND ( transmitlog.tablename = 'INVRPT_MVTCREATEOVAEXP_X' OR transmitlog.tablename = 'RECAP_INVRPT_MVTCREATEOVAEXP_X' ) AND transmitlog.transmitflag = '5' AND transmitlog.key1 = to_char(of_x.workorderid) AND of_x.storerkey = :vstorerkey ORDER BY orders.orderkey;   SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'allstats_last'));

---

je ne sais pas si vous avez besoin d'autre chose !!

Merci à tous.

Citation:

---

Envoyé par assahdo

OK mnitu c'est vrai Transmitlog n'est pas tres selective mais ça n'a marché qu'avec ça (grace au hint) il y'a 2 semaines.

---

Le hint force un chemin il ne laisse plus tous les choix à l’optimiseur. Et comme l’optimiseur évolue avec chaque version d’Oracle cela vous oblige à revalider tous les requêtes qui utilisent les hints pour s’assurer que les choses continues à tourner correctement. Or souvent il y a des autres solutions moins envahissantes pour suggérer à l’optimiseur de changer de plan d’exécution.

Citation:

---

Envoyé par assahdo

Parcontre Ahmed ce que j'ai mis là est le resultat avec :
...

---

Bref, il attend une sortie comme çelle ci, qui permet de voir les différences entres les estimations et l'exécution en termes des cardinalités

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));   PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------   SQL_ID ffpx7fjxgp12z, child number 0 ------------------------------------- Select /*+ gather_plan_statistics */ ename, dname From emp e join dept d On (e.deptno = d.deptno)   Plan hash value: 351108634   -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------- | 1 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 24 |   PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------   | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 8 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 14 | 1 | 14 |00:00:00.01 | 16 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 14 | 1 | 14 |00:00:00.01 | 2 | --------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   4 - access("E"."DEPTNO"="D"."DEPTNO")     21 ligne(s) sÚlectionnÚe(s).

---