Saturday, February 23, 2013

0 SQL Performance Analyzer (SPA)

SPA compare the SQL Performance change before and after the database changes. The database changes includes:

  • database upgrade
  • Parameter changes
  • Update statistic

 

  • Use PL/SQL to perform the SPA
  • Use OEM to preform the SPA
  • Drop the analysis task
  • Drop The SQL Tuning set

 

Use PL/SQL to perform the SPA

Create the test user

CREATE USER spa_user IDENTIFIED BY spa_user QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO spa_user;

 

Test user create the test table and run some quires

CREATE TABLE test_objects AS SELECT * FROM all_objects;
 
EXEC DBMS_STATS.gather_table_stats(USER, 'test_objects', cascade => TRUE);
 
SELECT COUNT(*) FROM test_objects WHERE object_id <= 1000;
SELECT object_name FROM test_objects WHERE object_id = 1000;
SELECT COUNT(*) FROM test_objects WHERE object_id <= 3000;
SELECT object_name FROM test_objects WHERE object_id = 3000;
SELECT COUNT(*) FROM test_objects WHERE object_id BETWEEN 1000 AND 3000;

image

Create the SQL Set

EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'SPA_STS');
 
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
     SELECT VALUE(a)
     FROM   TABLE(
              DBMS_SQLTUNE.select_cursor_cache(
                basic_filter   => 'sql_text LIKE ''%test_objects%'' and parsing_schema_name = ''SPA_USER''',
                attribute_list => 'ALL')
            ) a;
                                               
 
  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'SPA_STS',
                           populate_cursor => l_cursor);
END;
/
-- Confirm the SQL Set
SELECT sql_text FROM   DBA_SQLSET_STATEMENTS WHERE  sqlset_name = 'SPA_STS';

 

image

Create Analysis task

variable spa_task  VARCHAR2(64);
EXEC :spa_task :=  DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'SPA_STS', task_name => 'MY_SPA_TUNE', description => 'Task to test SPA');
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name =>'MY_SPA_TUNE',execution_type=>' test execute',execution_name=>'before_change');
 
select task_name,status from USER_ADVISOR_LOG  where task_name like 'MY_SPA_TUNE';

image

Test user create the index on the test table

image

Use SPA to analysis the impact after the index is being created and compare the before and after

EXECUTE DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name =>'MY_SPA_TUNE',execution_type=>' test execute',execution_name=>'after_change');
select task_name,status from USER_ADVISOR_LOG  where task_name like 'MY_SPA_TUNE'; 
 
exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'MY_SPA_TUNE',execution_type   => 'compare performance', execution_params => dbms_advisor.arglist( 'execution_name1', 'before_change', 'execution_name2', 'after_change')  );

 

image

General the report and output the report to html

SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON
SPOOL C:\temp\execute_comparison_report.htm
 
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_SPA_TUNE','HTML','ALL') FROM   dual;
 
SPOOL OFF

 

image

View the report in the OEM cloud control

image

image

image

Use OEM to preform the SPA

image

In this test, I choice “Parameter change”

image

I use the same SQL SET and choice the “optimizer_index_cost_ad”, the default value is 100, I changed to 10.

image

Here is the report.

image

DROP the analysis task

 exec DBMS_SQLPA.DROP_ANALYSIS_TASK(task_name => 'MY_SPA_TUNE')

image

DROP The SQL Tuning set

 EXEC DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'SPA_STS');

image

If the SQL set is still being used by other tuning tasks, the sql set can not be dropped. If we try to drop it, we would get the error: ORA-13757

ORA-13757: "SQL Tuning Set" "SPA_STS" owned by user "SYS" is active
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 13213
ORA-06512: at "SYS.DBMS_SQLTUNE", line 4563
ORA-06512: at line 1

image

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates