hi friends:
I have a database recently reporting latch free waitevent in statspack
report. Load on this server is increasing fast. I am not sure about:
1. Do I need to tune this latch free event? (performance now is still ok,but
capacity need to be doubled with this hardware).
2. Which latch wait contributes most to this latch free event? Is it enqueue
latch? We only have 80+ transactions per second, this seems should not cause
bottleneck, right?
Thanks
Zhu Chao.
(the following is a one-hour report)
Version: 8174 on solaris.
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 178,210.59 2,231.21
Logical reads: 73,705.28 922.80
Block changes: 1,351.63 16.92
Physical reads: 96.44 1.21
Physical writes: 179.81 2.25
User calls: 7,554.84 94.59
Parses: 608.42 7.62
Hard parses: 4.05 0.05
Sorts: 219.81 2.75
Logons: 0.24 0.00
Executes: 3,115.24 39.00
Transactions: 79.87
% Blocks changed per Read: 1.83 Recursive Call %: 23.38
Rollback per transaction %: 24.20 Rows per Sort: 19.30
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.87 In-memory Sort %: 99.96
Library Hit %: 99.64 Soft Parse %: 99.33
Execute to Parse %: 80.47 Latch Hit %: 99.64
Parse CPU to Parse Elapsd %: 87.33 % Non-Parse CPU: 99.99
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 96.85 95.29
% SQL with executions>1: 18.33 19.49
% Memory for SQL w/exec>1: 15.05 15.76
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait %
Total
Event Waits Time (cs) Wt
Time
-------------------------------------------- ------------ ------------ -----
--
db file sequential read 247,081 156,236
53.18
latch free 32,037 51,854
17.65
log file sync 229,920 37,703
12.83
db file parallel write 4,031 15,373
5.23
log file parallel write 231,245 8,837
3.01
-------------------------------------------------------------
^LLatch Activity for DB: BIDDB Instance: biddb Snaps: 8706 -8707
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg
Pct
Get Get Slps NoWait
NoWait
Latch Name Requests Miss /Miss Requests
Miss
----------------------------- -------------- ------ ------ ------------ ----
--
Token Manager 826 0.0 2,722
0.0
active checkpoint queue latch 5,274 0.0 0
archive control 16 0.0 0
archive process latch 15 0.0 0
cache buffer handles 71,939 0.0 0.0 0
cache buffers chains 458,381,552 0.1 0.0 379,261
0.0
cache buffers lru chain 769,630 0.0 0.1 330,629
0.0
channel handle pool latch 762 0.0 0
channel operations parent lat 1,144 0.0 0
checkpoint queue latch 1,678,323 0.0 0.1 0
constraint object allocation 24 0.0 0
dml lock allocation 748,437 0.0 0.0 0
enqueue hash chains 39,684,814 0.7 0.0 0
enqueues 59,663,678 2.4 0.0 0
event group latch 382 0.0 0
file number translation table 289 0.0 0
global transaction 16,901,964 0.0 0
global tx free list 5,271,806 0.2 0.0 0
global tx hash mapping 19,177,222 0.0 0.1 0
job_queue_processes parameter 56 0.0 0
ktm global data 12 0.0 0
latch wait list 5,736 2.3 0.0 5,681
0.3
library cache 20,233,930 0.4 0.1 53,113
0.5
library cache load lock 574 0.0 0
list of block allocation 514,632 0.0 0.0 0
loader state object freelist 1,366 0.0 0
longop free list 121 0.0 0
messages 1,247,705 0.8 0.0 0
multiblock read objects 14,112 0.0 0
ncodef allocation latch 56 0.0 0
process allocation 382 0.3 1.0 382
0.0
process group creation 762 0.0 0
redo allocation 3,133,111 0.1 0.0 0
redo writing 1,172,951 1.8 0.0 0
row cache objects 2,128,036 0.2 0.0 0
sequence cache 315,420 0.0 0.0 0
session allocation 5,708,886 0.4 0.0 0
session idle bit 57,210,144 0.1 0.1 0
session switching 56 0.0 0
shared pool 797,699 0.5 0.2 0
sort extent pool 1,746 0.0 0
transaction allocation 5,781,327 0.3 0.1 0
transaction branch allocation 14,003,724 1.1 0.0 0
undo global data 2,389,660 0.1 0.0 0
user lock 1,782 0.0 0
-------------------------------------------------------------
^LLatch Sleep breakdown for DB: BIDDB Instance: biddb Snaps: 8706 -8707
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps
1->4
-------------------------- -------------- ----------- ----------- ----------
--
enqueues 59,663,678 1,429,265 6,011
1423289/5941
/35/0/0
cache buffers chains 458,381,552 508,338 5,795
502579/5729/
27/3/0
enqueue hash chains 39,684,814 261,381 4,897
257220/3446/
699/16/0
transaction branch allocat 14,003,724 156,386 2,489
153926/2431/
29/0/0
library cache 20,233,930 89,412 7,273
84167/3632/1
232/381/0
session idle bit 57,210,144 37,682 2,379
35338/2315/2
5/4/0
redo writing 1,172,951 21,205 510
20698/504/3/
0/0
session allocation 5,708,886 20,666 525
20145/517/4/
0/0
transaction allocation 5,781,327 14,530 777
14092/362/7/
69/0
messages 1,247,705 10,577 168
10409/168/0/
0/0
global tx free list 5,271,806 10,066 212
9859/202/5/0
/0
row cache objects 2,128,036 5,194 20
5174/20/0/0/
0
redo allocation 3,133,111 3,754 106
3648/106/0/0
/0
shared pool 797,699 3,650 758
3237/97/291/
25/0
undo global data 2,389,660 1,952 59
1893/59/0/0/
0
checkpoint queue latch 1,678,323 251 16
235/16/0/0/0
global tx hash mapping 19,177,222 212 14
204/6/1/1/0
dml lock allocation 748,437 196 8
188/8/0/0/0
cache buffers lru chain 769,630 158 10
148/10/0/0/0
latch wait list 5,736 130 1
129/1/0/0/0
sequence cache 315,420 91 3 88/3/0/0/0
list of block allocation 514,632 74 2 72/2/0/0/0
process allocation 382 1 1 0/1/0/0/0
-------------------------------------------------------------
^LLatch Miss Sources for DB: BIDDB Instance: biddb Snaps: 8706 -8707
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
