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.
Hi Kerry,
SAP is sometimes a strange creature with Oracle ;-))
However the mentioned table name “/BIC/XXX” is corresponding to a SAP BI system and in this case there are known code modifications with such hints in ETL scenarios. It looks like the ABAP source code block “%_HINTS” was specified with the wrong RDBMS (e.g. ‘%_HINTS ORACLE ‘AVOID_FULL (“&table&”)’).
AVOID_FULL is a valid hint for Informix (http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls1143.htm) and it is implemented by several SAP note corrections with a RDBMS spec. However if you are using Oracle as RDBMS it should be something like ”%_HINTS ORACLE ‘INDEX(“&TABLE&” “\l_index\”)’ – looks like a wrong ABAP open SQL coding ;-))
Regards
Stefan
Ha – so that’s how it got there. Thanks for the insight Stefen. Hope you didn’t waste too much time reading through this. 🙂 Seems like we’re up to our eyeballs with SAP these days. Lots of companies wanting to try it on Exadata. We should really talk sometime.
Kerry
Hi Kerry,
of course, you got my contact data – maybe we can work some day together.
Exadata is a rare species in the SAP world nowadays (as far from my experience) – most of my clients are running their huge Oracle SAP ERP or BI databases on big power systems or big single rack mounted x86 boxes. Enkitec is absolutely the right partner for such Exadata evaluations of course :-))
Have fun in the SAP / Oracle world :-))
Regards
Stefan
I suppose it’s a good thing hints follow the syntax of an embedded comment in the SQL standard, which makes them harmless if unrecognized.
Good thing SAP queries are not that widespread!
Noons,
The fact that hints are embedded in comments is a good thing and a bad thing I think. I really have wished for years that there was a session level parameter that you could set to throw an error (or warning even) when invalid hints are specified. Something like “_enable_strong_hint_checking” or something like that. Thanks for playing along.
Kerry
$ strings `which oracle` | grep -i avoid_full
$
=> avoid_full – hint does not exist
reminds me of Charles Hooper’s article http://hoopercharles.wordpress.com/2011/01/15/adding-comments-to-sql-statements-improves-performance/ and the following discussion. I still think it’s quite weird that leading elements (comments, or even single signs) that are no hints may or may not invalidate following hints (‘A’ does, ‘B’ does not, ‘C’ does not, ‘D’ does etc.; still true in 12.1.0.1) – also looks like a contribution to april fools day…
Martin
Good joke 🙂