RE: Select statement runs slow until flush of shared pool
Date: Tue, 27 Nov 2012 22:12:35 -0500
Message-ID: <9CE162BC5ED2C643956B526A7EDE46FF03594458A606_at_EXM-OMF-04.Ceg.Corp.Net>
You're right Dmitri. I'm working down that path now. Reading the code, grabbing the SQL and catching SQL ID's for them. Then I'm ready to trap them next the problem happens to see if there's more than one hash plan for any of them or if a new one is produced after flushing. Once identified the plan is to lock the execution plan in with a stored outline or something similar.
Thanks for your help.
Thanks,
Finn
-----Original Message-----
From: Radoulov, Dimitre [mailto:cichomitiko_at_gmail.com]
Sent: Tuesday, November 27, 2012 4:30 PM
To: Jorgensen, Finn
Cc: Tanel Poder; oracle Freelists (Oracle-L_at_freelists.org)
Subject: Re: Select statement runs slow until flush of shared pool
On 27/11/2012 22:25, Radoulov, Dimitre wrote:
> On 27/11/2012 20:48, Jorgensen, Finn wrote:
>> The execution plan doesn't change once the statement is running.
>> That's why I went down the path of latches etc.
>
> As I already said, we had a very similar issue, flushing the shared
> pool seemed to affect the plan immediately.
> I don't know if the plan of the SQL invoked by the function is
> guaranteed to remain the same during execution.
Just to clarify: by "the function" I mean the "function calls in the select" list.
Regards
Dimitre
>>> This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the
addressee. If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. -IP2
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 28 2012 - 04:12:35 CET