Nothing but my learning and experience with Oracle..!: March 2017

There are some wonderful feature that were introduced in 11g R1 and I was too late to test this amazing feature. I am talking about SQL Performance Analyzer.Here is the link that I followed/tested -

https://oracle-base.com/articles/11g/sql-performance-analyzer-11gr1

1) Create the Sql Tuning Set using DBMS_SQLTUNE.
2) Create a analysis task to run Sql Performance Analyzer using DBMS_SQLPA.
3) Execute Before Change TEST EXECUTE (Pre-Change SQL Trial).
---> Make a change that needs to tested.
4) Execute After Change TEST EXECUTE (Post-Change SQL Trial).
5) Comparing SQL Trials.
6) Generate Compare report.

The concept of SQL tuning sets, along with the DBMS_SQLTUNE package to manipulate them, was introduced in Oracle 10g as part of the Automatic SQL Tuning functionality. Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor 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.

The SQL Performance Analyzer can be run manually using the DBMS_SQLPA package or using Enterprise Manager.

The SQL performance analyzer requires SQL tuning sets, and SQL tuning sets are pointless unless they contain SQL.

SQL> CREATE USER spa_test_user IDENTIFIED BY spa_test_user QUOTA UNLIMITED ON users;

User created.

SQL> GRANT CONNECT, CREATE TABLE TO spa_test_user;

Grant succeeded.

SQL> CONN spa_test_user/spa_test_user

Connected.

SQL> CREATE TABLE my_objects AS SELECT * FROM all_objects;

Table created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade => TRUE);

PL/SQL procedure successfully completed.

-->This schema represents our "before" state. Still logged in as the test user, issue the following statements.

SQL> SELECT COUNT(*) FROM my_objects WHERE object_id <= 100;

COUNT(*)

----------

SQL> SELECT object_name FROM my_objects WHERE object_id = 100;

OBJECT_NAME

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

ORA$BASE

SQL> SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000;

COUNT(*)

----------

SQL> SELECT object_name FROM my_objects WHERE object_id = 1000;

no rows selected

SQL> SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN 100 AND 1000;

COUNT(*)

----------

SQL>

Notice, all statements make reference to the currently unindexed 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 a SQL tuning set.

Creating SQL Tuning Sets using the DBMS_SQLTUNE Package

The DBMS_SQLTUNE package contains procedures and functions that allow us to create, manipulate and drop SQL tuning sets. The first step is to create a SQL tuning set called spa_test_sqlset using the CREATE_SQLSET procedure.

SQL> CONN / AS SYSDBA

Connected.

SQL> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'spa_test_sqlset');--- > created STS

PL/SQL procedure successfully completed.

SQL> 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 ''%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;

PL/SQL procedure successfully completed.

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

SQL_TEXT

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

SELECT object_name FROM my_objects WHERE object_id = 100

CREATE TABLE my_objects AS SELECT * FROM all_objects

SELECT COUNT(*) FROM my_objects WHERE object_id <= 100

SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN 100 AND 1000

SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000

SELECT object_name FROM my_objects WHERE object_id = 1000

6 rows selected.

SQL>

Now we have a SQL tuning set, we can start using the SQL performance analyzer.

Running the SQL Performance Analyzer using the DBMS_SQLPA Package by providing the STS that we created.

The DBMS_SQLPA package is the PL/SQL API used to manage the SQL performance ananlyzer. The first step is to create an analysis task using the CREATE_ANALYSIS_TASK function, passing in the SQL tuning set name and making a note of the resulting task name

SQL> VARIABLE v_task VARCHAR2(64);

SQL> EXEC :v_task := DBMS_SQLPA.create_analysis_task(sqlset_name => 'spa_test_sqlset');

PL/SQL procedure successfully completed.

SQL> PRINT :v_task

V_TASK

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

TASK_17292

When executing the task you have to decide, with execution_type parameter, which kind of execution you want to perform. A standard SPA task is made of following steps:

