Discussion :

[zidane2012]

Hello ,

J 'ai entendu parlé qu'une requête 11g peut avoir plusieurs plans d'exécution (un plan père et plusieurs plans fils ...).

Quand j'ai en requête affichée sous Toad ou SQL Developer ,le plan d'exécution affiché par la touche F5 (ou autre) affiche bien le bon plan d'exécution utilisé par la requête comme si elle était réellement exécutée ?

Autrement dit peut-on compter sur ces logiciels graphiques pour avoir exactement le plan d'exécution d'une requête SANS L’EXÉCUTER ???

Thanks
Z.

[mnitu]

C’est un peu inexact. Une requête SQL génère un curseur, en fait c’est un curseur enfant. Si la même requête, c’est-à-dire texte identique de la requête, est exécutée dans un environnement de compilation différent alors il y aura un autre curseur enfant la référençant. Le mot enfant dans ce contexte est trompeur parce qu’il n’y vraiment de relation père fils entre ces curseurs. Ce mécanisme n’est pas propre à Oracle 11g mais il est plus visible en Oracle11g a cause d’un autre mecanisme : adaptive cursor sharing

Quand le plan d’exécution d’une requête est affiché dans ces outils c’est le résultat d’une commande explain plan. Mais la commande explain plan ne fait pas d’introspection des valeurs des variables de liaison (bind variable peeking) donc il se peut que le plan affiché par l’outil soit différent d’un plan réel.

[zidane2012]

Si j'ai bien compris :

- Si une requête ne contient pas des "variables bind" alors le plan d'exécution affiché = réel ?

- Si la requête contient des "variables bind" la réalité n'est pas forcément affichée ? mais dans ce dernier cas comment deviner le plan d'exécution ?

[Pomalaix]

... mais dans ce dernier cas comment deviner le plan d'exécution ?

Vous ne pourrez pas le deviner, et il faudra une exécution véritable pour le connaître.
Les vues de base pour cela sont V$SQL_PLAN et V$SQL_PLAN_STATISTICS_ALL, et pour une consultation plus facile vous avez DBMS_XPLAN.DISPLAY_CURSOR, qui exploite ces vues par dessous et vous affiche donc le plan réel.

[mnitu]

Voila un example.
1) On met en place les données

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> Create Table t_exp (
  2    id         number(6) primary key,
  3    soc_id     Number(4) not null,
  4    soc_name   Varchar2(100) not null,
  5    dat_cre    Date
  6  )
  7  /
 
Table created
SQL> Create index ix_t_exp On t_exp(soc_id)
  2  /
 
Index created
SQL> Insert into t_exp
  2  Select Rownum as id,
  3         Case When rownum = 1 then 1 Else 7899 End as soc_id,
  4         dbms_random.string('X',100) As soc_name,
  5         Sysdate as dat
  6    From dual
  7   Connect By level <= 1000
  8  /
 
1000 rows inserted
SQL> commit
  2  /
 
Commit complete
SQL> exec dbms_stats.gather_table_stats(Null, 'T_EXP', method_opt => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY', cascade=>true)
 
PL/SQL procedure successfully completed
 
SQL>

Notez les valeurs de la zone soc_id qui sont fortement non-uniformes (un enregistrement a la valeur 1 et tous les autres la valeur 7899).

2) Explain plan

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
 
SQL> set linesize 132
var soc Number
exec :soc := 1
Explain plan for
Select soc_name
  From t_exp
 Where soc_id = :soc
/
selSQL> SQL> ect * from table(dbms_xplan.display)
/
 
Procédure PL/SQL terminée avec succès.
 
SQL>   2    3    4    5
Explicité.
 
SQL>   2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
------------------
Plan hash value: 2193715598
 
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   500 | 28000 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_EXP |   500 | 28000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
------------------
 
   1 - filter("SOC_ID"=TO_NUMBER(:SOC))
 
13 ligne(s) sélectionnée(s).

Donc l'explain plan prédit un full table scan

3) L'exécution réelle avec trace

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
 
SQL> exec dbms_monitor.session_trace_enable
Select soc_name
  From t_exp
 Where soc_id = :soc
/
exec dbms_monitor.session_trace_disable
 
Procédure PL/SQL terminée avec succès.
 
