| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Stored outline problems
yep -- and if my memory serves me right, this is because there were some performance issues with the combination of stored outlines and cursor sharing force...
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Wolfson Larry - lwolfs
Sent: Monday, December 20, 2004 19:52
To: lawrence.wolfson_at_acxiom.com; breitliw_at_centrexcc.com; Oracle Discussion
List
Subject: RE: Stored outline problems
I got a reply back from Oracle Support.
They referenced an unpublished Note 132547.1 Using Stored Outlines, Which
mentions on page 4:
"Stored outlines are not used when:
o A hint in the stored outline becomes invalid. o CURSOR_SHARING = FORCE
CURSOR_SHARING = FORCE disables the use of stored outlines. CURSOR_SHARING was introduced in Oracle8i Release 2. It internally replaces literals values in queries with bind variables, thus allowing these statements to be shared.
Stored outlines will not be used if not all hints are valid. This is in contrast to normal hints where if one hint fails (for example, an index has been removed) the rest of the hints are still considered"
I did get the SO to work when I changed session to CS=SIMILAR. I'll suggest to applications they try same with ALTER SESSION in their code.
No sure why this is unpublished as it was last "Updated-Date: 23-OCT-2002 03:59:49" Larry
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Wolfson Larry - lwolfs
Sent: Tuesday, December 14, 2004 9:57 AM
To: breitliw_at_centrexcc.com; Oracle Discussion List
Subject: 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_at_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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
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 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Dec 20 2004 - 13:43:04 CST
 |
 |