Plan d'exécution et base line

Version imprimable

12/12/2013, 09h13
macben

Plan d'exécution et base line

Bonjour,

Est-il possible de forcer une requête (identifiée par son sql_id) à utiliser un plan d'exécution (identifié par un plan_hash_value) ?

Par un base line ?

Je m'explique. Sur un infocentre j'ai une requête qui s'est mise à dérivé du jour au lendemain (de 4 mins de temps d'exécution à 6 heures), sans raison apparente (ni ré-écriture, ni administration bdd).

Je constate dans la vue dba_hist_sql_plan que le plan a changé depuis que l'on constate la dérive. :aie:

Je voudrais donc ré-affecter l'ancien plan. (sans passer par un profile qui est une mécanique que je trouve un peu compliquée).

J'ai les base_line d'activées et d'ailleurs sur cette requête j'en ai 5 dont je vois les noms depuis la database control. Or si je vais dans la vue dba_sql_plan_baselines je ne retrouve pas mes baseline (identifié par leur sql_handle). :roll:

Mes questions sont donc :

- Pourquoi je ne vois pas mes baselines dans la vue dba_sql_plan_baselines alors que je les vois dans la database control ?
- Comment définir un baseline depuis un plan_hash_value connu et l'affecter à un sql_id ?

8O

merci,
07/01/2014, 21h50
petitfrere

Pour répondre a votre première question: Je ne sais pas je viens de faire un test, je vois bien mes baselines depuis le grid control et depuis sqlplus

Pour votre deuxième question, il y a un tres bon article de arkzoid (petite recherche sur "big brother" )qui explique comment faire cela ou du moins vous pouvez le faire assez facilement apres lecture de son article, la difficulté (celle que j'ai pu rencontrer) et de récupérer le bon hash de la requête (caché dans les meandre de l'historique de tt les requêtes SQL).

Il m'est arrivé de régénérer le bon plan d’exécution en ré-exécutant la requête problématique a la main avec un 'HINT' pour utiliser le bon plan d'exec, et de récupérer son hash pour creer une baseline SQL

Citation:

Envoyé par macben

Bonjour,

Comment définir un baseline depuis un plan_hash_value connu et l'affecter à un sql_id ?

J'ai essayé de reproduire votre cas, mais après plusieurs tentatives, je n'ai pas réussi à affecter une baseline prédéfinie à un sql_id précis. Je vous laisse ici mon modèle et si quelqu'un a une idée qu'il y contribue

Code:

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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
 
SQL>  create table t2
  2      (col1  number
  3      ,col2  varchar2(50)
  4      ,flag  varchar2(2));
 
 
SQL>  insert into t2
  2      select rownum
  3      ,      lpad('X',50,'X')
  4      ,      case when rownum = 1
  5                  then 'Y1'
  6                  when rownum = 2
  7                  then 'Y2'
  8                  when mod(rownum,2) = 0
  9                  then 'N1'
 10                 else 'N2'
 11            end
 12     from   dual
 13    connect by rownum <= 100000;
 
100000 rows created.
 
SQL> create index i1 on t2 (flag);
 
Index created.
 
 
SQL> var n varchar2(2)
SQL> exec :n := 'Y1';
 
SQL> alter session set optimizer_capture_sql_plan_baselines = TRUE;
 
SQL> select count(1) from t2 where flag = :n;
 
  COUNT(1)
----------
         1
 
SQL> select count(1) from dba_sql_plan_baselines where sql_text like '%t2%';
 
  COUNT(1)
----------
         0
 
SQL> select count(1) from t2 where flag = :n;
 
  COUNT(1)
----------
         1
 
SQL> select count(1) from dba_sql_plan_baselines where sql_text like '%t2%';
 
  COUNT(1)
----------
         1 -- faut executer deux fois avant de le voir être capturé
 
SQL> alter session set optimizer_capture_sql_plan_baselines = FALSE;
 
SQL> select sql_handle,  accepted from dba_sql_plan_baselines where sql_text like '%t2 where flag%';
 