Execute the task in TEST EXECUTE mode and generate a before change task report.
Change what you want on your database (upgrade, optimizer parameters, statistics, …), execute the task in TEST EXECUTE mode and generate an after change task report.
Execute the task in COMPARE PERFORMANCE mode and generate a compare performance task report.

SQL> BEGIN

DBMS_SQLPA.execute_analysis_task(

task_name => :v_task,

execution_type => 'test execute',

execution_name => 'before_change');

END;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

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.

SQL> CONN spa_test_user/spa_test_user

Connected.

SQL> CREATE INDEX my_objects_index_01 ON my_objects(object_id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> sho user

USER is "SPA_TEST_USER"

SQL> conn / as sysdba

Connected.

SQL> BEGIN

DBMS_SQLPA.execute_analysis_task(

task_name => :v_task,

execution_type => 'test execute',

execution_name => 'after_change');

END;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_SQLPA.execute_analysis_task(

3 task_name => :v_task,

4 execution_type => 'compare performance',

execution_params => dbms_advisor.arglist(

5 6 'execution_name1',

'before_change',

7 8 'execution_name2',

9 'after_change')

);

END;

10 11 12 /

PL/SQL procedure successfully completed.

SQL> SET PAGESIZE 0

SQL> SET LINESIZE 1000

SQL> SET LONG 1000000

SQL> SET LONGCHUNKSIZE 1000000

SQL> SET TRIMSPOOL ON

SQL> SET TRIM ON

SQL> SPOOL /tmp/execute_comparison_report.txt

SQL> SELECT DBMS_SQLPA.report_analysis_task(:v_task, 'TEXT', 'ALL','ALL') FROM dual;

General Information

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

Task Information: Workload Information:

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

Task Name : TASK_17292 SQL Tuning Set Name : spa_test_sqlset

Task Owner : SYS SQL Tuning Set Owner : SYS

Description : Total SQL Statement Count : 6

Execution Information:

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

Execution Name : EXEC_18111 Started : 03/08/2017 02:43:12

Execution Type : COMPARE PERFORMANCE Last Updated : 03/08/2017 02:43:12

Description : Global Time Limit : UNLIMITED

Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED

Status : COMPLETED Number of Errors : 0

Number of Unsupported SQL : 1

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 : 03/08/2017 02:41:50 Started : 03/08/2017 02:43:04

Last Updated : 03/08/2017 02:41:50 Last Updated : 03/08/2017 02:43:04

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 : 98.82%

Improvement Impact : 98.82%

Regression Impact : 0%

SQL Statement Count

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

SQL Category SQL Count Plan Change Count

Overall 6 5

Improved 5 5

Unsupported 1 0

Top 5 SQL Sorted by Absolute Value of Change Impact on the Workload

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

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

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

| 19 | 7m2wr4twzmrkm | 20.57% | 1 | 2171 | 28 | 98.71% | y |

| 18 | 7k24y5pdhurxx | 20.44% | 1 | 2155 | 26 | 98.79% | y |

| 20 | 7vbu3057r64r0 | 20.39% | 1 | 2151 | 27 | 98.74% | y |

| 21 | fbsgyfwt1q29p | 18.79% | 1 | 1976 | 19 | 99.04% | y |

| 16 | 2j3dk2hcs8jq5 | 18.63% | 1 | 1963 | 23 | 98.83% | y |

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

Note: time statistics are displayed in microseconds

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

Report Details

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

SQL Details:

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

Object ID : 19

Schema Name : SPA_TEST_USER

Container Name : Unknown (con_dbid: 3041856058)

SQL ID : 7m2wr4twzmrkm

Execution Frequency : 1

SQL Text : SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN

100 AND 1000

Execution Statistics:

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

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

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

| elapsed_time | 20.57% | .002171 | .000028 | 98.71% |

| parse_time | 8.43% | .000315 | .000211 | 33.02% |

| cpu_time | 19.78% | .002111 | .000111 | 94.74% |

| user_io_time | 0% | 0 | 0 | 0% |

| buffer_gets | 19.97% | 1176 | 2 | 99.83% |

| cost | 19.88% | 327 | 2 | 99.39% |

| reads | 0% | 0 | 0 | 0% |

| writes | 0% | 0 | 0 | 0% |

| io_interconnect_bytes | 0% | 0 | 0 | 0% |

| rows | | 1 | 1 | |

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

Note: time statistics are displayed in seconds

Notes:

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

Before Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

After Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

Findings (2):

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

1. The performance of this SQL has improved.

2. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

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

Plan Id : 27852

Plan Hash Value : 3102251570

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 327 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | TABLE ACCESS FULL | MY_OBJECTS | 384 | 1920 | 327 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - filter("OBJECT_ID"<=1000 AND "OBJECT_ID">=100)

Execution Plan After Change:

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

Plan Id : 27858

Plan Hash Value : 4165478064

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | INDEX RANGE SCAN | MY_OBJECTS_INDEX_01 | 169 | 845 | 2 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=1000)

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

