RE: No stats, why the heck is CBO running???

Thanks Tanel!

Argh! I assumed they didn't have any hints since in past versions we = normally use RBO for dictionary views. I hate it when I assume. It = kills me.

Thanks to anyone else that responds. =20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Tanel P=F5der Sent: Tuesday, July 20, 2004 10:51 AM
To: oracle-l_at_freelists.org
Subject: Re: No stats, why the heck is CBO running???

> Yet a simple query to find the "next extext that wont fit" is always =
using
the CBO???
>
> This SQL queries DBA_TABLES and DBA_FREE_SPACE. There are NO hints in =
the
SQL.
>
> What the heck?

But there are hints in DBA_FREE_SPACE:

SQL> select text from dba_views where view_name =3D 'DBA_FREE_SPACE';

TEXT

-------------------------------------------------------------------------=

---
----
select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# =3D f.ts#
  and f.ts# =3D fi.ts#
  and f.file# =3D fi.relfile#
  and ts.bitmapped =3D 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# =3D f.ktfbfetsn
  and f.ktfbfetsn =3D fi.ts#
  and f.ktfbfefno =3D fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ =3D 0

These kind of issues are one of the reasons why one should avoid putting
hints inside views...

Now, when you join several unanalyzed tables with one analyzed one, CBO =
will
be used by default and default statistics will be used for unanalyzed =
tables
(or dynamic sampling depending on optimizer_dynamic_sampling parameter).

Tanel.


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------