RE: Optimizer change from 8i-9i

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Wed, 11 May 2005 15:56:37 +0200
Message-ID: <7F0C000A3ABA6241A10C9ABF37EEB46D040868@MSXVS01.trivadis.com>

Chao

>As Wolfgang said, I think although your autotrace shows RULE
>optimizer, oracle maybe actually using CBO.
>Can you enable SQL Trace and check whether it is actually=20
>parsed by RBO or CBO?

When the CBO is used statistics (Cost, Card and Bytes) are shown...

>By the way, team, anyone aware of some common situlation that
>autotrace can lie to us printing the execution plan and the optimizer
>used?

Yes. When there's an automatic switch from RBO to CBO.=20 E.g. when you want to use partitioned tables with the RBO:

SQL> alter session set optimizer_mode =3D rule;

SQL> select * from sales;

Execution Plan

---

SELECT STATEMENT Optimizer=3DRULE (Cost=3D405 Card=3D929300 = Bytes=3D26949700)
  PARTITION RANGE (ALL) (Cost=3D405 Card=3D929300 Bytes=3D26949700)     TABLE ACCESS (FULL) OF 'SALES' (TABLE) (Cost=3D405 Card=3D929300 = Bytes

>Can I ask one more question , about the
>"the index tie-break rule being the only reasonable exception, all
>the other possibilities are a bit far fetched IMO"

What do you mean???

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 11 2005 - 10:01:11 CDT