SQL Details:

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

Object ID : 18

Schema Name : SPA_TEST_USER

Container Name : Unknown (con_dbid: 3041856058)

SQL ID : 7k24y5pdhurxx

Execution Frequency : 1

SQL Text : SELECT COUNT(*) FROM my_objects WHERE object_id <= 100

Execution Statistics:

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

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

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

| elapsed_time | 20.44% | .002155 | .000026 | 98.79% |

| parse_time | -.24% | .00018 | .000183 | -1.67% |

| cpu_time | 20.88% | .002111 | 0 | 100% |

| user_io_time | 0% | 0 | 0 | 0% |

| buffer_gets | 19.97% | 1176 | 2 | 99.83% |

| cost | 19.88% | 327 | 2 | 99.39% |

| reads | 0% | 0 | 0 | 0% |

| writes | 0% | 0 | 0 | 0% |

| io_interconnect_bytes | 0% | 0 | 0 | 0% |

| rows | | 1 | 1 | |

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

Note: time statistics are displayed in seconds

Notes:

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

Before Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

After Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

Findings (2):

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

1. The performance of this SQL has improved.

2. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

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

Plan Id : 27851

Plan Hash Value : 3102251570

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 327 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | TABLE ACCESS FULL | MY_OBJECTS | 1 | 5 | 327 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - filter("OBJECT_ID"<=100)

Execution Plan After Change:

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

Plan Id : 27857

Plan Hash Value : 4165478064

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | INDEX RANGE SCAN | MY_OBJECTS_INDEX_01 | 14 | 70 | 2 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - access("OBJECT_ID"<=100)

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

SQL Details:

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

Object ID : 20

Schema Name : SPA_TEST_USER

Container Name : Unknown (con_dbid: 3041856058)

SQL ID : 7vbu3057r64r0

Execution Frequency : 1

SQL Text : SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000

Execution Statistics:

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

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

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

| elapsed_time | 20.39% | .002151 | .000027 | 98.74% |

| parse_time | -.16% | .000191 | .000193 | -1.05% |

| cpu_time | 20.88% | .002111 | 0 | 100% |

| user_io_time | 0% | 0 | 0 | 0% |

| buffer_gets | 19.97% | 1176 | 2 | 99.83% |

| cost | 19.88% | 327 | 2 | 99.39% |

| reads | 0% | 0 | 0 | 0% |

| writes | 0% | 0 | 0 | 0% |

| io_interconnect_bytes | 0% | 0 | 0 | 0% |

| rows | | 1 | 1 | |

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

Note: time statistics are displayed in seconds

Notes:

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

Before Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

After Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

Findings (2):

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

1. The performance of this SQL has improved.

2. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

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

Plan Id : 27853

Plan Hash Value : 3102251570

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 327 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | TABLE ACCESS FULL | MY_OBJECTS | 384 | 1920 | 327 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - filter("OBJECT_ID"<=1000)

Execution Plan After Change:

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

Plan Id : 27859

Plan Hash Value : 4165478064

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | INDEX RANGE SCAN | MY_OBJECTS_INDEX_01 | 169 | 845 | 2 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - access("OBJECT_ID"<=1000)

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

SQL Details:

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

Object ID : 21

Schema Name : SPA_TEST_USER

Container Name : Unknown (con_dbid: 3041856058)

SQL ID : fbsgyfwt1q29p

Execution Frequency : 1

SQL Text : SELECT object_name FROM my_objects WHERE object_id =

1000

Execution Statistics:

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

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

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

| elapsed_time | 18.79% | .001976 | .000019 | 99.04% |

| parse_time | .32% | .000199 | .000195 | 2.01% |

| cpu_time | 18.68% | .001888 | 0 | 100% |

| user_io_time | 0% | 0 | 0 | 0% |

| buffer_gets | 19.97% | 1176 | 2 | 99.83% |

| cost | 19.88% | 327 | 2 | 99.39% |

| reads | 0% | 0 | 0 | 0% |

| writes | 0% | 0 | 0 | 0% |

| io_interconnect_bytes | 0% | 0 | 0 | 0% |

| rows | | 0 | 0 | |

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

Note: time statistics are displayed in seconds

Notes:

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

Before Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

After Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

Findings (3):

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

1. The performance of this SQL has improved.

2. The structure of the SQL execution plan has changed.

3. This SQL statement returned zero rows.

Execution Plan Before Change:

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

Plan Id : 27854

Plan Hash Value : 880823944

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

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

| 0 | SELECT STATEMENT | | 1 | 31 | 327 | 00:00:01 |

| * 1 | TABLE ACCESS FULL | MY_OBJECTS | 1 | 31 | 327 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 1 - filter("OBJECT_ID"=1000)

Execution Plan After Change:

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

Plan Id : 27860

Plan Hash Value : 712466900

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

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

| 0 | SELECT STATEMENT | | 1 | 31 | 2 | 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | MY_OBJECTS | 1 | 31 | 2 | 00:00:01 |

| * 2 | INDEX RANGE SCAN | MY_OBJECTS_INDEX_01 | 1 | | 1 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - access("OBJECT_ID"=1000)

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

SQL Details:

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

Object ID : 16

Schema Name : SPA_TEST_USER

Container Name : Unknown (con_dbid: 3041856058)

SQL ID : 2j3dk2hcs8jq5

Execution Frequency : 1

SQL Text : SELECT object_name FROM my_objects WHERE object_id =

100

Execution Statistics:

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

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

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

| elapsed_time | 18.63% | .001963 | .000023 | 98.83% |

| parse_time | 9.64% | .000349 | .00023 | 34.1% |

| cpu_time | 18.68% | .001888 | 0 | 100% |

| user_io_time | 0% | 0 | 0 | 0% |

| buffer_gets | 19.95% | 1176 | 3 | 99.74% |

| cost | 19.88% | 327 | 2 | 99.39% |

| reads | 0% | 0 | 0 | 0% |

| writes | 0% | 0 | 0 | 0% |

| io_interconnect_bytes | 0% | 0 | 0 | 0% |

| rows | | 1 | 1 | |

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

Note: time statistics are displayed in seconds

Notes:

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

Before Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

After Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

Findings (2):

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

1. The performance of this SQL has improved.

2. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

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

Plan Id : 27849

Plan Hash Value : 880823944

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

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

| 0 | SELECT STATEMENT | | 1 | 31 | 327 | 00:00:01 |

| * 1 | TABLE ACCESS FULL | MY_OBJECTS | 1 | 31 | 327 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 1 - filter("OBJECT_ID"=100)

Execution Plan After Change:

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

Plan Id : 27855

Plan Hash Value : 712466900

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

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

| 0 | SELECT STATEMENT | | 14 | 434 | 2 | 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | MY_OBJECTS | 14 | 434 | 2 | 00:00:01 |

| * 2 | INDEX RANGE SCAN | MY_OBJECTS_INDEX_01 | 14 | | 1 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - access("OBJECT_ID"=100)

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

SQL>

Nothing but my learning and experience with Oracle..!

Pages

Wednesday, March 8, 2017

Sql performance analyzer after a major database change

Popular Posts