How to determine whether Hint is used or not ?

Today my friend Azad asked me a good question that, How to determine whether any HINT has used my optimizer or not?

We know hint can be ignored silently by the optimizer. In this post we will see how to check/confirm whether hint has used or not with FULL and APPEND hints.

1) FULL Hint:  Lets see how FULL hint usage will be addressed.

Following query doing count(*) from the table which has primary key on it. So obviously we can see "INDEX FAST FULL SCAN" in "Row Source Operation". And there is a INDEX_FFS hint in "Outline Data" section.

Query : SELECT COUNT(*) FROM TEST;

SQL> SELECT COUNT(*) FROM TEST;
  COUNT(*)
----------
   1149312
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Last statement has a PREV_SQL_ID of ZERO

SQL> SELECT COUNT(*) FROM TEST;
  COUNT(*)
----------
   1149312

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID  bu8b2nwacftug, child number 0
-------------------------------------
SELECT COUNT(*) FROM TEST
Plan hash value: 2983066704
---------------------------------------------------------------------------
| Id  | Operation             | Name     | E-Rows | Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |        |    46 (100)|          |
|   1 |  SORT AGGREGATE       |          |      1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TEST_IDX |  71832 |    46   (3)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / TEST@SEL$1

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))
      END_OUTLINE_DATA
  */
Column Projection Information (identified by operation id):
---------------------------------------------------------
   1 - (#keys=0) COUNT(*)[22]

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
45 rows selected.
SQL>

Now lets use FULL hint and see the result.... 

SQL> SELECT /*+ FULL(TEST) */ COUNT(*) FROM TEST;
 COUNT(*)
----------
   1149312
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  bs398sfuc0aa6, child number 0
-------------------------------------
SELECT /*+ FULL(TEST) */ COUNT(*) FROM TEST
Plan hash value: 1950795681
--------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows | Cost (%CPU)| E-Time   |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |  2489 (100)|          |
|   1 |  SORT AGGREGATE    |      |      1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST |    287K|  2489   (1)| 00:00:30 |
--------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / TEST@SEL$1

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TEST"@"SEL$1")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) COUNT(*)[22]
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
45 rows selected.
SQL>

Observation: We can see TABLE ACCESS FULL access path under Operation, and see FULL in "Outline Data" section. Which concludes that optimizer has used FULL hint.


2) APPEND hint : Now we will see how APPEND hint usage will be addressed.
Following query doing insert into from select, so obviously we can see "LOAD TABLE CONVENTIONAL" in "Row Source Operation". And there is NO direct path write wait event.

Query : INSERT INTO TEST SELECT * FROM TEST WHERE OBJECT_TYPE='TABLE'

********************************************************************************
INSERT INTO TEST SELECT * FROM TEST WHERE OBJECT_TYPE='TABLE'
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.04       0.15         41       1216        767        5818
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.04       0.15         41       1216        767        5818
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=1216 pr=41 pw=0 time=0 us)
   5818   TABLE ACCESS FULL TEST (cr=1071 pr=40 pw=0 time=5690 us cost=288 size=183330 card=1890)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.08          0.08
  db file scattered read                          1        0.01          0.01
  db file sequential read                         1        0.01          0.01
  SQL*Net message to client                       1        0.00          0.00

********************************************************************************

Now we will add APPEND hint to the same query and observe the output.


********************************************************************************
INSERT /*+ APPEND */ INTO TEST SELECT * FROM TEST WHERE OBJECT_TYPE='TABLE'
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          2         47          0           0
Execute      1      0.03       0.13          3       1028         46        2909
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.04       0.16          5       1075         46        2909

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD AS SELECT  (cr=1030 pr=5 pw=40 time=0 us)
   2909   TABLE ACCESS FULL TEST (cr=1028 pr=0 pw=0 time=3540 us cost=288 size=183330 card=1890)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         3        0.02          0.07
  direct path write                               1        0.04          0.04
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        9.36          9.36

********************************************************************************

Observation:  Now optimizer doing "LOAD AS SELECT" Row Source Operation and having "direct path write" wait event. Which concludes that optimizer has used APPEND hint.

-Asifkhan

SQL Performance Analyzer (SPA)

SQL Performance Analyzer(SPA) compares the SQL execution result, before and after the change, and generates a report outlining the net benefit on the workload due to the changes. SQL Performance Analyzer is well integrated with existing SQL Tuning Set (STS), SQL Tuning Advisor and SQL Plan Management functionalities.


We will see this feature with practical. We will see how performance will increase after adding index on predicate. SPA will give us a nice html report with differences.

The following steps shows before change activity...

1) Connect Scott user and perform below steps. We are creating table and gathering stats for the same. Then we are executing some queries on that table.

SQL> CREATE TABLE TEST AS SELECT * FROM all_objects;

Table created.

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

PL/SQL procedure successfully completed.

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

  COUNT(*)

----------

        99

SQL> SELECT object_name FROM TEST  WHERE object_id = 100;

OBJECT_NAME

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

ORA$BASE

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

  COUNT(*)

----------

       888

SQL> SELECT object_name FROM TEST  WHERE object_id = 1000;

OBJECT_NAME

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

EXPACT$

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

  COUNT(*)

----------

       790

SQL>

2) Connect db as sysdba and create SQL Tunning Sets (STS) with the name sqlset and load Step 1 query into it.

SQL> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'sqlset');

PL/SQL procedure successfully completed.

SQL> DECLARE
  2             l_cursor  DBMS_SQLTUNE.sqlset_cursor;
  3             BEGIN
  4               OPEN l_cursor FOR
  5             SELECT VALUE(a)
  6             FROM   TABLE(
  7                DBMS_SQLTUNE.select_cursor_cache(
  8               basic_filter   => 'sql_text LIKE ''%TEST%'' and parsing_schema_name = ''SCOTT''', attribute_list => 'ALL')
  9               ) a;
 10             DBMS_SQLTUNE.load_sqlset(sqlset_name => 'sqlset',  populate_cursor => l_cursor);
 11  END;
 12  /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT sql_text  FROM   dba_sqlset_statements  WHERE  sqlset_name = 'sqlset';
SQL_TEXT
--------------------------------------------------------------------------------
SELECT COUNT(*) FROM TEST WHERE object_id <= 100
CREATE TABLE TEST AS SELECT * FROM all_objects
/* SQL Analyze(0) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms
SELECT COUNT(*) FROM TEST  WHERE object_id <= 1000
SELECT COUNT(*) FROM TEST  WHERE object_id BETWEEN 100 AND 1000
SELECT object_name FROM TEST  WHERE object_id = 100
BEGIN DBMS_STATS.gather_table_stats(USER, 'TEST', cascade => TRUE); END;
SELECT object_name FROM TEST  WHERE object_id = 1000
8 rows selected.
SQL>


3) Create SPA analysis task with Step 2 STS set (sqlset). And execute that analysis task before change as "before_change".

SQL> VARIABLE v_task VARCHAR2(64);
SQL> EXEC :v_task :=  DBMS_SQLPA.create_analysis_task(sqlset_name => 'sqlset');
PL/SQL procedure successfully completed.
SQL> PRINT :v_task
V_TASK
--------------------------------------------------------------------------------
TASK_2309

SQL> BEGIN
  2              DBMS_SQLPA.execute_analysis_task(
  3             task_name       => :v_task,
  4             execution_type  => 'test execute',
  5              execution_name  => 'before_change');
  6             END;
  7             /
PL/SQL procedure successfully completed.
SQL>

4) Now create B tree index on object_id(TEST) and gather stats.
SQL> CREATE INDEX TEST_IDX ON TEST(object_id);
Index created.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TEST', cascade => TRUE);
PL/SQL procedure successfully completed.
SQL>

5) After the change, now execute SPA analysis task "after_change".

SQL> BEGIN

  2             DBMS_SQLPA.execute_analysis_task(

  3             task_name       => :v_task,

  4             execution_type  => 'test execute',

  5             execution_name  => 'after_change');

  6             END;

  7             /

PL/SQL procedure successfully completed.

SQL> BEGIN

  2        DBMS_SQLPA.execute_analysis_task(

  3          task_name        => :v_task,

  4          execution_type   => 'compare performance',

  5          execution_params => dbms_advisor.arglist(

  6                                'execution_name1',

  7                                'before_change',

  8                                'execution_name2',

  9                                'after_change')

 10          );

 11      END;

 12      /

PL/SQL procedure successfully completed.

SQL>

6) Now run the report and spool it in html format.

SQL> SET LONG 1000000
SQL> SET PAGESIZE 0
SQL> SET LINESIZE 200
SQL> SET LONGCHUNKSIZE 200
SQL> SET TRIMSPOOL ON
SQL>
SQL> SPOOL D:\execute_comparison_report.html
SQL> SELECT DBMS_SQLPA.report_analysis_task(:v_task,'HTML','ALL','ALL')     FROM   dual;
.
.
.
                <td class="s27" align="right">97</td>
                <td class="s27" align="right"/>
                <td class="s27" align="right"/>
            </tr>
            <tr bgcolor="#F7F777" valign="bottom">
                <td class="s27" align="right">2</td>
                <td class="s27" ora_space_char=".">
                    <font color="#F7F777">..</font>
                    TABLE ACCESS FULL
                </td>
                <td class="s27">TEST</td>
                <td class="s27" align="right">71832</td>
                <td class="s27" align="right">6967704</td>
                <td class="s27" align="right">289</td>
                <td class="s27" align="right">00:00:04 </td>
            </tr>
        </table>
        <hr size="1" width="650" align="left"/>
    </body>
</html>
SQL> SPOOL OFF

7) Open D:\execute_comparison_report.html file in browser. 

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

---

Task Information:	Workload Information:
Task Name : TASK_2309 Task Owner : SYS Description :	SQL Tuning Set Name : sqlset SQL Tuning Set Owner : SYS Total SQL Statement Count : 8

Execution Information:

---

Execution Name : EXEC_2313 Execution Type : COMPARE PERFORMANCE Description : Scope : COMPREHENSIVE Status : COMPLETED Number of Unsupported SQL : 2

Started : 10/29/2015 14:22:09 Last Updated : 10/29/2015 14:22:09 Global Time Limit : UNLIMITED Per-SQL Time Limit : UNUSED Number of Errors : 0

Analysis Information:

---

Before Change Execution:	After Change Execution:
Execution Name : before_change Execution Type : TEST EXECUTE Scope : COMPREHENSIVE Status : COMPLETED Started : 10/29/2015 14:20:24 Last Updated : 10/29/2015 14:20:25 Global Time Limit : UNLIMITED Per-SQL Time Limit : UNUSED Number of Errors : 0	Execution Name : after_change Execution Type : TEST EXECUTE Scope : COMPREHENSIVE Status : COMPLETED Started : 10/29/2015 14:21:45 Last Updated : 10/29/2015 14:21:46 Global Time Limit : UNLIMITED Per-SQL Time Limit : UNUSED Number of Errors : 0
Comparison Metric: ELAPSED_TIME
Workload Impact Threshold: 1%
SQL Impact Threshold: 1%

Report Summary

---

Projected Workload Change Impact:

---

Overall Impact	:	27.76%
Improvement Impact	:	27.76%
Regression Impact	:	0%

SQL Statement Count

---

SQL Category	SQL Count	Plan Change Count
Overall	8	5
Improved	5	5
Unchanged	1	0
Unsupported	2	0

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

---

object_id	sql_id	Impact on Workload	Execution Frequency	Metric Before	Metric After	Impact on SQL	Plan Change
20	0fu6mv7vxjpyj	8.81%	1	4845	60	98.76%	y
23	cg89jtag4jwgy	5.13%	1	2824	36	98.73%	y
26	fqp20ap9hs2u5	5.13%	1	2807	20	99.29%	y
24	dpb7b5g4mvqs5	4.36%	1	2382	15	99.37%	y
25	f64bsx6sp6w9t	4.34%	1	2370	15	99.37%	y
21	3a4ujc62aphhn	.32%	1	39097	38925	.44%	n

---

Report Details

---

SQL Details:

---

Object ID	: 20
Schema Name	: SCOTT
SQL ID	: 0fu6mv7vxjpyj
Execution Frequency	: 1
SQL Text	: SELECT COUNT(*) FROM TEST WHERE object_id <= 1000

Execution Statistics:

---

Stat Name	Impact on Workload	Value Before	Value After	Impact on SQL
elapsed_time	8.81%	.004845	.00006	98.76%
parse_time	-.21%	.000214	.000216	-.93%
cpu_time	6.45%	.003466	0	100%
user_io_time	0%	0	0	0%
buffer_gets	16.62%	1028	3	99.71%
cost	16.43%	288	4	98.61%
reads	0%	0	0	0%
writes	0%	0	0	0%
io_interconnect_bytes	0%	0	0	0%
rows		1	1

Notes:

---

Before Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

After Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

Findings (2):

---

The performance of this SQL has improved. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

---

Plan Id	: 8879
Plan Hash Value	: 1950795681

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	5	288	00:00:04
1	. SORT AGGREGATE		1	5
* 2	.. TABLE ACCESS FULL	TEST	909	4545	288	00:00:04

Predicate Information (identified by operation id):

---

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

Execution Plan After Change:

---

Plan Id	: 8887
Plan Hash Value	: 190015244

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	5	4	00:00:01
1	. SORT AGGREGATE		1	5
* 2	.. INDEX RANGE SCAN	TEST_IDX	909	4545	4	00:00:01

Predicate Information (identified by operation id):

---

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

---

SQL Details:

---

Object ID	: 23
Schema Name	: SCOTT
SQL ID	: cg89jtag4jwgy
Execution Frequency	: 1
SQL Text	: SELECT COUNT(*) FROM TEST WHERE object_id BETWEEN 100 AND 1000

Execution Statistics:

---

Stat Name	Impact on Workload	Value Before	Value After	Impact on SQL
elapsed_time	5.13%	.002824	.000036	98.73%
parse_time	-.63%	.000155	.000161	-3.87%
cpu_time	6.45%	.003466	0	100%
user_io_time	0%	0	0	0%
buffer_gets	16.62%	1028	3	99.71%
cost	16.48%	288	3	98.96%
reads	0%	0	0	0%
writes	0%	0	0	0%
io_interconnect_bytes	0%	0	0	0%
rows		1	1

Notes:

---

Before Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

After Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

Findings (2):

---

The performance of this SQL has improved. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

---

Plan Id	: 8882
Plan Hash Value	: 1950795681

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	5	288	00:00:04
1	. SORT AGGREGATE		1	5
* 2	.. TABLE ACCESS FULL	TEST	821	4105	288	00:00:04

Predicate Information (identified by operation id):

---

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

Execution Plan After Change:

---

Plan Id	: 8890
Plan Hash Value	: 190015244

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	5	3	00:00:01
1	. SORT AGGREGATE		1	5
* 2	.. INDEX RANGE SCAN	TEST_IDX	821	4105	3	00:00:01

Predicate Information (identified by operation id):

---

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

---

SQL Details:

---

Object ID	: 26
Schema Name	: SCOTT
SQL ID	: fqp20ap9hs2u5
Execution Frequency	: 1
SQL Text	: SELECT COUNT(*) FROM TEST WHERE object_id <= 100

Execution Statistics:

---

Stat Name	Impact on Workload	Value Before	Value After	Impact on SQL
elapsed_time	5.13%	.002807	.00002	99.29%
parse_time	-4.64%	.000101	.000145	-43.56%
cpu_time	6.45%	.003466	0	100%
user_io_time	0%	0	0	0%
buffer_gets	16.63%	1028	2	99.81%
cost	16.54%	288	2	99.31%
reads	0%	0	0	0%
writes	0%	0	0	0%
io_interconnect_bytes	0%	0	0	0%
rows		1	1

Notes:

---

Before Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

After Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

Findings (2):

---

The performance of this SQL has improved. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

---

Plan Id	: 8885
Plan Hash Value	: 1950795681

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	5	288	00:00:04
1	. SORT AGGREGATE		1	5
* 2	.. TABLE ACCESS FULL	TEST	90	450	288	00:00:04

Predicate Information (identified by operation id):

---

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

Execution Plan After Change:

---

Plan Id	: 8893
Plan Hash Value	: 190015244

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	TEST_IDX	90	450	2	00:00:01

Predicate Information (identified by operation id):

---

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

---

SQL Details:

---

Object ID	: 24
Schema Name	: SCOTT
SQL ID	: dpb7b5g4mvqs5
Execution Frequency	: 1
SQL Text	: SELECT object_name FROM TEST WHERE object_id = 1000

Execution Statistics:

---

Stat Name	Impact on Workload	Value Before	Value After	Impact on SQL
elapsed_time	4.36%	.002382	.000015	99.37%
parse_time	-.11%	.000123	.000124	-.81%
cpu_time	3.23%	.001733	0	100%
user_io_time	0%	0	0	0%
buffer_gets	16.62%	1028	3	99.71%
cost	16.54%	288	2	99.31%
reads	0%	0	0	0%
writes	0%	0	0	0%
io_interconnect_bytes	0%	0	0	0%
rows		1	1

Notes:

---

Before Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

After Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

Findings (2):

---

The performance of this SQL has improved. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

---

Plan Id	: 8883
Plan Hash Value	: 1357081020

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	30	288	00:00:04
* 1	. TABLE ACCESS FULL	TEST	1	30	288	00:00:04

Predicate Information (identified by operation id):

---

• 1 - filter("OBJECT_ID"=1000)

Execution Plan After Change:

---

Plan Id	: 8891
Plan Hash Value	: 1389866015

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	30	2	00:00:01
1	. TABLE ACCESS BY INDEX ROWID	TEST	1	30	2	00:00:01
* 2	.. INDEX RANGE SCAN	TEST_IDX	1		1	00:00:01

Predicate Information (identified by operation id):

---

• 2 - access("OBJECT_ID"=1000)

---

SQL Details:

---

Object ID	: 25
Schema Name	: SCOTT
SQL ID	: f64bsx6sp6w9t
Execution Frequency	: 1
SQL Text	: SELECT object_name FROM TEST WHERE object_id = 100

Execution Statistics:

---

Stat Name	Impact on Workload	Value Before	Value After	Impact on SQL
elapsed_time	4.34%	.00237	.000015	99.37%
parse_time	-2.21%	.000133	.000154	-15.79%
cpu_time	3.23%	.001733	0	100%
user_io_time	0%	0	0	0%
buffer_gets	16.62%	1028	3	99.71%
cost	16.54%	288	2	99.31%
reads	0%	0	0	0%
writes	0%	0	0	0%
io_interconnect_bytes	0%	0	0	0%
rows		1	1

Notes:

---

Before Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

After Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

Findings (2):

---

The performance of this SQL has improved. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

---

Plan Id	: 8884
Plan Hash Value	: 1357081020

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	30	288	00:00:04
* 1	. TABLE ACCESS FULL	TEST	1	30	288	00:00:04

Predicate Information (identified by operation id):

---

• 1 - filter("OBJECT_ID"=100)

Execution Plan After Change:

---

Plan Id	: 8892
Plan Hash Value	: 1389866015

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	30	2	00:00:01
1	. TABLE ACCESS BY INDEX ROWID	TEST	1	30	2	00:00:01
* 2	.. INDEX RANGE SCAN	TEST_IDX	1		1	00:00:01

Predicate Information (identified by operation id):

---

• 2 - access("OBJECT_ID"=100)

---

SQL Details:

---

Object ID	: 21
Schema Name	: SCOTT
SQL ID	: 3a4ujc62aphhn
Execution Frequency	: 1
SQL Text	: /* SQL Analyze(0) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */ to_char(count("OWNER")),to_char(substrb(dump(min("OWNER"),16,0,32),1,120)),to_char(substrb(dump(max("OWNER"),16,0,32),1,120)),to_char(count("OBJECT_NAME")),to_char(substrb(dump(min("OBJECT_NAME"),16,0 ,32),1,120)),to_char(substrb(dump(max("OBJECT_NAME"),16,0,32),1,120)),to_char(count("SUBOBJECT_NAME")),to_char(substrb(dump(min("SUBOBJECT_NAME"),16,0,32),1,120)),to_char(substrb(dump(max("SUBOBJECT_N AME"),16,0,32),1,120)),to_char(count("OBJECT_ID")),to_char(substrb(dump(min("OBJECT_ID"),16,0,32),1,120)),to_char(substrb(dump(max("OBJECT_ID"),16,0,32),1,120)),to_char(count("DATA_OBJECT_ID")),to_cha r(substrb(dump(min("DATA_OBJECT_ID"),16,0,32),1,120)),to_char(substrb(dump(max("DATA_OBJECT_ID"),16,0,32),1,120)),to_char(count("OBJECT_TYPE")),to_char(substrb(dump(min("OBJECT_TYPE"),16,0,32),1,120)) ,to_char(substrb(dump(max("OBJECT_TYPE"),16,0,32),1,120)),to_char(count("CREATED")),to_char(substrb(dump(min("CREATED"),16,0,32),1,120)),to_char(substrb(dump(max("CREATED"),16,0,32),1,120)),to_char(co unt("LAST_DDL_TIME")),to_char(substrb(dump(min("LAST_DDL_TIME"),16,0,32),1,120)),to_char(substrb(dump(max("LAST_DDL_TIME"),16,0,32),1,120)),to_char(count("TIMESTAMP")),to_char(substrb(dump(min("TIMEST AMP"),16,0,32),1,120)),to_char(substrb(dump(max("TIMESTAMP"),16,0,32),1,120)),to_char(count("STATUS")),to_char(substrb(dump(min("STATUS"),16,0,32),1,120)),to_char(substrb(dump(max("STATUS"),16,0,32),1 ,120)),to_char(count("TEMPORARY")),to_char(substrb(dump(min("TEMPORARY"),16,0,32),1,120)),to_char(substrb(dump(max("TEMPORARY"),16,0,32),1,120)),to_char(count("GENERATED")),to_char(substrb(dump(min("G ENERATED"),16,0,32),1,120)),to_char(substrb(dump(max("GENERATED"),16,0,32),1,120)),to_char(count("SECONDARY")),to_char(substrb(dump(min("SECONDARY"),16,0,32),1,120)),to_char(substrb(dump(max("SECONDAR Y"),16,0,32),1,120)),to_char(count("NAMESPACE")),to_char(substrb(dump(min("NAMESPACE"),16,0,32),1,120)),to_char(substrb(dump(max("NAMESPACE"),16,0,32),1,120)),to_char(count("EDITION_NAME")),to_char(su bstrb(dump(min("EDITION_NAME"),16,0,32),1,120)),to_char(substrb(dump(max("EDITION_NAME"),16,0,32),1,120)) from "SCOTT"."TEST" t /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL, NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/

