A taste of SQL Performance Analyzer in oracle 11g

we got a case when trying to change a table property, before we need to do thorough testing by collecting all related sql from production, then simulate the sql and test the performance. In 11g, it can be handled by oracle engine

[@more@]

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> show user
USER is "SZHAO"
SQL> create table my_tests as
2 select * from sys.dba_objects;

Table created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'MY_tests', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM my_tests WHERE object_id <= 100;
SQL> SELECT object_name FROM my_tests WHERE object_id = 100;
SQL> SELECT COUNT(*) FROM my_tests WHERE object_id <= 1000;
SQL> SELECT object_name FROM my_tests WHERE object_id = 1000;

COUNT(*)
----------
99

SQL> SELECT COUNT(*) FROM my_tests WHERE object_id BETWEEN 100 AND 1000;
OBJECT_NAME
--------------------------------------------------------------------------------
FIXED_OBJ$

SQL>
COUNT(*)
----------
941

SQL>
OBJECT_NAME
--------------------------------------------------------------------------------
EXTERNAL_LOCATION$

SQL>

COUNT(*)
----------
843

SQL> conn / as sysdba
Connected.

#create an SQL tuning set called szhao_my_tests

SQL> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'szhao_my_tests');

PL/SQL procedure successfully completed.

#retrieve a cursor containing all SQL statements by szhao

SQL> DECLARE
2 l_cursor DBMS_SQLTUNE.sqlset_cursor;
3 BEGIN
4 OPEN l_cursor FOR
5 SELECT VALUE(a)
6 FROM TABLE(
7 DBMS_SQLTUNE.select_cursor_cache(
8 basic_filter => 'sql_text LIKE ''%my_tests%'' and parsing_schema_name = ''SZHAO''',
9 attribute_list => 'ALL')
10 ) a;
11
12
13 DBMS_SQLTUNE.load_sqlset(sqlset_name => 'szhao_my_tests',
14 populate_cursor => l_cursor);
15 END;
16 /

PL/SQL procedure successfully completed.

# check with SQLs have been associated with the tuning set.

SQL> SELECT sql_text
SQL> FROM dba_sqlset_statements
SQL> WHERE sqlset_name = 'szhao_my_tests';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT object_name FROM my_tests WHERE object_id = 100
create table my_tests as
select * from sys.dba_objects

SELECT COUNT(*) FROM my_tests WHERE object_id <= 100
SELECT object_name FROM my_tests WHERE object_id = 1000
SELECT COUNT(*) FROM my_tests WHERE object_id BETWEEN 100 AND 1000
SELECT COUNT(*) FROM my_tests WHERE object_id <= 1000

6 rows selected.

# create an analysis task by giving tuning set name and return task name

SQL> VARIABLE v_task VARCHAR2(64);
EXEC :v_task := DBMS_SQLPA.create_analysis_task(sqlset_name => 'szhao_my_tests');SQL>

PL/SQL procedure successfully completed.

SQL> PRINT :v_task

V_TASK
--------------------------------------------------------------------------------
TASK_351

# execute the contents of the SQL tuning set

SQL>
BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :v_task,
execution_type => 'test execute',
execution_name => 'before_change');
END;
SQL> 2 3 4 5 6 7
8 /

PL/SQL procedure successfully completed.

SQL> conn szhao/szhao***
Connected.
SQL> CREATE INDEX my_tests_index_01 ON my_tests(object_id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'MY_TESTS', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> conn / as sysdba
Connected.
SQL> PRINT :v_task

V_TASK
--------------------------------------------------------------------------------
TASK_351

# execute the contents of the SQL tuning set again with different execution name

SQL> BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :v_task,
execution_type => 'test execute',
execution_name => 'after_change');
END;
/
2 3 4 5 6 7
PL/SQL procedure successfully completed.

# run a comparison analysis task

SQL> BEGIN
2 DBMS_SQLPA.execute_analysis_task(
3 task_name => :v_task,
4 execution_type => 'compare performance',
5 execution_params => dbms_advisor.arglist(
6 'execution_name1',
7 'before_change',
8 'execution_name2',
9 'after_change')
);
10 11 END;
12 /

