The bits of Oracle which aren’t documented seem to be the bits that are hardest to resist so I thought I’d make a brief comment on undocumented hints.
Standard Warning: you should not take advantage of any undocumented feature without first getting approval from Oracle support for your particular circumstances. On the other hand it seems to be that there are some hints that fall into a special category where you are more likely to get that approval – and here are my thoughts on why.
There seem to be four main reasons why some hints are not documented:
- There are hints that you’re absolutely not supposed to know about. One such example is the infamous no_trigger hint that appeared briefly in a version that I will deliberately leave unspecified (in case anyone is still using it). And if anyone posts the version number in a comment, I will delete it.
- There are hints which are actually internal hints that operate in situations that the end-user cannot manufacture. The index_rrs() hint that used to appear in some parallel query slave code (for parallel index fast full scans) was an example of this type of hint.
- There are hints which I assume are not yet documented because there is some beta code that is still subject to finalisation sitting in the production release. The hint may become official, it may cease to exist. The not-quite-documented selectivity() hint of 9i – which subsequently disappeared – may have been such a case.
- There are hints which never got into the (right place in the) documentation because no-one told the editors about it in time. I like to think that most of the undocumented hints like this – but I may be wrong. The dynamic_sampling_est_cdn() hint is probably an example of this type. It’s in the 9.2 Performance Tuning Guide and Reference – but not in the table of contents, the index, or the lists of hints given in the chapter on hints. Instead it appeared as an afterthought in the section on the dynamic_sampling() hint. It’s gone from the 10gR2 manual (and the documentation for hints has moved into the SQL Reference)
So when you look at undocumented hints, you have to ask yourself – are they supposed to be official, or will they soon be official, or are they actually official if only you can find where they’ve been documented.
Footnote:
If you want to see a complete list of hints, including the version where they appeared, and the version where they got into the “stored outline” code, then 11g gives you the view v$sql_hint. (Sample query with output from 19c)
Oracle Scratchpad 
v$sql_hint is one of my favorite views of 11g – thenk you for pointing to it in “Hints on hints” document.
My list of hints which I’d likte to be documented:
with CARDINALITY in the very top of the list.
Comment by Timur Akhmadeev — June 15, 2009 @ 7:40 am BST Jun 15,2009 |
Timur,
I’ll work on writing notes about the ones I know. But I’m not sure that worrying about cardinality() is a good idea anymore – I think it has been deprecated (at least internally) in favour of a couple of the opt_estimate() options.
The only hint I would really like to see documented (hence made legal) is opt_estimate(). If I know why the the optimizer is getting it’s arithmetic wrong, I want to give it a hint that tells it how to correct it.
Comment by Jonathan Lewis — June 15, 2009 @ 9:42 am BST Jun 15,2009 |
Jonathan,
would you use some hints for table() function? Are there any legal hints for correcting cardinality?
I tend to use cardinality hint when I’m working with collections in DML.
Comment by radino83 — June 15, 2009 @ 11:47 am BST Jun 15,2009 |
Well, CARDINALITY is just simpler then OPT_ESTIMATE :) and is invaluable for TABLE expressions.
I don’t know if it’s really deprecated and where it is used internally, but it seems to work as expected in 11g too. That’s good, since lots of code relies on it.
Comment by Timur Akhmadeev — June 15, 2009 @ 11:52 am BST Jun 15,2009 |
Radino,
At present it is possible to make the cardinality() hint work on query blocks that use the table() operator. There are other hints that are better, though, and give more detail – but can’t be used (opt_estimate, col_stats).
Timur,
“deprecated (at least internally)“ There are a couple of places (e.g. materialized view refresh) where the code once used the cardinality() hint but now uses the opt_estimate() hint. But at present cardinality() is the best we have.
It’s only easy to use when it’s easy to use ;) But it’s very limited in use because you often need to supply information like “number of distinct values” for columns at the same time; otherwise you just end up putting in the entire structural set of hints to define the plan.
Comment by Jonathan Lewis — June 15, 2009 @ 1:41 pm BST Jun 15,2009 |
This is a timely discussion because I’ve almost finished writing up an article on four different ways to supply cardinality for pipelined and table functions.
In addition to the (unsupported) CARDINALITY and OPT_ESTIMATE hints, from 11.1.0.7, dynamic sampling works with pipelined functions (not table functions it seems and only via the DYNAMIC_SAMPLING hint and not the OPTIMIZER_DYNAMIC_SAMPLING parameter). But beware as this takes a 100% sample of the pipelined function’s rowsource regardless of the sampling level used (between 2 and 10).
What I wanted to show here for Radino’s benefit was the only supported method I know of and that is to use the Extensible Optimiser and the ODCIStatsTableFunction method available from 10g. I’ve put in a quick demo below – the full write-up will be on my website in a few days time.
SQL> -- 1. sample collection type and pipelined function
SQL>
SQL> CREATE TYPE collection_type AS TABLE OF NUMBER;
2 /
Type created.
SQL>
SQL> CREATE FUNCTION pipelined_function(
2 p_cardinality IN INTEGER
3 ) RETURN collection_type PIPELINED AS
4 BEGIN
5 FOR i IN 1 .. p_cardinality LOOP
6 PIPE ROW (i);
7 END LOOP;
8 RETURN;
9 END pipelined_function;
10 /
Function created.
SQL>
SQL> -- 2. statistics type to supply cardinality to the CBO
SQL>
SQL> CREATE TYPE cardinality_type AS OBJECT (
2
3 dummy NUMBER,
4
5 STATIC FUNCTION ODCIGetInterfaces (
6 p_interfaces OUT SYS.ODCIObjectList
7 ) RETURN NUMBER,
8
9 STATIC FUNCTION ODCIStatsTableFunction (
10 p_function IN SYS.ODCIFuncInfo,
11 p_stats OUT SYS.ODCITabFuncStats,
12 p_args IN SYS.ODCIArgDescList,
13 p_cardinality IN INTEGER
14 ) RETURN NUMBER
15 );
16 /
Type created.
SQL>
SQL> CREATE TYPE BODY cardinality_type AS
2
3 STATIC FUNCTION ODCIGetInterfaces (
4 p_interfaces OUT SYS.ODCIObjectList
5 ) RETURN NUMBER IS
6 BEGIN
7 p_interfaces := SYS.ODCIObjectList(
8 SYS.ODCIObject ('SYS', 'ODCISTATS2')
9 );
10 RETURN ODCIConst.success;
11 END ODCIGetInterfaces;
12
13 STATIC FUNCTION ODCIStatsTableFunction (
14 p_function IN SYS.ODCIFuncInfo,
15 p_stats OUT SYS.ODCITabFuncStats,
16 p_args IN SYS.ODCIArgDescList,
17 p_cardinality IN INTEGER
18 ) RETURN NUMBER IS
19 BEGIN
20 p_stats := SYS.ODCITabFuncStats(p_cardinality);
21 RETURN ODCIConst.success;
22 END ODCIStatsTableFunction;
23
24 END;
25 /
Type body created.
SQL>
SQL> -- 3. associate the function with the interface type
SQL>
SQL> ASSOCIATE STATISTICS WITH FUNCTIONS pipelined_function USING cardinality_type;
Statistics associated.
SQL>
SQL> -- 4. test it - generate 7 rows
SQL>
SQL> set autotrace on
SQL>
SQL> SELECT * FROM TABLE(pipelined_function(7));
COLUMN_VALUE
------------
1
2
3
4
5
6
7
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1115071714
------------------------------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| PIPELINED_FUNCTION | |
------------------------------------------------------------------------
As I mentioned, more detail will be available in a few days.
Regards,
Adrian
Comment by Adrian Billington — June 16, 2009 @ 7:26 pm BST Jun 16,2009 |
Adrian,
Thanks for the example.
I’ve done a quick edit to make it more readable – and I’ve re-activated the link to your blog to make it easier for people to get back to your website in a few days, but feel free to put a new comment here when you publish the article
Comment by Jonathan Lewis — June 20, 2009 @ 5:29 pm BST Jun 20,2009
Jonathan,
Thanks for re-formatting the example – I used the code tags suggested on your How To Comment page and saw how I’d failed to take care of the whitespace after I had submitted it!
For those who are interested, my article on table/pipelined function cardinality is now available.
Regards,
Adrian
Comment by Adrian Billington — June 21, 2009 @ 7:28 pm BST Jun 21,2009
Jonathan,
thank you very much for pointing out the new view v$sql_hint.
This seems to answer a question I once asked you: how to get all available hints
(
)
Comment by Sokrates — June 15, 2009 @ 7:37 pm BST Jun 15,2009 |
I used to refer to V$RESERVED_WORDS for all hints in older versions
srivenu
Comment by srivenu kadiyala — June 16, 2009 @ 5:33 am BST Jun 16,2009 |
Following Timur’s comment about wanting more information on undocumented hints, here’s an idea for a collaborative effort.
If there’s a hint that you want to know more about, leave a comment.
If a hint appeared on the list and you know where there’s some OFFICIAL documentation, leave a comment.
If a hint appears on the list and you know how it works, or where some unofficial documentation is, leave a comment.
Remember to use the Reply button to link related comments together.
If this effort does take off and the chain gets messy, I may end up collating the results and merging the content of some of the comments.
Comment by Jonathan Lewis — June 15, 2009 @ 10:23 pm BST Jun 15,2009 |
Jonathan,
good initiative to combine info about hints in one place. Probably some wiki engine would suit better for such collaborative task.
My “Hints wish list” to know something about:
Comment by Timur Akhmadeev — June 16, 2009 @ 9:39 am BST Jun 16,2009 |
FBTSCAN – https://www.sql.ru/forum/602758/lokalno-upravlyaemyy-fleshbek
Comment by Anonymous — April 10, 2022 @ 2:28 am BST Apr 10,2022 |
Adding to Timur’s wishlist here are two I would like to see (do tell me if they are already possible) :
DYNAMIC_SAMPLING (table_name, blocks)
— dynamic sampling for the table, ignoring existing table statistics
IGNORE_BIND_PEEKED (table_name)
— ignore previously peeked binds for this table and re-peek in this execution.
Hemant
Comment by Hemant K Chitale — June 22, 2009 @ 3:15 am BST Jun 22,2009 |
Hemant,
The dynamic_sampling() hint allows you to specify a table and a level and samples blocks from that table unconditionally. Level 1 is (by default 32 blocks, and each level doubles the sample up to level 9, then level 10 is the whole table).
A directive to peek sounds nice. The adaptive cursor sharing in 11g is roughly in that area – except it has to do it wrong before it tries again. And if you’re using cursor_sharing=force there is a cursor_sharing_exact hint (from 9i) which, again, is working in the same area.
Comment by Jonathan Lewis — June 24, 2009 @ 7:37 am BST Jun 24,2009 |
Re: USE_HASH_AGGREGATION
Apparently, the hint has no effect when DML is involved:
This is a pity because in our specific scenario, doing unique count through hashing is 2.4 times faster than by sorting (sort group by). Another database whose name shall remain unspoken does hash group by in the insert/select statement just fine. Is there some hidden hint per chance that would force Oracle to do the proper thing ?
Thanks.
Comment by Val — March 7, 2010 @ 11:56 pm GMT Mar 7,2010 |
10053 gives a hint as to what can be done:
select x, count(y) cnt from
(
select x, y from t1 group by x, y
)
group by x
The above is what Oracle does with the original ‘select x, count(unique y) from t1 group by x’.
Adding an ‘insert’ in front the re-written statement does not break the plan any more. Why Oracle abandons a more performant ‘hash group by’ plan when you add an insert to the original statement is unclear.
Comment by Val — March 8, 2010 @ 7:06 pm GMT Mar 8,2010 |
@Val
This is bug 9148171. It’s since been fixed so it works with CTAS.
explain plan for create table foo as SELECT deptno, mgr, sum(sal) sum_sal, count(sal) cnt_sal, max(hiredate) max_hd, count(distinct hiredate) dist_hd_cnt FROM emp GROUP BY deptno, mgr; select * from table(dbms_xplan.display(format=>'outline basic')); Plan hash value: 192513176 -------------------------------------------------- | Id | Operation | Name | -------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | | 1 | LOAD AS SELECT | FOO | | 2 | HASH GROUP BY | | | 3 | VIEW | VW_DAG_0 | | 4 | HASH GROUP BY | | | 5 | TABLE ACCESS STORAGE FULL| EMP | -------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@"SEL$5771D262") FULL(@"SEL$5771D262" "EMP"@"SEL$1") USE_HASH_AGGREGATION(@"SEL$C33C846D") NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D") TRANSFORM_DISTINCT_AGG(@"SEL$1") OUTLINE(@"SEL$5771D262") OUTLINE(@"SEL$1") OUTLINE_LEAF(@"SEL$C33C846D") TRANSFORM_DISTINCT_AGG(@"SEL$1") OUTLINE_LEAF(@"SEL$5771D262") NO_PARALLEL ALL_ROWS DB_VERSION('11.2.0.1') OPTIMIZER_FEATURES_ENABLE('11.2.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */Comment by Greg Rahn — June 7, 2010 @ 4:55 am BST Jun 7,2010 |
Greg,
Thanks for the follow-up.
Comment by Jonathan Lewis — June 8, 2010 @ 9:55 pm BST Jun 8,2010 |
can somebody help me to tunup the query utilizing hints?
[SQL statement removed by JPL]
Thanks in advance
Igor
Comment by Igor — May 5, 2011 @ 4:56 pm BST May 5,2011 |
Igor,
This is not the forum for getting SQL fixed. You might try the OTN Database or SQL forum.
Before you do so, though, read up on using dbms_xplan to generate a complete plan, and especially the option for collecting and reporting rowsource execution statistics to see where the work was done. At present If you select dbms_xplan from the Categeries drop-down list at the top right of this block you will find 12 articles on the topic.
Comment by Jonathan Lewis — May 7, 2011 @ 8:05 am BST May 7,2011 |