Execution Statistics:

---

Stat Name	Impact on Workload	Value Before	Value After	Impact on SQL
elapsed_time	.32%	.039097	.038925	.44%
parse_time	9.06%	.000223	.000137	38.57%
cpu_time	3.23%	.039867	.038133	4.35%
user_io_time	0%	0	0	0%
buffer_gets	0%	1028	1028	0%
cost	0%	289	289	0%
reads	0%	0	0	0%
writes	0%	0	0	0%
io_interconnect_bytes	0%	0	0	0%
rows		1	1

Notes:

---

Before Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

After Change:

The statement was first executed to warm the buffer cache. Statistics shown were averaged over next 9 executions.

Findings (1):

---

The structure of the SQL plan in execution 'before_change' is different than its corresponding plan which is stored in the SQL Tuning Set.

Execution Plan Before Change:

---

Plan Id	: 8880
Plan Hash Value	: 1950795681

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	97	289	00:00:04
1	. SORT AGGREGATE		1	97
2	.. TABLE ACCESS FULL	TEST	71832	6967704	289	00:00:04

Execution Plan After Change:

---

Plan Id	: 8888
Plan Hash Value	: 1950795681

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	97	289	00:00:04
1	. SORT AGGREGATE		1	97
2	.. TABLE ACCESS FULL	TEST	71832	6967704	289	00:00:04

---

SQL> SPOOL OFF

--Asifkhan