petit souci d'Optimisation

**assahdo** · 10/05/2010, 17h08

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 :

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

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.

**mnitu** · 10/05/2010, 17h14

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

**assahdo** · 10/05/2010, 17h18

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

**assahdo** · 10/05/2010, 17h22

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

**mnitu** · 10/05/2010, 22h03

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 :

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

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

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

**assahdo** · 11/05/2010, 10h03

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.

**mnitu** · 11/05/2010, 10h57

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 :

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

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

**assahdo** · 11/05/2010, 11h34

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

**Ahmed AANGOUR** · 11/05/2010, 12h04

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 :

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

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?

**assahdo** · 11/05/2010, 17h36

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 :

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

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.

**assahdo** · 11/05/2010, 17h41

j'ai peut-etre oublié qq lignes:

Code :

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

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

**Ahmed AANGOUR** · 11/05/2010, 18h00

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 :

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

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 :

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

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

**mnitu** · 12/05/2010, 09h01

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.

**assahdo** · 12/05/2010, 16h48

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 :

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

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.

**mnitu** · 12/05/2010, 17h14

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.

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 :

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

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).

petit souci d'Optimisation

SQL Oracle

Discussions similaires

Partager

Partager