RULE hint: why get rid of it ???

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;

[Quoted] [Quoted] 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. Received on Fri Aug 25 2000 - 11:15:27 CEST