| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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.
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).
1.1.1.1 ALL_ROWS
Optimize a statement block for best throughput (minimum total resource
consumption).
SELECT /*+ ALL_ROWS */ columns FROM table
1.1.1.2 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:
· Set operators (UNION, INTERSECT, MINUS, UNION ALL)
· 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
1.1.1.3 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.
1.1.1.4 (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.
1.1.1.5 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.
1.1.1.6 (NO)PARALLEL
Specify the desired number of concurrent servers that
can be used for a parallel operation.
SELECT /*+ PARALLEL(table, 3) */ ename
SELECT /*+ NOPARALLEL(table) */
1.1.1.7 (NO) APPEND
Append enables direct-path faster inserts. Noappend conventional inserts.
1.1.1.8 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.
1.1.1.9 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)
1.1.1.10 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:
http://asktom.oracle.com/pls/ask/
f?p=4950:8:::::F4950_P8_DISPLAYID:3779680732446
Juan Carlos Reyes Pacheco
OCP
Database 9.2 Standard Edition
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Apr 02 2004 - 12:43:14 CST
 |
 |