Ecart sur explain plan

**olivier]** · 18/11/2014, 16h52

Bonjour.

J'ai une base 10g sur une machine en winserver 2003 que je remonte chaque soir dans une base 10g sur une machine en winserver 2000. Je n'ai donc qu'un jour d'écart entre les 2 bases.

J'ai fait un explain plan sur la requête suivante.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
select min(code_t) mini, max(code_t) maxi
from 
(	select code_s
	from t_session 
	where types = 0
	and h_d_o between add_months(trunc(sysdate,'mm'),-1)+19 and  trunc(sysdate,'mm')+18
) s
	inner join m on m.code_s = s.code_s
	inner join a on a.code_m = m.code_m
	inner join t on t.code_a =a.code_a;

avec les tables

Code :

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

1
2
3
4
5
 
s (code_s, h_d_o, types)
m (code_m, code_s)
a (code_a, code_m) index I_FK_M0 sur a.code_m
t (code_t, code_a) index I_FK_A sur t.code_a

Ce que je ne comprends pas c'est que je n'obtiens pas les mêmes résultats sur les 2 machines.

machine source

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
 
------------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |     1 |    56 |       |   736K  (1)| 02:27:13 |
|   1 |  SORT AGGREGATE        |               |     1 |    56 |       |            |          |
|*  2 |   FILTER               |               |       |       |       |            |          |
|*  3 |    HASH JOIN           |               |  1652K|    88M|    12M|   736K  (1)| 02:27:13 |
|*  4 |     HASH JOIN          |               |   235K|  9659K|  1136K| 64115   (2)| 00:12:50 |
|*  5 |      HASH JOIN         |               | 28191 |   798K|       |  7126   (3)| 00:01:26 |
|*  6 |       TABLE ACCESS FULL| S             |  9119 |   151K|       |  2493   (4)| 00:00:30 |
|   7 |       TABLE ACCESS FULL| M             |  3081K|    35M|       |  4614   (1)| 00:00:56 |
|   8 |      TABLE ACCESS FULL | A             |    24M|   307M|       | 27262   (2)| 00:05:28 |
|   9 |     TABLE ACCESS FULL  | T             |   150M|  2004M|       |   484K  (1)| 01:37:00 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-1)+19<=TRUNC(SYSDATE@!,'fmmm')+18)
   3 - access("T"."CODE_A"="A"."CODE_A")
   4 - access("A"."CODE_M"="M"."CODE_M")
   5 - access("M"."CODE_S"="CODE_S")
   6 - filter("TYPES"=0 AND "H_D_O">=ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-
              1)+19 AND "H_D_O"<=TRUNC(SYSDATE@!,'fmmm')+18)
 
26 ligne(s) sélectionnée(s).

machine de copie

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
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                 |     1 |    56 |  1011K  (1)| 01:24:17 |
|   1 |  SORT AGGREGATE                  |                 |     1 |    56 |            |          |
|*  2 |   FILTER                         |                 |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID   | T               |     7 |    98 |     4   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                 |  1652K|    88M|  1011K  (1)| 01:24:17 |
|   5 |      NESTED LOOPS                |                 |   235K|  9659K| 99074   (2)| 00:08:16 |
|*  6 |       HASH JOIN                  |                 | 28191 |   798K| 14536   (7)| 00:01:13 |
|*  7 |        TABLE ACCESS FULL         | S               |  9119 |   151K|  5242  (11)| 00:00:27 |
|   8 |        TABLE ACCESS FULL         | M               |  3081K|    35M|  9170   (4)| 00:00:46 |
|   9 |       TABLE ACCESS BY INDEX ROWID| A               |     8 |   104 |     3   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN          | I_FK_M0         |     9 |       |     2   (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN            | I_FK_A          |     7 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-1)+19<=TRUNC(SYSDATE@!,'fmmm')+18)
   6 - access("M"."CODE_S"="CODE_S")
   7 - filter("TYPES"=0 AND "H_D_O">=ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-1)+1
              9 AND "H_D_O"<=TRUNC(SYSDATE@!,'fmmm')+18)
  10 - access("A"."CODE_M"="M"."CODE_M")
  11 - access("T"."CODE_A"="A"."CODE_A")
 
28 ligne(s) sÚlectionnÚe(s).

La machine source n'utilise pas les indexes.
J'ai comparé les parametres avec l'instruction show parameters, tous les paramètres sont identiques aux chemins près bien sûr.

Quelqu'un pourrait-il m'expliquer pourquoi j'ai cette différence?

**al1_24** · 18/11/2014, 17h24

Les statistiques sont à jour sur les deux bases ?

**olivier]** · 19/11/2014, 08h57

Bonjour.

gather_stats_job tourne en prod tous les soirs et la copie est effectuée après.

J'imagine que ça suffit... me trompe-je?

pour info, la copie se déroule de la manière suivante.
expdp sous system de la prod
drop user xxx cascade (tout le schéma dépend de ce user) dans la copie
impdp sous system dans la copie

**mnitu** · 19/11/2014, 10h10

Vérifiez dans le fichier log de l'export que les statistiques pour les tables en question ont été également exportés.

Sinon faite une trace de l'optimiseur sur les deux environnements et comparez les résultats.

**olivier]** · 19/11/2014, 10h45

J'ai cherche "STAT" dans le log d'export et voici ce que j'ai trouvé.

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
 
