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@remote; Without the hint, rows from table@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 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------