SQL_HANDLE                     ACC
------------------------------ ---
SQL_38c7f32b9603f5d8           YES
 
SQL> select count(1) from t2 where flag = :n;
 
  COUNT(1)
----------
         1
 
SQL> select * from table(dbms_xplan.display_cursor);
 
SQL_ID  9zr7y0b3d8h5m, child number 2
-------------------------------------
select count(1) from t2 where flag = :n
 
Plan hash value: 3900446664
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     9 |    27 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("FLAG"=:N)
 
Note
-----
   - SQL plan baseline SQL_PLAN_3jjzm5fb07xfs3f0011ba used for this statement
 
 
 
SQL> alter session set optimizer_capture_sql_plan_baselines = TRUE;
 
SQL> select /*+ full (t2) */ count(1) from t2 where flag = :n;
 
  COUNT(1)
----------
         1
 
SQL> select /*+ full (t2) */ count(1) from t2 where flag = :n;
 
  COUNT(1)
----------
         1
 
SQL> select sql_handle,  accepted from dba_sql_plan_baselines where sql_text like '%t2 where flag%';
 
SQL_HANDLE                     ACC
------------------------------ ---
SQL_38c7f32b9603f5d8           YES
SQL_d82a3703500d9194           YES
 
SQL> alter session set optimizer_capture_sql_plan_baselines = FALSE;
 
 
SQL> select count(1) from t2 where flag = :n;
 
  COUNT(1)
----------
         1
 
SQL> select * from table(dbms_xplan.display_cursor);
 
SQL_ID  9zr7y0b3d8h5m, child number 2
-------------------------------------
select count(1) from t2 where flag = :n
 
Plan hash value: 3900446664
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     9 |    27 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("FLAG"=:N)
 
Note
-----
   - SQL plan baseline SQL_PLAN_3jjzm5fb07xfs3f0011ba used for this statement

Maintenant, ma requête sans le hint full, utilise le plan dans la baseline SQL_38c7f32b9603f5d8(index range scan)
Mais je veux qu'elle utilise le plan de la baseline SQL_d82a3703500d9194 (full scan). Je vais donc commencer par
rapatrier les infos du plan de cette baseline(full table scan)

Code:

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
 
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_dhajr0d80v4cn1c6cf506'));
 
--------------------------------------------------------------------------------
SQL handle: SQL_d82a3703500d9194
SQL text: select /*+ full (t2) */ count(1) from t2 where flag = :n
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_dhajr0d80v4cn1c6cf506         Plan id: 476902662
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
 
Plan hash value: 3321871023
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |   241   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   | 25000 | 75000 |   241   (3)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=:N)

que je vais finir par associer à ma requête (via son sql_id). Et c'est là où se situe mon problème.

Code:

1
2
3
4
5
6
7
8
 
var pls number
SQL> begin
  2   :pls := dbms_spm.load_plans_from_cursor_cache(sql_id => '9zr7y0b3d8h5m'
  3                                                ,plan_hash_value => 3321871023
  4                                                ,sql_handle      => 'SQL_38c7f32b9603f5d8');
  5  end;
  6  /

Malgré avoir affecté le plan SQL_38c7f32b9603f5d8 à mon SQL_id, ce plan n'est pas choisi. J'ai ensuite "disablé" le plan de l'ancienne baseline (index range scan) mais cela n'a pas permis au plan SQL_38c7f32b9603f5d8 d'être affecté à mon SQL_id.

Il est peut-être tout simplement impossible d'attribuer une baseline (un plan d'execution) à une requête ayant une "force_matching_signature" différente du sql_id qui est à l'origine de la capture de ladite baseline.

15/01/2014, 14h17
mnitu

How does SQL Plan Management match SQL statements to SQL plan baselines?

Citation:

SQL statements are matched to SQL plan baselines using the signature of the SQL statement. A signature is a unique SQL identifier generated from the normalized SQL text (uncased and with whitespaces removed).

Citation:

Envoyé par mnitu

