Vous avez déjà pu explorer SQL Plan Management (11.1+ et Enterprise Edition) et Oracle SQL Profile (10.1+ et Tuning Pack) ! Vous trouverez 3 posts sur le sujet illustrer comment ces 2 outils peuvent s’emmêler jusqu’à donner des résultats surprenants a priori. Nous allons procéder comme suit:
- Partie 1/3 : Schema exemple, exemple de SQL Tuning Set et de SQL Plan Management
- Partie 2/3 : Créer un SQL Profile
- Partie 3/3 : Intégrer le plan du SQL Profile dans SQL Plan Management… et plus
Prêt?
1. Exemple
Pour commencer, vous allez créer une table, l’alimenter, créer 2 index et capturer les statistiques associées:
create table T(
a number,
b number,
c number);
begin
for i in 1..10000 loop
insert into t values (i,i,i);
end loop;
end;
/
create index t_i1 on t(a);
create index t_i2 on t(a,b);
exec dbms_stats.gather_table_stats(user, 'T');
Pour illustrer SQL Plan Management et SQL Profile, nous allons utiliser la requête qui suit:
set lines 120
set pages 1000
alter system flush shared_pool;
exec dbms_application_info.set_module(null,null);
exec dbms_application_info.set_module('DEMO','DEMO');
set serveroutput off
select /*+ no_index(t t_i1) no_index(t t_i2) */ count(c)
from t
where a=1;
COUNT(C)
----------
1
select *
from table(
dbms_xplan.display_cursor(
null,
null,
'basic,rows,cost,projection,predicate')
);
PLAN_TABLE_OUTPUT
------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ no_index(t t_i1) no_index(t t_i2) */ count(c) from t
where a=1
Plan hash value: 2966233522
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 9 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| T | 1 | 9 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[NUMBER,22]
exec dbms_application_info.set_module(null,null);
2. Créer un SQL Tuning Set
Pour faciliter l’utilisation du SQL Tuning Advisor et de SQL Plan Management, vous pouvez commencer par créer un SQL Tuning Set (STS) comme ci-dessous:
col sql_text format a50 word wrapped
col sql_id format a13
exec dbms_sqltune.create_sqlset('DEMO_STS');
select sql_id, sql_text
from v$sql
where module='DEMO'
and sql_text like '%count%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------
9buc0ty0uq98v select /*+ no_index(t t_i1) no_index(t t_i2) */ co
unt(c) from t where a=1
DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'sql_id = ''9buc0ty0uq98v''',
NULL, NULL, NULL, NULL, 1, NULL,
'ALL')) P;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'DEMO_STS',
populate_cursor => cur,
update_attributes=>'ALL');
END;
/
select sql_id, sql_text
from table(
dbms_sqltune.select_sqlset('DEMO_STS')
);
SQL_ID SQL_TEXT
------------- --------------------------------------------------
9buc0ty0uq98v select /*+ no_index(t t_i1) no_index(t t_i2) */ co
unt(c) from t where a=1
Vous noterez qu’en spécifiant update_attributes=>'ALL', le plan de la requête est stocké dans le SQL Tuning Set; Vous pouvez le vérifier grâce à dbms_xplan comme ci-dessous:
select *
from table(
dbms_xplan.display_sqlset(
sqlset_name => 'DEMO_STS',
sql_id => '9buc0ty0uq98v',
format => 'basic,rows,cost,projection,predicate'));
PLAN_TABLE_OUTPUT
-----------------------------
SQL Tuning Set Name: DEMO_STS
SQL Tuning Set Owner: SCOTT
SQL_ID: 9buc0ty0uq98v
SQL Text: select /*+ no_index(t t_i1) no_index(t t_i2) */ count(c) from t
where a=1
-----------------------------
Plan hash value: 2966233522
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 9 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| T | 1 | 9 (0)|
--------------------------------------------------------
3. Capturer le plan dans SQL Plan Management
Pour tout savoir sur SQL Plan Management, reportez-vous au Chapitre 15 de Oracle 11g Performance Tuning Guide et aussi aux descriptions de DBMS_SPM, dba_sql_management_config et dba_sql_plan_baselines; Vous pouvez capturer le plan de la requête à partir du SQL Tuning Set comme ci-dessous:
var x number;
exec :x:=DBMS_SPM.LOAD_PLANS_FROM_SQLSET (-
sqlset_name=>'DEMO_STS', -
basic_filter=>'sql_id=''9buc0ty0uq98v''');
print x
X
----------
1
col signature format 99999999999999999999
select sql_handle, enabled, accepted, fixed, signature
from dba_sql_plan_baselines;
SQL_HANDLE ENA ACC FIX SIGNATURE
------------------------------ --- --- --- ---------------------
SYS_SQL_a6604c14af92d9a3 YES YES NO 11988665859788954019
Vous pouvez visualiser les plans contenus dans une SQL Plan Management Baseline au moyen de DBMS_XPLAN comme ci-dessous:
select *
from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle => 'SYS_SQL_a6604c14af92d9a3',
format => 'basic,rows,cost,projection,predicate'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_a6604c14af92d9a3
SQL text: select /*+ no_index(t t_i1) no_index(t t_i2) */ count(c) from t
where a=1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_af92d9a33fdbb376
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 2966233522
------ --------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 (0)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| T | 1 | 9 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[NUMBER,22]
4. Quelques remarques…
Avant d’attaquer la 2ème partie, vous noterez quelques points intéressants:
- D’abord, la signature de la baseline est identique à celle d’un SQL Profile pour lequel fixed=>’NO’:
var sqltext clob
begin
:sqltext:='select /*+ no_index(t t_i1) no_index(t t_i2) */ count(c)
from t
where a=1';
end;
/
col signature format 99999999999999999999
select dbms_sqltune.sqltext_to_signature(:sqltext) signature from dual;
SIGNATURE
---------------------
11988665859788954019
- Ensuite, si vous exécutez de nouveau la requête, vous pourrez constater (via l’attribut note) que son plan est désormais calculé à partir de la SQL Plan Management Baseline. C’est d’ailleurs sa raison d’être :
PLAN_TABLE_OUTPUT
------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ no_index(t t_i1) no_index(t t_i2) */ count(c) from t
where a=1
Plan hash value: 2966233522
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 9 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| T | 1 | 9 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[NUMBER,22]
Note
-----
- SQL plan baseline SYS_SQL_PLAN_af92d9a33fdbb376 used for this statement
- Vous pouvez voir le contenu de la baseline en interrogeant
sys.sqlobj$data:
col signature format 99999999999999999999
set long 10000
set longchunksize 100
select so.signature,
so.plan_id,
decode(so.obj_type, 1,'SQL_PROFILE',
2,'SQL_PLAN_BASELINE',
3,'SQL_PATCH',
null) obj_type,
sod.comp_data
from sys.sqlobj$ so,
sys.sqlobj$data sod
where so.signature=sod.signature
and so.plan_id=sod.plan_id
and so.obj_type=sod.obj_type
and so.obj_type=2;
SIGNATURE PLAN_ID OBJ_TYPE
--------------------- ---------- -----------------
COMP_DATA
--------------------------------------------------------------------------------
11988665859788954019 1071362934 SQL_PLAN_BASELINE
<outline_data>
<hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
<hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.1.0.7')]]></hint>
<hint><![CDATA[DB_VERSION('11.1.0.7')]]></hint>
<hint><![CDATA[ALL_ROWS]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
<hint><![CDATA[FULL(@"SEL$1" "T"@"SEL$1")]]></hint>
</outline_data>
- Enfin, vous pouvez exporter une SQL Plan Management Baseline à des fins plus ou moins honnêtes:
exec dbms_spm.create_stgtab_baseline('SPM_STGTAB');
var x number
exec :x:=dbms_spm.pack_stgtab_baseline (-
table_name => 'SPM_STGTAB')
print x
X
----------
1
set lines 120
select obj_name, sql_handle, signature from spm_stgtab;
OBJ_NAME SQL_HANDLE SIGNATURE
------------------------------ ------------------------------ ---------------------
SYS_SQL_PLAN_af92d9a33fdbb376 SYS_SQL_a6604c14af92d9a3 11988665859788954019
Voilà pour commencer! Dans la 3ème partie, nous verrons comment ajouter les plans que vous avez choisis ou un plan généré par un SQL Profile à une Baseline… Pour l’instant faisons un zoom sur un SQL Profile et n’hésitez pas à laisser vos commentaires.
