Running 184.108.40.206. 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
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 220.127.116.11 and we had to upgrade to 18.104.22.168. 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.