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

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Tue, 20 Jul 2004 17:50:41 +0300
Message-ID: <30ee01c46e68$ee2a4be0$0a879fd9@porgand>

> 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 = '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# = f.ts#

  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 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# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 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
-----------------------------------------------------------------

Received on Tue Jul 20 2004 - 09:47:28 CDT