Home » RDBMS Server » Performance Tuning » Resolving "latch free" problems
Resolving "latch free" problems [message #135638] Thu, 01 September 2005 11:15 Go to next message
cubittm
Messages: 1
Registered: September 2005
Junior Member
Running 9.2.0.6.0;

My statspack says:

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free                                        100,173       3,605    67.06
CPU time                                                        1,280    23.80
SQL*Net more data to client                         4,276         243     4.53
db file sequential read                            84,554          95     1.76
db file scattered read                             64,661          86     1.60



and I know that there is an issue with latch free, but how can I further reveal the cause and finally obtain a solution.

Am I right to assume it's the library cache?

Other areas of "interest":
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   97.99    In-memory Sort %:  100.00
            Library Hit   %:   99.82        Soft Parse %:   98.49
         Execute to Parse %:   89.78         Latch Hit %:   99.62
Parse CPU to Parse Elapsd %:   26.17     % Non-Parse CPU:   86.05


 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   93.50   93.48
    % SQL with executions>1:   59.63   59.58
  % Memory for SQL w/exec>1:   54.07   54.07



                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
latch free                        100,173        276      3,605     36      7.3
SQL*Net more data to client         4,276          0        243     57      0.3
db file sequential read            84,554          0         95      1      6.2
db file scattered read             64,661          0         86      1      4.7
db file parallel write                253          0         36    142      0.0
log file sync                       1,001          0         22     22      0.1
control file parallel write           241          0          6     25      0.0
buffer busy waits                     375          0          2      4      0.0
log file parallel write             6,187          0          1      0      0.5
SQL*Net break/reset to clien          152          0          0      2      0.0
local write wait                        3          0          0     25      0.0
LGWR wait for redo copy                93          1          0      1      0.0
wait list latch free                    5          0          0     10      0.0
db file parallel read                   2          0          0     18      0.0
control file sequential read          266          0          0      0      0.0
enqueue                                 1          0          0      0      0.0
row cache lock                          1          0          0      0      0.0
SQL*Net message from client       101,717          0    162,883   1601      7.5
jobq slave wait                     1,383        956      3,459   2501      0.1
SQL*Net more data from clien        3,646          0          1      0      0.3
SQL*Net message to client         101,724          0          0      0      7.5


                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write                252          0         36    141      0.0
control file parallel write           241          0          6     25      0.0
log file parallel write             6,187          0          1      0      0.5
latch free                             19          3          1     31      0.0
db file scattered read                 49          0          0      4      0.0
LGWR wait for redo copy                93          1          0      1      0.0
db file sequential read                38          0          0      1      0.0
rdbms ipc reply                        16          0          0      0      0.0
control file sequential read          168          0          0      0      0.0
buffer busy waits                       1          0          0      0      0.0
rdbms ipc message                   3,351        700      5,352   1597      0.2
pmon timer                            249        247        713   2863      0.0
smon timer                             32          0        700  21865      0.0



                                           Pct    Avg   Wait                 Pct
                              Get          Get   Slps   Time       NoWait NoWait
Latch                       Requests      Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
Consistent RBA                    6,218    0.0             0            0
FOB s.o list latch                  494    0.0             0            0
JOX SGA heap latch                1,008    0.0             0            0
SQL memory manager latch              1    0.0             0          241    0.0
SQL memory manager worka         45,448    0.0    0.0      0            0
active checkpoint queue             530    0.0             0            0
archive control                      39    0.0             0            0
archive process latch                15    0.0             0            0
cache buffer handles            103,751    0.0    0.0      0            0
cache buffers chains         46,322,453    0.1    0.0      0    1,373,728    0.0
cache buffers lru chain          11,296    0.1    0.1      0    1,495,395    0.1
channel handle pool latc            234    0.0             0            0
channel operations paren            978    0.0             0            0
checkpoint queue latch           82,884    0.0    0.0      0       13,304    0.0
child cursor hash table          17,513    0.0    0.0      0            0
commit callback allocati             19    0.0             0            0
dictionary lookup                     2    0.0             0            0
dml lock allocation              45,297    0.1    0.0      0            0
dummy allocation                    660    0.0             0            0
enqueue hash chains              83,492    0.0    0.1      0            0
enqueues                         21,783    0.2    0.0      0            0
event group latch                    91    0.0             0            0
hash table column usage             233    0.0             0    1,840,644    0.0
job workq parent latch                0                    0          431    9.5
job_queue_processes para            124    0.0             0            0
ktm global data                      40    0.0             0            0
lgwr LWN SCN                      6,220    0.0             0            0
library cache                 6,002,146    2.8    0.6   3529        3,785   22.7
library cache load lock             186    0.0             0            0
library cache pin             4,356,882    0.1    0.0      1            0
library cache pin alloca        818,859    0.0    0.0      0            0
list of block allocation          1,216    0.0             0            0
longop free list parent              11    0.0             0           10    0.0
message pool operations              58    0.0             0            0
messages                         18,305    0.0             0            0
mostly latch-free SCN             6,248    0.0    0.0      0            0
multiblock read objects         250,616    0.0    0.0      0            0
ncodef allocation latch              13    0.0             0            0
object stats modificatio              3    0.0             0            0
post/wait queue                   1,898    0.0             0        1,009    0.0
process allocation                   91    0.0             0           91    0.0
process group creation              178    0.0             0            0
redo allocation                 145,044    0.0    0.0      0            0
redo copy                             0                    0      132,755    0.1
redo writing                     19,684    0.0             0            0
resumable state object                2    0.0             0            0
row cache enqueue latch       1,986,568    0.1    0.0      0            0
row cache objects             1,996,691    0.2    0.0      0           80    0.0
sequence cache                    6,629    0.0             0            0
session allocation            1,694,385    0.6    0.0      0            0




                                           Pct    Avg   Wait                 Pct
                              Get          Get   Slps   Time       NoWait NoWait
Latch                       Requests      Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
session idle bit                223,807    0.0    0.0      0            0
session switching                    13    0.0             0            0
session timer                       249    0.0             0            0
shared pool                   2,620,427    0.6    0.1     39            0
simulator hash latch          2,710,650    0.0    0.0      0            0
simulator lru latch               9,462    0.1    0.3      0       62,516    0.2
sort extent pool                    223    0.0             0            0
temp lob duration state              20    0.0             0            0
transaction allocation           37,111    0.0    1.0      0            0
transaction branch alloc             13    0.0             0            0
undo global data                 54,842    0.0    0.0      0            0
user lock                           839    0.8    0.0      0            0



                                      Get                            Spin &
Latch Name                       Requests      Misses      Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
library cache                   6,002,146     168,215      97,321 74194/91284/
                                                                  2411/326/0
cache buffers chains           46,322,453      58,422         297 0/0/0/0/0
shared pool                     2,620,427      16,482       1,568 15000/1402/7
                                                                  5/5/0
session allocation              1,694,385       9,405          11 9394/11/0/0/
                                                                  0
library cache pin               4,356,882       4,390          44 4346/44/0/0/
                                                                  0
row cache objects               1,996,691       3,049          12 3037/12/0/0/
                                                                  0
row cache enqueue latch         1,986,568       1,789           2 1787/2/0/0/0
library cache pin allocati        818,859         402           3 399/3/0/0/0
multiblock read objects           250,616          77           1 76/1/0/0/0
redo allocation                   145,044          49           1 48/1/0/0/0
enqueue hash chains                83,492          25           2 23/2/0/0/0
cache buffers lru chain            11,296          15           1 14/1/0/0/0
simulator lru latch                 9,462           6           2 4/2/0/0/0
transaction allocation             37,111           4           4 3/0/0/1/0

.
.
.
Re: Resolving "latch free" problems [message #136960 is a reply to message #135638] Mon, 12 September 2005 08:29 Go to previous message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
This might help:

"Most latch problems are related to the failure to use bind variables (library cache latch), redo generation issues (redo allocation latch), buffer cache contention issues (cache buffers LRU chain), and hot blocks in the buffer cache (cache buffers chain). There are also latch waits related to bugs; check MetaLink for bug reports if you suspect this is the case (oracle.com/support). When latch miss ratios are greater than 0.5 percent, you should investigate the issue."

Source: http://www.oracle.com/technology/oramag/oracle/03-jan/o13expert.html

And yes, you most definitely have library cache/ shared pool latch contention. Does your application use bind variables? Also, check that your shared pool is correctly sized.

Best regards.

Frank
Previous Topic: Function Index, is there a limit to the complexity ?
Next Topic: Can I change the DB_BLOCK_SIZE in Oracle 9i?
Goto Forum:
  


Current Time: Thu Mar 28 10:00:51 CDT 2024