Re: Really strange performance issue
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: "Ruel, Chris" <Chris.Ruel@xxxxxxx>
- Date: Thu, 30 Oct 2014 09:56:02 -0500
I don't think that's it, I can get rid of the problem by using a plan baseline,
but I really need to know the cause. Library hit is over 99%, SQL with
executions >1 is 93%
Sent from my iPad
> On Oct 30, 2014, at 9:47 AM, Ruel, Chris <Chris.Ruel@xxxxxxx> wrote:
>
> Do you have lots of unshared SQL in your shared pool? I had an issue similar
> to this many years ago on 10gR2. If there was a ton of unshared SQL in the
> shared pool, Oracle had to spend a lot of time searching for a match (only to
> never ind one and re-parse anyway) However, I never got near 15 minutes so
> that is a little baffling. I am talking maybe a minute vs. sub-second (after
> a flush). We had two solutions on the table. One, a job that flushed the
> shared pool once per hour. Two, shrinking the shared pool. Our shared pool
> was something like 4GB. Shrinking it to 1 GB really helped. Also, we
> disabled ASSM so the SP would not continue to grow, stealing memory from our
> DB Cache due the non-shared SQL clogging it up. There was just no sense in a
> large shared pool when a majority of our SQL was not shared (vendor app).
>
> Chris..
>
> _____________________________________________________________________
> Chris Ruel * Oracle Database Administrator * Lincoln Financial Group
> cruel@xxxxxxx * Desk:317.759.2172 * Cell 317.523.8482
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Andrew Kerber
> Sent: Thursday, October 30, 2014 10:38 AM
> To: oracle-l
> Subject: Really strange performance issue
>
> 11.2.0.3 EE on aix
>
> I have a query that runs in .01 seconds when not in shared pool. If it is
> present in the shared pool it takes over 15 minutes to run. In other words,
> the first time I run it, it runs immediately. The second time, it takes 15
> minutes. If I flush the shared pool it again runs in .01 sec. This looks
> like a bug to me, but I can't find it. Anyone have any ideas on this?
>
> Sent from my iPad--
> //www.freelists.org/webpage/oracle-l
>
>
> Notice of Confidentiality: **This E-mail and any of its attachments may
> contain
> Lincoln National Corporation proprietary information, which is privileged,
> confidential,
> or subject to copyright belonging to the Lincoln National Corporation family
> of
> companies. This E-mail is intended solely for the use of the individual or
> entity to
> which it is addressed. If you are not the intended recipient of this E-mail,
> you are
> hereby notified that any dissemination, distribution, copying, or action
> taken in
> relation to the contents of and attachments to this E-mail is strictly
> prohibited
> and may be unlawful. If you have received this E-mail in error, please notify
> the
> sender immediately and permanently delete the original and any copy of this
> E-mail
> and any printout. Thank You.**
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
