SQL REPLAY and SQL Performance Analyzer

By Virag virag123@gmail.com       VIRAG SHARMA BLOG

 

Home

Oracle Database 11g Top 10 New Feature for DBA

Oracle Database 11g Download

 

Oracle SQL REPLAY

 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 detail

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 your update_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 in AWR and 100 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 

declare
    l_task_id varchar2(20);
begin

l_task_id := dbms_sqltune.create_tuning_task (
                     sqlset_name => ‘MYSQLSET’,
                    task_name => 'MY_REPLAY_TASK’);

end;

/

 

  1. Collect SQL Performance data Before Changes

 

begin
    dbms_sqltune.execute_tuning_task (
                              execution_name => 'MY_REPLAY_EXECUTE_BEFORE',
                             task_name => 'MY_REPLAY_TASK');
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_sqltune.execute_tuning_task (
                           execution_name => 'MY_REPLAY_EXECUTE_AFTER',
                           task_name => 'MY_REPLAY_TASK');

end;

/

  1. Comparing SQL Performance Before and After Change

Analyze the Performance (Improvement or regressions)

begin

dbms_sqltune.execute_tuning_task (
                                    execution_name => 'MY_COMPARE_EXECUTION',
                                    task_name => 'MY_REPLAY_TASK');

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_sqltune.report_tuning_task (‘MY_REPLAY_TASK’) from dual;

 -- Note Above command will take few Min.

 

SQL Tuning Information Views

  • Advisor information views, such as DBA_ADVISOR_TASKS, DBA_ADVISOR_EXECUTIONS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, and DBA_ADVISOR_RATIONALE views.
  • SQL tuning information views, such as DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS, and DBA_SQLTUNE_PLANS views.
  • SQL Tuning Set views, such as DBA_SQLSET, DBA_SQLSET_BINDS, DBA_SQLSET_STATEMENTS, and DBA_SQLSET_REFERENCES views.
  • Information on captured execution plans for statements in SQL Tuning Sets are displayed in the DBA_SQLSET_PLANS and USER_SQLSET_PLANS 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.
     
  •  Dynamic views containing information relevant to the SQL tuning, such as V$SQL, V$SQLAREA, V$SQLSTATS, and V$SQL_BINDS views.

References

    Automatic SQL Tuning