SQL Performance Analyzer


Virag Sharma virag123@gmail.com
Virag Blog

Home

Oracle Database 11g Top 10 New Feature for DBA

Oracle Database 11g Download


SQL Performance Analyzer

[Click Here for PDF ]

Case: - You want to adjust some parameters in production and you want to check how over all application going to run i.e. is SQL performance improved or it degrade ?

Solution: - Oracle 11g nw features SQL Performance Analyzer (SPA) analyze the SQL performance impact of any type of system changes. SQL Performance Analyzer automates the process of assessing the overall effect of a change on the full SQL workload by identifying performance divergence for each statement.

SPA report that shows the net impact on the workload performance due to the change is provided. For regressed SQL statements, SQL Performance Analyzer also provides appropriate executions plan details along with tuning recommendations. As a result, DBAs can remedy any negative outcome before their end users are affected and can validate, with significant time and cost savings, that the system change to the production environment will result in net improvement.

The steps of the SQL Performance Analyzer workflow are as follows:

1) Capture SQL (Steps on production)

exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL');
exec dbms_sqltune.create_sqlset('STS_RAG_CMUL');

DECLARE
        sts_cmul VARCHAR2(30) := 'STS_RAG_CMUL';
BEGIN
        dbms_sqltune.capture_cursor_cache_sqlset(sts_cmul,
                                                                                750000,
                                                                                1,
                                                                                'MERGE',
                                                                            dbms_sqltune.MODE_ACCUMULATE_STATS
                                                                             );
END;

capture_cursor_cache_sqlset : The procedure captures a workload from the cursor cache into a SQL tuning set, polling the cache multiple times over a time period and updating the workload data stored there. It can execute over as long a period as required to capture an entire system workload.

  • time_limit : 750000 ( The total amount of time, in seconds, to execute )
  • repeat_interval : 1 ( The amount of time, in seconds, to pause between sampling )
  • capture_option During capture, either insert new statements, update existing ones, or both.
    • 'INSERT'
    • 'UPDATE',
    • 'MERGE'
  • capture_mode capture Option (UPDATE and MERGE capture options).
    • MODE_REPLACE_OLD_STATS - Replace statistics when the number of executions seen is greater than that stored in the SQL tuning set

MODE_ACCUMULATE_STATS - Add new values to current values for SQL we already store. Note that this mode detects if a statement has been aged out, so the final value for a statistics will be the sum of the statistics of all cursors that statement existed under.

The CAPTURE_CURSOR_CACHE_SQLSET function enables the capture of the full
system workload by repeatedly polling the cursor cache over a specified interval.

This function is a lot more efficient than repeatedly using the SELECT_CURSOR_CACHE
and LOAD_SQLSET procedures to capture the cursor cache over an extended period
of time. This function effectively captures the entire workload, as SQL Profiles opposed
to the AWR—which only captures the workload of high-load SQL statements or the
LOAD_SQLSET procedure, which accesses the data source only once.

2) Move SQLs to test system

Steps on production database

Create staging table for STS and pack STS data into staging table and import the staging table

a) Create a staging table

exec dbms_sqltune.create_stgtab_sqlset('SQLSET_tab');

b) Pack the sqlset in a staging table

exec dbms_sqltune.pack_stgtab_sqlset('STS_RAG_CMUL','APPS',
'SQLSET_TAB','APPS');

c) Export the staging table

exp apps/apps file=sqlset_tab.dmp tables=sqlset_tab

Steps on test database

Import staging table and unpack staged data in STS

d) Import the staging table in the target database

imp apps/apps file=sqlset_tab.dmp full=y

e) unpack the staging table into sqlset

exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL');
exec dbms_sqltune.create_sqlset('STS_RAG_CMUL');

exec dbms_sqltune.UNPACK_STGTAB_SQLSET(
                                SQLSET_NAME=>'STS_RAG_CMUL',
                                SQLSET_OWNER=>'APPS',
                                REPLACE=>true,
                                STAGING_TABLE_NAME=>'SQLSET_TAB',
                                STAGING_SCHEMA_OWNER=>'APPS'
                                );

/

3) Create a SQL Performance Analyzer task on the test system

Create a SQL Performance Analyzer task on the test system using the SQL Tuning Set as its input source.

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

Since we processing whole system load, So TIME_LIMIT need to increase 

Exec dbms_sqltune.set_tuning_task_parameter('MY_REPLAY_TASK',
'LOCAL_TIME_LIMIT',60);

exec dbms_sqltune.set_tuning_task_parameter('MY_REPLAY_TASK',
'TIME_LIMIT',200000000000);

The time_limit parameter specifies the global time limit to process all SQL statements in a SQL Tuning Set 
before timing out.
The local_time_limit parameter specifies the time limit to process each SQL statement in a 
SQL Tuning Set before timing out.

4)Generate and store the query execution statistics before the change

Use SQL Performance Analyzer to build the pre-change performance data by executing the SQL statements stored in the SQL Tuning Set.

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

Check status of Analysis task 

SQL> col TASK_NAME format a30
SQL> col EXECUTION_NAME for a30
SQL> l
1select execution_name,
2status,
3execution_end
4from DBA_ADVISOR_EXECUTIONS
5where task_name='MY_REPLAY_TASK'
6*order by execution_end

SQL> /
EXECUTION_NAMESTATUSEXECUTION_END
--------------------------------- --------------------------------- ---------------
MY_REPLAY_EXECUTE_BEFOREEXECUTING

SQL> select TASK_ID,TASK_NAME,TASK_NAME , STATUS
FROM USER_ADVISOR_TASKS
WHERE task_name = 'MY_REPLAY_TASK';

TASK_ID TASK_NAMETASK_NAMESTATUS
---------- -------------------- -------------------- ---------------------------------
244563 MY_REPLAY_TASKMY_REPLAY_TASKEXECUTING

SQL>SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS
where TASK_ID=244563;

SOFARTOTALWORK
---------- ----------
142100630

SQL> SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS where TASK_ID=244563;

SOFARTOTALWORK
---------- ----------
100630100630

SQL>select TASK_ID,TASK_NAME,TASK_NAME , STATUS
FROM USER_ADVISOR_TASKS
WHERE task_name = 'MY_REPLAY_TASK';

TASK_ID TASK_NAMETASK_NAMESTATUS
---------- -------------------- -------------------- ---------------------------------
244563 MY_REPLAY_TASKMY_REPLAY_TASKCOMPLETED

5) Make Changes

alter system set "_b_tree_bitmap_plans" = false;

--
- More application change
- Stats collection
- etc



6) Generate and store the query execution statistics after the change.

Use SQL Performance Analyzer to build the post-change performance data by re-executing the SQL
statements in the SQL Tuning Set on the post-change test system.

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

7) Compare execution plans

Use SQL Performance Analyzer to compare and analyze the pre-change and post-change versions of 
performance data.

Begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'MY_REPLAY_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'MY_COMPARE_EXECUTION');

end;
/

8) Compare the execution changes due to your database changes

Generate a report to identify the SQL statements in the SQL workload that have improved, remained 
unchanged, or regressed after the system change. Review the report and interpret the results.
 

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

-- [ Click Here for Sample Report of above command ]

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

--Note above command will take few Min.

9) Drop Task

exec dbms_sqlpa.DROP_ANALYSIS_TASK('MY_REPLAY_TASK');



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