Sunday, August 12, 2007

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

  1. 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;
/

  1. 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

  1. 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;
/

  1. 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;

  1. 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

Google