SQL Performance Analyzer, Step 2 Create Analysis Task, Make the Changes and Review Impact

I started this with the goal of being able to measure the impact of partitioning a table or set of tables.  The first Step is to get all the SQL I can from the system that executes against the table(s) that are intended to be partitioned so that we can ensure that we make a positive impact on the SQL and ensure that we are not going to make a negative impact on any existing SQL.

In this case I am choosing to use SQL Performance Analyzer to measure the impact on the SQL I gathered into my SQL Tuning Set  from

** Keep in mind I am using a test environment where I can capture the SQL, execute and measure my changes all in the same database, in same cases you may need to capture the SQL tuning set in production and then transfer the SQL Tuning Set to a test environment to execute the analysis make the changes and then make the comparison.  See Transfer SQL Tuning Sets between Databases post from October 2010.

Create an Analysis Task

In this case I will be using the dbms_sqlpa package using the create_analysis_task which will allow me to analyze the SQL in my SQL Tuning Set which is the SQL I captured that executes against the table(s) I am planning on partitioning.

dbms_sqlpa.create_analysis_task(sqlset_name => ‘STS_SPA_1‘,

task_name => ‘sts_spa1_task‘,

description => ‘test partitioning‘);

Execute the Analysis Task

Once the Analysis Task has been created we will need to execute the task to create the baseline that will be used to compare against after we make the changes.  We will be using the dbms_sqlpa package and the execute_analysis_task procedure.

dbms_sqlpa.execute_analysis_task(task_name => ‘STS_SPA1_task‘,

execution_type => ‘test execute‘,

execution_name => ‘before_partition_change‘);

Once the execution of the analysis task is complete we can make our changes.  Once the change has been made then we will need to execute another analysis task to get the information to compare against the base after the change.

dbms_sqlpa.execute_analysis_task(task_name => ‘STS_SPA1_task‘,

execution_type => ‘test execute‘,

execution_name => ‘after_partition_change‘);

Compare Analysis Task
Once the initial analysis task is done, the change is made the post analysis task is done we are read yo compare the initial to the after.  Again we will use the dbms_sqlpa package, this time we will use the execute_analysis task again but will set the analysis to compare performance.

dbms_sqlpa.execute_analysis_task(

task_name => ‘STS_SPA1_task‘,

execution_type => ‘compare performance‘,

execution_name => ‘analysis_results‘,

execution_params => dbms_advisor.arglist(‘execution_name1‘,‘before_partition_change‘,

‘execution_name2‘, ‘after_partition_change‘,

‘comparison_metric‘,‘buffer_gets‘));

Run Compare Report
Once the Compare Analysis is complete we can run a report that will show us the results of the compare analysis.  The follow short script will output to a file called SPA_COMPARE_REPORT.out 3 possible reports, the first being the full analysis report, the second being a summary report and the 3 being like a top offenders report called a finding report from the analysis.

---------------------------------------------------------------------------

-- Script: spa_compare.sql

-- Author; Michael R. Messina, Managing Consultant 

-- Rolta-TUSC, Infrastructure Services

--

-- Executes a SQL Tuning Set Comparison and then outputs a report

-- from Performance Analyzer. 

---------------------------------------------------------------------------

spool SPA_COMPARE_REPORT.out

-- Get the whole report for the single statement case.

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('STS_SPA1_task') from dual;

-- Show me the summary for the sts case.

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('STS_SPA1_task', 'TEXT', 'TYPICAL', 'SUMMARY')

FROM DUAL;

-- Show me the findings for the statement I'm interested in.

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('STS_SPA1_task', 'TEXT', 'TYPICAL', 'FINDINGS', 5) from dual;

spool off