buffer busy wait for insert session
- From: zhu chao <zhuchao@xxxxxxxxx>
- To: "Oracle-L (oracle-l@xxxxxxxxxxxxx)" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 May 2005 22:19:52 +0800
Hi, All,=20
We have a database with buffer busy wait that we can't explain.=20
A huge table (1.5TB) with long raw column and high concurrency insert.
(6-9M rows inserted per day)
No FK etc. Just one PK and two non-unique indexes.(created_date and
end_date column)
Table/index has set the freelists=3D23.
But we are seeing high buffer busy wait event for the insert session.
The reason code is mainly 120(concurrent read) and 220(concurrent
modify)
What is the root cause for this buffer busy wait? Could it because
index problem? Can someone explain? Is there any solution to this
situlation?
SQL:
=20
insert into gallery ( item_id, x_size, y_size, start_date,
end_date, thumbnail, thumbnail_len )
values
( :item_id, :x_size, :y_size, TO_DATE(:start_date, :"SYS_B_0"),
TO_DATE(:end_date, :"SYS_B_1"), :thumbnail, :thumbnail_len )
call count cpu elapsed disk query current =
rows
------- ------ -------- ---------- ---------- ---------- ---------- -----=
-----
Parse 0 0.00 0.00 0 0 0 =
0
Execute 837 11.24 185.55 1130 91 35956 =
825
Fetch 0 0.00 0.00 0 0 0 =
0
------- ------ -------- ---------- ---------- ---------- ---------- -----=
-----
total 837 11.24 185.55 1130 91 35956 =
825
=20
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 18
=20
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Wait=
ed
---------------------------------------- Waited ---------- ----------=
--
SQL*Net message from client 837 5.14 163.=
56
buffer busy waits 31720 0.12 158.=
55
db file sequential read 1125 0.10 12.=
39
latch free 474 0.03 3.=
33
log file sync 918 0.76 3.=
08
SQL*Net message to client 836 0.01 0.=
01
SQL*Net break/reset to client 24 0.01 0.=
01
db01$>grep "buffer busy waits" prod_ora_10686.trc|grep "p3=3D120" |wc -l
27536
db01$>grep "buffer busy waits" prod_ora_10686.trc|grep "p3=3D220" |wc -l
4183
--=20
Regards
Zhu Chao
www.cnoug.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
