RE: No stats, why the heck is CBO running???
- From: "Grabowy, Chris" <chris.grabowy@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 20 Jul 2004 11:15:53 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Tanel P=F5der
Sent: Tuesday, July 20, 2004 10:51 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
