La requête s'exécute plus lentement après la création des index et le calcul de dbms_stats est utilisé

J'ai une table avec 1,5 million de lignes. Je exécutez une requête qui récupère les dossiers de non répétition de valeurs dans une colonne. J'observe un comportement dans lequel, après la création d'index, les performances de la requête se dégrade. J'ai aussi utilisé dbms_stats avec 100% estimation du pourcentage(mode de calcul)
pour rassembler des données statistiques, de sorte que oracle 11g CBO rend une décision plus éclairée pour le plan de requête, mais il n'est pas d'améliorer le temps d'exécution requête.

 SQL> desc tab3;
 Name                 Null?    Type
 ----------------------------------------------
 COL1                          NUMBER(38)
 COL2                          VARCHAR2(100)
 COL3                          VARCHAR2(36)
 COL4                          VARCHAR2(36)
 COL5                          VARCHAR2(4000)
 COL6                          VARCHAR2(4000)
 MEASURE_0                     VARCHAR2(4000)
 MEASURE_1                     VARCHAR2(4000)
 MEASURE_2                     VARCHAR2(4000)
 MEASURE_3                     VARCHAR2(4000)
 MEASURE_4                     VARCHAR2(4000)
 MEASURE_5                     VARCHAR2(4000)
 MEASURE_6                     VARCHAR2(4000)
 MEASURE_7                     VARCHAR2(4000)
 MEASURE_8                     VARCHAR2(4000)
 MEASURE_9                     VARCHAR2(4000)

La colonne measure_0 a de 0,4 million de valeurs uniques.

SQL> select count(*) from (select measure_0 from tab3 group by measure_0 having count(*) = 1) abc;

  COUNT(*)
----------
    403664

Voici la requête avec le plan d'exécution, attention, il n'existe pas d'indices sur la table.

