Home > PerformanceTesting > SQL Performance Analyzer: Compare two workloads using DBMS_SPA

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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

BigData - Oracle-info.com

Sureshgandhi's Blog : Its time to learn BigData

Oracle Alchemist

Steve Karam's Oracle Blog

ishudba

The King's .... "Joy of Life"..

the dba diary !!!

Naga's Blog : Oracle DBA experiences

ORACLE-INFO

Sureshgandhi's Blog: It's all about Oracle..Just Learn,Apply & Share.

SureshGandhi

All about Oracle.......

All about Oracle.......

DB Optimizer

database performance, SQL tuning and data visualizatoin

jarneil

The thoughts of Jason Arneil

All Things Oracle

All about Oracle.......

Dion Cho - Oracle Performance Storyteller

We are natural born scientists

All about Oracle.......

All about Oracle.......

All about Oracle.......

Miladin Modrakovic's Blog: Oraclue

Oracle internals, debugging and undocumented features

Coskan's Approach to Oracle

What I learned about Oracle

The Arup Nanda Blog

All about Oracle.......

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Martins Blog

Trying to explain complex things in simple terms

All about Oracle.......

Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

All about Oracle.......

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Iordan Iotzov's DBA blog

The opinions here may not necessarily reflect that of my employers - both past and present. The comments left by the reviewers are theirs alone and may not reflect my opinion. None of the advice is warranted to be free of errors and omissions. Please use at your own risk and after thorough testing in your environment.

Doug's Oracle Blog

All about Oracle.......

Oracle related stuff

All about Oracle.......

All about Oracle.......

Charles Hooper's Oracle Notes

Miscellaneous Random Oracle Topics: Stop, Think, ... Understand

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Oracle E-Business Suite Technology

All about Oracle.......

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

Oracle DBA - Tips and Techniques

Knowledge Is Power - Share IT ....

Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Laurent Schneider

All about Oracle.......

Kerry Osborne's Oracle Blog

All about Oracle.......

Hemant's Oracle DBA Blog

All about Oracle.......

Oracle Enterprise Manager

All about Oracle.......

Structured Data

Thoughts on: Big Data, Hadoop, Databases, Platform, Performance & Scalability

Oracle Scratchpad

Just another Oracle weblog

Karl Arao's Blog

Just another weblog about Oracle,Linux,Troubleshooting,Performance,etc..etc..

The Data Warehouse Insider

All about Oracle.......

Online Apps DBA

One Stop Shop for Apps DBA's

All about Oracle.......

Microsoft SQL Server

All about Oracle.......

Follow

Get every new post delivered to your Inbox.

Join 83 other followers

%d bloggers like this: