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
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
