Home » RDBMS Server » Performance Tuning » Resolving "latch free" problems
| Resolving "latch free" problems [message #135638] |
Thu, 01 September 2005 11:15  |
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  |
Frank Naude
Messages: 4580
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
|
|
|
|
Goto Forum:
Current Time: Tue Nov 12 15:48:35 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
