J’ai déjà présenté plusieurs fois SQL Plan Management disponible avec Oracle 11g Enterprise Edition sur ce blog mais également sur le blog de The Pythian Group. Vous l’aurez compris, je suis un fan!
Parmi les articles intéressants sur le sujet, je vous conseille la lecture de l’article de Dion Cho. Dans ce nouvel article, vous trouverez un exemple très simple qui illustre comment fixer un plan en l’enregistrant dans la baseline SQL Plan Management
Une requête exemple
Pour illustrer cette fonctionnalité, vous trouverez ci-dessous une table simple avec un index:
drop table t1 purge;
create table t1(id number,
text varchar2(1000));
insert into T1
(select case when mod(rownum,2)=0 then 1 else rownum end id,
rpad('X',1000,'X') text
from dual connect by level < 10000);
commit;
create index t1_idx on t1(id);
La requête suivante nous servira de base pour nos exemples:
var id number;
exec :id:=1;
select count(distinct text)
from T1
where id=:id;
Capturer la requête dans une baseline
Pour capturer la requête depuis la SGA, il suffit de retrouver le SQL_ID et éventuellement la valeur de hash du plan d’exécution associé:
col sql_text format a50
col sql_id format a15 new_value sql_id
set lines 80
select sql_id, substr(sql_text,1,50) sql_text
from v$sql
where regexp_like(sql_text, '*count([d]istinct*');
col plan_hash_value format 999999999999999999 new_value phv
select distinct sql_id, plan_hash_value
from v$sql_plan
where sql_id='&&sql_id';
Une fois que vous avez le SQL_ID et la valeur de hash du plan, vous pouvez charger l’ordre SQL dans les baselines de SQL Plan Management comme ci-dessous:
set serveroutput on
declare
x pls_integer;
begin
x:=dbms_spm.load_plans_from_cursor_cache(
sql_id => '&&sql_id',
plan_hash_value => &&phv,
fixed => 'NO',
enabled => 'YES');
dbms_output.put_line('Le code retour est :'||to_char(x));
end;
/
col sql_handle format a25 new_value sql_handle
col schema format a10
select sql_handle,
plan_name,
parsing_schema_name schema,
enabled,
accepted
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SCHEMA ENA ACC
------------------------- ------------------------------ ---------- --- ---
SYS_SQL_086cdcb5431cdbcd SQL_PLAN_0hv6wqp1jtqydf96d6cbf SCOTT YES YES
Vous pouvez visualiser les plans stockés dans la baseline SQL Plan Management avec dbms_xplan.display_sql_plan_baseline:
set lines 180
set pages 1000
select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'&&sql_handle'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_086cdcb5431cdbcd
SQL text: select count(distinct text) from T1 where id=:id
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_0hv6wqp1jtqydf96d6cbf Plan id: 4184698047
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 405047221
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 502 | 3 (34)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 502 | | |
| 2 | VIEW | VW_DAG_0 | 1 | 502 | 3 (34)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 515 | 3 (34)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 1 | 515 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"=TO_NUMBER(:ID))
Ajouter un plan à la liste des plans acceptés
Pour ajouter un plan à la baseline, il suffit de charger ce plan, indépendamment du texte SQL dans la SGA, en utilisant des hints par exemples:
var id number;
exec :id:=1;
select /*+ index(t1) */ count(distinct text)
from T1
where id=:id;
col sql_text format a50
col sql_id format a15 new_value sql_id
set lines 80
select sql_id, substr(sql_text,1,50) sql_text
from v$sql
where regexp_like(sql_text, '.*index.*count([d]istinct.*');
SQL_ID SQL_TEXT
--------------- --------------------------------------------------
780csgw67nysp select /*+ index(t1) */ count(distinct text) fro
col plan_hash_value format 999999999999999999 new_value phv
select distinct sql_id, plan_hash_value
from v$sql_plan
where sql_id='&&sql_id';
SQL_ID PLAN_HASH_VALUE
--------------- -------------------
780csgw67nysp 125724415
Une fois le plan dans la SGA, vous pouvez le charger dans la baseline en précisant le sql_handle auquel le plan doit être raccroché comme ci-dessous:
set serveroutput on
declare
x pls_integer;
begin
x:=dbms_spm.load_plans_from_cursor_cache(
sql_id => '&&sql_id',
plan_hash_value => &&phv,
sql_handle => '&&sql_handle',
fixed => 'NO',
enabled => 'YES');
dbms_output.put_line('Le code retour est :'||to_char(x));
end;
/
Vous pouvez vérifier les plans d’exécution associés à la baseline comme ci-dessous:
set lines 180
set pages 1000
select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'&&sql_handle'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_086cdcb5431cdbcd
SQL text: select count(distinct text) from T1 where id=:id
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_0hv6wqp1jtqydb0cdda18 Plan id: 2966280728
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 125724415
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 502 | 2 (50)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 502 | | |
| 2 | VIEW | VW_DAG_0 | 1 | 502 | 2 (50)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 515 | 2 (50)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 515 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID"=TO_NUMBER(:ID))
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_0hv6wqp1jtqydf96d6cbf Plan id: 4184698047
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 405047221
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 502 | 3 (34)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 502 | | |
| 2 | VIEW | VW_DAG_0 | 1 | 502 | 3 (34)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 515 | 3 (34)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 1 | 515 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"=TO_NUMBER(:ID))
Activer le plan de votre choix
Les 2 plans sont possibles selon la baseline de SQL Plan Management. Effectuez un explain plan pour visualiser le plan de la requête:
explain plan for select count(distinct text)
from T1
where id=:id;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 125724415
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 502 | 2 (50)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 502 | | |
| 2 | VIEW | VW_DAG_0 | 1 | 502 | 2 (50)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 515 | 2 (50)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 515 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID"=TO_NUMBER(:ID))
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_0hv6wqp1jtqydb0cdda18" used for this statement
Vous pouvez désactiver ou fixer le plan avec la commande dbms_spm.alter_sql_plan_baseline comme ci-dessous :
declare
x binary_integer;
begin
x:=dbms_spm.alter_sql_plan_baseline(
sql_handle => '&&sql_handle',
plan_name => 'SQL_PLAN_0hv6wqp1jtqydb0cdda18',
attribute_name => 'enabled',
attribute_value => 'no');
end;
/
Le plan est modifié comme le montre la commande explain plan ci-dessous:
explain plan for select count(distinct text)
from T1
where id=:id;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 405047221
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 502 | 3 (34)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 502 | | |
| 2 | VIEW | VW_DAG_0 | 1 | 502 | 3 (34)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 515 | 3 (34)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 1 | 515 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"=TO_NUMBER(:ID))
Note
-----
- SQL plan baseline "SQL_PLAN_0hv6wqp1jtqydf96d6cbf" used for this statement
Supprimer le SQL de la baseline
Pour enlever les ordres SQL de la baseline, utilisez dbms_spm.drop_sql_plan_baseline comme ci-dessous:
set serveroutput on
declare
x pls_integer;
begin
x := dbms_spm.drop_sql_plan_baseline(
sql_handle=>'&&sql_handle');
dbms_output.put_line('Le code retour est :'||to_char(x));
end;
/
