Oracle Scratchpad

November 30, 2012


Filed under: AWR,Bugs,Oracle,Troubleshooting — Jonathan Lewis @ 5:45 pm GMT Nov 30,2012

Sometimes you find bugs on MOS (Metalink, OCIS, whatever) that make you feel positively ill. I’ve just been on a customer site where (in passing) they mentioned that one of their historic queries against v$sqlstats now took just over one second (CPU) in when it had previously taken about 200ms on[1]. After a little checking it seemed likely that the change was possibly related to the fact that they had increased the size of the SGA significantly, allowing for a much larger shared pool and library cache; however there have been numerous code changes in the shared pool area on the route from 10g to 11g so I decided to check MOS to see if anyone else had seen a similar problem. I found this:



Fixed in 12.1

Last updated: 29th Nov 2012

There is a documented workaround in the base bug – stop collecting SQL execution statistics. This rather defeats the point of having AWR snapshots capturing all that “SQL ordered by … “ stuff. On the other hand, if some queries against v$sqlstats can cause sessions to crash or the database to hang then every AWR snapshot might be the one that hangs your database!

The bug in question references Solaris 10 on SPARC but a quick search for the bug number revealed 66 other entries in the bug database and the first one I looked at was on Redhat Linux 5; and the bug also describes itself as “platform generic”. On a positive note the status is currently “80 – Development to QA/Fix Delivered Internal”, and when I raised the reported the threat to the client they contacted their Oracle Support contact and discovered that there was a patch available already (number 15033625) and that they had already installed it as a side effect of installing a merge patch to fix a problem with corruption in the shared pool. If you search the patch lists on MOS you’ll find that there are patches for many different versions in the and range, for several different platforms.

[1] An increase from 200 ms to 1 second may not seem something worth worrying about, but the client ran the query every 10 seconds because (as it said on the packet in 10g) v$sqlstats is latch free and very cheap to run and (b) they have SLAs which mean they basically need to know if any of their critical, high-frequency, queries are going bad within one minute of the problem starting to occur.

Base Bug: 12340939, fixed in 12.1




  1. The bug is not about v$sqlstats. It is about literal replacement.
    You should look a the bugtag note13550185 .8 Bug 13550185 Hang / SGA memory corruption / ORA-7445 [kglic0] when using multiple shared pool subpools

    Comment by abel — December 4, 2012 @ 5:36 pm GMT Dec 4,2012 | Reply

  2. This reminds me of a bug in Oracle which caused Statspack level 6+ snaps to hang sometimes:


    Actually it might have been you, Jonathan, whom I heard about it first … (at a Hotsos conference?)

    Comment by Tanel Poder — December 10, 2012 @ 9:59 pm GMT Dec 10,2012 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by