Après une première partie consacrée à l’élaboration d’un exemple, la création d’un SQL Tuning Set et la création d’une SQL Plan Management Baseline, vous allez maintenant créer un SQL Profile avec la même requête. Vous noterez que le fait que vous puissiez ou non générer ce SQL Profile dépend du SQL Tuning Advisor. Dans cet exemple, le plan de la requête est évidemment sous-optimal et le SQL Tuning Advisor vous proposera de l’améliorer; ce n’est pas toujours le cas!
1. Créer et exécuter une SQL Tuning Advisor Task.
Un SQL Profile est l’un des résultats possible d’une SQL Tuning Advisor Task. Vous allez donc vous appuyer sur le SQL Tuning Set créé dans la partie précédente et créer une SQL Tuning Advisor Task:
set serveroutput on
declare
ttask varchar2(80);
begin
ttask:=dbms_sqltune.create_tuning_task(
sqlset_name => 'DEMO_STS',
task_name => 'DEMO_TTASK');
dbms_output.put_line('SQL Tuning Task Name: '||ttask);
end;
/
SQL Tuning Task Name: DEMO_TTASK
exec dbms_sqltune.set_tuning_task_parameter(-
task_name => 'DEMO_TTASK', -
parameter => 'TEST_EXECUTE',-
value => 'FULL')
exec dbms_sqltune.set_tuning_task_parameter(-
task_name => 'DEMO_TTASK', -
parameter => 'MODE', -
value => 'COMPREHENSIVE')
exec dbms_sqltune.execute_tuning_task(-
task_name => 'DEMO_TTASK')
Une fois la Tuning Advisor Task créée, vous pouvez visualiser le rapport associé:
var ttask clob
exec :ttask:=dbms_sqltune.report_tuning_task(-
task_name => 'DEMO_TTASK')
set long 10000
set longchunksize 10000
print ttask
TTASK
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : DEMO_TTASK
Tuning Task Owner : SCOTT
Workload Type : SQL Tuning Set
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 12/14/2008 12:59:06
Completed at : 12/14/2008 12:59:07
SQL Tuning Set (STS) Name : DEMO_STS
SQL Tuning Set Owner : SCOTT
Number of Statements in the STS : 1
-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed : 1
Number of SQLs in the Report : 1
Number of SQLs with Findings : 1
Number of SQLs with SQL profiles recommended : 1
-------------------------------------------------------------------------------
SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID SQL ID statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
2 9buc0ty0uq98v 90.44%
-------------------------------------------------------------------------------
DETAILS SECTION
-------------------------------------------------------------------------------
Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID : 2
Schema Name: SCOTT
SQL ID : 9buc0ty0uq98v
SQL Text : select /*+ no_index(t t_i1) no_index(t t_i2) */ count(c)
from t
where a=1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 90.44%)
------------------------------------------
- Consider accepting the recommended SQL profile. A SQL plan baseline
corresponding to the plan with the SQL profile will also be created.
execute dbms_sqltune.accept_sql_profile(task_name => 'DEMO_TTASK',
object_id => 2, task_owner => 'SCOTT', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time(ms): 0 0
CPU Time(ms): 4 0 100%
User I/O Time(ms): 0 0
Buffer Gets: 31 3 90.32%
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
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)
2- Original With Adjusted Cost
------------------------------
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)
3- Using SQL Profile
--------------------
Plan hash value: 35579277
------------------------------------------------------------------
| I d | 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)
------------------------------------------------------------------
2. Accepter le SQL Profile
Vous pouvez accepter le SQL Profile proposé par la SQL Tuning Advisor Task comme ci-dessous:
execute dbms_sqltune.accept_sql_profile(-
task_name => 'DEMO_TTASK', -
object_id => 2, -
task_owner => 'SCOTT', -
replace => TRUE, -
force_match=>true);
set long 1000
set longchunksize 50
select NAME, SQL_TEXT
from dba_sql_profiles;
NAME SQL_TEXT
------------------------------ --------------------------------------------------
SYS_SQLPROF_011e3578c3270000 select /*+ no_index(t t_i1) no_index(t t_i2) */ co
unt(c)
from t
where a=1
3. Quelques remarques:
Vous pouvez noter plusieurs choses lorsque vous utilisez un SQL Profile avec une SQL Plan Management Baseline et notamment:
- Malgré la création du SQL Profile, le plan ne change pas
En effet, le plan créé n’est pas automatiquement ajouté à la SQL Plan Management Baseline et par conséquent, le plan n’est pas encore valide:
select /*+ no_index(t t_i1) no_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 /*+ 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 | | 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
-----
Note
-----
- SQL profile SYS_SQLPROF_011e3578c3270000 used for this statement
- SQL plan baseline SYS_SQL_PLAN_af92d9a33fdbb376 used for this statement
- Si vous avez accepter le SQL Profile avec
force_match=>true, les requêtes dont les valeurs des littéraux changent sont également impactées par le SQL Profile
select /*+ no_index(t t_i1) no_index(t t_i2) */ count(c)
from t
where a=2;
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=2
Plan hash value: 35579277
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | SORT AGGREGATE | | | |
| 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"=2)
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
- Contrairement au SQL Plan Management Baseline, le SQL Profile ne fixe pas le plan mais l’impacte:
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=1;
SIGNATURE PLAN_ID OBJ_TYPE
--------------------- ---------- -----------------
COMP_DATA
----------------------------------------------------------------------
10340412069353135001 0 SQL_PROFILE
<outline_data>
<hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint>
<hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
</outline_data>
- Enfin, comme pour les SQL Plan Management Baseline, vous pouvez exporter les SQL Profile:
exec dbms_sqltune.create_stgtab_sqlprof(-
'SQLPROF_STGTAB');
exec dbms_sqltune.pack_stgtab_sqlprof ( -
profile_name => 'SYS_SQLPROF_011e3578c3270000',-
staging_table_name => 'SQLPROF_STGTAB');
set long 1000
set longchunksize 50
select sql_handle, sql_text from SQLPROF_STGTAB;
SQL_HANDLE SQL_TEXT
------------------------------ --------------------------------------------------
SYS_SQL_8f8085fdc0f49b99 select /*+ no_index(t t_i1) no_index(t t_i2) */ co
unt(c)
from t
where a=1
Voilà! Dans la troisième partie de cet article vous verrez en particulier comment ajouter le SQL Profile à la SQL Plan Management Baseline mais également comment ajouter et fixer un plan spécifique à cette même SQL Plan Management Baseline.
