From oracle-l-bounce@freelists.org Fri Mar 19 07:46:36 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2JDkaP29762 for ; Fri, 19 Mar 2004 07:46:36 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2JDkKo29704 for ; Fri, 19 Mar 2004 07:46:20 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 957F2390B58; Fri, 19 Mar 2004 08:43:23 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 19 Mar 2004 08:42:10 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from brcexc04.churchill.com (mx.churchill.com [193.130.154.133]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9664F39099F for ; Fri, 19 Mar 2004 08:42:09 -0500 (EST) Received: by brcexc04.churchill.com with Internet Mail Service (5.5.2653.19) id ; Fri, 19 Mar 2004 13:48:21 -0000 Message-ID: <4661DD1EF5C4EF4B9D2BCE3A3E52EF3302765555@brcexm04.churchill.com> From: Srinivasan Vasan To: "'oracle-l@freelists.org'" Cc: Srinivasan Vasan Subject: RE: Stored Outlines and Optimizer Mode Date: Fri, 19 Mar 2004 13:48:29 -0000 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C40DB8.DC8E3ACE" X-archive-position: 1228 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Vasan.Srinivasan@churchill.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l ------_=_NextPart_001_01C40DB8.DC8E3ACE Content-Type: text/plain Paula, Please let me know if this works out and what you did. As far as I know, stored outlines do not work under RULE as they are a CBO feature. However, it will still work without database statistics, if you are telling the query what to do (as we would in RULE). For example, we have OPTIMIZER_MODE=CHOOSE at our site and we use a mixture of RULE and COST optimized queries. We do not use any database statistics - instead we use HINTS (in some of the queries, which make them use CBO), but since the query is already optimally written (using RULEs), we are able to make the best use of a RULE-COST combination. Watch out for when you upgrade to 9i - since the CBO is still undergoing enhancements and the RBO is sitting still, you may hit unanticipated problems with queries mis-behaving. Cheers, Vasan (x5707) Mailpoint 28 ============================================ Vasan Srinivasan * 020 8313 5707 Infrastructure Service Manager * 020 8313 5646 Oracle Technologies Churchill Insurance, IT Department Purple Floor, Phase 1, Churchill Court 1 Westmoreland Road, Bromley, Kent, BR1 1DP. * Vasan.Srinivasan@churchill.com Mobile * 07710 154 987 http://oratech ============================================ Views Presented here are not necessarily the views of my Employer ============================================ -----Original Message----- From: Paula Winkler [mailto:pw41972@yahoo.com] Sent: 19 March 2004 04:07 To: oracle-l@freelists.org 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@radisys.com 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 Sent by: oracle-l-bounce@freelists.org 03/18/2004 02:10 PM Please respond to oracle-l To: "'oracle-l@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@lifetouch.com -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Paula Winkler Sent: Thursday, March 18, 2004 3:35 PM To: oracle-l@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!? 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@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 ----------------------------------------------------------------- Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam ___________________________________________________________________________ This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. ------_=_NextPart_001_01C40DB8.DC8E3ACE Content-Type: text/html

Paula,

 

Please let me know if this works out and what you did. As far as I know, stored outlines do not work under RULE as they are a CBO feature. However, it will still work without database statistics, if you are telling the query what to do (as we would in RULE). For example, we have OPTIMIZER_MODE=CHOOSE at our site and we use a mixture of RULE and COST optimized queries. We do not use any database statistics - instead we use HINTS (in some of the queries, which make them use CBO), but since the query is already optimally written (using RULEs), we are able to make the best use of a RULE-COST combination.

Watch out for when you upgrade to 9i - since the CBO is still undergoing enhancements and the RBO is sitting still, you may hit unanticipated problems with queries mis-behaving.

Cheers,

Vasan (x5707)
Mailpoint 28
============================================
Vasan Srinivasan                                   ( 020 8313 5707
Infrastructure Service Manager              2 020 8313 5646
Oracle Technologies
Churchill Insurance, IT Department
Purple Floor, Phase 1, Churchill Court
1 Westmoreland Road,
Bromley, Kent, BR1 1DP.
* Vasan.Srinivasan@churchill.com
Mobile ( 07710 154 987
http://oratech
============================================
Views Presented here are not necessarily the views
                         
of my Employer
============================================

-----Original Message-----
From: Paula Winkler [mailto:pw41972@yahoo.com]
Sent:
19 March 2004 04:07
To: oracle-l@freelists.org
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@radisys.com 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@LIFETOUCH.COM>
Sent by: oracle-l-bounce@freelists.org

 03/18/2004 02:10 PM
 Please respond to oracle-l

       
        To:        "'oracle-l@freelists.org'" <oracle-l@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@lifetouch.com

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

Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam


___________________________________________________________________________



This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you.


Churchill Insurance Group plc. Company Registration Number - 2280426. England.


Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP.



------_=_NextPart_001_01C40DB8.DC8E3ACE-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------