Dans la première partie de cet article, vous avez élaboré un exemple et créé
une SQL Plan Management Baseline; vous avez manipulé un SQL Profile avec la même requête dans la deuxième partie. Avec cette troisième et dernière partie, vous intègrerez le SQL Profile dans la SQL Plan Management Baseline…
1. Ajouter le SQL Profile dans la Baseline
Vous pouvez utiliser dbms_spm.evolve_sql_plan_baseline pour explorer des nouveaux plans d’exécutions et ainsi bénéficier du SQL Profile que vous avez créé dans l’étape précédente:
select SQL_HANDLE, SQL_TEXT
from dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT
------------------------ --------------------------------------------------
SYS_SQL_a6604c14af92d9a3 select /*+ no_index(t t_i1) no_index(t t_i2) */ co
unt(c)
from t
where a=1
var output clob
exec :output:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (-
sql_handle=> 'SYS_SQL_a6604c14af92d9a3',-
commit=>'YES')
print output
OUTPUT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_a6604c14af92d9a3
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_af92d9a3b238cfe2
-----------------------------------
Plan was verified: Time used .05 seconds.
Passed performance criterion: Compound improvement ratio >= 10.33
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): 0 0
CPU Time(ms): 0 0
Buffer Gets: 31 3 10.33
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
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(
format => 'basic,rows,cost,projection,predicate,note'));
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: 35579277
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 3 | INDEX RANGE SCAN | T_I1 | 1 | 1 (0)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[NUMBER,22]
3 - "T".ROWID[ROWID,10]
Note
-----
- SQL profile SYS_SQLPROF_011e3578c3270000 used for this statement
- SQL plan baseline SYS_SQL_PLAN_af92d9a3b238cfe2 used for this statement
select *
from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle => 'SYS_SQL_a6604c14af92d9a3',
format => 'basic,rows,cost,projection,predicate,note'));
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]
Note
-----
- SQL profile "SYS_SQLPROF_011e3578c3270000" used for this statement
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_af92d9a3b238cfe2
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 35579277
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 3 | INDEX RANGE SCAN | T_I1 | 1 | 1 (0)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[NUMBER,22]
3 - "T".ROWID[ROWID,10]
Note
-----
- SQL profile "SYS_SQLPROF_011e3578c3270000" used for this statement
2. Ajouter un troisième plan dans
la SQL Plan Management Baseline
Vous allez ajouter le plan ci-dessous à la SQL Plan Management Baseline; vous noterez que le texte de la requête est différent du texte original:
select /*+ index(t t_i2) */ count(c)
from t
where a=1;
select *
from table(
dbms_xplan.display_cursor(
format => 'basic,rows,cost,projection,predicate,note'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ index(t t_i2) */ count(c) from t where a=1
Plan hash value: 2267460261
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 3 (0)|
|* 3 | INDEX RANGE SCAN | T_I2 | 1 | 2 (0)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[NUMBER,22]
3 - "T".ROWID[ROWID,10]
Vous pouvez ajouter ce plan à la baseline. Pour cela, il suffit d’utiliser dbms_spm.load_plans_from_cursor_cache après avoir exécuté la requête et de référencer SQL_ID et PLAN_HASH_VALUE du nouveau plan, comme ci-dessous:
select sql_handle, plan_name, enabled, accepted, fixed, signature
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA ACC FIX SIGNATURE
------------------------------ ------------------------------ --- --- --- ---------------------
SYS_SQL_a6604c14af92d9a3 SYS_SQL_PLAN_af92d9a33fdbb376 YES YES NO 11988665859788954019
SYS_SQL_a6604c14af92d9a3 SYS_SQL_PLAN_af92d9a3b238cfe2 YES YES NO 11988665859788954019
set long 1000
set longchunksize 50
col sql_text format a50
SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------
9sr036wfgwvjv 2267460261 select /*+ index(t t_i2) */ count(c) from t whe
re a=1
var x number;
exec :x:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (-
sql_id=>'9sr036wfgwvjv', -
plan_hash_value=>'2267460261', -
sql_handle=>'SYS_SQL_a6604c14af92d9a3', -
fixed=>'YES', -
enabled=>'YES')
print x
X
----------
1
select sql_handle, plan_name, enabled, accepted, fixed, signature
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA ACC FIX SIGNATURE
------------------------ ----------------------------- --- --- --- --------------------
SYS_SQL_a6604c14af92d9a3 SYS_SQL_PLAN_af92d9a3077981e5 YES YES YES 11988665859788954019
SYS_SQL_a6604c14af92d9a3 SYS_SQL_PLAN_af92d9a33fdbb376 YES YES NO 11988665859788954019
SYS_SQL_a6604c14af92d9a3 SYS_SQL_PLAN_af92d9a3b238cfe2 YES YES NO 11988665859788954019
Si vous utilisez fixed=>'YES', le plan sera fixé, comme vous pouvez le constater en exécutant la requête une nouvelle fois:
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(
format => 'basic,rows,cost,projection,predicate,note'));
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: 2267460261
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 3 (0)|
|* 3 | INDEX RANGE SCAN | T_I2 | 1 | 2 (0)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[NUMBER,22]
3 - "T".ROWID[ROWID,10]
Note
-----
- SQL profile SYS_SQLPROF_011e3578c3270000 used for this statement
- SQL plan baseline SYS_SQL_PLAN_af92d9a3077981e5 used for this statement
3. Un peu de ménage
Après ces quelques exemples, tout doit disparaître:
exec dbms_sqltune.drop_tuning_task('DEMO_TTASK');
exec :x:=DBMS_SPM.DROP_SQL_PLAN_BASELINE (-
sql_handle=>'SYS_SQL_a6604c14af92d9a3')
exec dbms_sqltune.drop_sql_profile(-
'SYS_SQLPROF_011e3578c3270000');
exec dbms_sqltune.drop_sqlset('DEMO_STS');
drop table spm_stgtab purge;
drop table sqlprof_stgtab purge;
drop table t purge;