SQL> set autotrace traceonly;
SQL> SELECT * FROM (
2     SELECT
3             (ROWNUM -1) AS COL1,
4             ft.COL1         AS OLD_COL1,
5             ft.COL2,
6             ft.COL3,
7             ft.COL4,
8             ft.COL5,
9             ft.COL6,
10             ft.MEASURE_0,
11             ft.MEASURE_1,
12             ft.MEASURE_2,
13             ft.MEASURE_3,
14             ft.MEASURE_4,
15             ft.MEASURE_5,
16             ft.MEASURE_6,
17             ft.MEASURE_7,
18             ft.MEASURE_8,
19             ft.MEASURE_9
20     FROM tab3 ft
21     WHERE MEASURE_0 IN
22      (
23             SELECT MEASURE_0
24             FROM tab3
25             GROUP BY MEASURE_0
26             HAVING COUNT(*) = 1
27      )
28  ) ABC WHERE COL1 >= 0 AND COL1 <=449;
450 rows selected.
Elapsed: 00:00:01.90
Execution Plan
----------------------------------------------------------
Plan hash value: 3115757351
------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |  1243 |    28M|   717K  (1)| 02:23:29 |
|*  1 |  VIEW                   |          |  1243 |    28M|   717K  (1)| 02:23:29 |
|   2 |   COUNT                 |          |       |       |            |          |
|*  3 |    HASH JOIN            |          |  1243 |    30M|   717K  (1)| 02:23:29 |
|   4 |     VIEW                | VW_NSO_1 |  1686K|  3219M|  6274   (2)| 00:01:16 |
|*  5 |      FILTER             |          |       |       |            |          |
|   6 |       HASH GROUP BY     |          |     1 |  3219M|  6274   (2)| 00:01:16 |
|   7 |        TABLE ACCESS FULL| TAB3     |  1686K|  3219M|  6196   (1)| 00:01:15 |
|   8 |     TABLE ACCESS FULL   | TAB3     |  1686K|    37G|  6211   (1)| 00:01:15 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1">=0 AND "COL1"<=449)
3 - access("MEASURE_0"="MEASURE_0")
5 - filter(COUNT(*)=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
354  recursive calls
0  db block gets
46518  consistent gets
45122  physical reads
0  redo size
43972  bytes sent via SQL*Net to client
715  bytes received via SQL*Net from client
31  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
450  rows processed

La requête prend 1.90 secondes. Si j'exécute la requête à nouveau, il prend 1.66 secondes. Pourquoi a-t-il plus de temps lors de la première manche?

Afin de l'accélérer, j'ai créé des index sur les deux colonnes utilisées dans la requête.

SQL> create index ind_tab3_orgid on tab3(COL1);
Index created.
Elapsed: 00:00:01.68
SQL> create index ind_tab3_msr_0 on tab3(measure_0);
Index created.
Elapsed: 00:00:01.83

Quand j'ai tiré la requête après avoir pour la première fois, il a eu une coqueluche 21 secondes pour revenir. Attendu qu'à la suite fonctionne acheté pour 2.9 secondes. Pourquoi oracle de prendre autant de temps dans la première manche, c'est l'échauffement ou quelque chose.. me déroute!

C'est le plan lorsqu'il prend en 2,9 secondes-

450 rows selected.
Elapsed: 00:00:02.92
Execution Plan
----------------------------------------------------------
Plan hash value: 240271480
-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |  1243 |    28M|   711K  (1)| 02:22:15 |
|*  1 |  VIEW                          |                |  1243 |    28M|   711K  (1)| 02:22:15 |
|   2 |   COUNT                        |                |       |       |            |          |
|   3 |    NESTED LOOPS                |                |       |       |            |          |
|   4 |     NESTED LOOPS               |                |  1243 |    30M|   711K  (1)| 02:22:15 |
|   5 |      VIEW                      | VW_NSO_1       |  1686K|  3219M|  6274   (2)| 00:01:16 |
|*  6 |       FILTER                   |                |       |       |            |          |
|   7 |        HASH GROUP BY           |                |     1 |  3219M|  6274   (2)| 00:01:16 |
|   8 |         TABLE ACCESS FULL      | TAB3           |  1686K|  3219M|  6196   (1)| 00:01:15 |
|*  9 |      INDEX RANGE SCAN          | IND_TAB3_MSR_0 |  1243 |       |     2   (0)| 00:00:01 |
|  10 |     TABLE ACCESS BY INDEX ROWID| TAB3           |  1243 |    28M|    44   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1">=0 AND "COL1"<=449)
6 - filter(COUNT(*)=1)
9 - access("MEASURE_0"="MEASURE_0")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
660054  consistent gets
22561  physical reads
0  redo size
44358  bytes sent via SQL*Net to client
715  bytes received via SQL*Net from client
31  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
450  rows processed

Je m'attendais à le temps d'être plus faible que lorsque la table n'était pas indexé. Pourquoi la version indexée de la table prend plus de temps pour aller chercher les résultats que les non indexées version? Si je ne me trompe pas il s'agit de la TABLE ACCESS BY INDEX ROWID qui prend du temps. Puis-je appliquer oracle d'utiliser une TABLE ACCESS FULL?

J'ai ensuite rassemblé les statistiques sur la table afin que le CBO améliore le plan de calculer l'option. Alors maintenant, les statistiques seraient exactes.

SQL> EXECUTE dbms_stats.gather_table_stats (ownname=>'EQUBE67DP', tabname=>'TAB3',estimate_percent=>null,cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:01:02.47
SQL> set autotrace off;
SQL> select COLUMN_NAME,NUM_DISTINCT,SAMPLE_SIZE,HISTOGRAM,LAST_ANALYZED from dba_tab_cols where table_name = 'TAB3' ;
COLUMN_NAME                    NUM_DISTINCT SAMPLE_SIZE HISTOGRAM       LAST_ANALYZED
------------------------------ ------------ ----------- --------------- ---------
COL1                                1502257     1502257 NONE            27-JUN-12
COL2                                      0             NONE            27-JUN-12
COL3                                      1     1502257 NONE            27-JUN-12
COL4                                      0             NONE            27-JUN-12
COL5                                1502257     1502257 NONE            27-JUN-12
COL6                                1502257     1502257 NONE            27-JUN-12
MEASURE_0                            405609     1502257 HEIGHT BALANCED 27-JUN-12
MEASURE_1                            128570     1502257 NONE            27-JUN-12
MEASURE_2                           1502257     1502257 NONE            27-JUN-12
MEASURE_3                            185657     1502257 NONE            27-JUN-12
MEASURE_4                               901     1502257 NONE            27-JUN-12
MEASURE_5                                17     1502257 NONE            27-JUN-12
MEASURE_6                              2202     1502257 NONE            27-JUN-12
MEASURE_7                              2193     1502257 NONE            27-JUN-12
MEASURE_8                                21     1502257 NONE            27-JUN-12
MEASURE_9                             27263     1502257 NONE            27-JUN-12

J'ai de nouveau l'exécution de la requête

450 rows selected.
Elapsed: 00:00:02.95
Execution Plan
----------------------------------------------------------
Plan hash value: 240271480
-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |    31M|   718G|  8046   (2)| 00:01:37 |
|*  1 |  VIEW                          |                |    31M|   718G|  8046   (2)| 00:01:37 |
|   2 |   COUNT                        |                |       |       |            |          |
|   3 |    NESTED LOOPS                |                |       |       |            |          |
|   4 |     NESTED LOOPS               |                |    31M|    62G|  8046   (2)| 00:01:37 |
|   5 |      VIEW                      | VW_NSO_1       |  4057 |  7931K|  6263   (2)| 00:01:16 |
|*  6 |       FILTER                   |                |       |       |            |          |
|   7 |        HASH GROUP BY           |                |     1 | 20285 |  6263   (2)| 00:01:16 |
|   8 |         TABLE ACCESS FULL      | TAB3           |  1502K|  7335K|  6193   (1)| 00:01:15 |
|*  9 |      INDEX RANGE SCAN          | IND_TAB3_MSR_0 |     4 |       |     2   (0)| 00:00:01 |
|  10 |     TABLE ACCESS BY INDEX ROWID| TAB3           |   779K|    75M|     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1">=0 AND "COL1"<=449)
6 - filter(COUNT(*)=1)
9 - access("MEASURE_0"="MEASURE_0")
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
660054  consistent gets
22561  physical reads
0  redo size
44358  bytes sent via SQL*Net to client
715  bytes received via SQL*Net from client
31  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
450  rows processed

Cette fois, la requête est revenue dans la 2.9 secondes (parfois il a fallu 3.9 secondes trop).

Mon objectif est de minimiser le temps d'exécution requête, autant que possible. Mais après l'ajout des index ou après le calcul des statistiques de la requête juste continué à augmenter. Pourquoi est-ce qui se passe et comment puis-je améliorer, même en gardant l'index?

source d'informationauteur rirhs