Solved

High Library Cache Mutex Problem

Posted on 2014-09-25
6
901 Views
Last Modified: 2014-09-26
Running 11.2.0.4.  Database hung and we restarted the application and database.  

Looking at AWR i'm seeing:

Only Statements with Version Count greater than 20 are displayed
Version Count  Executions  SQL Id SQL Module SQL Text
32 10,826 75av7a5ga6qa5 JDBC Thin Client  SELECT 1 FROM XYF_SIZE_OFFSET ...
30 12,267 6dxm40zxyssr3 JDBC Thin Client  SELECT XF.FILE_TYPE_CODE, XU.F...
28 1 584wx6ptru9au DBMS_SCHEDULER  CREATE TABLE ODS_SUB
27 1 69x93bpx3g8um DBMS_SCHEDULER  CREATE TABLE ODS_SUB
25 21,883 7rnd050nadwn3 JDBC Thin Client  SELECT XF.FILE_TYPE_CODE, XU.F...
23 2,635 dk1g6z0sdh1zy JDBC Thin Client  SELECT XF.FILE_TYPE_CODE, XU.F...




ordered by number of sleeps desc
Mutex Type Location Sleeps Wait Time (ms)
Library Cache kglhdgn2 106 3,494,579 0
Cursor Pin kkslce [KKSCHLPIN2] 1,362,057 0
Library Cache kgllkdl1 85 1,293,054 0
Library Cache kglpin1 4 464,590 0
Library Cache kglpndl1 95 316,663 0
Library Cache kglhdgh1 64 272,937 0
Library Cache kgllkc1 57 197,836 0
Cursor Pin kksfbc [KKSCHLFSP2] 154,734


Top 10 Foreground Events by Total Wait Time



Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
library cache: mutex X 5,754 139.6K 24260 23.3 Concurrency
latch: row cache objects 3,901 104.7K 26834 17.5 Concurrency
row cache lock 1,339 68.9K 51438 11.5 Concurrency




From Oracle support i'm getting the recommendation:

alter system set "_memory_broker_stat_interval"=999;
- Set shared_pool_size=3GB
- db_cache_size=4GB

From Google, i'm seeing that modifying that undocumented parameter is a quick fix in 10.2, but not 11.2.  
Do any of the experts here know about this?
Earlier this year we were on version 11.2.02 and experienced the library cache mutex problem frequently.
At that time it turned out to be a bug for which a patch had been applied, but the patch did not work in 11.2.0.2 and we had to upgrade to 11.2.0.4.  That upgrade was early March and we did not experience any more database problems at all until today .  We applied the PSU for July 2014 a few days ago and this is the first day since applying that PSU that the database is getting moderate use.
Just wondering if possibly the July PSU broke the patch that had previously gotten rid of the problem.
0
Comment
Question by:xoxomos
  • 3
  • 3
6 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40345048
Oracle is famous for a later patch to undo a previous fix or at least create a new related bug.

This is the main reason I don't patch just because Oracle 'suggests' it.  Until I con fully regression test for all previously known problems I've had, I don't do it.  As you can tell, this can take a LOT of time!

From the previous encounter with the mutex issues, could you not create a test case that would cause the issues?  This is really good for regression testing on later releases.

I would work directly with Oracle Support on this one.
0
 

Author Comment

by:xoxomos
ID: 40345084
Oracle support is excellent for me about thirty percent of the time.  In this case seeing he has recommended :
alter system set "_memory_broker_stat_interval"=999;
and I'm seeing on Google that yes, making that parameter change resolves the issue in 10.2, but not in 11.2 has me a bit wary just now :-)
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40345144
Well, Oracle Support is really the ONLY one you should listen to when dealing with hidden parameters.

For me, I don't close the SR until I am satisfied so I have a slightly higher percentage of satisfaction.  Not much mind you...  ;)

In fact, I have two current bugs/SRs filed that have been open for over a year.  Granted, I have work-arounds for the bugs but filed the SRs to let them know.

Personally, I would rely on Support over Google.  If Support leads you down the wrong path, they are responsible for getting you back up.  Google makes no such guarantee.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:xoxomos
ID: 40346316
Our server administrator noticed the problem that occurred yesterday is the same as one that has happened in the past.  Like Oracle, there was a fix for it and the problem was not seen again until after another upgrade of the application so they are wondering if the upgrade broke the fix to the application just as i'm wondering if the last PSU broke the patch for that mutex X bug. The vendor, Blackboard Learn, has a similar reputation of it's upgrades breaking previous patches :-)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40346328
I suppose I should have added:
Only Oracle Patchsets include/rollup previous one-off patches.

Individual patches typically do NOT contain previous fixes.

The PSU notice claims they may not be cumulative:
Oracle Critical Patch Update Advisory - July 2014


Description

A Critical Patch Update (CPU) is a collection of patches for multiple security vulnerabilities. Critical Patch Update patches are generally cumulative, but each advisory describes only the security fixes added since the previous Critical Patch Update advisory. Thus, prior Critical Patch Update advisories should be reviewed for information regarding earlier published security fixes
0
 

Author Comment

by:xoxomos
ID: 40346383
Thanks.  I guess I'll need to find out what that patch was that did not work in 11.2.02 and did seem to work in 11.2.0.4 and see if it's still there since the July PSU.:-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now