Deux plans d'exécution différents pour la même requete

Version imprimable

Bonjour,
Pour une requete, j'obtiens 2 plan d'exécutions différents.

Version Oracle : 11.2.0.3.0

requete en question :

Code:

1
2
3
4
 
SELECT /*+ INDEX_FFS(CUST1_BLCKLST_IMF_AMC_TBL_SA CUST_BLCKLST_IMF_AMC_TBL)  */  COUNT (*), SUM (OUTSTANDING_AMT) 
FROM
 CUSTOM.CUST_BLCKLST_IMF_AMC_TBL WHERE TRIM(NAT_ID) = :B1

Je vous informe que l'index crée est un index de fonction :

Code:

1
2
CREATE INDEX CUSTOM.CUST1_BLCKLST_IMF_AMC_TBL_SA ON CUSTOM.CUST_BLCKLST_IMF_AMC_TBL
(TRIM("NAT_ID"))

Le plan affiché au niveau du TKPROF est le suivant :

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
 
SQL ID: 9dsvg3x0sjw37 Plan Hash: 2981321476
 
SELECT /*+ INDEX_FFS(CUST1_BLCKLST_IMF_AMC_TBL_SA CUST_BLCKLST_IMF_AMC_TBL) 
  */ COUNT (*), SUM (OUTSTANDING_AMT) 
FROM
 CUSTOM.CUST_BLCKLST_IMF_AMC_TBL WHERE TRIM(NAT_ID) = :B1 
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    117      0.01       0.01          0          0          0           0
Fetch      117     70.64      84.00    2663238    2674375        116         116
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      235     70.65      84.01    2663238    2674375        116         116
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 143     (recursive depth: 1)
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=23042 pr=22946 pw=0 time=735226 us)
         1          1          1   TABLE ACCESS FULL CUST_BLCKLST_IMF_AMC_TBL (cr=23042 pr=22946 pw=0 time=735211 us cost=4458 size=119757 card=10887)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path read                            27383        0.03         12.78
********************************************************************************

Alors au niverau SQLPLUS, et quand je génére le plan d'exécution pour la même requete, j'obtiens un autre plan qui utilise l'index sur la table :

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
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8g7hwyturg642, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   COUNT (*), SUM (OUTSTANDING_AMT)
FROM  CUSTOM.CUST_BLCKLST_IMF_AMC_TBL WHERE TRIM(NAT_ID) = 'F739736'
 
Plan hash value: 2986778378
 
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                              |      1 |        |      1 |00:00:00.01 |       5 |      5 |
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |                              |      1 |      1 |      1 |00:00:00.01 |       5 |      5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUST_BLCKLST_IMF_AMC_TBL     |      1 |      1 |      2 |00:00:00.01 |       5 |      5 |
|*  3 |    INDEX RANGE SCAN          | CUST1_BLCKLST_IMF_AMC_TBL_SA |      1 |      1 |      2 |00:00:00.01 |       3 |      3 |
--------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CUST_BLCKLST_IMF_AMC_TBL"."SYS_NC00022$"='F739736')

30/09/2015, 14h27
mnitu
Vous allez beaucoup souffrir si vous continuez à comparer des requêtes utilisant des variables de liaison
Code:

1 2 WHERE TRIM(NAT_ID) = :B1
avec des autres qui utilisent les valeurs en dur
Code:

1 2 WHERE TRIM(NAT_ID) = 'F739736'
Pour la première catégorie lors du hard parsing de la requête la valeur contenu dans la variable b1 détermine le plan d'exécution via le mécanisme d'introspection utilisé par l'optimiseur. Donc la valeur utilisée lors de l'optimisation n'est pas la même que celle que vous utilisez en sqlplus et cela change le plan comme vous pouvez le constater.
30/09/2015, 16h02
ora_home

OUI tout à fait d'accord. Alors comment peut on justifier que dans la première requête, l'optimiseur n'as pas choisi le parcours de l'index, sachons que dans le première cas, la valeur de la variable :B1 est surement une valeur indéxés.
30/09/2015, 16h54
pachot

Bonjour,
Si le but du hint est de forcer l'index, la syntaxe n'est pas bonne. INDEX_FFS c'est pour du Fast Full Scan et non du range scan, et les arguments (nom alias, nom index) sont inversés.
Cordialement,
Franck.
30/09/2015, 17h12
ora_home

Bonjour Frank, oui effectivement le hint INDEX_FFS c'est pour utiliser le parcour de l'index en mode FAST FULL SCAN, vu qu'avant j'avais un : INDEX FULL SCAN qui est plus coûteux que Index FFS.

Mon souci c'est que malgré les hints, au niveau du fichier TKPROF on a toujours un TABLE FULL SCAN !!!! Alors que normalement meme si on élimine le HINT on doit y avoir l'utilisation de l'index d'une maniére ou d'une autre (RANG SCAN, FFS, Index FFS,...).

c'est le problème que je viens pas de comprendre.
01/10/2015, 09h32
mnitu
Quelques pistes:
1. pour que le hint sql soit utilisé il faut employer la bonne syntaxe. Assurez vous que c'est le cas en utilisant des variables de liaison dans votre requête.
2. un index n'est pas utilisé quand l'optimiseur considère que son coût est supérieur aux autres alternatives. Tracez l'optimiseur pour y voir plus claire.
3. assurez vous que la session qui utilise la trace sql est paramétrée de la même manière que celle où vous analysez votre requête.
4. une fois "full table scan" une autre "index" c'est le signe typique des requêtes avec des variables de liaison pour lesquelles des histogrammes existent parce que les valeurs ne sont pas distribuées d'une façon homogène. Supprimez la histogramme et probablement que vous n'aurez plus besoin d'employer le hint.
03/10/2015, 18h24
dell68

Deux plans d'exécution différents pour la même requete

bonjour,
Afin de mieux analyser la requête bindés, je te suggère que tu l' éxécute comme suivant :
1- select /*+ use_index(alias..) gather_plan_statistics */ from ... , ensuite
select * from table(dbms_xplan.display_cursor (sql_id=>' xxxxx',format=>'allstats last')) ; tu obtiendra un plan plus lisible à analyser !
2- select /*+ gather_plan_statistics */ from ... , ensuite encore
select * from table(dbms_xplan.display_cursor (sql_id=>' yyyyyyy',format=>'allstats last')) ; tu obtiendra un second plan plus lisible à analyser !

cordialement !