From:Steve Adams
Date:14-Dec-2000 04:54
Subject:   library cache latch contention

Well done on your investigation so far. To get the same number of library cache latches on both nodes you can set the _kgl_latch_count parameter to 29. Alternatively, you can probably resolve the problem by just reducing the shared pool size.

The large number of objects on child library cache latch #1 is probably indicative of a problem with a large version count. Please check with APT Script version_count.sql.

Platform
Oracle RDBMS 7.3.4.1
Sun OS 2.6
Oracle Mfg 10.7 NCA
The Shared Pool size 350 MB
Shared Pool reserved 35 MB
Oracle Block Size 8K

Problem
The user complaining that the system is very slow and I checked the wait events and found that there are 78 session which are waiting on library cache, shared pool and row cache objects latches.

Action/Investigation
I ran your script latch_levels.sql which shows library cache child lacthes 17. And I generated the trace file using the following sql statement:

alter session set events 'immediate trace name library_cache level 3'
From the trace file I grep the latch= pattern and counted for each latch number (the output is given below). The output shows the distribution of latch and latch 1 is very hot. (Am I interpreting correctly?)

Question
We are running the production database in E10K cluster environment having two nodes. Whenever the database fail over to 2nd node we experience the latch problem but on the other node we do not have any such problem. While we are using the same init.ora parameter file, the number of library latch children are different on node 1 it is 29 and on node 2 it 17. (Is it because we are having 28 CPU on node 1 and 16 on node 2? So, can the number of library cache latches be tuned? If yes then how, please. Can I affect the distribution of latches so that distribution should not get skewed, if yes then how?


latch 0 =      368
latch 1 =     3061
latch 2 =      407
latch 3 =      375
latch 4 =      366
latch 5 =      354
latch 6 =      387
latch 7 =      376
latch 8 =      388
latch 9 =      422
latch 10 =      390
latch 11 =      379
latch 12 =      386
latch 13 =      396
latch 14 =      398
latch 15 =      392
latch 16 =      348