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

Yep, but you are reporting off the datadictionary, and that's old... have found similar results on several occasions - just keep it in (until statistics against datadictionary bring momre good than evil... grin).

BTW why runs this query so often? If ther's a danger of talespaces getting full so rapidly, consider
alter database datafile '...' autoextend on next ...M maxsize .....M;

--
Kind Regards,
Frank
<monsri_at_my-deja.com> wrote in message news:8o5df4$16h$1_at_nnrp1.deja.com...


> 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.