L'optimiseur d'Oracle fait des mauvais choix ?

**pachot** · 27/08/2013, 22h05

Code :

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

1
2
3
4
-----------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   3 |    TABLE ACCESS FULL| DWH_STATS_TICKETS |  2936K|    11M| 11465   (1)| 00:02:18 |

Le coût du FULL TABLE SCAN est très surprenant.

Il est normalement calculé comme le nombre de blocs à lire, divisé par le nombre de blocs lus par u i/o et multiplié par le ratio durée d'i/o multibloc par rapport à i/o single bloc.

Si on a une taille de bloc de 8k, vu qu'on a 11MB de données, on aurait 1408 blocs. Ce qui avec les valeurs par défaut ( MBRC=8 lecture 8k=12ms lecture 8*8k=26ms) fait un coût de 413. Et ici on a 11465

Ce serait intéressant de voir les stats système:

Code :

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

select * from sys.aux_stats$;

**Mohamed.Houri** · 28/08/2013, 10h46

@skuatamad

Merci pour l'idée du nls_sort. Dans ce qui suit j'ai utilisé Oracle 12c

En nsl_sort=BINARY j'ai deux plans d'exécution : plan hash values 2111031280 et 755059269

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
 
Pour un optimizer_index_cost_adj <= 20 
 
Plan hash value: 2111031280                                                                                                                                                                             
 
-----------------------------------------------------------------------------                                                                                                                           
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                           
-----------------------------------------------------------------------------                                                                                                                           
|   0 | SELECT STATEMENT     |      |       |       |    82 (100)|          |                                                                                                                           
|   1 |  SORT GROUP BY NOSORT|      |     3 |     9 |    82   (0)| 00:00:01 |                                                                                                                           
|   2 |   INDEX FULL SCAN    | T_PK |  2000K|  5859K|    82   (0)| 00:00:01 | --> optimizer_index_cost_adj <= 20                                                                                                                          
-----------------------------------------------------------------------------
 
Pour un optimizer_index_cost_adj >= 30
 
Plan hash value: 755059269                                                                                                                                                                              
 
------------------------------------------------------------------------------                                                                                                                          
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                          
------------------------------------------------------------------------------                                                                                                                          
|   0 | SELECT STATEMENT      |      |       |       |  2282 (100)|          |                                                                                                                          
|   1 |  SORT GROUP BY        |      |     3 |     9 |  2282   (3)| 00:00:01 |                                                                                                                          
|   2 |   INDEX FAST FULL SCAN| T_PK |  2000K|  5859K|  2234   (1)| 00:00:01 |   --> optimizer_index_cost_adj >= 30                                                                                                                       
------------------------------------------------------------------------------

En nsl_sort=FRENCH j'ai deux plans d'exécution : plan hash values 1760210272 et 65194836

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
 
Pour un optimizer_index_cost_adj <= 20 
 
Plan hash value: 1760210272                                                                                                                                                                             
 
--------------------------------------------------------------------------------------                                                                                                                  
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                  
--------------------------------------------------------------------------------------                                                                                                                  
|   0 | SELECT STATEMENT      |      |       |       |       |  5420 (100)|          |                                                                                                                  
|   1 |  SORT ORDER BY        |      |     3 |     9 |    22M|  5420   (1)| 00:00:01 |                                                                                                                  
|   2 |   SORT GROUP BY NOSORT|      |     3 |     9 |       |  5420   (1)| 00:00:01 |                                                                                                                  
|   3 |    INDEX FULL SCAN    | T_PK |  2000K|  5859K|       |    82   (0)| 00:00:01 | -->  optimizer_index_cost_adj <=20                                                                                                               
--------------------------------------------------------------------------------------                                                                                                                  
 
 
Pour un optimizer_index_cost_adj >= 30 
 
Plan hash value: 65194836                                                                                                                                                                               
 
