RE: Stored Outlines and Optimizer Mode

From: <Jared.Still_at_radisys.com>
Date: Thu, 18 Mar 2004 16:35:28 -0800
Message-ID: <OF2BACD033.242E10C0-ON88256E5C.00030951-88256E5C.00032943@radisys.com>

Oh, but you *will* get CBO if you use hints.

I mention this because it was referred to earlier in this thread.

Oracle uses default values when statistics are lacking.

This is 9.2.0.4 on RH 7.1

Jared

16:32:44 SQL>
16:32:44 SQL>alter session set optimizer_mode = rule;

Session altered.

16:32:44 SQL>
16:32:44 SQL>drop table cbo;

Table dropped.

16:32:44 SQL>
16:32:44 SQL>create table cbo
16:32:44   2  as
16:32:44   3  select owner, object_name, object_type
16:32:44   4  from dba_objects
16:32:44   5  /

Table created.

16:32:45 SQL>
16:32:45 SQL>
16:32:45 SQL>create index cboidx on cbo(owner, object_name)
16:32:45   2  /

Index created.

16:32:46 SQL>
16:32:46 SQL>
16:32:46 SQL>delete from plan_table;

6 rows deleted.

16:32:46 SQL>
16:32:46 SQL>commit;

Commit complete.

16:32:46 SQL>
16:32:46 SQL>explain plan
16:32:46   2  set statement_id 'Q1'
16:32:46   3  for
16:32:46   4  select count(*)
16:32:46   5  from cbo
16:32:46   6  /

Explained.

16:32:46 SQL>
16:32:46 SQL>
16:32:46 SQL>explain plan
16:32:46   2  set statement_id 'Q2'
16:32:46   3  for
16:32:46   4  select /*+ index(cbo cboidx) */ count(*)
16:32:46   5  from cbo
16:32:46   6  /

Explained.

16:32:46 SQL>
16:32:46 SQL>
16:32:46 SQL>@showplan9i Q1
16:32:46 SQL>
16:32:46 SQL>-- showplan9i.sql
16:32:46 SQL>
16:32:46 SQL>SET PAUSE OFF
16:32:46 SQL>SET VERIFY OFF
16:32:46 SQL>set trimspool on
16:32:46 SQL>set line 200 arraysize 1
16:32:46 SQL>clear break
16:32:46 SQL>clear compute
16:32:46 SQL>
16:32:46 SQL>
16:32:46 SQL>select *
16:32:46   2  from table(dbms_xplan.display( 'PLAN_TABLE', '&&1'))
16:32:46   3  /

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | CBO | | | |

Note: rule based optimization

10 rows selected.

16:32:46 SQL>
16:32:46 SQL>
16:32:46 SQL>
16:32:46 SQL>@showplan9i Q2
16:32:46 SQL>
16:32:46 SQL>-- showplan9i.sql
16:32:46 SQL>
16:32:46 SQL>SET PAUSE OFF
16:32:46 SQL>SET VERIFY OFF
16:32:46 SQL>set trimspool on
16:32:46 SQL>set line 200 arraysize 1
16:32:46 SQL>clear break
16:32:46 SQL>clear compute
16:32:46 SQL>
16:32:46 SQL>
16:32:46 SQL>select *
16:32:46   2  from table(dbms_xplan.display( 'PLAN_TABLE', '&&1'))
16:32:46   3  /

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 1 | | 19 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL | CBO | 2000 | | 19 |

Note: cpu costing is off

10 rows selected.

16:32:46 SQL>
16:32:46 SQL> DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> Sent by: oracle-l-bounce_at_freelists.org
03/18/2004 02:10 PM
Please respond to oracle-l

        To:     "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>
        cc: 
        Subject:        RE: Stored Outlines and Optimizer Mode


Paula
     How are you ensuring RULE is used today? Not analyzing tables? That

is
an issue because CBO needs you to analyze, which will cause everything to be
CBO. If the SQL has a hint, then analyze won't be a problem, but you won't get CBO anyway.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Paula Winkler
Sent: Thursday, March 18, 2004 3:35 PM
To: oracle-l_at_freelists.org
Subject: RE: Stored Outlines and Optimizer Mode

Thank you Niall and Lex for your responses.

We have a 3rd-party Oracle8i system running under the RULE-based optimizer.
We have identified a handful of poor performing SQLs. We can't change the generated SQLs therefore we are looking into using stored outlines to store
the access plans for those exceptional SQLs. Our thought is CBO would kick
in for the SQLs with the stored outlines and RBO would kick in for the other
acceptable SQLs. Does this sound like it will work?

Paula W.

Do you Yahoo!?
<http://us.rd.yahoo.com/mailtag_us/*http://mail.yahoo.com> Yahoo! Mail - More reliable, more storage, less spam

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
-----------------------------------------------------------------

Received on Thu Mar 18 2004 - 18:35:49 CST