SQL>   2    3    4
SOC_NAME
----------------------------------------------------------------------------------------------------
SRL8LL99NKULCY4ER1M1QQEXU5LMTFKUY4TF8WFRVMOXILVBVK5F0IFNW6VE1U3LPN1KUK5MOAZ8B98QPQ1TY0HUAOU9QJCKAPIC
 
SQL>
Procédure PL/SQL terminée avec succès.
 
SQL> exit

et le plan dans le fichier de trace est

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
 
Select soc_name
  From t_exp
 Where soc_id = :soc
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 67
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T_EXP (cr=4 pr=0 pw=0 time=33 us)
      1   INDEX RANGE SCAN IX_T_EXP (cr=3 pr=0 pw=0 time=29 us)(object id 108781)

qui montre que l'accès se fait en réalité via l'index

[Mohamed.Houri]

Hello ,

Quand j'ai en requête affichée sous Toad ou SQL Developer ,le plan d'exécution affiché par la touche F5 (ou autre) affiche bien le bon plan d'exécution utilisé par la requête comme si elle était réellement exécutée ?

Autrement dit peut-on compter sur ces logiciels graphiques pour avoir exactement le plan d'exécution d'une requête SANS L’EXÉCUTER ???

Thanks
Z.

Marius et Pomalais vous ont déjà donné l'essentiel de ce que vous devez savoir. J'ajouterai ceci

1. Si vous voulez avoir le vrai plan d'exécution il faut alors exécuter la requête et la faire suivre par un appel au package dbms_xplan.display_cursor. Vous ne devez pas attendre la fin de la requête (lorsque celle-ci est trop lente en temps de réponse) pour avoir le vrai plan d'exécution.

2. Les outils tels que Toad utilisent la commande ''explain plan for" ou son équivalent "set autotrace on"

J'ai écris quelque chose à ce propos ici

3. Dans l'exemple de marius, on voit bien la limite de la commande "explain plan for" : observez bien ci-dessous la partie predicate avec la conversion implicite qui s'y opère : filter("SOC_ID"=TO_NUMBER(:SOC)). Pourquoi à votre avis la commande "explain plan for" fait-elle un TO_NUMBER pour une variable qui a été déclarée en NUMBER? c'est tout simplement parce que cette commande considère toutes les variables de liaisons comme étant du type varchar2 (

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
 
SQL> EXPLAIN plan FOR
  2  SELECT soc_name
  3    FROM t_exp
  4   WHERE soc_id = :soc
  5  ;
 
Explained.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2193715598
 
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   500 | 28000 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_EXP |   500 | 28000 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SOC_ID"=TO_NUMBER(:SOC))
 
13 rows selected.
 
SQL> SELECT soc_name
  2    FROM t_exp
  3   WHERE soc_id = :soc;
 
SOC_NAME
----------------------------------------------------------------------------------------------------
RI35H2KIUUQ4CM5KJRT77KUESJSCSXGLOWLMEC511ANZ4G9APS67RMVJ2VIAZ13IRBQRZBML2G7C13OHCYJKKC5QS3WDE8KSMDQ3
 
SQL> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  4xcu1adhxdq62, child number 0
-------------------------------------
SELECT soc_name   FROM t_exp  WHERE soc_id = :soc
 
Plan hash value: 282033877
 
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_EXP    |     1 |    56 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_T_EXP |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("SOC_ID"=:SOC)

[ojo77]

Vous ne pourrez pas le deviner, et il faudra une exécution véritable pour le connaître.
Les vues de base pour cela sont V$SQL_PLAN et V$SQL_PLAN_STATISTICS_ALL, et pour une consultation plus facile vous avez DBMS_XPLAN.DISPLAY_CURSOR, qui exploite ces vues par dessous et vous affiche donc le plan réel.

J'ajouterais qu'au fur et à mesure du nombre d'exécution d'une requête utilisant à la fois des variables de liaison (bind variables) sur une colonne comprotant des histogrammes, le plan peut évoluer.

En effet en 11g Oracle dispose d'un mécanisme qui lui permet de voir si un prédicat est sensible aux valeurs qui lui sont passées par l'intermédiaire des variables de liaison et donc une même requête peut avoir plusieurs plans d'exécution différents selon la valeur qui lui sera passée en paramètre.