Bonjour tout le monde,
Je continue mon voyage au sein de l'optimiseur Oracle; fascinant mais ardu par moment.
Dernièrement je me suis posé une question toute bête : comment être sur que le CBO utilise bien mes hints?
J'ai lu que si je fais une erreur de syntaxe en utilisant un hint, Oracle n'affiche aucun message d'erreur. OK, mais si la syntaxe est correcte et qu'il l'utilise bien, comment le valider? Facile vous me direz : il suffit de voir le plan d'exécution; on devrait voir les lignes liés au hint PARRALEL, celles sur l'index dont je veux forcer l'utilisation...
OK, mais si je me trompe sur la syntaxe du hint (forcer l'utilisation d'un index par exemple) et que le CBO utilise quand même cet index, je vais être trompé sur la syntaxe du hint.
J'ai donc cherché sur le Net et c'est pas terrible; à croire que je suis le seul à me poser ce genre de question :calim2:
Néanmoins j'ai trouvé deux requêtes : select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); ou select * from table(dbms_xplan.display_cursor(null, null, 'outline')); des plus intéressantes.
Je fais une recherche dans ma table bic_code.
Code:
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
60select /*+ parallel(b,10) */ * from bic_code b where rownum < 100; select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 2ut1kawn1yg75, child number 0 ------------------------------------- select /*+ parallel(b,10) */ * from bic_code b where rownum < 100 Plan hash value: 2632626061 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | |* 1 | COUNT STOPKEY | | | | | | | 2 | TABLE ACCESS FULL| BIC_CODE | 99 | 4554 | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / B@SEL$1 Outline Data PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "B"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<100) Column Projection Information (identified by operation id): ----------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - "B"."BIC_BANK_CODE"[VARCHAR2,8], "B"."BIC_BRANCH_CODE"[VARCHAR2,3], "B"."BIC_NAME"[VARCHAR2,35], "B"."IC_BIC_CAC_TO_CAC_AUTH"[VARCHAR2,3], "B"."IC_BIC_TYPE"[VARCHAR2,3], "B"."BIC_END_DATE"[DATE,7] 2 - "B"."BIC_BANK_CODE"[VARCHAR2,8], "B"."BIC_BRANCH_CODE"[VARCHAR2,3], "B"."BIC_NAME"[VARCHAR2,35], "B"."IC_BIC_CAC_TO_CAC_AUTH"[VARCHAR2,3], "B"."IC_BIC_TYPE"[VARCHAR2,3], "B"."BIC_END_DATE"[DATE,7] 51 rows selected.
Je ne vois aucune trace dans le plan d'exécution de parallélisme :aie:
Et, f**k, je vois une ligne "IGNORE_OPTIM_EMBEDDED_HINTS" qui ne me plait ABSOLUMENT PAS car il y a IGNORE et HINT.
Sur le net (mais pas sur le site de Oracle malheureusement) je lis "IGNORE_OPTIM_EMBEDDED_HINTS : this special hint instructs the CBO to ignore most of all the other supplied hints (there are some exceptions to this) except the ones that are flanked by BEGIN_OUTLINE_DATA and END_OUTLINE_DATA (= outline hints)".
Euh, est-ce que ça veut dire que ce paramètre empêche l'utilisation des hints sur ma base de données?
Pourquoi sur tous les sites parlant de hints il n'est jamais mentionné?
La commande "show parameter IGNORE_OPTIM_EMBEDDED_HINTS" ne renvoit rien donc où est géré ce paramètre?
Autre test car ça m'énerve bien cette histoire : j'utilise le vieux hint RULE et là je vois qu'il est utilisé car la ligne RBO remplace la ligne ALL_ROWS :aie: (est-ce une des exceptions sus-mentionnée?).
Autre preuve : une note parle du RBO "Note rule based optimizer used (consider using cbo)".
Code:
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
65select /*+ RULE */ * from bic_code b where rownum < 100; select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 392rr3jh2qvgn, child number 0 ------------------------------------- select /*+ RULE */ * from bic_code b where rownum < 100 Plan hash value: 2632626061 --------------------------------------- | Id | Operation | Name | --------------------------------------- | 0 | SELECT STATEMENT | | |* 1 | COUNT STOPKEY | | | 2 | TABLE ACCESS FULL| BIC_CODE | --------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / B@SEL$1 Outline Data PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') RBO_OUTLINE OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "B"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<100) Column Projection Information (identified by operation id): ----------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - "B"."BIC_BANK_CODE"[VARCHAR2,8], "B"."BIC_BRANCH_CODE"[VARCHAR2,3], "B"."BIC_NAME"[VARCHAR2,35], "B"."IC_BIC_CAC_TO_CAC_AUTH"[VARCHAR2,3], "B"."IC_BIC_TYPE"[VARCHAR2,3], "B"."BIC_END_DATE"[DATE,7] 2 - "B"."BIC_BANK_CODE"[VARCHAR2,8], "B"."BIC_BRANCH_CODE"[VARCHAR2,3], "B"."BIC_NAME"[VARCHAR2,35], "B"."IC_BIC_CAC_TO_CAC_AUTH"[VARCHAR2,3], "B"."IC_BIC_TYPE"[VARCHAR2,3], "B"."BIC_END_DATE"[DATE,7] Note ----- - rule based optimizer used (consider using cbo)
Si quelqu'un pouvait me faire une petite synthèse sur cette histoire, je lui serais bien reconnaissant.
[EDIT 20/04/2016 14:16]
Ah, je poursuis mes tests et je suis GRANDEMENT rassuré par ce que je vois 8-)
Code:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25select count(*) from bic_code; select * from table(dbms_xplan.display_cursor(null, null, 'basic')); COUNT(*) ---------- 161644 1 row selected. PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ select count(*) from bic_code Plan hash value: 3562512118 ---------------------------------------- | Id | Operation | Name | ---------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | INDEX FULL SCAN| PK_BIC_CODE | ---------------------------------------- 14 rows selected.
Utilisation du hint FULL --> voilà un résultat qui me plait!
Visiblement l'utilisation de ce paramètre IGNORE_OPTIM_EMBEDDED_HINTS n'est pas très claire.
Code:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26select /*+ FULL(bic_code) */ count(*) from bic_code; select * from table(dbms_xplan.display_cursor(null, null, 'basic')); COUNT(*) ---------- 161644 1 row selected. PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ select /*+ FULL(bic_code) */ count(*) from bic_code Plan hash value: 1320553103 --------------------------------------- | Id | Operation | Name | --------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| BIC_CODE | --------------------------------------- 14 rows selected.