How does SQL Plan Management match SQL statements to SQL plan baselines?

Citation:

Est-il possible de forcer une requête (identifiée par son sql_id) à utiliser un plan d'exécution (identifié par un plan_hash_value) ?
Par un base line ?

Oui c'est possible. J'ai enfin réussi à le simuler. Reprenez mon exemple ci-dessus

Code:

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
 
SQL> SELECT count(1) FROM t2 WHERE flag = :n;
 
SQL_ID  8mq4n7mxp5ddr, child number 1
-------------------------------------
SELECT count(1) FROM t2 WHERE flag = :n
 
Plan hash value: 3900446664
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |      |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     2 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("FLAG"=:N)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline SQL_PLAN_3jjzm5fb07xfs3f0011ba used for this statement

j'ai une requête protégée par une baseline qui utilise un index range scan. Je ne veux plus qu'elle soit protégée par ce plan. Je veux qu'elle le soit par un autre plan d'une autre baseline qui utilise un full scan. Ce plan correspond au select suivant

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
SQL> SELECT /*+ full (t2) */ count(1) FROM t2 WHERE flag = :n;
 
SQL_ID  23665n23gqrju, child number 0
-------------------------------------
SELECT /*+ full (t2) */ count(1) FROM t2 WHERE flag = :n
 
Plan hash value: 3321871023
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   452 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |     2 |    12 |   452   (3)| 00:00:02 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=:N)
 
Note
-----
   - dynamic sampling used for this statement (level=2)

Ok. Je vais donc commencer par créér une baseline de ce second sql

Code:

1
2
3
4
5
6
 
SQL> -- create a sql plan baseline for full plan
SQL> variable cnt number;
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '23665n23gqrju');
 
PL/SQL procedure successfully completed.

Et je n'oublie pas de "disabler" ma baseline d'origine

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SQL> -- disable l'ancienne baseline
SQL> declare
  2   ln_ps number;
  3  begin
  4  
  5  ln_ps := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_38c7f32b9603f5d8'
  6                                           ,plan_name => 'SQL_PLAN_3jjzm5fb07xfs3f0011ba'
  7                                           ,attribute_name => 'enabled'
  8                                           ,attribute_value => 'NO');
  9  end;
 10  /
 
PL/SQL procedure successfully completed.

Si bien que maintenant j'ai l'image suivante:

Code:

1
2
3
4
5
6
7
 
SQL> select sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;
 
SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_38c7f32b9603f5d8           SQL_PLAN_3jjzm5fb07xfs3f0011ba NO  YES  -- ancienne baseline avec index
SQL_d82a3703500d9194           SQL_PLAN_dhajr0d80v4cn1c6cf506 YES YES  -- nouvelle baseline avec full scan

Enfin il ne me reste plus qu' affecter le sql_id du full scan(23665n23gqrju) + son plan avec le full scan(23665n23gqrju) à mon SQL_HANDLE qui correspond à mon toute première baseline (SQL_38c7f32b9603f5d8)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SQL> declare
  2   ln_ps number;
  3  begin
  4  
  5  ln_ps := dbms_spm.load_plans_from_cursor_cache(sql_id => '23665n23gqrju'
  6                                           ,plan_hash_value => 3321871023
  7                                           ,sql_handle => 'SQL_38c7f32b9603f5d8');
  8  end;
  9  /
 
PL/SQL procedure successfully completed.

Et voilà ce que j'obtiens lorsque j'exécute ma requête d'origine

Code:

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
 
SQL> SELECT count(1) FROM t2 WHERE flag = :n;
 
  COUNT(1)
----------
         2
 
SQL_ID  8mq4n7mxp5ddr, child number 1
-------------------------------------
SELECT count(1) FROM t2 WHERE flag = :n
 
Plan hash value: 3321871023
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   239 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |   763 |  4578 |   239   (2)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("FLAG"=:N)
 
Note
-----
   - SQL plan baseline SQL_PLAN_3jjzm5fb07xfs1c6cf506 used for this statement