Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Which are the GOOD HINTS for you and why?, this list I got from TKyte.

Which are the GOOD HINTS for you and why?, this list I got from TKyte.

From: Juan Cachito Reyes Pacheco <>
Date: Fri, 2 Apr 2004 14:43:31 -0400
Message-ID: <036e01c418e2$6bb5f960$>

Hi I got from tom kyte their good hints (hint suggested to be used to tune in normal situations by every one (not hints in exceptional or too complex querys) )
, this was interesting, because some of them I didn't though was a good idea to use.
and explained them in my paper, (and pasted here) Please do you have some coment about (or another good hint missed).?

1.1.1 Good HINTS
The hint adds weight to a cost estimation toward some CBO behavior, this means if even with the hint this is not a good execution path for the CBO, it will be ignored.

Tom Kyte’s good list hints (hint you can use when needed), if not on good list, it would be on the other kind of list(hints you should not use without a really good reason). ALL_ROWS
Optimize a statement block for best throughput (minimum total resource consumption).

SELECT /*+ ALL_ROWS */ columns FROM table FIRST_ROWS(n) or FIRST_ROWS The hints FIRST_ROWS(n) (where n is any positive integer) or FIRST_ROWS instruct

Oracle to optimize an individual SQL statement for fast response. FIRST_ROWS(n)

Optimize to return the first n rows most efficiently.

SELECT /*+ FIRST_ROWS(7) */ columns FROM emp

The optimizer ignores this hint in DELETE and UPDATE statement blocks and in

SELECT statement blocks that contain any of the following syntax:


· GROUP BY clause

· FOR UPDATE clause

· Aggregate functions

· DISTINCT operator

· ORDER BY clauses, when there is no index on the ordering columns

These statements cannot be optimized for best response time, because Oracle must retrieve all rows accessed by the statement before returning the first row.

If you specify either the ALL_ROWS or the FIRST_ROWS hint in a SQL statement, and if the data dictionary does not have statistics about tables accessed by thestatement, then the optimizer uses default statistical values (such as allocatedstorage for such tables) to estimate the missing statistics CHOOSE
Causes the optimizer to choose between the rule-based and cost-based approaches for a SQL statement. The optimizer bases its selection on the

presence of statistics for the tables accessed by the statement. (NO)REWRITE
(NO)REWRITE hint forces the cost-based optimizer to (no) rewrite a query in terms of materialized views, when possible, without cost consideration. DRIVING_SITE
Is useful if you are using distributed query.

SELECT /*+DRIVING_SITE(table)*/ * FROM table2, table_at_remote;

Without the hint, rows from table_at_remote are sent to the local site, and the join is executed there. With the hint, the rows are sent to the remote site, and the query is executed there, returning the result to the local site. (NO)PARALLEL
Specify the desired number of concurrent servers that

can be used for a parallel operation.

SELECT /*+ PARALLEL(table, 3) */ ename

Append enables direct-path faster inserts. Noappend conventional inserts. CURSOR_SHARING_EXACT If you had set CURSOR_SHARING for fix binding problems, you can use this hint to get a query use CURSOR_SHARING binding mode. DYNAMIC_SAMPLING
enables dynamic sampling if all of the following conditions are true:

· There is more than one table in the query.

· Some table has not been analyzed and has no indexes.

· The optimizer determines that a relatively expensive table scan
would be required for this unanalyzed table.

Basically lets sql optimizer process interrogate the database table that

is not analyzed but used in a query with other tables that are before parsing

the query. So , the database "can have a clue" as to the statistics regarding

the unanalyzed table.

For Global Temporary Tables, at least a value of 2 -- in order to get all unanalyzed tables (the GTT in this case) to be sampled (since 0 disables this and 1 doesn't do anything if an index exists) CARDINALITY
It works for procedure tables, setting it, indicates the number of records you will get.

SELECT /*+ cardinality(table 10 )

If you are using as subquery, in the subquery include a WHERE ROWNUM>0, for more information read:


Juan Carlos Reyes Pacheco
Database 9.2 Standard Edition

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Apr 02 2004 - 12:43:14 CST

Original text of this message