SQL Performance Analyzer: Compare two workloads using DBMS_SPA

Hello,

The following Sql performance analyzer procedure helps you to create a comparison report of multiple runs.

This will especially useful when there is a major change in application for example: Application release, or Database optimizer settings changes etc.

**************************************************************************************

Step 1: Adjust the snapshot collection interval accordingly to 15 mins, the default is 30 mins.

    SQL> exec dbms_workload_repository.modify_snapshot_settings (interval => 15);

**************************************************************************************

Step 2: Create a Manual snapshot and note down the snap ID

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
SQL> select max(snap_id) from dba_hist_snapshot;

**************************************************************************************

Step 3: Ask App team to run RUN the load or assuming you are testing with optimizer features enable parameter = 10.2.0.4 or 9.2.0.8

alter session set optimizer_features_enable='9.2.0.8';

and Ask to run the load

**************************************************************************************

Note:- Two choices if you want to load from Cache use Step 4a, If you want to use AWR repository for sql tuning set  use step 4b.

******************************************************************************

Step 4a: From Buffer Cache
****************************************************************************   

DECLARE
cur1 DBMS_SQLTUNE.SQLSET_CURSOR;
cur2 DBMS_SQLTUNE.SQLSET_CURSOR;
h1 NUMBER ;
v_schema_name VARCHAR2(30) := '&1' ;
v_tablespace_name VARCHAR2(30) := 'USERS'
v_table_name VARCHAR2(10) := '&2' ;
v_sts_name VARCHAR2(10) := '&3' ;
v_logfile VARCHAR2(30) ;
v_dumpfile VARCHAR2(100) ;
v_export_name VARCHAR2(30) ;
v_condition VARCHAR2(100) ;
v_beginsnap number;:= &4;
v_endsnap number;:= &5;
BEGIN
v_logfile := v_schema_name || '_' || v_sts_name || '_' || to_char(SYSDATE,'yyyymmdd') || '.dmp.log' ;
v_dumpfile := v_schema_name || '_' || v_sts_name || '_' || to_char(SYSDATE,'yyyymmdd') || '.dmp.log' ;
v_export_name := 'STS_' || v_sts_name || '_' || to_char(SYSDATE,'yyyymmdd') ;
v_condition := 'parsing_schema_name <> ' || '''' || 'SYS' || '''' || ' AND (sql_text like ' || '''' || 'SELECT%' || '''' || ' or sql_text like ' || '''' || 'select%' || '''' || ') ' ;
-- Create the sql set
DBMS_OUTPUT.PUT_LINE ('Creating SQL Tuning Set') ;

        DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => v_sts_name, sqlset_owner=> v_schema_name);
-- open cursor of our sql to load into the tuning set
OPEN cur1 FOR
SELECT VALUE(P)
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(v_condition, NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;
DBMS_OUTPUT.PUT_LINE ('Loading Buffer Cache Data into SQL Tuning Set') ;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => v_sts_name, populate_cursor => cur1, sqlset_owner=> v_schema_name);
CLOSE cur1 ;

    END ;
/

**************************************************************************************

Step 4b: From AWR :

****************************************************************************

 The following block will ask to provide
schema name -> Parsing schema name i.e App schema
Tablename -> staging tablename
Tuningset name -> name ideally "Parellel1"
beginsnap -> generated at step 1
end snap -> generated at this step.

        Take a manual snap shot again and get snap id

        SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
SQL> select max(snap_id) from dba_hist_snapshot;

        *** Run This block using Sysdba ***
        Set serveroutput on
DECLARE
cur1 DBMS_SQLTUNE.SQLSET_CURSOR;
cur2 DBMS_SQLTUNE.SQLSET_CURSOR;
h1 NUMBER ;
v_schema_name VARCHAR2(30) := '&1' ;
v_tablespace_name VARCHAR2(30) := 'USERS'
v_table_name VARCHAR2(10) := '&2' ;
v_sts_name VARCHAR2(10) := '&3' ;
v_logfile VARCHAR2(30) ;
v_dumpfile VARCHAR2(100) ;
v_export_name VARCHAR2(30) ;
v_condition VARCHAR2(100) ;
v_beginsnap number;:= &4;
v_endsnap number;:= &5;
BEGIN
v_logfile := v_schema_name || '_' || v_sts_name || '_' || to_char(SYSDATE,'yyyymmdd') || '.dmp.log' ;
v_dumpfile := v_schema_name || '_' || v_sts_name || '_' || to_char(SYSDATE,'yyyymmdd') || '.dmp.log' ;
v_export_name := 'STS_' || v_sts_name || '_' || to_char(SYSDATE,'yyyymmdd') ;
v_condition := 'parsing_schema_name <> ' || '''' || 'SYS' || '''' || ' AND (sql_text like ' || '''' || 'SELECT%' || '''' || ' or sql_text like ' || '''' || 'select%' || '''' || ') ' ;
-- Create the sql set
DBMS_OUTPUT.PUT_LINE ('Creating SQL Tuning Set') ;
DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => v_sts_name, sqlset_owner=> v_schema_name);
-- open cursor of our sql to load into the tuning set
OPEN cur1 FOR
SELECT VALUE(P)
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(v_condition, NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;
DBMS_OUTPUT.PUT_LINE ('Loading Buffer Cache Data into SQL Tuning Set') ;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => v_sts_name, populate_cursor => cur1, sqlset_owner=> v_schema_name);
CLOSE cur1 ;
OPEN cur2 FOR
SELECT VALUE(P)
FROM
table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&4,&5,v_condition,NULL, NULL,NULL,NULL,1,NULL,'ALL')) P;
DBMS_OUTPUT.PUT_LINE ('Loading AWR Data into SQL Tuning Set.') ;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => v_sts_name,
populate_cursor => cur2,
load_option => 'MERGE',
update_option => 'ACCUMULATE',
sqlset_owner=>v_schema_name) ;
CLOSE cur2 ;
END ;
/
**************************************************************************************

**************************************************************************************

Step 5: Repeat the above 4a or 4b after the change in the parameter

alter session set optimizer_features_enable='11.2.0.2';

and again ask App team to run the load, change the name of the above load run to "Parallel2"

**************************************************************************************

Step 6: Analysis the task name

    EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Parallel1', -
execution_type => 'TEST EXECUTE', -
execution_name => 'Parallel1') ;

    EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Parallel1', -
execution_type => 'TEST EXECUTE', -
execution_name => 'Parallel2') ;

**************************************************************************************

Step 7: Compare the performances (Applicable from Parallel 2 run only)

    begin
dbms_sqlpa.execute_analysis_task(
task_name => 'Parallel2',
execution_type => 'compare performance',
execution_name => 'analysis_results',
execution_params => dbms_advisor.arglist('Parallel1','1stRUN','Parallel2', '2ndRUN'));
end ;
/

**************************************************************************************

Step 8: Report

    spool SPA_COMPARE_REPORT.out
-- Get the whole report for the single statement case.
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Parallel2') from dual;
-- Show me the summary for the sts case.
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Parallel2', 'TEXT', 'TYPICAL', 'SUMMARY')
FROM DUAL;
-- Show me the findings for the statement I'm interested in.
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Parallel2', 'TEXT', 'TYPICAL', 'FINDINGS', 5) from dual;
spool off

**************************************************************************************

Hope this helps.