Oracle SQL REPLAY & Sql Performance Analyzer(SPA) Part 1

Oracle SQL REPLAY Part 1
Virag Sharma virag123@gmail.com

You can use Sql Performance Analyzer (SPA) to analyze the SQL performance impact of any type of system changes.

• Implementation of tuning recommendations ( like parameter changes )
• Schema changes (eg Application Patch )
• Statistics gathering
• Database upgrades
• OS/hardware changes

SQL Performance Analyzer allows for the comparison of SQL performance statistics before and after changes and provide comparison report.

Steps for SQL REPLAY

1. Collect Sqls
2. Create the SQL Replay Task with SQL Tuning Set (STS)
3. Collect SQL Performance Before Changes
4. Make Database Change ( eg collect stats etc)
5. Collect SQL Performance After Changes
6. Comparing SQL Performance Before and After Change
7. Display the Results of a SQL Replay Task

   Steps in details

1. Collect SQL

To create an SQL Tuning Set (STS)

exec dbms_sqltune.create_sqlset (‘MYSQLSET’); PL/SQL procedure successfully completed.

Collect sqls in the SQL Tuning Set(STS) with all queries from the cursor cache.
You can call the procedure multiple times to add new SQL statements or replace
attributes of existing statements.

DECLARE cur dbms_sqltune.sqlset_cursor; BEGIN OPEN cur FOR SELECT VALUE(p) FROM TABLE (dbms_sqltune.select_cursor_cache) p; dbms_sqltune.load_sqlset( sqlset_name => ‘MYSQLSET’, populate_cursor => cur); END; /

Collect sql mentioned in AWR as well , You populate the tuning set with ‘ACCUMULATE’ as yourupdate_option because it is assumed the cursors currently in the cache had aged out since the snapshot was taken.

DECLARE cur DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1,100) P; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => ‘MYSQLSET’, populate_cursor => cur, load_option => ‘MERGE’, update_option => ‘ACCUMULATE’); END; Note 1 is start snap id and 100 is END snap ID

2. Create the SQL Replay Task with SQL Tuning Set (STS)

Create the SQL Replay Task with SQL Tuning Set(STS) named MYSQLSET

var l_task_id char(30) begin :l_task_id:=dbms_sqlpa.create_analysis_task( sqlset_name => ‘MYSQLSET’, task_name => ‘MY_REPLAY_TASK’); end; /

Collect SQL Performance data Before Changes

begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => ‘MY_REPLAY_TASK’, execution_type => ‘TEST EXECUTE’, execution_name => ‘MY_REPLAY_EXECUTE_BEFORE’); end; /

4. Make Database Change ( eg collect stats etc)

Make application , database changes

alter system set “_b_tree_bitmap_plans” = false; alter system set optimizer_index_cost_adj=15

5. Collect SQL Performance After Changes

begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => ‘MY_REPLAY_TASK’, execution_type => ‘TEST EXECUTE’, execution_name => ‘MY_REPLAY_EXECUTE_after’); end; /

6. Comparing SQL Performance Before and After Change

Analyze the Performance (Improvement or regressions)

begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => ‘MY_REPLAY_TASK’, execution_type => ‘COMPARE PERFORMANCE’, execution_name => ‘MY_COMPARE_EXECUTION’); end;

7. Display the Results of a SQL Replay Task

– Checking the Status of a SQL Tuning Task SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'MY_REPLAY_TASK' – Checking the Progress of the SQL Tuning Advisor SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE task_name = 'MY_REPLAY_TASK' –Displaying the Results of a SQL Tuning Task set serveroutput on size 999999 set long 999999 select DBMS_SQLPA.REPORT_ANALYSIS_TASK(‘MY_REPLAY_TASK’) from dual; –Note above command will take few Min. OR VAR rep CLOB; EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(‘MY_REPLAY_TASK’, - ‘text’, ‘typical’, ’summary’); SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130 PRINT :rep

- Sample Output of report Projected Workload Change Impact: ——————————————- Overall Impact : 92.33% REP ———————————————— Improvement Impact : 92.33% Regression Impact : 0% SQL Statement Count ——————————————- SQL Category SQL Count Plan Change Count Overall 3813 0 Improved 2 0 Unchanged 3775 0 with Errors 36 0

In this article Sql Performance Analyzer Part 1,we just covered Sql Performance Analyzer (SPA) basic functionality , In part 2 I will cover more expects and case study of SPA

Some more article on 11g 

1. Oracle 11g TOP feature for DBA
2. 11g Automatic Diagnostic Repository (ADR)
3. Sql Performance Analyzer (SPA) Part 1
4. Sql Performance Analyzer (SPA) Part 2

SQL Tuning Information Views

· Advisor views,


• DBA_ADVISOR_TASK
• DBA_ADVISOR_EXECUTIONS,
• DBA_ADVISOR_FINDINGS
• DBA_ADVISOR_RECOMMENDATIONS
• DBA_ADVISOR_RATIONALE views

· SQL tuning views,

• DBA_SQLTUNE_STATISTICS
• DBA_SQLTUNE_BINDS, and
• DBA_SQLTUNE_PLANS views

· SQL Tuning Set views,

• DBA_SQLSET,
• DBA_SQLSET_BINDS,
• DBA_SQLSET_STATEMENTS,
• DBA_SQLSET_REFERENCES views.

· Captured execution plans for statements in SQL Tuning Sets

• DBA_SQLSET_PLANS
• USER_SQLSET_PLANS

· SQL tuning view,

• V$SQL,
• V$SQLAREA,
• V$SQLSTATS,
• V$SQL_BINDS views

· SQL Profile information is displayed in the DBA_SQL_PROFILES view.
The TYPE parameter shows if the SQL profile was created manually by the SQL Tuning Advisor (if TYPE = MANUAL) or automatically by automatic SQL tuning (if TYPE = AUTO).

· Advisor execution progress information is displayed in the V$ADVISOR_PROGRESS view.

References

Automatic SQL Tuning

• Oracle 11g Performance Tuning Guide – 23.SQL Performance Analyzer
• Oracle 11g PL/SQL Types and Packages Reference – DBMS_SQLPA
• Oracle 11g Reference
  • DBA_ADVISOR_TASKS
  • DBA_ADVISOR_EXECUTIONS
  • DBA_ADVISOR_LOG
  • DBA_ADVISOR_FINDINGS
  • DBA_ADVISOR_SQLPLANS
  • DBA_ADVISOR_SQLSTATS

The views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine