RE: Select statement runs slow until flush of shared pool
- From: "Jorgensen, Finn" <Finn.Jorgensen@xxxxxxxxxxxxxxxxx>
- To: Carlos Sierra <carlos.sierra.usa@xxxxxxxxx>, "cichomitiko@xxxxxxxxx" <cichomitiko@xxxxxxxxx>
- Date: Thu, 29 Nov 2012 17:01:11 -0500
Thanks to everybody who responded.
I still don't know which of the SQL statements changed execution plan but
rather than doing lengthy troubleshooting and aggravation for the client I
chose to generate stored outlines for all 8 SQL statements executed by the
stored functions while the plans were good. By pinning the good plan to the
statement the hope is that the problem doesn't return.
Thanks again.
Finn
From: Carlos Sierra [mailto:carlos.sierra.usa@xxxxxxxxx]
Sent: Wednesday, November 28, 2012 6:30 AM
To: cichomitiko@xxxxxxxxx
Cc: Jorgensen, Finn; Tanel Poder; oracle Freelists (Oracle-L@xxxxxxxxxxxxx)
Subject: Re: Select statement runs slow until flush of shared pool
Clarification here. The main SQL cannot change its execution plan while it is
execution. That is given. But this SQL executes recursively two functions that
contain some SQL statement. Those SQL statements get executed many times and
they can be invalidated and re-parsed while the parent SQL is still in
execution.
On Tue, Nov 27, 2012 at 4:25 PM, Radoulov, Dimitre
<cichomitiko@xxxxxxxxx<mailto:cichomitiko@xxxxxxxxx>> wrote:
On 27/11/2012 20:48, Jorgensen, Finn wrote:
> [...]
> I've had a couple of people suggest maybe the statement was running with a
> bad plan and thus it was slow. However, the slow running statements are fixed
> more or less immediately by a flush of the shared pool. What I'm trying to
> explain is that "currently running" SQL is fixed by the flush. 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.
Regards
Dimitre
--
//www.freelists.org/webpage/oracle-l
--
Cheers -- Carlos Sierra
http://carlos-sierra.net/
>>> 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. -IP1
--
//www.freelists.org/webpage/oracle-l
Other related posts:
