Oracle Technical Track

Oracle 11g's SQL Performance Analyzer

by Pythian Marketing
5 min read
Aug 16, 2007 12:00:00 AM
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):

SQLPA Report

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?
On this page
Tuning Pack 11g : Real-Time SQL Monitoring

Tuning Pack 11g : Real-Time SQL Monitoring

Aug 14, 2007 12:00:00 AM 4 min read
Case Study: How to Return a Good SQL Execution Plan from 10g Days After an 11g Migration
MySQL

Case Study: How to Return a Good SQL Execution Plan from 10g Days After an 11g Migration

Nov 12, 2012 12:00:00 AM 3 min read
Yury, Remember: This is How You Disable AUTO_TASKs in 11G
Oracle

Yury, Remember: This is How You Disable AUTO_TASKs in 11G

Nov 11, 2012 12:00:00 AM 3 min read

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

Follow Us     Pythian-LinkedIn   Pythian-Twitter   Pythian-FB

 

Pythian-logo-White

© 2025 All rights reserved.