Re: Really strange performance issue

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Thu, 30 Oct 2014 09:56:02 -0500
Message-Id: <AADDF0E7-B576-4300-A8BA-673D8BE86FF3_at_gmail.com>

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_at_lfg.com> 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_at_lfg.com * Desk:317.759.2172 * Cell 317.523.8482
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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--
> http://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.**
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 30 2014 - 15:56:02 CET