Oracle 11g Release 2 Real Application Testing (a.k.a RAT) permet, en 11g Release 2, de comparer le contenu de 2 SQL Tuning Set (a.k.a STS). Dans cet article, vous trouverez un exemple de comparaison de 2 SQL Tuning Set grace au package DBMS_SQLPA.
Table Exemple
Pour commencer, je vous propose de créer un schéma exemple. Créez une table dans l’utilisateur de votre choix; SYS fait très bien l’affaire pour ce petit test;
create table t1 (
id number,
text varchar2(1000))
tablespace users;
insert into t1
(select case when mod(rownum,2)=1 then 1 else rownum end,
rpad('X',1000,'X')
from dual
connect by level <=10000);
create index t1ix
on t1(id)
tablespace users;
exec dbms_stats.gather_table_stats(-
USER, 'T1', -
method_opt=>'for all columns size 254');
Et 1, et 2 SQL Tuning Set
Je ne m’étendrai pas trop sur la création des SQL Tuning Set puisqu’il ne s’agit pas vraiment du sujet. Lancez les 2 scripts ci-dessous pour créer 2 Tuning Set avec le même ordre SQL mais un plan différent et un temps d’exécution différent. Le premier script permet de créer le premier Tuning Set nommé STS1 :
var id number;
exec :id:=1;
exec dbms_application_info.set_module('STS1', null);
select count(text) from t1 where id=:id;
exec dbms_application_info.set_module(null, null);
declare
cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
begin
DBMS_SQLTUNE.DROP_SQLSET (
sqlset_name=>'STS1');
exception when others then null;
end;
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name=>'STS1',
description=>'STS1 of a query using an index');
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'module = ''STS1'' and command_type =3',
NULL, NULL, NULL, NULL, 1, NULL,
'ALL')) P;
DBMS_SQLTUNE.LOAD_SQLSET (
sqlset_name => 'STS1',
populate_cursor=>cur);
end;
/
col sql_id format a15
col plan_hash_value format 999999999999
col sql_text format a80
set lines 120 pages 100
set long 80
set longchunksize 80
select p.sql_id, p.plan_hash_value, p.sql_text
from table(dbms_sqltune.SELECT_SQLSET('STS1')) p;
SQL_ID PLAN_HASH_VALUE SQL_TEXT
--------------- --------------- ----------------------------------------
dk0ftg7zdwtwq 3724264953 select count(text) from t1 where id=:id
Le second script permet de créer le second SQL Tuning Set nommé STS2 après avoir redémarré l’instance pour nettoyer le contexte du module avec un arrêt redémarrage :
Petit problème personnel qu’il faut que j’investigue : « flusher » la shared pool ne suffit pas à changer le module associé à un ordre SQL
startup force
exec :id:=2;
exec dbms_application_info.set_module('STS2', null);
select count(text) from t1 where id=:id;
exec dbms_application_info.set_module(null, null);
declare
cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
begin
DBMS_SQLTUNE.DROP_SQLSET (
sqlset_name=>'STS2');
exception when others then null;
end;
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name=>'STS2',
description=>'STS1 of a query using an index');
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'module = ''STS2'' and command_type =3',
NULL, NULL, NULL, NULL, 1, NULL,
'ALL')) P;
DBMS_SQLTUNE.LOAD_SQLSET (
sqlset_name => 'STS2',
populate_cursor=>cur);
end;
/
col sql_id format a15
col plan_hash_value format 999999999999
col sql_text format a80
set lines 120 pages 100
set long 80
set longchunksize 80
select p.sql_id, p.plan_hash_value, p.sql_text
from table(dbms_sqltune.SELECT_SQLSET('STS2')) p;
SQL_ID PLAN_HASH_VALUE SQL_TEXT
--------------- --------------- ---------------------------------------
dk0ftg7zdwtwq 2165244694 select count(text) from t1 where id=:id
Lancer la comparaison
Pour lancer la comparaison, il faut utiliser le package DBMS_SQLPA avec un type d’exécution convert sqlset (et disposer des licences associées); voici un exemple ci-dessous
VAR aname varchar2(30);
EXEC :aname := 'STS1_VS_STS2';
EXEC :aname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(task_name => :aname);
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => :aname, -
execution_type => 'convert sqlset', -
execution_name => 'first trial', -
execution_params => DBMS_ADVISOR.ARGLIST('sqlset_name', 'STS1', -
'sqlset_owner', user));
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => :aname, -
execution_type => 'convert sqlset', -
execution_name => 'second trial', -
execution_params => DBMS_ADVISOR.ARGLIST('sqlset_name', 'STS2', -
'sqlset_owner', user));
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => :aname, -
execution_type => 'compare', -
execution_name => 'comparison');
Vous pourrez voir le résultat en interrogeant dbms_sqlpa.report_analysis_task :
set lines 120
set pages 1000
set longchunksize 10000
set long 10000
select DBMS_SQLPA.REPORT_ANALYSIS_TASK(:aname, 'TEXT', 'ALL') text
from dual;
TEXT
-----------------------------------------------------------------------------------------
General Information
-----------------------------------------------------------------------------------------
Task Information: Workload Information:
--------------------------------------------- -----------------------------------------
Task Name : STS1_VS_STS2 SQL Tuning Set Name : STS2
Task Owner : SYS SQL Tuning Set Owner : SYS
Description : Total SQL Statement Count : 1
Execution Information:
-----------------------------------------------------------------------------------------
Execution Name : comparison Started : 06/02/2010 22:01:20
Execution Type : COMPARE PERFORMANCE Last Updated : 06/02/2010 22:01:20
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 0
Analysis Information:
-----------------------------------------------------------------------------------------
Before Change Execution: After Change Execution:
--------------------------------------------- -----------------------------------------
Execution Name : second trial Execution Name : first trial
Execution Type : CONVERT SQLSET Execution Type : CONVERT SQLSET
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLETED
Started : 06/02/2010 22:01:20 Started : 06/02/2010 22:01:20
Last Updated : 06/02/2010 22:01:20 Last Upda ted : 06/02/2010 22:01:20
Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED
Before Change Workload: After Change Workload:
--------------------------------------------- -----------------------------------------
SQL Tuning Set Name : STS2 SQL Tuning Set Name : STS1
SQL Tuning Set Owner : SYS SQL Tuning Set Owner : SYS
Total SQL Statement Count : 1 Total SQL Statement Count : 1
---------------------------------------------
Comparison Metric: ELAPSED_TIME
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------
Report Summary
-----------------------------------------------------------------------------------------
Projected Workload Change Impact:
-------------------------------------------
Overall Impact : 63.5%
Improvement Impact : 63.5%
Regression Impact : 0%
Missing-SQL Impact : 0%
New-SQL Impact : 0%
SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall 1 1
Common 1 1
Improved 1 1
Different 0 0
Top 1 SQL Sorted by Absolute Value of Change Impact on the Workload
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
| | | Impact on | Total Metric | Total Metric | Impact | Plan |
| object_id | sql_id | Workload | Before | After | on SQL | Change |
-----------------------------------------------------------------------------------------
| 1 | dk0ftg7zdwtwq | 63.5% | 11303 | 4126 | 63.5% | y |
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
DBMS_XPLAN pour visualiser les plans
Pour afficher les plans des ordres stockés dans les SQL Tuning Set, vous pouvez utiliser la commande dbms_xplan.display_sqlset :
select * from table(dbms_xplan.display_sqlset('STS1','dk0ftg7zdwtwq'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL Tuning Set Name: STS1
SQL Tuning Set Owner: SYS
SQL_ID: dk0ftg7zdwtwq
SQL Text: select count(text) from t1 where id=:id
--------------------------------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 409 (100)| |
| 1 | SORT AGGREGATE | | 1 | 1004 | | |
| 2 | TABLE ACCESS FULL| T1 | 4921 | 4824K| 409 (0)| 00:00:05 |
---------------------------------------------------------------------------
select * from table(dbms_xplan.display_sqlset('STS2','dk0ftg7zdwtwq'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL Tuning Set Name: STS2
SQL Tuning Set Owner: SYS
SQL_ID: dk0ftg7zdwtwq
SQL Text: select count(text) from t1 where id=:id
--------------------------------------------------------------------------------
Plan hash value: 2165244694
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 1004 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1004 | 2 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN | T1IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Conclusion
Comme souvent, c’est simple et efficace quand on a construit la séquence des ordres Oracle; pour en terminer avec cet exemple, supprimer l’analyse SQL Performance Analyzer, les SQL Tuning Set et la table :
EXEC DBMS_SQLPA.DROP_ANALYSIS_TASK(task_name => :aname);
exec dbms_sqltune.drop_sqlset('STS1')
exec dbms_sqltune.drop_sqlset('STS2')
drop table T1 purge;
2 réflexions sur “Comparer le contenu de 2 SQL Tuning Set avec Oracle 11g R2”
Bonjour,
En effet, il manque l’ordre « drop index » avant de créer STS2 ! Pour que la valeur de hash du plan soit différente, il faut et il suffit que le plan soit différent, y compris si l’ordre SQL est différent d’ailleurs.
On pourrait créer un outline ou une baseline SPM mais ce n’était pas l’esprit ; c’est un oubli de ma part.
Greg
Bonjour,
Je ne vois pas bien comment on obtiens une valeur hash différente
dans le cas présent.
A mon sens pour obtenir cette valeur hash il faudrait dropper l’index.
En droppant l’index lorsque j’effectue un explain plan ma valeur hash est bien différente.
Par contre lorsque je visualise les 2 SQLSET creer leur valeur hash est identique meme apres la suppression de l’index et le recalcul des stats.
Cordialement
Les commentaires sont fermés.