As salamo alaikum wa rahmatullah
SQL Performance Analyzer in Oracle Database 11g Release - 1
The concept of SQL tuning sets, along with the DBMS_SQLTUNE package to manupulate them, was introduced in Oracle 10g as port of the Automatic Sql Tunning functionality. Oracle 11g makes futher user of SQL tunning set with the SQL performance anlyzer, which compares the performance of the statements in tunning set before and after a database change. The database change can be as major as you like, such as:
Database, operating system, or hardware upgrades.
Database, operating system, or hardware configuration changes.
Database initialization parameter changes.
Schema changes, such as adding indexes or materialized views.
Refreshing optimizer statistics.
Creating or changing SQL profiles.
Unlike Database Replay, the SQL Performance Analyzer does not try and replicate the workload on the system. It just plugs through each statement gathering performance statistics.
The SQL Performance Analyzer can be run manually using the DBMS_SQLPA package or using Enterprise Manager. This article gives an overview of both methods.
Setting Up the Test
Creating SQL Tuning Sets using the DBMS_SQLTUNE Package
Running the SQL Performance Analyzer using the DBMS_SQLPA Package
Creating SQL Tuning Sets using Enterprise Manager
Running the SQL Performance Analyzer using Enterprise Manager
Optimizer Upgrade Simulation
Parameter Change
Transferring SQL Tuning Sets
Setting Up the Test
The Sql Performance analyzer requires SQL tunning sets, and SQL tuning sets are pointless unless they contain SQL, so the first task should be to issue some SQL statements. We are only trying to demonstrate the technology, so the examples can be really simple. The following code creates a test user called SPA_TEST_USER.
CONNECT AS SYSDBA
Create user spa_test_user IDENTIFIED BY spa_test_user QUOTA UNLIMITED ON users;
GRANT CONNECT,CREATE TABLE TO spa_test_user;
Next, connect to the test user and create a test table called 'MY_OBJECTS' using a query from the ALL_OBJECTS view.
CONNECT spa_test_user/spa_test_user
CREATE TABLE MY_OBJECTS AS SELECT * FROM ALL_OBJECTS;
Exec DBMS_STATS.gather_table_Stats(USER,'MY_OBJECTS',cascade=>TRUE);
or EXEC DBMS_STATS.gather_table_stats('spa_test_user','MY_OBJECTS',cascade=>TRUE);
This schema represents our "before" state. Still logged in as the test user, issue the following statemtns.
SELECT COUNT(*) FROM MY_OBJECT WHERE OBJECT_ID <=100;
SELECT COUNT(*) FROM MY_OBJECT WHERE OBJECT_ID =100;
SELECT COUNT(*) FROM MY_OBJECT WHERE OBJECT_ID <=1000;
SELECT COUNT(*) FROM MY_OBJECT WHERE OBJECT_ID =1000;
SELECT COUNT(*) FROM MY_OBJECT WHERE OBJECT_ID BETWEEN 100 AND 1OOO;
Notice all statements make reference to the currently unidexed OBJECT_ID column. Later we will be indexing this column to create our changed "after" state.
The select statements are now in the shared pool, so we can start creating an SQL Tunning set.
Creating SQL Tunning Sets using DNMS_SQLTUNE Package
The DBMS_SQLTUNE package contains procedure and functions that allow us to create, manipulate and drop the SQL tunning sets. The first step is to create an SQL tunning set called spa_test_sqlset using the CREATE_SQLSET procedure.
Sql> Conn / as sysdba
Sql> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name=>'spa_test_sqlset');
Next, the SELECT_CURSOR_CACHE table function is used to retrieve a cursor containing all SQL statements that were parsed by the SPA_TEST_USER schema and contain the word "MY_OBJECTS". The resulting cursor is loaded into the the tuning test using the LOAD_SQLSET procedure.
DECLARE
L_CURSOR DBMPS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN L_CURSOR FOR
SELECT VALUE(a)
FROM TABLE (
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
BASIC_FILTER => 'SQL_TEXT LIKE ''%MY_OBJECTS'' AND PARSING_SCHEMA_NAME=''SPA_TEST_USER'', ATTRIBUTE_LIST => 'ALL')
) a;
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME => 'SPA_TEST_SQLSET', POPULATE_CURSOR => L_CURSOR);
END;
The DBA_SQLSET_STATEMENTS view allows us to see which statements have been associated with the tuning set.
Select sql_text From dba_select_statements where sqlset_name ='spa_test_sqlset'
out put
------------------
-----------------
Now we have an SQL tuning set, we can start using the SQL performance analyzer.
Running the SQL Performance Analyzer using the DBMS_SQLPA Package
The DBMS_SQLPA package is the PL/SQL API used to manage the SQL performance analyzer. The first is to create an analysis task using the CREATE_ANALYSIS_TASK function, passing in the SQL tunning set name and making a not of the resulting task name.
Conn / AS SYSDBA
VARIABLE V_TASK VARCHAR(64);
EXEC :V_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK(SQLSET_NAEM=>'SPA_TEST_SQLSET');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> PRINT :V_TASK
Next, user the EXECUTE_ANALYSIS_TASK procedure to execute the contents of the SQL tunning set against the current state of the database to gather information about the performance before any modifications are made. This analysis run is named before_change.
BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :v_task,
execution_type =>'test execute',
execution_name => 'before_change');
END;
/
Now we have the "Before" performance information, we need to make a change so we can test the "after" performance. For this example we will simply add an index to the test table on the OBJECT_ID column.
In a new SQL*PLUS session create the index using the following statements.
Conn spa_test_user/spa_test_user
CREATE INDEX my_objects_index_01 ON my_objects(object_id);
EXEC DBMS_STATS.gather_table_stats(USER,'MY_OBJECTS',cascade => TRUE);
Now, we can return to our original session and test the performance after the database change. Once again use the EXECUTE_ANALYSIS_TASK procedure, naming the analysis task "after_change".
BEGIN
DBMS_SQLPA.execute_analysis_task (
task_name => :v_task,
execution_type => 'test execute',
execution_name => 'after_change');
END;
/
Once the before and after analysis task completed, we must run a comparison analysis task. The following code explicitly names the analysis tasks to compare using name-value pairs in the EXECUTION_PARAMS parameter. If this is ommited, the latest two analysis runs are compared.
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;
/
With this final analysis run complete, we can check out the comparison report using REPORT_ANALYSIS_TASK function. The function returns a CLOB containing the report in TEXT, XML or HTML format.
Its usage is shown below
Note :- Oracle 11gR2 also includes an 'ACTIVE' format that looks more like the Enterprise Manager output.
SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON
SPOOL /tmp/execute_comparison_report.htm
SELECT DBMS_SQLPA.report_analysis_task(:v_task,'HTML','ALL') From dual;
SPOOL OFF
---
Mohammad Shahnawaz
Oracle's Passion
