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

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Tue, 20 Jul 2004 09:55:15 -0500
Message-id: <002e01c46e69$90f5b620$212f200a@rshamsudxp>

Chris

USER/DBA_EXTENTS AND USER/DBA_FREE_SPACE have hints ? At least in 8i. Refer catspace.sql
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Grabowy, Chris Sent: Tuesday, July 20, 2004 9:40 AM
To: oracle-l_at_freelists.org
Subject: No stats, why the heck is CBO running???

Hello All,

We have NO stats on all the tables/indexes in SYS. Parallel and instances for all tables/indexes in SYS is set to 0 or 1. The optimizer mode is set to CHOOSE. We are not using any new features that requires the CBO.

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?

I kicked off a 10053 trace, and now Im searching my pile for Wolfgang's paper. Hopefully, that trace will give me some clues why RBO is not used??

BTW, when the query runs with CBO then it 4031s. The explain plan costs are insane. Probably because there are no stats and the stats are made up.

Is it Monday? Any ideas?

Chris

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

Attached file included as plaintext by Ecartis --
Desc: Signature

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Tue Jul 20 2004 - 09:53:05 CDT