-------------------------------------------------------------------------------                                                                                                                         
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                         
-------------------------------------------------------------------------------                                                                                                                         
|   0 | SELECT STATEMENT       |      |       |       |  2330 (100)|          |                                                                                                                         
|   1 |  SORT ORDER BY         |      |     3 |     9 |  2330   (5)| 00:00:01 |                                                                                                                         
|   2 |   HASH GROUP BY        |      |     3 |     9 |  2330   (5)| 00:00:01 |                                                                                                                         
|   3 |    INDEX FAST FULL SCAN| T_PK |  2000K|  5859K|  2234   (1)| 00:00:01 |     -->  optimizer_index_cost_adj>=30                                                                                                                    
-------------------------------------------------------------------------------

Il est donc très intéressant de savoir que la valeur de l'optimizer_index_cost_adj influe également sur le choix du type d'accès à un index (ffs ou fs).
Au passage avez vous remarqué cette double opération

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
Plan hash value: 1760210272                                                                                                                                                                             
 
--------------------------------------------------------------------------------------                                                                                                                  
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                  
--------------------------------------------------------------------------------------                                                                                                                  
|   0 | SELECT STATEMENT      |      |       |       |       |  5420 (100)|          |                                                                                                                  
|   1 |  SORT ORDER BY        |      |     3 |     9 |    22M|  5420   (1)| 00:00:01 |                                                                                                                  
|   2 |   SORT GROUP BY NOSORT|      |     3 |     9 |       |  5420   (1)| 00:00:01 |                                                                                                                  
|   3 |    INDEX FULL SCAN    | T_PK |  2000K|  5859K|       |    82   (0)| 00:00:01 | -->  optimizer_index_cost_adj <=20                                                                                                               
--------------------------------------------------------------------------------------

SORT GROUP BY NOSORT suivie d'un SORT ORDER BY. Si l'opération 2 a évité un ORDER BY grâce à l'opération 3, il sert alors à quoi de faire
une opération supplémentaire SORT ORDER BY (opération 1)?

**skuatamad** · 28/08/2013, 11h39

Envoyé par Mohamed.Houri

Au passage avez vous remarqué cette double opération SORT GROUP BY NOSORT suivie d'un SORT ORDER BY. Si l'opération 2 a évité un ORDER BY grâce à l'opération 3, il sert alors à quoi de faire
une opération supplémentaire SORT ORDER BY (opération 1)?

Oui, je suppose qu'il est obligé de refaire un tri en NLS_SORT = FRENCH

En utilisant ADVANCED dans dbms_xplan.display_cursor comme suggéré par pachot on voit bien l'étape de tri supplémentaire :

En french :

Code :

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

1
2
3
4
5
6
7
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) NLSSORT("C1",'nls_sort=''FRENCH''')[522],
       "C1"[VARCHAR2,64]
   2 - "C1"[VARCHAR2,64]
   3 - "C1"[VARCHAR2,64]

En binary :

Code :

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

1
2
3
4
5
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "C1"[VARCHAR2,64]
   2 - "C1"[VARCHAR2,64]

**pacmann** · 28/08/2013, 11h51

Envoyé par Mohamed.Houri

Il est donc très intéressant de savoir que la valeur de l'optimizer_index_cost_adj influe également sur le choix du type d'accès à un index (ffs ou fs).

L'indexffs n'est pas très différent d'un fts, dans la mesure où on ignore totalement la structure logique de l'index lors de la lecture (qui peut se faire en multiblock read), ce qui d'ailleurs implique une tâche supplémentaire de nettoyage de la structure d'index pour extraire les données réelles. En ce sens, l'optimzer_index_cost_adj ne peut s'appliquer à l'indexffs, et favorise donc uniquement le range scan.

Envoyé par Mohamed.Houri

SORT GROUP BY NOSORT suivie d'un SORT ORDER BY. Si l'opération 2 a évité un ORDER BY grâce à l'opération 3, il sert alors à quoi de faire
une opération supplémentaire SORT ORDER BY (opération 1)?

Le SORT GROUP BY NOSORT exploite le tri binaire de l'index pour réaliser un GROUP BY par algorithme de tri. Pour ce besoin précis, les modalités finales du tri attendu ne jouent pas de rôle.
Par contre, le tri supplémentaire doit être effectué pour que la sortie soit conforme aux attentes (NLS_SORT=FRENCH). Ce tri par ailleurs n'augmente pas le coût au vu du nombre restreint de lignes à la sortie de l'opération de GROUP BY.

