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