Step by step test Sql Performance Analyzer of Real Application Test

–Test version: 11.1.0.6


–create an analysis task

–Use the following procedures to create a sql tuning set
— populate the tuning set from the cursor cache
DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => ‘my_sts’,
description  => ‘Sql tunning set for SPA’);
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
‘parsing_schema_name <> ”SYS” ‘,
NULL, NULL, NULL, NULL, 1, NULL,
‘ALL’)) P;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => ‘my_sts’,
populate_cursor => cur);
END;
/
–show sql tune set contents
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(‘my_sts’));

–create analysis task
VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => ‘my_sts’,task_name => ‘my_spa_task’);

–Also you can create a analysis task by giving sql_text
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sql_text => ‘select * from t2 where object_id=990’,task_name => ‘single_spa_task’);

–create a pre-change sql trail
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => ‘my_spa_task’, execution_type => ‘TEST EXECUTE’, execution_name => ‘my_exec_BEFORE_change’);

–perform changes on the test system
SYS@ORA11G>alter system set memory_target=140m;
T@ORA11G>drop index t2_oid;

–create a post-change sql trial
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => ‘my_spa_task’, execution_type => ‘TEST EXECUTE’, execution_name => ‘my_exec_AFTER_change’);

–compare sql trials
–analysis
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => ‘my_spa_task’, execution_type => ‘COMPARE PERFORMANCE’, execution_name => ‘my_exec_compare’, execution_params => dbms_advisor.arglist(‘execution_name1′,’my_exec_BEFORE_change’,’execution_name2′,’my_exec_AFTER_change’,’comparison_metric’, ‘buffer_gets’));

–report
VAR rep   CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(‘my_spa_task’,’text’, ‘typical’, ‘summary’);
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep

REP
————————————————————————————————————-
———-
General Information
———————————————————————————————
Task Information:                              Workload Information:
———————————————  ———————————————
Task Name    : my_spa_task                     SQL Tuning Set Name        : my_sts
Task Owner   : SYS                             SQL Tuning Set Owner       : SYS
Description  :                                 Total SQL Statement Count  : 17

Execution Information:
———————————————————————————————
Execution Name  : my_exec_compare        Started             : 12/12/2008 15:52:51
Execution Type  : COMPARE PERFORMANCE    Last Updated        : 12/12/2008 15:52:51
Description     :                        Global Time Limit   : UNLIMITED
Scope           : COMPREHENSIVE          Per-SQL Time Limit  : UNUSED
Status          : COMPLETED              Number of Errors    : 8

Analysis Information:
———————————————————————————————
Comparison Metric: BUFFER_GETS
——————
Workload Impact Threshold: 1%
————————–
SQL Impact Threshold: 1%
———————-
Before Change Execution:                       After Change Execution:
———————————————  ———————————————
Execution Name      : my_exec_BEFORE_change    Execution Name      : my_exec_AFTER_change
Execution Type      : TEST EXECUTE             Execution Type      : TEST EXECUTE
Description         :                          Description         :
Scope               : COMPREHENSIVE            Scope               : COMPREHENSIVE
Status              : COMPLETED                Status              : COMPLETED
Started             : 12/12/2008 15:43:56      Started             : 12/12/2008 15:46:02
Last Updated        : 12/12/2008 15:43:58      Last Updated        : 12/12/2008 15:46:03
Global Time Limit   : UNLIMITED                Global Time Limit   : UNLIMITED
Per-SQL Time Limit  : UNUSED                   Per-SQL Time Limit  : UNUSED
Number of Errors    : 1                        Number of Errors    : 2

Report Summary
———————————————————————————————
Projected Workload Change Impact:
——————————————-
Overall Impact      :  -3.57%
Improvement Impact  :  0%
Regression Impact   :  -3.57%

SQL Statement Count
——————————————-
SQL Category  SQL Count  Plan Change Count
Overall              17                  2
Regressed             2                  2
Unchanged             7                  0
with Errors           8                  0

Projected Workload Performance Distribution
————————————————–
——————————————————————–
|          | Cumulative Perf. |        | Cumulative Perf. |        |
| Bucket   | Before Change    | (%)    | After Change     | (%)    |
——————————————————————–
| < = 1    |                0 |     0% |                0 |     0% |
| < = 4    |                9 |   .11% |                6 |   .07% |
| < = 32   |               31 |   .38% |                0 |     0% |
| < = 256  |              328 |  4.04% |              652 |  7.75% |
| < = 8192 |             7756 | 95.47% |             7756 | 92.18% |
——————————————————————–

