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
Vous devez vous connecter pour publier un commentaire.
Tout d'abord, permettez-moi de citer Tom Kyte:
L'index des pas toujours améliorer les performances, ils ne sont pas magiques balle en argent (si une telle chose n'a jamais existé 🙂
Vous êtes maintenant en demandant pourquoi il faut plus de temps avec votre index. La réponse est assez simple:
En d'autres termes: Oracle effectuer plus d'opérations de lecture avec votre index avec la table d'analyse. Cela se produit parce que:
Pourquoi l'optimiseur a choisi d'utiliser à cette évidence de la non-index efficace, c'est parce que même avec
esimate_percent=100
et plein d'histogrammes (que vous avez recueillies sur leMEASURE_0
colonne), certaines données de distribution ne peut toujours pas être fiable exprimé par la simple analyse de l'optimiseur. En particulier, la croix-colonne et de la croix-table de dépendance n'est pas bien comprise par l'analyseur. Cela conduit à de mauvaises estimations, qui conduisent à une mauvaise plan de choix.Edit: il semble que l'hypothèse de travail de la communauté de base ne sont pas de travail à toutes et tous pour cette auto-jointure (votre dernière requête s'attend à 31 millions de lignes, alors que seulement 450 sont choisis !). C'est assez déroutant car la table a seulement 1,5 M de lignes. Quelle version d'Oracle utilisez-vous?
Je pense que vous trouverez que vous pouvez retirer de l'auto-jointure, et donc améliorer les performances de la requête avec google analytics:
Vous avez également de se demander pourquoi la première fois qu'une requête est exécutée prend plus de temps en général. C'est parce qu'il y a des caches au travail. Sur la base de données au niveau de la SGA où tous les blocs sont d'abord copiées à partir du disque et puis peut être lu plusieurs fois (la première fois qu'un bloc est interrogé est toujours une lecture physique). Ensuite, certains systèmes ont un système indépendant de cache qui va renvoyer les données plus rapidement que s'il a été lu récemment.
Pour davantage de lecture:
Comment fonctionne ce code?