**tipeh** · 29/08/2013, 12h58

Envoyé par Mohamed.Houri

Vous pouvez injecter un hint sans toucher à l'application. Vous avez un exemple ici et une discussion sur le package SQL_PATCH ici

Très intéressant, merci !

Envoyé par Mohamed.Houri

Le 'OUTLINE' montre également plusieurs colonnes indexées (en plus de la colonne REGISTRE), quels sont tous les indexes sur cette table?

Le seul indexe que j'ai c'est l'index de ma clé primaire. Mais effectivement, c'est un indexe composite qui regroupe plusieurs colonnes (Je me suis trompé sur mon premier poste en disant que ce n'était que deux colonnes).
En gros, ma table de faits contient 9 axes d'analyses et donc une clé primaire de 9 colonnes.

Envoyé par skuatamad

Vos paramètres ressemblent plus à du OLTP en 9i qu'à du DWH en 11G

Mais votre base héberge peut être aussi du OLTP.

Ce que vous dites me parait tout a fait possible : ma base n'héberge pas de l'OLTP. Par contre, elle a été configuré par les DBA de l'entreprise qui ne connaissaient jusqu'a mon arrivé que les bases OLTP. Cela explique peut être leur choix des paramètres !
Pourriez vous me donner des conseils pour les valeurs à donner aux paramètres pour une configuration DWH ?

Envoyé par skuatamad

Par ailleurs l'index et la table sont ils créés dans le même tablespace ?
Et lors de votre test avec l'index bitmap, a-t-il été créé dans le même tablespace que l'index PK ?

Oui tous mes index sont dans le même tablespace que ma table de fait. Aurai-je intérêt à changer ça ?

Envoyé par pachot

Le coût du FULL TABLE SCAN est très surprenant.

Il est normalement calculé comme le nombre de blocs à lire, divisé par le nombre de blocs lus par u i/o et multiplié par le ratio durée d'i/o multibloc par rapport à i/o single bloc.

Si on a une taille de bloc de 8k, vu qu'on a 11MB de données, on aurait 1408 blocs. Ce qui avec les valeurs par défaut ( MBRC=8 lecture 8k=12ms lecture 8*8k=26ms) fait un coût de 413. Et ici on a 11465

Ce serait intéressant de voir les stats système:

Code :

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

select * from sys.aux_stats$;

Ce que vous dites m'a l'air un peu inquiétant. Voici mes stats système :

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
ME                          PNAME                               PVAL1 PVAL2                                                                                                                                                                                                                                                         
------------------------------ ------------------------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED                                                                                                                                                                                                                                                       
SYSSTATS_INFO                  DSTART                                    09-17-2011 10:21                                                                                                                                                                                                                                                
SYSSTATS_INFO                  DSTOP                                     09-17-2011 10:21                                                                                                                                                                                                                                                
SYSSTATS_INFO                  FLAGS                                   1                                                                                                                                                                                                                                                                 
SYSSTATS_MAIN                  CPUSPEEDNW                     1751.758794                                                                                                                                                                                                                                                                 
SYSSTATS_MAIN                  IOSEEKTIM                              10                                                                                                                                                                                                                                                                 
SYSSTATS_MAIN                  IOTFRSPEED                           4096                                                                                                                                                                                                                                                                 
SYSSTATS_MAIN                  SREADTIM                                                                                                                                                                                                                                                                                                  
SYSSTATS_MAIN                  MREADTIM                                                                                                                                                                                                                                                                                                  
SYSSTATS_MAIN                  CPUSPEED                                                                                                                                                                                                                                                                                                  
SYSSTATS_MAIN                  MBRC                                                                                                                                                                                                                                                                                                      
SYSSTATS_MAIN                  MAXTHR                                                                                                                                                                                                                                                                                                    
SYSSTATS_MAIN                  SLAVETHR

Pourriez vous me donner votre analyse ?

**Waldar** · 29/08/2013, 14h08

Envoyé par tipeh

Le seul indexe que j'ai c'est l'index de ma clé primaire. Mais effectivement, c'est un indexe composite qui regroupe plusieurs colonnes (Je me suis trompé sur mon premier poste en disant que ce n'était que deux colonnes).
En gros, ma table de faits contient 9 axes d'analyses et donc une clé primaire de 9 colonnes.

Dans ce cas, oui neuf index bitmap sont beaucoup plus indiqués qu'un seul index !
Vérifier néanmoins la cardinalité de vos neuf colonnes.

Oui tous mes index sont dans le même tablespace que ma table de fait. Aurai-je intérêt à changer ça ?

Oui, non, peut-être

Ça dépend de l'architecture disque qu'il y a derrière.
Si le tablespace pointe sur un seul disque physique, oui il faut impérativement changer cela.
Si le tablespace pointe sur une LUN avec n disques physiques en raid derrière, vous n'aurez pas de gain de performance.
Vous pourriez toutefois en avoir en terme de gestion de tablespace.

**Mohamed.Houri** · 29/08/2013, 21h18

Pacmann,

Envoyé par pacmann

En ce sens, l'optimzer_index_cost_adj ne peut s'appliquer à l'indexffs, et favorise donc uniquement le range scan.

Tu as entièrement raison de dire que le paramètre optimizer_index_cost_adj (ioca) n'intervient pas dans le calcul du coût d'un accès via un index fast full scan (iffs) parce qu'en effet cette opération se fait en lecture multi_blocs c'est à dire soit avec un db file scattered read soit avec un direct path read; ce qui signifie que l'iffs est influencé plutôt par le paramètre db_file_multi_block_read.

Dans notre exemple actuel c'est l'augmentation du coût de lecture par index (index full scan via db file sequential read) qui a fait en sorte que c'est une lecture en multi_bloc qui a été préférée car son coût reste fixe quelque soit la valeur du paramètre ioca comme montré ci-dessous

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
SQL> alter session set optimizer_index_cost_adj = 20;
 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ c1
  2            FROM t
  3           GROUP BY c1
  4           ORDER BY c1 ASC NULLS LAST;
 
--------------------------------------------------------------------------------------                                                                                                                  
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                  
--------------------------------------------------------------------------------------                                                                                                                  
|   0 | SELECT STATEMENT      |      |       |       |       |  6983 (100)|          |                                                                                                                  
|   1 |  SORT ORDER BY        |      |     3 |     9 |    22M|  6983   (1)| 00:00:01 |                                                                                                                  
|   2 |   SORT GROUP BY NOSORT|      |     3 |     9 |       |  6983   (1)| 00:00:01 |                                                                                                                  
|   3 |    INDEX FULL SCAN    | T_PK |  2000K|  5859K|       |  1645   (1)| 00:00:01 |--->  optimizer_index_cost_adj= 20                                                                                                    
--------------------------------------------------------------------------------------                                                                                                                  
 
 
 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS index_ffs(t) */ c1
  2            FROM t
  3           GROUP BY c1
  4           ORDER BY c1 ASC NULLS LAST;                                                                                                                                                                      
 
-------------------------------------------------------------------------------                                                                                                                         
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                         
-------------------------------------------------------------------------------                                                                                                                         
|   0 | SELECT STATEMENT       |      |       |       |  2330 (100)|          |                                                                                                                         
|   1 |  SORT ORDER BY         |      |     3 |     9 |  2330   (5)| 00:00:01 |                                                                                                                         
|   2 |   HASH GROUP BY        |      |     3 |     9 |  2330   (5)| 00:00:01 |                                                                                                                         
|   3 |    INDEX FAST FULL SCAN| T_PK |  2000K|  5859K|  2234   (1)| 00:00:01 |     --->  optimizer_index_cost_adj= 20                                                                                                                    
-------------------------------------------------------------------------------                                                                                                                         
 
 
SQL> alter session set optimizer_index_cost_adj = 100;
 
Session altered.
 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS index(t) */ c1
  2            FROM t
  3           GROUP BY c1
  4           ORDER BY c1 ASC NULLS LAST;
 
 
--------------------------------------------------------------------------------------                                                                                                                  
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                  
--------------------------------------------------------------------------------------                                                                                                                  
|   0 | SELECT STATEMENT      |      |       |       |       | 13563 (100)|          |                                                                                                                  
|   1 |  SORT ORDER BY        |      |     3 |     9 |    22M| 13563   (1)| 00:00:01 |                                                                                                                  
|   2 |   SORT GROUP BY NOSORT|      |     3 |     9 |       | 13563   (1)| 00:00:01 |                                                                                                                  
|   3 |    INDEX FULL SCAN    | T_PK |  2000K|  5859K|       |  8226   (1)| 00:00:01 |    --->  optimizer_index_cost_adj= 100                                                                                                               
--------------------------------------------------------------------------------------                                         
 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ c1
  2            FROM t
  3           GROUP BY c1
  4           ORDER BY c1 ASC NULLS LAST;
 
-------------------------------------------------------------------------------                                                                                                                         
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                         
-------------------------------------------------------------------------------                                                                                                                         
|   0 | SELECT STATEMENT       |      |       |       |  2330 (100)|          |                                                                                                                         
|   1 |  SORT ORDER BY         |      |     3 |     9 |  2330   (5)| 00:00:01 |                                                                                                                         
|   2 |   HASH GROUP BY        |      |     3 |     9 |  2330   (5)| 00:00:01 |                                                                                                                         
|   3 |    INDEX FAST FULL SCAN| T_PK |  2000K|  5859K|  2234   (1)| 00:00:01 |    --->  optimizer_index_cost_adj= 100                                                                                                                  
-------------------------------------------------------------------------------

Le coût d'un accès via iffs est identique, 2234, quelque soit la valeur du paramètre ioca (20 ou 100). Par contre le coût d'un accès via un index full scan (ifs) est de 1645 pour un ioca = 20 et de 8226 pour un ioca = 100.

Par contre, le paramètre ioca influe sur tous les autres accès via index (index full scan, index skip scan et index range scan) et pas seulement sur les accès via index range scan

**pachot** · 30/08/2013, 13h13

Les stats système sont à leur valeur par défaut.
Combien de blocs dans la table ? select * from dba_table where table_name='...'

**tipeh** · 30/08/2013, 13h59

Envoyé par pachot

Les stats système sont à leur valeur par défaut.
Combien de blocs dans la table ? select * from dba_table where table_name='...'

Voila le résultat

Code :

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

1
2
3
OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME                   IOT_NAME                       STATUS     PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS LOGGING BACKED_UP   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE     INSTANCES  CACHE TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTITIONED IOT_TYPE     TEMPORARY SECONDARY NESTED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE ROW_MOVEMENT GLOBAL_STATS USER_STATS DURATION        SKIP_CORRUPT MONITORING CLUSTER_OWNER                  DEPENDENCIES COMPRESSION COMPRESS_FOR DROPPED READ_ONLY SEGMENT_CREATED RESULT_CACHE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------- --------- ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ---------- ---------- ----- ---------- ----------- ------------- ----------- ------------ --------- --------- ------ ----------- ----------- ---------------- ------------ ------------ ---------- --------------- ------------ ---------- ------------------------------ ------------ ----------- ------------ ------- --------- --------------- ------------
DWH                            DWH_STATS_TICKETS              USERS                                                                                        VALID            10                     1        255          65536     1048576           1  2147483645                                         YES     N            2949443      42112            0          0          0         121                         0                   0          1          1     N ENABLED        2949443 29/08/13      NO                       N         N         NO     DEFAULT     DEFAULT     DEFAULT          DISABLED     YES          NO                         DISABLED     YES                                       DISABLED     DISABLED                 NO      NO        YES             DEFAULT

**mnitu** · 30/08/2013, 14h47

Donc le coût calculé est correct. Comme ça a été déjà dit vos problèmes viennent des paramètres optimizer_index_caching et optimizer_index_cost_adj qui devrait garder leurs valeurs par défaut pour votre version d’Oracle

Code :

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

1
2
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100

L'optimiseur d'Oracle fait des mauvais choix ? [11g]

Oracle

Discussions similaires

Partager

Partager