Single SQL Statement Execution Count Distribution
——————————————————-
————————————————————-
|          | SQL Count     |        | SQL Count    |        |
| Bucket   | Before Change | (%)    | After Change | (%)    |
————————————————————-
| < = 1    |             2 | 11.76% |            2 | 11.76% |
| < = 4    |             3 | 17.65% |            2 | 11.76% |
| < = 32   |             1 |  5.88% |            0 |     0% |
| < = 256  |             2 | 11.76% |            4 | 23.53% |
| < = 8192 |             1 |  5.88% |            1 |  5.88% |
————————————————————-

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 |
———————————————————————————————————
|        49 | 2ydjxgd87t9hk |    -1.96% |      3 |    162 |   -5300% |       .04% |      1.93% | y   |
|        47 | 94dwfa8yd87kw |    -1.61% |     31 |    162 | -422.58% |       .38% |      1.93% | y   |
———————————————————————————————————

SQL Statements with Errors Sorted by their object_id (8)
————————————————————
——————————————————————————–
| object_id | sql_id        | Error Message                                    |
——————————————————————————–
|        36 | 572674pu6mra0 | Type of SQL statement not supported.             |
|        37 | 8qhjcm5uv4j85 | Type of SQL statement not supported.             |
|        38 | 2jpf0qbj9vxq3 | Type of SQL statement not supported.             |
|        40 | g4y6nw3tts7cc | Type of SQL statement not supported.             |
|        43 | 36s7502m471xz | Error in execution ‘my_exec_AFTER_change’:       |
|           |               | ORA-01760: illegal argument for function         |
|        46 | ccuwpvmn6rp9t | Error in execution ‘my_exec_AFTER_change’:       |
|           |               | ORA-00907: missing right parenthesis             |
|        51 | bsaf69ghqw2ja | Type of SQL statement not supported.             |
|        52 | caj558xbnmytz | Type of SQL statement not supported.             |
——————————————————————————–
———————————————————————————————

–find out the sql
SYS@ORA11G>SELECT sql_text FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(‘my_sts’)) where sql_id=’2ydjxgd87t9hk’;

SQL_TEXT
———————————————————————————————————–
———-
select count(*) from t2 where object_id<1000

SYS@ORA11G>SELECT sql_text FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(‘my_sts’)) where sql_id=’94dwfa8yd87kw’;

SQL_TEXT
———————————————————————————————————
———-
select count(*) from t2

–tune regressed sql statement,reference SQL Tuning Advisor and SQL plan baselines procedures
–For sql ’94dwfa8yd87kw’, the sql plan before change is
T@ORA11G>select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
————————————————————————–
————
SQL_ID  94dwfa8yd87kw, child number 0
————————————-
select count(*) from t2

Plan hash value: 3659760998

————————————————————————
| Id  | Operation             | Name   | Rows  | Cost (%CPU)| Time     |
————————————————————————
|   0 | SELECT STATEMENT      |        |       |     9 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T2_OID | 11892 |     9   (0)| 00:00:01 |
————————————————————————

–the sql plan after change, index is deleted, is
T@ORA11G>select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
——————————————————————-
————
SQL_ID  2hkvgb0cwhvvn, child number 0
————————————-
select count(*) from t2

Plan hash value: 3321871023

——————————————————————-
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
——————————————————————-
|   0 | SELECT STATEMENT   |      |       |    48 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 11892 |    48   (0)| 00:00:01 |
——————————————————————-

–views related to SPA
DBA_ADVISOR_TASKS
DBA_ADVISOR_EXECUTIONS
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_SQLPLANS
DBA_ADVISOR_SQLSTATS
V$ADVISOR_PROGRESS

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

9 Responses to Step by step test Sql Performance Analyzer of Real Application Test

  1. я считаю: неподражаемо!

  2. neworacledba says:

    good post on performance tuning…great post

  3. dominical says:

    Hi Alex,

    Thanks for posting, I just test the SQL performance analyser testing (part of my RAT testing).
    I follow your guideline(method).
    It works, thank you very much,

    Dominica L.

  4. dominical says:

    Hi Alex,

    I have a strange question, so the SQL PERFORMANCE ANALYZER won’t actually “run” those sql statement,
    it just analyse them right?

    at first, I thought, it is just like capture and replay?

    Dominica L

  5. Alex Zeng says:

    hi Dominica,

    In my test case, execution_type => ‘TEST EXECUTE’ is used, so it will actually run the SQL statement. There are other 3 options for this parameter: EXPLAIN PLAN, COMPARE PERFORMANCE, CONVERT SQLSET. If you use EXPLAIN PLAN, it will not run the SQL.

    Alex

  6. dominical says:

    Thank you for clearing my concept. Also thanks for inviting me to your linkedin contact.

    Dominica

  7. maclean says:

    Real application testing has a so cool function!

  8. Erwin Geeraerts says:

    Hi Mr. Zeng,

    Is there any chance of comparing a trial run on a (physical) server-1 with a trial run on a (virtual) server-2, where the sql tuning set is transported from server-1 into server-2 ?

    Regards,
    Erwin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: