RE: Really strange performance issue
- From: "Ruel, Chris" <Chris.Ruel@xxxxxxx>
- To: "andrew.kerber@xxxxxxxxx" <andrew.kerber@xxxxxxxxx>, oracle-l <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 30 Oct 2014 14:47:12 +0000
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:
