12c: KGLH0 process is causing Shared Pool ORA-04031 errors and latch contention

Are you looking to upgrade your Oracle database to 12.1.0.2 soon?  You might want to read "Bug 20370037 - Shared Pool from KGLH0 constantly growing causing ORA-04031 and Latch contention" from MOS note 2119923.1 because there seems to be several well known bugs with the KGLH0 process and this document has steps for different ways to mitigate this.  What does that mean?  If there are a lot of ways to encounter this in the system, then you need to prepare for encountering it so deploy the upgrade in QA and stress test to see if you run into this!!

"Latch free"/"Real-time plan statistics latch" wait events seen during slow system performance

Tonight provides us an opportunity to learn more about the SQL Monitor interface from MOS document "Slow Performance Featuring Contention on "latch free" with "Real-time plan statistics latch" Having the Most Sleeps and Causing High Operating System Activity" by searching for article ID 2030320.1 which will lead you not only to the Database SQL Tuning Guide if you were not already familiar with it, but ALSO the section detailing the Real-Time SQL Monitoring and Real-Time Database Operations.  Why would you be concerned with this?  Well, do you know how your system is setup to monitor SQL?  Would you like to AND feel comfortable that this feature will not cause you problems in the future?

Weekend Learning: Troubleshooting row cache enqueue locks

Check out MOS document ID 278316.1 to get up to speed with "Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "" which was created for Oracle databases 8i but appears to be completely relevant for us today on 11g and 12c databases!  There are a TON of other articles linked on here as well that you should take a look too!!

Magical Mystery Event Tour

I have posted before about R12 having some event changes that you need to prepare for, but a good three months in I am still seeing events that are completely new to me.  We had a session that appeared to be stalled and our investigation found the thread was in the "gcs drm freeze in enter server mode" wait event locking against LMON when it was just sitting there in the event "rdbms ipc message".  Oddly enough, the situation just resolved itself but not before I captured another event in the wild that I had never seen before: "latch: object queue header operation".  I am not quite sure what happened here, so this gets tagged as a mystery....until the next time and I can solve it

Latency heat mapping

Reading a post from my blog roll, I was looking forward to reading the originating post about how to create a latency heat map/chart but thought it would be clunky and would involve some long and drawn out installation.  Surprisingly, it is neither clunky or involved and I can't wait to run this at work tomorrow!

Of course since the original post, there is a new version of code online after feedback and the rubber meeting the road.

Concurrent Manager holding lock on a report

For some reason, we had a report which was locked up with “Could not reserve record x tries” so you couldn't View Details or Hold Request and even running a script to change the HOLD_FLAG was just hanging out.  Looking at gv$session, the user didn't have any sessions which would've lead me to believe they had this locked up so I ran my handy dandy Exadata lock script to find the session holding my script up.  The session had nothing for MODULE or ACTION, but the CLIENT_INFO "0                                                     0         " looked familiar so I bounced the Conflict Resolution Manager without any changes happening.  I started looking closer, as the report was sitting in the Quick Manager so I issued a Deactivate which took it down to only 1 Actual process and a Target of 0 but it just kept sitting there.  Looking at the log files via Processes showed just one thread Active but when I clicked on Manager Log I received this error message:

APP-FND-01632: Cannot open file <directory>/w<process ID?>.mgr for reading

Cause: [Routine] encountered an error when attempting to open file <directory>/w<process ID?>.mgr for reading.

Action: Verify that the filename is correct, the environment variables controlling that filename are correct, and the file exists.

Action: Verify that protections on that file permit reading by this program.

Knowing that there was a problem with this thread, I then issued a Terminate for the Quick Manager and once it was completed with the Actual/Target both at 0 my script finally finished running as it no longer was encountering the locking condition.  After committing my script, so the report was now Inactive/On Hold rather than Inactive/No Manager, I did an Activate for the Quick Manager and once it was back up I removed the hold on the report.  It ran to completion in 2 seconds.

Now there are obviously some questions I have about this, as to why the manager thread had just locked up when it tried to pick up or start running the request, or if the string of numbers in the file name is actually a process ID which I can then backtrack later and give you more information in a later posting.

System Wait Events Part 1

Before I go too much further with the blog, I have to do some housecleaning!  Back in August I had realized that a post went missing, so I put the URL into a draft and saved it to come back to and last week I was looking for that article......but I had never come back to it to re-write the article!  That's bad on me in general, and more specifically this post and the one last week have gone without a true origination and credit which bugs me quite a bit.

After upgrading to Exadata with 11.2.0.2, we started running into sessions hanging in the "inactive transaction branch" which were running statements over a DB link to an instance running on 10.2.0.4.  A quick search lead me to this page which not only suggested a fix (patching didn't actually resolve it), but also a way to look into our systems which is proactive and at a deeper level than we had before.

Troubleshooting latch contention by Tanel

In trying to find out what was holding latches in our new shiny 11.2.0.2 system, I started digging around on the web and lo and behold I ran across a really good article by Tanel Poder a few years ago not only explaining latches, and their contention, but giving us tools to be able to try and catch them in the act!

P.S.  I know I've taken a short break from the blog, but I've got some really good posts coming up over the next few weeks so stay tuned.