ine 42: Traitement du type d'objet DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
	Line 42: Traitement du type d'objet DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
	Line 60: Traitement du type d'objet DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
	Line 60: Traitement du type d'objet DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
	Line 61: Traitement du type d'objet DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
	Line 61: Traitement du type d'objet DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
	Line 73: . . export : "xxx"."INDEX_STATS_SBI"                  46.02 KB     420 lignes
	Line 481: . . export : "SYSMAN"."MGMT_PURGE_POLICY_TARGET_STATE"   8.226 KB      36 lignes
	Line 507: . . export : "SYSTEM"."IDXNOSTAT"                        5.664 KB      38 lignes
	Line 512: . . export : "SYSTEM"."REPCAT$_TEMPLATE_STATUS"          5.304 KB       3 lignes
	Line 659: . . export : "SYSMAN"."MGMT_BLACKOUT_STATE"                  0 KB       0 lignes
	Line 742: . . export : "SYSMAN"."MGMT_JOB_EMD_STATUS_QUEUE"            0 KB       0 lignes
	Line 840: . . export : "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL"          0 KB       0 lignes
	Line 842: . . export : "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS"        0 KB       0 lignes

Au moment de l'import, les stats ne sont pas recalculées automatiquement?

Pour ce qui est de tracer l'optimiseur, pouvez-vous m'expliquer comment faire?

Mais au regard de votre question, j'ai peur d'avoir mal précisé mon problème. C'est dans la base source que l'optimiseur n'utilise pas les index, pas dans la base copiée.

**Mohamed.Houri** · 19/11/2014, 21h23

Envoyé par olivier]

J'ai cherche "STAT" dans le log d'export et voici ce que j'ai trouvé.

Mais au regard de votre question, j'ai peur d'avoir mal précisé mon problème. C'est dans la base source que l'optimiseur n'utilise pas les index, pas dans la base copiée.

C'est normal que dans votre base source le CBO n'utlise pas les indexes. Il a opté pour des HASH JOIN et dans ce cas il est impossible de faire un accès via index (range/unique) scan de la probe table (la deuxième table) en utilisant la clé de jointure provenant de la build table (la première table). C'est ainsi car les HASH JOIN sont des opérations appelées "Unrelated Combine operations" qui veut dire que le "data set" provenant de la table conductrice est indépendant du data set de la deuxième table. Tout ce que vous pouvez espérer dans ce cas c'est un index full scan (ou un index fast full scan) mais dans ce cas je préfère un full table scan. Il est néanmoins possible que la probe table(d'une HASH JOIN) soit visitée via un index range/unique scan lorsque vous ajoutez une clause where sur la colonne de jointure en la comparant à une valeur constante (même transmise en bind variable). Ce qui n'est pas votre cas ici

Code :

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

1
2
3
4
 
        inner join m on m.code_s = s.code_s
	inner join a on a.code_m = m.code_m
	inner join t on t.code_a =a.code_a;

Ce que je viens d'expliquer est la caractéristique principale qui différencie les opérations HASH JOIN des opérations NESTED LOOP où la outer table (première table dans la nested loop) conduit et impose le nombre de fois la inner table est executée. Dans le cas des Nested Loop, il est tout a fait possible de visiter la deuxième table via un index lookup en utilisant la clé de jointure comme dans votre cas ici:

Code :

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

1
2
3
4
 
|* 10 |        INDEX RANGE SCAN          | I_FK_M0 
 
 10 - access("A"."CODE_M"="M"."CODE_M")

Ceci dit je constate que c'est votre plan avec le NESTED LOOP qui risque de vous causer le plus de soucis car les statistiques n'étant pas à jour, et si l'opération HASH JOIN n°6 génère réellement 28191 lignes alors votre opération n°9 TABLE ACCESS BY INDEX ROWID va être exécutée 28191 fois alors que votre opération n°11 que vous semblez tant désirer à savoir INDEX RANGE SCAN (I_FK_A) va être exécutée 235 milles fois.

Si vous voulez comprendre les HASH JOIN et les NESTED LOOP je vous invite à lire mon article publié chez Dell Softwares

http://www.toadworld.com/platforms/o...ex-lookup.aspx

Bien Respectueusement
Mohamed Houri

**olivier]** · 20/11/2014, 10h54

Je n'ai pas absolument tout compris dans votre article, loin s'en faut, notamment à cause de mon anglais.
Pour autant, vous avez raison, l'exécution de la requête dure 5 min sur la base source contre 25 minutes sur la base copiée.
Plus je pousse dans mes analyses plus je vois que c'est clairement une question de stat.

Par contre, pourquoi est-ce plus long sur la base copiée alors que l'explain plan semble dire le contraire?

**Mohamed.Houri** · 20/11/2014, 11h21

L'information Time dans le plan d’exécution peut ne pas refléter le temps réel pris par la requête. Même le A-Time, que vous auriez obtenu si vous aviez utilisé dbms_xplan.display_cursor (sql_id, child_number, 'allstats last')) précédé évidement d'un alter session set statistics_level=all (ou hint /*+ gather_plan_statistics */), peut lui aussi parfois "mentir"

Bien à vous
Mohamed Houri

**olivier]** · 20/11/2014, 11h22

ma question est idiote ... l'explain plan se base sur les stats et elles sont fausses.
Donc post résolu c'était une question de stat.

Merci à tous.

**olivier]** · 20/11/2014, 11h26

Désolé, nous avons répondu en même temps.
Merci pour le détail des commandes.

Ecart sur explain plan

Oracle

Discussions similaires

Partager

Partager