Oracle SQL Tuning Pocket Reference
by Mark Gurry


The unconfirmed error reports are from readers.  They have not yet been 
approved or disproved by the author or editor and represent solely the
opinion of the reader.

Here's a key to the markup:
        [page-number]: serious technical mistake
        {page-number}: minor technical mistake
        <page-number>: important language/formatting problem
        (page-number): language change or minor formatting problem
        ?page-number?: reader question or request for clarification

This page was updated September 18, 2003.


UNCONFIRMED  errors and comments from readers: 


(Chapter 1.7)  Explanation of ORDERED hint;
The sample statement contains the line:
AND c.catgory    = 'RETAIL'
which should read:
AND c.category    = 'RETAIL'


[1]  SAMPLE EXCERPT  "Understanding the Rule-Based Optimizer";
Understanding the Rule-Based Optimizer
The rule-based optimizer (RBO) uses a predefined set of precedence 
rules to figure out which path it will use to access the database. 
The RDBMS kernel defaults to the rule-based optimizer under a number
 of conditions, including:

OPTIMIZER_MODE = RULE is specified in your INIT.ORA file 

OPTIMIZER_MODE = CHOOSE is specified in your INIT.ORA file, and no 
statistics exist for any table involved in the statement 

My comment:
I do not think that the second case is true, that the RBO would be 
used if there are no statistics for *any* table involved.
In fact the CBO *will* be used if *any* table involved has statistics.

I'd say this can be very verified fairly easily, as I performed a quick 
check on 2 tables, accessed them with a join, dropped statistics on one, 
reran the query and got the same execution plan.
Then I ran the query with the RULE hint, and there was a completely 
different execution plan.


(22)  5th line down;
The text contains reference to a table used in a previous example:

... (e.g., DEPT in the example above) ...

The example referred to does not contain the table DEPT. The text should refer to the
table TRANS.



{68}  3rd paragraph;
SQL script is missing second set of "||" - should be:

select sql_text n1, 'Executions='||
executions n1,
'Expected Response Time (Sec)= '||
buffer_gets / decode (executions, 0,1, executions) /4000 "Reponse"
from v$sql
where buffer_gets / decode (executions, 0,1, executions) /4000 > 10
and executions > 0
order by hash_value, child_number



{75}  Code example for the APPEND hint;
The code example reads:

INSERT /*+ APPEND */ * INTO y
SELECT FROM winners;

This is of course invalid SQL, and should read:

INSERT /*+ APPEND */ INTO y
SELECT * FROM winners;

There is a similar problem with the associated NOAPPEND hint.


{84}  Code example for the NOAPPEND hint;
The code example reads:

INSERT /*+ NOAPPEND */ * INTO y
SELECT FROM winners;

This is of course invalid SQL, and should read:

INSERT /*+ NOAPPEND */ INTO y
SELECT * FROM winners;

There is a similar problem with the associated APPEND hint.

{85}: minor technical mistake...
Top of page 85: 1st paragraph.
NOINDEX may have to be spelled as NO_INDEX.

The neigboring paragraphs on the same and the previous page need checking
for the correct syntax of the optimizer hints shown.