Oracle’s OPT_ESTIMATE hint: Usage Guide

Good. I’ve been waiting for someone to make a list for this hint.

Thanks for the work.

e.g. This doesn’t work on my 11.2 database,

select /*+ OPT_ESTIMATE(INDEX_SCAN, D4, PK_DEPT, ROWS=555 ) */
dname
from scott.dept D4
where deptno > 0;

SELECT * FROM table(DBMS_XPLAN.DISPLAY_cursor(format => ‘TYPICAL’ ));

Thanks,
Charlie

[…] OPT_ESTIMATE (Undocumented but useful link) […]

Thank you for that Roundup! It saved my day today when I troubleshot a problem involving a table function within a package. I didn’t want to take the risk to deploy a statistics type and ASSOCIATE STATISTICS in production w/o further testing, so tuning the troublesome SQL with OPT_ESTIMATE was a good quick fix.

BTW: Meanwhile, there are a few documents on MOS that offer OPT_ESTIMATE as a workaround for bugs. But that certainly cannot be interpreted as an official support of that hint. :-(

Cheers,
Uwe

Thank you for sharing. It’s very helpful. But is there any chances that it could by applied to estimate cardinality of the join of a nonmerged inline view and a table? I struggled to do this but with no success. The obvious solution to put synonym of the inline view as one of the tables’ names didn’t work for me. Neither did any other solution I crafted. Maybe I miss something simple.

Hi, Christo
I am runing query on the IOT table and hint is not working.
select /*+ OPT_ESTIMATE(INDEX_SKIP_SCAN dd IDX01_CONFIG_VALUE_NUMBER ROWS=1234) */ count(*) cnt from CONFIG_VALUE_NUMBER dd where value_number in (:p1);
———————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————-
| 0 | SELECT STATEMENT | | | | 2185 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX SKIP SCAN| IDX01_CONFIG_VALUE_NUMBER | 21 | 126 | 2185 (1)| 00:00:27 |
———————————————————————————————-
When table is not in the plan, hint does not work, if table is hint work.

select /*+ gather_plan_statistics OPT_ESTIMATE(INDEX_SKIP_SCAN dd IDX01_CONFIG_VALUE_NUMBER ROWS=1234) */
count(ts) cnt from CONFIG_VALUE_NUMBER dd where value_number in (:p1);

————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————
| 0 | SELECT STATEMENT | | | | 2930 (100)| |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | INDEX UNIQUE SCAN| CONFIG_VALUE_NUMBER_PK | 21 | 294 | 2930 (1)| 00:00:36 |
|* 3 | INDEX SKIP SCAN | IDX01_CONFIG_VALUE_NUMBER | 1234 | | 2185 (1)| 00:00:27 |
————————————————————————————————

How to force a hint to work in the first case?

Thanks

Interestingly the JOIN option doesn’t appear to work the way you would expect when joining views.

So this doesn’t work:

SELECT /*+ opt_estimate(JOIN (yy xx) ROWS=4) */
xx.*
FROM dba_objects xx INNER JOIN dba_indexes yy ON (xx.owner = yy.owner);

while this does work:

SELECT /*+ qb_name(vw) opt_estimate( @vw query_block ROWS=4)*/
*
FROM dba_objects xx INNER JOIN dba_indexes yy ON (xx.owner = yy.owner)

Hi!

from my colleague Havi:
The QUERY_BLOCK tag means that you refer to the actual, current SQL block.
That is: you are using the hint right there in the query block, and not outside of it.

select /*+OPT_ESTIMATE(QUERY_BLOCK ROWS=10)*/
count(*) from VAU;

Cheers,
Rob