Wednesday, November 14, 2012

SQL Performance Analyzer (SPA)


SQL Performance Analyzer:-
=========================
Before you make any change on the database (hardware, init parameters, indexes , Mviews etc), we
can use the SPA to find out how much the change will impact (improve / reduce) the sql performance.

Here we use the sql tunings sets with SPA as an example.


Test Case:-
==========

SQL> create user test_user identified by test default tablespace ahamed_tblspc temporary tablespace temp1;

User created.

SQL> ALTER USER test_user QUOTA UNLIMITED ON ahamed_tblspc;

User altered.

SQL> grant dba to test_user;

Grant succeeded.

SQL> conn test_user
Enter password:
Connected.


SQL> create table test_object as select * from dba_objects;

Table created.

SQL> SELECT COUNT(*) FROM TEST_OBJECT;

  COUNT(*)
----------
    575768

SQL> exec dbms_stats.gather_table_stats('TEST_USER','TEST_OBJECT');

PL/SQL procedure successfully completed.


SQL> set timing on;

SQL> select count(*) from test_object where object_id = 2000;

  COUNT(*)
----------
         8

Elapsed: 00:00:01.17
SQL> select count(*) from test_object where object_id between 1000 and 2000;

  COUNT(*)
----------
      7920

Elapsed: 00:00:01.20
SQL> select count(*) from test_object where object_id > 3000;

  COUNT(*)
----------
    552312

Elapsed: 00:00:01.15
SQL> select count(*) from test_object where object_id < 3000;

  COUNT(*)
----------
     23448

Elapsed: 00:00:01.21


The above query took 1.21 seconds for its execution lets

We are using the object_id column in the where clause, all the above queries
will be using the full table scan since we dont have the index on the object_id
column.

Now lets use the SPA to analyze how much the sql performance will
get increased if we use an index on the object_id column.


Lets create the sqlsets using dbms_sqltune package.


PROCEDURE CREATE_SQLSET
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQLSET_NAME                    VARCHAR2                IN
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 SQLSET_OWNER                   VARCHAR2                IN     DEFAULT


SQL> exec dbms_sqltune.create_sqlset (sqlset_name => 'tuning_test_object', description => 'For testing SPA');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.43


Now using the select_cursor_cache function we can retrive the sql which been parsed by
the test_user and then using the load_sqlset procedure we need to load the sqls to the sqlset tuning_test_object.


FUNCTION SELECT_CURSOR_CACHE RETURNS SQLSET
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BASIC_FILTER                   VARCHAR2                IN     DEFAULT
 OBJECT_FILTER                  VARCHAR2                IN     DEFAULT
 RANKING_MEASURE1               VARCHAR2                IN     DEFAULT
 RANKING_MEASURE2               VARCHAR2                IN     DEFAULT
 RANKING_MEASURE3               VARCHAR2                IN     DEFAULT
 RESULT_PERCENTAGE              NUMBER                  IN     DEFAULT
 RESULT_LIMIT                   NUMBER                  IN     DEFAULT
 ATTRIBUTE_LIST                 VARCHAR2                IN     DEFAULT


PROCEDURE LOAD_SQLSET
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQLSET_NAME                    VARCHAR2                IN
 POPULATE_CURSOR                REF CURSOR              IN
 LOAD_OPTION                    VARCHAR2                IN     DEFAULT
 UPDATE_OPTION                  VARCHAR2                IN     DEFAULT
 UPDATE_CONDITION               VARCHAR2                IN     DEFAULT
 UPDATE_ATTRIBUTES              VARCHAR2                IN     DEFAULT
 IGNORE_NULL                    BOOLEAN                 IN     DEFAULT
 COMMIT_ROWS                    BINARY_INTEGER          IN     DEFAULT
 SQLSET_OWNER                   VARCHAR2                IN     DEFAULT



declare
sql_cur dbms_sqltune.sqlset_cursor;
begin
open sql_cur for
select value(a) from table
(
dbms_sqltune.select_cursor_cache
(
basic_filter => 'sql_text like ''%test_object where object_id%'' and parsing_schema_name = ''TEST_USER''',
attribute_list  => 'ALL'
)
) a;
dbms_sqltune.load_sqlset
(
sqlset_name => 'tuning_test_object',
populate_cursor => sql_cur
);

end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.69


Now we can view the dba_sqlset_statements view to find out the sql's which
are loaded in the sqlset.


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

SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from test_object where object_id = 2000
select count(*) from test_object where object_id > 3000
select count(*) from test_object where object_id < 3000
 SELECT /*+ first_rows(1) */ sql_id, force_matching_signature, sql_text, cast(NU
SELECT VALUE(A) FROM TABLE ( DBMS_SQLTUNE.SELECT_CURSOR_CACHE ( BASIC_FILTER =>
select count(*) from test_object where object_id between 1000 and 2000

6 rows selected.

Elapsed: 00:00:00.17

Now we can start using the SPA. Package name dbms_sqlpa.

1) Create the analysis task.


FUNCTION CREATE_ANALYSIS_TASK RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQLSET_NAME                    VARCHAR2                IN     DEFAULT
 BASIC_FILTER                   VARCHAR2                IN     DEFAULT
 ORDER_BY                       VARCHAR2                IN     DEFAULT
 TOP_SQL                        NUMBER                  IN     DEFAULT
 TASK_NAME                      VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 SQLSET_OWNER                   VARCHAR2                IN     DEFAULT



SQL> variable v_task varchar2(64);
SQL> exec :v_task := dbms_sqlpa.create_analysis_task(sqlset_name => 'tuning_test_object', sqlset_owner => 'TEST_USER');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.24
SQL> print :v_task;

V_TASK
--------------------------------------------------------------------------------
TASK_127


2) Execute the analysis task.

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :v_task,
    execution_type  => 'test execute',
    execution_name  => 'before_change');
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.59


SQL> conn test_user/test
Connected.

SQL> create index idx_obj_id on test_object (object_id);

Index created.

Elapsed: 00:00:01.82

SQL> EXEC DBMS_STATS.gather_table_stats('TEST_USER','TEST_OBJECT',CASCADE=> true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.26

SQL> conn / as sysdba
Connected.

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :v_task,
    execution_type  => 'test execute',
    execution_name  => 'after_change');
END;
/

Now run the comparison analysis task

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name        => :v_task,
    execution_type   => 'compare performance',
    execution_params => dbms_advisor.arglist(
                          'execution_name1',
                          'before_change',
                          'execution_name2',
                          'after_change')
    );
END;
/


FUNCTION REPORT_ANALYSIS_TASK RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TASK_NAME                      VARCHAR2                IN
 TYPE                           VARCHAR2                IN     DEFAULT
 LEVEL                          VARCHAR2                IN     DEFAULT
 SECTION                        VARCHAR2                IN     DEFAULT
 OBJECT_ID                      NUMBER                  IN     DEFAULT
 TOP_SQL                        NUMBER                  IN     DEFAULT
 EXECUTION_NAME                 VARCHAR2                IN     DEFAULT
 TASK_OWNER                     VARCHAR2                IN     DEFAULT
 ORDER_BY                       VARCHAR2                IN     DEFAULT




SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON


SQL> SELECT DBMS_SQLPA.report_analysis_task('TASK_127','TEXT','ALL') from dual;
General Information
---------------------------------------------------------------------------------------------

 Task Information:                              Workload Information:
 ---------------------------------------------  ---------------------------------------------
  Task Name    : TASK_127                        SQL Tuning Set Name        : tuning_test_object
  Task Owner   : SYS                             SQL Tuning Set Owner       : TEST_USER
  Description  :                                 Total SQL Statement Count  : 6

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name  : EXEC_118               Started             : 11/15/2012 08:44:20
  Execution Type  : COMPARE PERFORMANCE    Last Updated        : 11/15/2012 08:44:21
  Description     :                        Global Time Limit   : UNLIMITED
  Scope           : COMPREHENSIVE          Per-SQL Time Limit  : UNUSED
  Status          : COMPLETED              Number of Errors    : 0

Analysis Information:
---------------------------------------------------------------------------------------------
 Before Change Execution:                       After Change Execution:
 ---------------------------------------------  ---------------------------------------------
  Execution Name      : before_change            Execution Name      : after_change
  Execution Type      : TEST EXECUTE             Execution Type      : TEST EXECUTE
  Scope               : COMPREHENSIVE            Scope               : COMPREHENSIVE
  Status              : COMPLETED                Status              : COMPLETED
  Started             : 11/15/2012 08:34:28      Started             : 11/15/2012 08:43:38
  Last Updated        : 11/15/2012 08:34:40      Last Updated        : 11/15/2012 08:43:43
  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

 ---------------------------------------------
 Comparison Metric: ELAPSED_TIME
 ------------------
 Workload Impact Threshold: 1%
 --------------------------
 SQL Impact Threshold: 1%
 ----------------------

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

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

SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count
 Overall               6                  4
 Improved              4                  4
 Unchanged             2                  0

Top 6 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
|           |               | Impact on | Execution | Metric  | Metric | Impact | Plan   |
| object_id | sql_id        | Workload  | Frequency | Before  | After  | on SQL | Change |
------------------------------------------------------------------------------------------
|        18 | 6nrcat73nzqhw |    28.03% |         1 | 1415497 |    935 | 99.93% | y      |
|        21 | cr2bsxvmby2zb |     24.5% |         1 | 1236756 |    352 | 99.97% | y      |
|        16 | 0xhqjft6b43s9 |     23.2% |         1 | 1170776 |     64 | 99.99% | y      |
|        17 | 5ygm3n7fs0715 |    22.16% |         1 | 1153815 |  35505 | 96.92% | y      |
|        20 | avd78ah9m0x86 |      .03% |         1 |   36901 |  35139 |  4.77% | n      |
|        19 | 7p7xh2a92f4qm |      .02% |         1 |   32632 |  31473 |  3.55% | n      |
------------------------------------------------------------------------------------------


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


Now we could see that there is around 97% of improvement on the sqls.

No comments:

Post a Comment