The AVOID_FULL hint
I saw this very odd statement on an SAP system last week.
SELECT /*+ AVOID_FULL ("/bic/xxx") */ * FROM "/BIC/XXX" WHERE "/BIC/XXX"=:A0
I had never seen that hint before so I thought I’d do a little investigation. First I did a quick check on a test case to see if it worked.
SYS@DEMO1> select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=234657;
COUNT(*)
----------
32
SYS@DEMO1> @x
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID adpsagc1zb5fj, child number 0
-------------------------------------
select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=234657
Plan hash value: 1638045392
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| SKEW_COL1 | 32 | 160 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=234657)
19 rows selected.
So in my first test case it clearly caused the optimizer to avoid a full scan on my table. So I then started wondering how long has this hint been around and so I looked in v$sql_hint, but to my surprise it wasn’t there.
SYS@DEMO1> select name, version, inverse from v$sql_hint where upper(name) like '%AVOID%';
no rows selected
SYS@DEMO1> select name, version, inverse from v$sql_hint where upper(name) like '%FULL%';
NAME VERSION INVERSE
-------------------------------------------------- ---------- ----------------------------------------------------------------
FULL 8.1.0
NATIVE_FULL_OUTER_JOIN 10.2.0.3 NO_NATIVE_FULL_OUTER_JOIN
NO_NATIVE_FULL_OUTER_JOIN 10.2.0.3 NATIVE_FULL_OUTER_JOIN
FULL_OUTER_JOIN_TO_OUTER 11.2.0.3 NO_FULL_OUTER_JOIN_TO_OUTER
NO_FULL_OUTER_JOIN_TO_OUTER 11.2.0.3 FULL_OUTER_JOIN_TO_OUTER
The AVOID_FULL hint is not present in v$sql_hint. But wait, maybe it’s one of those top secret hidden hints like PARALLEL, which is a valid hint but doesn’t show up in v$sql_hint.
SYS@DEMO1> select name, version, inverse from v$sql_hint where name like '%PARALLEL%';
NAME VERSION INVERSE
-------------------------------------------------- ---------- ----------------------------------------------------------------
SYS_PARALLEL_TXN 8.1.6
NOPARALLEL 8.1.0 SHARED
NO_PARALLEL 10.1.0.3 SHARED
PARALLEL_INDEX 8.1.0 NO_PARALLEL_INDEX
NO_PARALLEL_INDEX 8.1.0 PARALLEL_INDEX
SYS@DEMO1> -- hmmm there is no PARALLEL hint listed
SYS@DEMO1> -- but it clearly works
SYS@DEMO1> --
SYS@DEMO1> select count(*) from kso.skew2;
COUNT(*)
----------
32000004
SYS@DEMO1> @x
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 56v09mkbstyaa, child number 0
-------------------------------------
select count(*) from kso.skew2
Plan hash value: 4220890033
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 89256 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| SKEW2 | 32M| 89256 (1)| 00:11:38 |
----------------------------------------------------------------------------
14 rows selected.
SYS@DEMO1> select /*+ parallel 2 */ count(*) from kso.skew2;
COUNT(*)
----------
32000004
SYS@DEMO1> @x
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9rgx66dnd21zj, child number 1
-------------------------------------
select /*+ parallel 2 */ count(*) from kso.skew2
Plan hash value: 2117817910
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3095 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 32M| 3095 (1)| 00:00:25 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS STORAGE FULL| SKEW2 | 32M| 3095 (1)| 00:00:25 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 32 because of degree limit
27 rows selected.
So the PARALLEL hint is not listed but it clearly is a valid hint (even though the SHARED hint is documented as the inverse of the NOPARALLEL hint). So maybe this AVOID_FULL hint is one of those corner cases. So I did some more testing and found a special case where the hint didn't work as I expected. When I set parallel_degree_policy to LIMITED and decorated my table with a degree setting of DEFAULT I got this behavior.
SYS@DEMO1> select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=1;
COUNT(*)
----------
3199971
SYS@DEMO1> @x
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 591tybw6c8vth, child number 0
-------------------------------------
select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=1
Plan hash value: 578366071
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1548 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 5 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 5 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 5 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 3023K| 14M| 1548 (1)| 00:00:13 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS STORAGE FULL| SKEW | 3023K| 14M| 1548 (1)| 00:00:13 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z AND "COL1"=1)
filter("COL1"=1)
24 rows selected.
The optimizer picked a full scan, despite the hint to the contrary. My next thought was to try Wolfganging the statement (generating 10053 trace) to see if maybe the hint syntax was slightly off and so it was silently ignored. (I wish there was a setting to throw an error when an invalid hint is specified in a statement by the way, but as far as I know there isn't such a switch). Anyway, here's the trace bit.
SYS@DEMO1> @wolfgang
VALUE
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/demo/DEMO1/trace/DEMO1_ora_16420.trc
Session altered.
SYS@DEMO1> select /*+ avoid_full(a) 2 */ count(*) from kso.skew2 a where col1 is not null;
COUNT(*)
----------
32000003
SYS@DEMO1> !vi /u01/app/oracle/diag/rdbms/demo/DEMO1/trace/DEMO1_ora_16420.trc
. . .
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "KSO"."SKEW2" "A" WHERE "A"."COL1" IS NOT NULL
. . .
Dumping Hints
=============
====================== END SQL Statement Dump ======================
Not too helpful, the trace didn't say whether the hint was valid or not. In fact, it didn't even mention it at all. Strange. So then I thought I'd go back to the production system and see what the plan looked like there. Sure enough, the hint wasn't working on the production system either - the plan was a full scan on the table.
So the bottom line is there is this new (to me) hint that has been around for some time (I don't know how long because it's not documented) that seems to work sometimes but not all the time (but since it's not documented I don't know the syntax - so I may just be messing it up). Anyway, due to this erratic behavior you should definitely use it with care. 🙂
Happy April Fools Day!
Note: I actually saw this statement in a production system.