Charles,
Thanks for the input. Yes, we've thought about that but this particular one requires many cursors to be pinned and I do not want to polute the OUTLN tables with these. However, the response time for this transaction is critical (we want it to be 1-2 seconds at most).
I guess the question boils down to:
How to capture some SQL's in v$sqltext and execute these using the same hash values? There has to be a way.
Thanks.
From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
Reply-To: sacrophyte@xxxxxxxxx
To: binhpham15@xxxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
Subject: Re: Pinning cursors knowing the hash_values and SQL's ahead of time
Date: Sat, 2 Dec 2006 06:37:33 -0600
You are attempting to pin the cursors so they do not have to be hard parsed again, right? What about using outlines?
binhpham15@xxxxxxxxxxx> wrote:Trying to automate a process of pinning cursors of certain SQL's after database bounce.These are the data:
1. Know the hash values of these SQL's.
2. Have captured these SQL's from V$SQLTEXT and V$SQLTEXT_WITH_NEWLINES.
3. Can generate SQL's to pin these cursors.Problems:
1. Can't pin what is not there.
2. Have written PLSQL to retrieve these SQL's from (2) above and execute them in hope that they would produce the same hash values and then I can pin them.
3. Since I could not create the same hashes, no use to pin them.The results were not successful and different hash_values are created than the one created from the original Oracle forms.
Did I miss anything? Any idea would be greatly appreciated.
Thank you.
(Note: thinking about using Mercury tool or Oracle form record/playback to execute the forms before pinning but this is not what I'd like to do)
Talk now to your Hotmail contacts with Windows Live Messenger. -- //www.freelists.org/webpage/oracle-l
--
Charles Schultz
