Oracle 11g’s SQL Performance Analyzer

A lot of things have already been said about the Real Application Testing Option — from the price to the most interesting technical details — by Ken Jacobs, Arup Nanda, and even by me. Why add something? Because while Database Replay gets most people’s attention, Real Application Testing offers another interesting feature called SQL Performance Analyzer (SQLPA). So what is it?

The DBMS_SQLPA package enables you to register and compare the statistics of several SQL query executions stored in an SQL Tuning Set (STS). With SQL Performance Analyzer, you can compare the executions of queries before and after you make some changes to your database. As you might guess, I’m going to illustrate this new feature in Oracle 11g with a simple example.

1. Create a sample table and its data

You’ll need to create a table and add a couple of rows for what comes next. The script below does this:

create table gark
  (id number not null);

begin
for i in 1..10000 loop
insert into gark(id)
   values (i);
end loop;
commit;
end;
/

2. Run a query and look at its plan

The query below demonstrates the point. Run it from the SQL*Plus command line:

set serveroutput off

col id format 99999
select a.id, b.id
  from gark a, gark b
 where a.id=b.id
   and b.id=500;

select * from table(
   dbms_xplan.display_cursor);

Here is the plan. Keep the SQL ID to add to the query in the SQL Tuning Set in the next section:

-------------------------------------
SQL_ID  683kdkrs2dmrk, child number 0
-------------------------------------
select a.id, b.id   from gark a, gark b  where a.id=b.id
and b.id=500

Plan hash value: 2625395012

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    15 (100)|
|*  1 |  HASH JOIN         |      |     1 |    26 |    15   (7)|
|*  2 |   TABLE ACCESS FULL| GARK |     1 |    13 |     7   (0)|
|*  3 |   TABLE ACCESS FULL| GARK |     1 |    13 |     7   (0)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
2 - filter("A"."ID"=500)
3 - filter("B"."ID"=500)

Note
-----
- dynamic sampling used for this statement

3. Capture the query in an SQL Tuning Set

Once the query is in the Shared Pool, you can create a new SQL Tuning Set and add the query to it:

• Firstly, create a new STS:

  BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
      sqlset_name => 'gark_sts',
      description => 'STS for SPA demo');
  END;
  /

• Secondly, add the query to the STS by querying the Cursor Cache:

  accept sql_id prompt "Enter value for sql_id: "
  683kdkrs2dmrk
  
  DECLARE
    l_cursor  DBMS_SQLTUNE.sqlset_cursor;
  BEGIN
    OPEN l_cursor FOR
        SELECT VALUE(p)
          FROM   TABLE (
             DBMS_SQLTUNE.select_cursor_cache (
               'sql_id = ''&sql_id''', -- basic_filter
               NULL, -- object_filter
               NULL, -- ranking_measure1
               NULL, -- ranking_measure2
               NULL, -- ranking_measure3
               NULL, -- result_percentage
               1)    -- result_limit
          ) p;
    DBMS_SQLTUNE.load_sqlset (
        sqlset_name    => 'gark_sts',
        populate_cursor => l_cursor);
  END;
  /
  
  PL/SQL procedure successfully completed.

• Finally, you can query the STS content to make sure it is properly registered:

  col sql format a50
  set lines 120
  SELECT sql_id,
         substr(sql_text, 1, 50) sql
    FROM TABLE(
        DBMS_SQLTUNE.select_sqlset (
          'gark_sts'));
  SQL_ID        SQL
  ------------- ---------------------
  683kdkrs2dmrk select a.id, b.id
                  from gark a, gark b
                 where a.i

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

This step can take a while. It runs the queries from the STS and stores their execution statistics. In order to do that, you must:

• Create a SQLPA analysis task that reference the STS:

var v_out char(50)
begin
  :v_out:=dbms_sqlpa.create_analysis_task(
              sqlset_name => 'gark_sts',
              task_name   => 'gark_spa_task');
end;
/
print v_out

V_OUT
-------------
gark_spa_task

• Check the task has been created:

col TASK_NAME format a14
col ADVISOR_NAME format a24
select TASK_NAME,
       ADVISOR_NAME,
       created
  from DBA_ADVISOR_TASKS
 where task_name='gark_spa_task';

TASK_NAME      ADVISOR_NAME             CREATED
-------------- ------------------------ ---------
gark_spa_task  SQL Performance Analyzer 15-AUG-07

• Run the SQLPA Analysis Task:

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

• Monitor the task and its status until it is completed:

col TASK_NAME format a20
select execution_name,
       status,
       execution_end
  from DBA_ADVISOR_EXECUTIONS
 where task_name='gark_spa_task'
 order by execution_end;

EXECUTION_NAME                 STATUS      EXECUTION
------------------------------ ----------- ---------
gark_spa_task_before           COMPLETED   15-AUG-07

5. Perform the changes.

I’m not very proud of this change, but lets create an index on the GARK table.

create unique index
    gark_idx on gark(id);

Index created.

6. Run the SQLPA analysis task after the changes

The script is similar from the previous run. You just have to change the name to differentiate execution statistics before and after the changes:

• Run the SQLPA Analysis Task:

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

• Monitor the task and its status until it is completed:

col TASK_NAME format a20
select execution_name,
       status,
       execution_end
  from DBA_ADVISOR_EXECUTIONS
 where task_name='gark_spa_task'
 order by execution_end;

EXECUTION_NAME                 STATUS      EXECUTION
------------------------------ ----------- ---------
gark_spa_task_before           COMPLETED   15-AUG-07
gark_spa_task_after            COMPLETED   15-AUG-07

7. Compare the execution changes due to your database changes

You’ll run the analysis task once again. This time the Analyzer will compare and store the result of this comparison:

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
   task_name        => 'gark_spa_task',
   execution_type   => 'COMPARE PERFORMANCE',
   execution_name   => 'gark_spa_task_compare',
   execution_params => dbms_advisor.arglist(
         'comparison_metric',
         'buffer_gets'));
end;
/
PL/SQL procedure successfully completed.

Once done, you can print a report of this analysis with the REPORT_ANALYSIS_TASK function as below:

variable rep CLOB;
begin
  :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
              task_name=>'gark_spa_task',
              type=>'HTML',
              level=>'ALL',
              section=>'ALL');
end;
/

SET LONG 100000
set LONGCHUNKSIZE 100000
set LINESIZE 200
set head off
set feedback off
set echo off
spool sts_changes.html
PRINT :rep
spool off
set head on

My tests suggest that the SECTION parameter must contain SUMMARY or ALL and not SECTION_ALL as quoted in the DBMS_SQLPA documentation.

You can then view the result of your report from SQL*Plus (text output) or from a web browser (HTML output):

You’ll find the full demo report here.

8. Another way to display the comparison result:

Instead of printing the report, you can query the result from the following views:

• DBA_ADVISOR_FINDINGS
• DBA_ADVISOR_SQLPLANS
• DBA_ADVISOR_SQLSTATS

9. Drop execution statistics, analysis tasks and the table

You can reset the task results:

begin
  dbms_sqlpa.reset_analysis_task(task_name=>'gark_spa_task');
end;
/
col TASK_NAME format a20
select execution_name,
       status,
       execution_end
  from DBA_ADVISOR_EXECUTIONS
 where task_name='gark_spa_task'
 order by execution_end;

no rows selected

As well as the task itself:

begin
 dbms_sqlpa.drop_analysis_task(task_name=>'gark_spa_task');
end;
/

col TASK_NAME format a14
col ADVISOR_NAME format a24
select TASK_NAME,
       ADVISOR_NAME,
       created
  from DBA_ADVISOR_TASKS
 where task_name='gark_spa_task';

no rows selected

Note that you can also:

• cancel a running analysis task with the cancel_analysis_task procedure
• interrupt a running analysis task with the interrupt_analysis_task procedure
• resume an interrupted analysis task with the resume_analysis_task procedure

To clean the table and index we’ve used for this demo:

drop table gark cascade constraints purge;

10. Conclusion

This demonstration is very basic, but running the task on a test database with hundreds or thousands of queries won’t really be more complex — it simply might take more time.

SQL Performance Analyzer is very different from Database Replay. I’ll let you draw your own conclusions about the pros and cons:

• You can easily capture the STS queries from AWR.
• You don’t need to rebuild the test system (only the “SELECT” part of the INSERT/UPDATE/DELETE is executed).
• The queries in a STS are only a sample of a real application workload.

For more details, see:

• Oracle 11g Performance Tuning Guide – 23.SQL Performance Analyzer
• Oracle 11g PL/SQL Types and Packages Reference – DBMS_SQLPA
• Oracle 11g Reference

So the good news is, it works pretty well, and my question is, would you buy it?