RE: Stored Outlines and Optimizer Mode

  • From: Jared.Still@xxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 18 Mar 2004 16:35:28 -0800

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

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   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
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   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@xxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
 03/18/2004 02:10 PM
 Please respond to oracle-l

 
        To:     "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
        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@xxxxxxxxxxxxx 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx 
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Paula Winkler
Sent: Thursday, March 18, 2004 3:35 PM
To: oracle-l@xxxxxxxxxxxxx
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@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: