RE: Pinning/keeping plans in shared pool - using dbms_advanced_rewrite with query > 2499 characters

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Mon, 10 Jul 2006 15:42:49 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45059E1A84@NT15.oneneck.corp>

Thanks Ramick. So far I've tried 6 changes to the destination_stmt with the following results:

Three resulted in "ORA-30394: source statement identical to the destination statement"

1) Added RULE hint 
2) Changed case of text
3) Changed white space

Three resulted in "ORA-30389: the source statement is not compatible with the destination statement"

1) Changing the alias name of one table
2) Adding a dummy table with no rows to the join
3) Adding " || ' ' " to one of the WHERE columns

So, it appears that it is case, comment, and whitespace insensitive and any other change I try makes the two statements incompatible for some reason. I'm running out of ideas . . .

I just posted this on asktom too so hopefully Mr. Kyte will save me.

Here is the full text of the procedure I'm trying to execute in case anyone can find a problem - this one is with only the RULE hint added to the destination_stmt, and when I run it it returns "ORA-30394: source statement identical to the destination statement":

begin
dbms_advanced_rewrite.declare_rewrite_equivalence(

name=>'tccom1101m000_rule_hint',

source_stmt=>'SELECT /*+FIRST_ROWS INDEX_ASC(a ttccom010105$idx1)*/ * FROM baan.ttccom010105 a,baan.ttccom015105 b,baan.ttccom001105

c,baan.ttcmcs009105 d,baan.ttcmcs064105 e,baan.ttccom000100
f,baan.ttfacr001105 g,baan.ttccom015105 h,baan.ttfcmg003105
i,baan.ttfacr311105 j,baan.ttfacr310105 k,baan.ttcmcs055105
l,baan.ttcmcs042107 m,baan.ttccom020105 n,baan.ttcmcs011105
o,baan.ttcmcs041105 p,baan.ttcmcs013105 q,baan.ttcmcs004105
r,baan.ttcmcs045105 s,baan.ttcmcs002105 t,baan.ttcmcs031105
u,baan.ttcmcs046105 v,baan.ttccom013105 w,baan.ttccom012105
x,baan.ttcmcs010105 y,baan.ttcmcs019105 z,baan.ttcmcs020105 a0 WHERE (1

<> 1 OR a.t$cnpa <> 4) AND z.t$ctit (+) = a.t$ctit AND y.t$ccty (+) =

a.t$ccty AND x.t$cuno (+) = a.t$cuno AND x.t$ccor (+) = a.t$ccor AND
w.t$cuno (+) = a.t$cuno ANDw.t$cdel (+) = a.t$cdel AND v.t$clan (+) =
a.t$clan AND u.t$cbrn (+) = a.t$cbrn AND t.t$ccur (+) = a.t$ccur AND
s.t$creg (+) = a.t$creg AND r.t$crte (+) = a.t$crte AND c.t$emno (+) =
a.t$crep AND q.t$cpay (+) = a.t$cpay AND p.t$cdec (+) = a.t$cdec AND
o.t$ccrs (+) = a.t$ccrs AND n.t$suno (+) = a.t$ccsu AND m.t$cotp (+) =
a.t$cotp AND l.t$cinm (+) = a.t$cinm AND k.t$mrem (+) = a.t$mrem AND
j.t$mstm (+) = a.t$mstm AND i.t$paym (+) = a.t$paym AND b.t$cuno (+) =
a.t$cuno AND b.t$cban (+) = a.t$cban AND a0.t$cbad (+) = b.t$cbad AND
h.t$cuno (+) = a.t$cuno AND h.t$cban (+) = a.t$cban AND g.t$ficu (+) =
a.t$cfcg AND f.t$ncmp (+) = a.t$iscn AND e.t$crat (+) = a.t$crat AND
d.t$ccic (+) = a.t$ccic AND (a.t$cuno >= ''500200'') ORDER BY 43',

destination_stmt=> 'SELECT /*+ RULE FIRST_ROWS INDEX_ASC(a ttccom010105$idx1)*/ * FROM baan.ttccom010105 a,baan.ttccom015105

b,baan.ttccom001105 c,baan.ttcmcs009105 d,baan.ttcmcs064105
e,baan.ttccom000100 f,baan.ttfacr001105 g,baan.ttccom015105
h,baan.ttfcmg003105 i,baan.ttfacr311105 j,baan.ttfacr310105
k,baan.ttcmcs055105 l,baan.ttcmcs042107 m,baan.ttccom020105
n,baan.ttcmcs011105 o,baan.ttcmcs041105 p,baan.ttcmcs013105
q,baan.ttcmcs004105 r,baan.ttcmcs045105 s,baan.ttcmcs002105
t,baan.ttcmcs031105 u,baan.ttcmcs046105 v,baan.ttccom013105
w,baan.ttccom012105 x,baan.ttcmcs010105 y,baan.ttcmcs019105
z,baan.ttcmcs020105 a0 WHERE (1 <> 1 OR a.t$cnpa <> 4)AND z.t$ctit (+) =
a.t$ctit AND y.t$ccty (+) = a.t$ccty AND x.t$cuno (+) = a.t$cuno AND
x.t$ccor (+) = a.t$ccor AND w.t$cuno (+) = a.t$cuno AND w.t$cdel (+) =
a.t$cdel AND v.t$clan (+) = a.t$clan AND u.t$cbrn (+) = a.t$cbrn AND
t.t$ccur (+) = a.t$ccur AND s.t$creg (+) = a.t$creg AND r.t$crte (+) =
a.t$crte AND c.t$emno (+) = a.t$crep AND q.t$cpay (+) = a.t$cpay AND
p.t$cdec (+) = a.t$cdec AND o.t$ccrs (+) = a.t$ccrs AND n.t$suno (+) =
a.t$ccsu AND m.t$cotp (+) = a.t$cotp AND l.t$cinm (+) = a.t$cinm AND

k.t$mrem (+) = a.t$mrem AND j.t$mstm (+) = a.t$mstm AND i.t$paym (+) = a.t$paym AND b.t$cuno (+) = a.t$cuno AND b.t$cban (+) = a.t$cban AND a0.t$cbad (+) = b.t$cbad AND h.t$cuno (+) = a.t$cuno AND h.t$cban (+) = a.t$cban AND g.t$ficu (+) = a.t$cfcg AND f.t$ncmp (+) = a.t$iscn AND e.t$crat (+) = a.t$crat AND d.t$ccic (+) = a.t$ccic AND (a.t$cuno >= ''500200'') ORDER BY 43', validate=>false, rewrite_mode=> 'GENERAL');

end;
/

I've also tried with rewrite_mode=> 'TEXT_MATCH'. Note the SQL statements above are using literals just for testing purposes, the actual statement coming from the application uses bind variables.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ramick Sent: Monday, July 10, 2006 3:06 PM
To: ramick_at_dotster.com; Allen, Brandon; 'Alex Gorbachev' Cc: oracle-l_at_freelists.org
Subject: RE: Pinning/keeping plans in shared pool - using dbms_advanced_rewrite with query > 2499 characters

Please disregard - from your 10053, seems every access is using a unique index scan...

Is dbms_advanced_rewrite.declare_rewrite_equivalence case sensitive or white space sensitive?

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of ramick
Sent: Monday, July 10, 2006 3:03 PM
To: Brandon.Allen_at_OneNeck.com; 'Alex Gorbachev' Cc: oracle-l_at_freelists.org
Subject: RE: Pinning/keeping plans in shared pool - using dbms_advanced_rewrite with query > 2499 characters

How about using one of the old tricks to force a different plan like col+0 or col || `` on some column where there is not already an index used?

This should make the SQL different...

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 10 2006 - 17:42:49 CDT