PL/SQL procedure successfully completed.

# produce the comparison report

SQL> SET LONG 1000000
SET PAGESIZE 0
SET LINESIZE 200
SET LONGCHUNKSIZE 200
SET TRIMSPOOL ON

SPOOL /tmp/my_comp_report.txt

SELECT DBMS_SQLPA.report_analysis_task(:v_task)
FROM dual;

SPOOL OFF

$> cat my_comp_report.txt

SQL> SELECT DBMS_SQLPA.report_analysis_task(:v_task)
2 FROM dual;
General Information
---------------------------------------------------------------------------------------------

Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : TASK_351 SQL Tuning Set Name : szhao_my_tests
Task Owner : SYS SQL Tuning Set Owner : SYS
Description : Total SQL Statement Count : 6

Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : EXEC_351 Started : 08/01/2008 12:24:12
Execution Type : COMPARE PERFORMANCE Last Updated : 08/01/2008 12:24:13
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 1

Analysis Information:
---------------------------------------------------------------------------------------------
Comparison Metric: ELAPSED_TIME
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ---------------------------------------------
Execution Name : before_change Execution Name : after_change
Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE
Description : Description :
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLETED
Started : 08/01/2008 12:21:23 Started : 08/01/2008 12:23:26
Last Updated : 08/01/2008 12:21:23 Last Updated : 08/01/2008 12:23:26
Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED
Number of Errors : 0 Number of Errors : 0

Report Summary
---------------------------------------------------------------------------------------------

Projected Workload Change Impact:
-------------------------------------------
Overall Impact : 100%
Improvement Impact : 100%
Regression Impact : 0%

SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall 6 5
Improved 5 5
with Errors 1 0

Projected Workload Performance Distribution
--------------------------------------------------
-------------------------------------------------------------
| | Cumulative Perf. | | Cumulative Perf. | |
| Bucket | Before Change | (%) | After Change | (%) |
-------------------------------------------------------------
| < = 1 | 0 | 0% | 0 | 0% |
| < = 16 | 51 | 100% | 0 | 0% |
-------------------------------------------------------------

Single SQL Statement Execution Count Distribution
-------------------------------------------------------
-----------------------------------------------------------
| | SQL Count | | SQL Count | |
| Bucket | Before Change | (%) | After Change | (%) |
-----------------------------------------------------------
| < = 1 | 0 | 0% | 5 | 83.33% |
| < = 16 | 5 | 83.33% | 0 | 0% |
-----------------------------------------------------------

SQL Statements Sorted by their Absolute Value of Change Impact on the Workload
--------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
| | | Impact on | Metric | Metric | Impact | % Workload | % Workload | Plan |
| object_id | sql_id | Workload | Before | After | on SQL | Before | After | Change |
-------------------------------------------------------------------------------------------------------
| 15 | 3cnja27ruac9z | 25.49% | 13 | 0 | 100% | 25.49% | 0% | y |
| 16 | c1bsa5kvqq6n5 | 19.61% | 10 | 0 | 100% | 19.61% | 0% | y |
| 17 | g2h665m38xg2x | 19.61% | 10 | 0 | 100% | 19.61% | 0% | y |
| 18 | 1f27fs929zvta | 17.65% | 9 | 0 | 100% | 17.65% | 0% | y |
| 19 | 5u25ukujcypgc | 17.65% | 9 | 0 | 100% | 17.65% | 0% | y |
-------------------------------------------------------------------------------------------------------

SQL Statements with Errors Sorted by their object_id (1)
------------------------------------------------------------
--------------------------------------------------------------------------------
| object_id | sql_id | Error Message |
--------------------------------------------------------------------------------
| 14 | 2j8uqymqntssd | Type of SQL statement not supported. |
--------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

SQL>
SQL> SPOOL OFF

ignore sql:

SQL> select sql_text from V$sql where sql_id='2j8uqymqntssd';

SQL_TEXT
--------------------------------------------------------------------------------
create table my_tests as select * from sys.dba_objects