RE: Stored outline problems

Hi, Wolfgang nice to hear from you.

Umm, I only meant the code is really the same both in PROD & TEST (which is
a copy of PROD from 3 or 4 weeks ago)  since we never used outlines in
either I'm thinking I can just generate one on TEST and import it into PROD.

        I just can't get the text portion to generate with the bind
variables as Tom Kyte points out
http://asktom.oracle.com/pls/ask/f?p=4950:8:5474914190641052077::NO::F4950_P
8_DISPLAYID,F4950_P8_CRITERIA:4144341695521
"tkyte@TKYTE816> select name, category, sql_text from user_outlines;

NAME                          CATEGORY        SQL_TEXT
----------------------------- --------------- ------------------------------
SYS_OUTLINE_0104122003150057  DEFAULT         select * from dual where dummy
                                                = :SYS_B_0

MY_OUTLINE                    MY_CATEGORY     select * from dual where dummy
                                                = 'X'

As you can see, the stored queries are very different from each other. The
one 
we generated via the CREATE OUTLINE command is exactly as we had entered it.
The 
CURSOR_SHARING code was not executed in this case, since we did not actually
run 
the query. The query text was stored verbatim. On the other hand, the query
text 
for the implicitly generated outline shows the effect of the query rewrite
for 
us. We can plainly see that our constant X was turned into a bind variable
for 
us. This SQL was stored for us.

Depending on your needs, both methods may be applicable. It is just
important to 
understand that there is a subtle different between the explicitly generated

plan, and the implicitly generated one with CURSOR_SHARING enabled.
...................

so, yes you can -- and the side effects are as above -- beyond the normal
side 
effects of cursor sharing."

        Going over my command sequence to try and identify what is missing.
Like I said I got idea from PS Red Paper we talked about before, but this is
not PS database.

        Thanks
        Larry


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Wolfgang Breitling
Sent: Tuesday, December 14, 2004 8:48 AM
To: Oracle Discussion List
Subject: Re: Stored outline problems

That's the problem with this (not so new anymore) "reply all" scheme. If 
some posters only "reply", the rest of the list is left wondering what 
was said.

Wolfson Larry - lwolfs wrote:

> Well export not a problem and there aren't any outlines on PROD.
> 
>       Larry

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l


**********************************************************************
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

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

Other related posts: