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 -
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(*)
----------
1
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(*)
----------
13
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(*)
----------
13
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
---------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
|
| | Impact on |
Execution | Metric | Metric | Impact | Plan |
| object_id | sql_id
| Workload | Frequency | Before | After
| on SQL | Change |
-----------------------------------------------------------------------------------------
|
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:
-----------------------------
------------------------------------------------------------------
|
| Impact on | Value
| Value | Impact |
| Stat Name
| Workload | Before | After
| on SQL |
------------------------------------------------------------------
| 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
----------------------------------------------------------------------------
| Id | Operation
| Name | Rows |
Bytes | Cost | Time |
----------------------------------------------------------------------------
| 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
------------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------
| 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:
-----------------------------
------------------------------------------------------------------
|
| Impact on | Value
| Value | Impact |
| Stat Name
| Workload | Before | After
| on SQL |
------------------------------------------------------------------
| 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
----------------------------------------------------------------------------
| Id | Operation
| Name | Rows |
Bytes | Cost | Time |
----------------------------------------------------------------------------
| 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
------------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------
| 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:
-----------------------------
------------------------------------------------------------------
|
| Impact on | Value
| Value | Impact |
| Stat Name
| Workload | Before | After
| on SQL |
------------------------------------------------------------------
| 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
----------------------------------------------------------------------------
| Id | Operation
| Name | Rows |
Bytes | Cost | Time |
----------------------------------------------------------------------------
| 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
------------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------
| 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:
-----------------------------
------------------------------------------------------------------
|
| Impact on | Value
| Value | Impact |
| Stat Name
| Workload | Before | After
| on SQL |
------------------------------------------------------------------
| 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
---------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes |
Cost | Time |
---------------------------------------------------------------------------
| 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
------------------------------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------
| 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:
-----------------------------
------------------------------------------------------------------
|
| Impact on | Value
| Value | Impact |
| Stat Name
| Workload | Before | After
| on SQL |
------------------------------------------------------------------
| 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
---------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes |
Cost | Time |
---------------------------------------------------------------------------
| 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
------------------------------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------
| 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>
No comments:
Post a Comment