RE: Stored Outlines and Optimizer Mode
- From: Paula Winkler <pw41972@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 20 Mar 2004 17:19:20 -0800 (PST)
Thank you Mark! I will incorporate this into our test plan. I will share our
findings with all once this test plan gets approved and the testing is
completed.
"Powell, Mark D" <mark.powell@xxxxxxx> wrote:If it was my system I would
generate statistics for the objects. Because the database is running rule
based the presence of statistics would have no effect on the existing
application, but the statistics would be available to the CBO for the hinted
queries created via the stored outlines. This would probably allow the use of
general hints like ALL_ROWS instead of having to resort to a full series of
hints to try to lock in a detailed plan.
Just a though -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Paula Winkler
Sent: Thursday, March 18, 2004 11:07 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Stored Outlines and Optimizer Mode
Thank you Dennis, John, and Jared -
Our database configuration setting is OPTIMIZER_MODE=RULE in Oracle 8.1.7.4
with no database statistics. We came up with the optimal plans for the
exceptional SQLs by running the 3rd-party application under CBO in one of our
test environment which mirror Production. We can't use hints as we have no
control over the generated SQLs. We can't move the application completely to
CBO therefore thought using stored outlines in a Oracle8i RULE-based optimizer
environment may be a solution. Our intention is stay with the
OPTIMIZER_MODE=RULE without any database statistics and with the addition of
the handful of stored outlines. It seems stored outlines under Oracle8i+
doesn't require 1) OPTIMIZER_MODE to be CHOOSE or COST and 2) the collection of
database statistics.
Just wanted to get some insights from gurus on this great list before we
conduct additional validation testing.
- Paula W.
Jared.Still@xxxxxxxxxxx wrote:
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.
1 6: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 | &nb sp; | | | |
| 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
-----------------------------------------------------------------
Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
Other related posts:
