Discussion :

[couak]

Bonjour,

J'ai un problème bizarre avec un progiciel sur une requête qui utilise une bind variable. La requête est en elle-même assez simple, sur une table de 1,7 millions de lignes :

Code :

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

1
2
SELECT A.DOC_ID,A.STAMP_UID,A.STAMP_DATE,A.LOG_INDEX,A.LOG_COMMENT
FROM ACTION_LOG A WHERE (( A.DOC_ID = :PAR_FILTER0_0 ));

Par contre, malgré des stats à jour et la présence d'un index sur DOC_ID le plan d'exécution est catastrophique :

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
select * from table(dbms_xplan.display_cursor('d4mkvkrz4q58s',null,'ADVANCED'))
 
SQL_ID  d4mkvkrz4q58s, child number 1
-------------------------------------
SELECT A.DOC_ID,A.STAMP_UID,A.STAMP_DATE,A.LOG_INDEX,A.LOG_COMMENT
  )A.DOC_ID = :PAR_FILTER0_0 )
 
Plan hash value: 367639580
 
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |  5138 (100)|          |
|*  1 |  TABLE ACCESS FULL| ACTION_LOG |   189K|    15M|  5138   (1)| 00:01:02 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("A"."DOC_ID"=:PAR_FILTER0_0)

Pour invalider le plan d'exécution, je fais une opération de type DDL sur la table (REVOKE ou GRANT) et là je me retrouve avec un plan d'exécution correcte sur ce SQL ID :

Code :

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

1
2
3
4
5
6
7
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |       |       |     9 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTION_LOG        |    16 |  1392 |     9   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ACTION_LOG_DOC_ID |    16 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Problème : au bout d'un certain temps (genre le lendemain), il me refait le coup du plan d'exécution foireux. C'est particulièrement gênant car ca sollicite le disque, et le progiciel n'arrête pas d'utiliser cette requête qui met 1 min à s'exécuter. Existe-t-il une façon de forcer le plan d'exécution mis à part le hint (car je n'ai pas la main sur le progiciel) ?

Mon environnement :
- Windows 2003 Server
- Oracle 10.2.0.4 32 bits, Enterprise Edition

[13thFloor]

Bonjour,
qu'est-ce qui te dit que le plan est foireux ?
Si la valeur bindée est très présente dans la table, l'optimiseur estime, à juste titre, qu'il est plus avantageux de lire complètement la table.
As-tu un histogramme sur cette colonne ?
Comment sont réparties les différentes valeurs ?

[farenheiit]

Quel est le résultat de la requête suivante:

Code :

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

1
2
3
4
5
6
 
select DOC_ID, count(1)
from 
ACTION_LOG
group by DOC_ID
order by 2 desc

Combien de lignes y'a t'il dans ta table ACTION_LOG ?
Y'a t'il bien environ 189 000 lignes dans ta table qui ont un DOC_ID testé par le progiciel ?

[couak]

Quel est le résultat de la requête suivante:

Code :

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

1
2
3
4
5
6
 
select DOC_ID, count(1)
from 
ACTION_LOG
group by DOC_ID
order by 2 desc

Combien de lignes y'a t'il dans ta table ACTION_LOG ?
Y'a t'il bien environ 189 000 lignes dans ta table qui ont un DOC_ID testé par le progiciel ?

1,7 millions de lignes
les stats sont mises à jour toutes les nuits, avec un échantillon à 100%

Code :

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

1
2
3
4
5
SQL> select num_rows from user_tables where table_name='ACTION_LOG';
 
  NUM_ROWS
----------
   1784853

le résultat de ta requête me donne 102 727

[farenheiit]

102727 ? pour quel DOC_ID ?
quelle est la valeur du DOC_ID que tu testes quand ta requête met une minute ?

le mieux pour comparer les cardinalités estimées et les cardinalités réelles c'est de faire la chose suivante:

Code :

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

1
2
3
4
5
6
7
 
alter session set statistics_level=all;
 
SELECT A.DOC_ID,A.STAMP_UID,A.STAMP_DATE,A.LOG_INDEX,A.LOG_COMMENT
FROM ACTION_LOG A WHERE (( A.DOC_ID = :PAR_FILTER0_0 ));
 
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last'));

remplace la variable bindée par la valeur du DOC_ID que tu veux tester.

Ensuite on pourra comparer les colonnes E-rows et A-rows.

[13thFloor]

Un histogramme réglera sans doute le problème :

Code :

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

 exec dbms_stats.gather_table_stats('SCHEMA','ACTION_LOG',estimate_percent=>100,method_opt=>'for method_opt=>'for columns DOC_ID size auto');

[couak]

102727 ? pour quel DOC_ID ?
quelle est la valeur du DOC_ID que tu testes quand ta requête met une minute ?

102727 c'est le nbre de lignes retournées en fait j'ai entre 1 à 30 DOC_ID identiques dans cette table de 1,7 millions de lignes pour un nbre total de 102727 DOC_ID distinct

pour la cardinalité :

Code :

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

1
2
3
4
5
6
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTION_LOG        |      1 |     16 |     19 |00:00:00.01 |      10 |
|*  2 |   INDEX RANGE SCAN          | ACTION_LOG_DOC_ID |      1 |     16 |     19 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------

le problème c'est que maintenant qu'il utilise l'index ca m'a l'air tout bon... ce qui me gêne c'est que demain je vais sûrement me retrouver avec un plan d'exécution qui fait un full scan sur une table de 1,7 millions de lignes

[couak]

Bonjour,
qu'est-ce qui te dit que le plan est foireux ?
Si la valeur bindée est très présente dans la table, l'optimiseur estime, à juste titre, qu'il est plus avantageux de lire complètement la table.
As-tu un histogramme sur cette colonne ?
Comment sont réparties les différentes valeurs ?

Foireux car quand il fait son FULL TABLE SCAN ca prend 1 min, et en utilisant l'index ca prend 1 sec. J'ai énormément d'I/O disque quand il fait le full scan

Les différentes valeurs sont répartie de la sorte : il y a entre 1 à 30 fois la même valeur