Re: RULE hint: why get rid of it ???

monsri_at_my-deja.com wrote:
>
> Hello,
>
> I had the following statement:
> > select fs.tablespace_name,
> > round(max(fs.bytes)/1024/1024,2) "Max. free (MB)",
> > round(sum(fs.bytes)/1024/1024,2) "Sum free (MB)",
> > round(max(tab.next_extent)/1024/1024,2) "Max. Extent (MB)"
> > from dba_free_space fs,
> > dba_tables tab
> > where fs.tablespace_name = tab.tablespace_name
> > having max(fs.bytes) < max(tab.next_extent)
> > group by fs.tablespace_name;
>
> run several times per day by a monitoring tool (from a
> Linux machine) against 8.1.5 on NT Terminal Server.
>
> The CPU used to go up to 99% for about 3 to 4 minutes
> EVERY TIME this query as such was executed, thus
> resulting in very poor performances for people then
> connected.
>
> I tested the query with the 4 optimizer hints and found
> out the RULE one solves our problem beyond my greatest
> hopes ! I had first "real: 23688" (with the option SET
> TIMING ON in SQL*Plus), I now get with
> > select --+ RULE
> > round(max(fs.bytes)/1024/1024,2) "Max. free (MB)",
> > round(sum(fs.bytes)/1024/1024,2) "Sum free (MB)",
> > ...
>
> "real: 531" (70 times smaller ! -by the way, these are not
> milliseconds, are these ??-). And the execution time taken
> by the query is of course noticeable, all my users are
> relieved.
>
> I had without the RULE hint the following statistics:
> > 0 recursive calls
> > 7344 db block gets
> > 1630427 consistent gets
> > 0 physical reads
> > 0 redo size
> > 1122 bytes sent via SQL*Net to client
> > 908 bytes received via SQL*Net from client
> > 3 SQL*Net roundtrips to/from client
> > 2 sorts (memory)
> > 0 sorts (disk)
> > 0 rows processed
>
> and I now have with the hint:
> > 1 recursive calls
> > 24 db block gets
> > 13060 consistent gets
> > ...
> (about the same for the rest).
>
> It looks like I've solved the trouble, haven't I ?? Therefore
> how can you explain I read all these warnings in the manual
> regarding the RULE hint (page 8-10 of Oracle8 Tuning Release
> 8.0 text):
>
> "Rule-based optimization is supported in Oracle8, but you are
> advised to write any new applications using cost-based
> optimization. [...]"
> "However, you should eventually migrate your existing
> applications to use the cost-based approach, because the
> rule-based approach will not be available in future
> versions of Oracle."
>
> Have I made a mistake using the RULE hint ? If yes, why,
> insofar as this solution works (the original optimizer
> rule was ALL_ROWS, I tried FIRST_ROWS and CHOOSE without
> results) ??
>
> Could you please enlighten me a bit ?!!?
>
> Thanks ever so much !
>
> Seb
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Do you have statistics on your data dictionary tables....???

Thats a no-no..

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse