Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: CBO & different execution plans

Re: CBO & different execution plans

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 12 Mar 2007 15:44:03 -0700
Message-ID: <1173739442.992506.28540@t69g2000cwt.googlegroups.com>


On Mar 12, 6:16 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Charles Hooper wrote:
> > *
> > ERROR at line 1:
> > ORA-30394: source statement identical to the destination statement
> > ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
> > ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
> > ORA-06512: at line 2
>
> > It would be nice to use DBMS_ADVANCED_REWRITE to add a hint, such as
> > (for the OP):
> > /*+ LEADING (HTS_ASSAY HTS_COMPOUND_LOT HTS_ASSAY_RESULT) */
>
> > And leave the rest of the SQL statement alone.
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> I agree. Maybe you could fool the CBO by making some inconsequential
> change so that it would see the statement as different. For example:
>
> For example:
> WHERE C2+0 = x+0;
>
> Though, of course, that can cause unintended collateral damage.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

It looks like you might have a good work around, as long as the extra predicate does not significantly affect the cost. Here is an example: BEGIN
  SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE

  ( NAME             => 'T1_EQ2',
    SOURCE_STMT      => 'SELECT * FROM T1 WHERE C2=5',
    DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5
AND SYSDATE=SYSDATE',
    VALIDATE         => FALSE,
    REWRITE_MODE     => 'TEXT_MATCH' );

END;
/

SELECT
  *
FROM
  T1
WHERE
  C2=5;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); SQL_ID 7q30ztw0h1w4t, child number 1



SELECT * FROM T1 WHERE C2=5 Plan hash value: 3332582666

| Id | Operation | Name | Starts | E-Rows | A-Rows | A- Time | Buffers |

|* 1 | FILTER | | 1 | | 200 | 00:00:00.01 | 37 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 200 | 200 | 00:00:00.01 | 37 |

Predicate Information (identified by operation id):


   1 - filter(SYSDATE@!=SYSDATE@!)
   2 - filter("C2"=5)

And now to put the system back to normal: EXEC SYS.DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('T1_EQ2'); SQL_ID 7q30ztw0h1w4t, child number 0



SELECT * FROM T1 WHERE C2=5 Plan hash value: 3586113557

| Id | Operation | Name | Starts | E-Rows | A-Rows | A- Time | Buffers |

|* 1 | INDEX RANGE SCAN| IND_JT2 | 1 | 200 | 200 | 00:00:00.01 | 16 |

Predicate Information (identified by operation id):


   1 - access("C2"=5)

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Mar 12 2007 - 17:44:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US