Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> buffer busy wait for insert session

buffer busy wait for insert session

From: zhu chao <zhuchao_at_gmail.com>
Date: Thu, 19 May 2005 22:19:52 +0800
Message-ID: <962cf44b0505190719d3edcd4@mail.gmail.com>


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
  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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 19 2005 - 10:29:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US