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

Home -> Community -> Mailing Lists -> Oracle-L -> Insert into CLOB field causes enqueue

Insert into CLOB field causes enqueue

From: San Sridharan <san_oracle_dba_at_yahoo.com>
Date: Thu, 12 Oct 2006 14:53:19 -0700 (PDT)
Message-ID: <20061012215319.52059.qmail@web36313.mail.mud.yahoo.com>

We have an application that writes to a CLOB field in the database. The DB version is Oracle9i Enterprise Edition Release 9.2.0.7.0

INSERT INTO usr_sssn_item
(sssn_id, last_updt_dt, KEY, data_item_bl
)
VALUES (:sessionid, SYSDATE, :KEY, :dataitem
)

Recently we have been seen lots of ENQUEUE wait events and sessions that does the insert blocks other sessions. User has been complaining about performance.

Here is an extract from the STATSPACK report

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue                                             2,932       7,413    49.77
db file sequential read                            42,824       3,052    20.49
db file scattered read                             38,173       2,101    14.10
CPU time                                                        1,308     8.78
direct path read (lob)                              4,665         464     3.12
          -------------------------------------------------------------
Wait Events for DB: MERP  Instance: MERP  Snaps: 22 -23
-> s  - second
-> cs - centisecond -     100th of a second
-> ms - millisecond -    1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
enqueue                             2,932      2,129      7,413   2528      1.9
db file sequential read            42,824          0      3,052     71     27.3
db file scattered read             38,173          0      2,101     55     24.3
direct path read (lob)              4,665          0        464    100      3.0
direct path write (lob)             4,354          0        304     70      2.8
SQL*Net more data to client       156,523          0        116      1     99.8
db file parallel write                360          0         74    206      0.2
buffer busy waits                     238          0         30    126      0.2
log file sync                       1,988          0         13      7      1.3
log file parallel write             4,668          0         13      3      3.0
log file sequential read              206          0          4     21      0.1
control file parallel write           360          0          1      3      0.2
write complete waits                    1          1          1    983      0.0
latch free                            129        115          1      4      0.1
async disk IO                         393          0          1      1      0.3
log file switch completion              9          0          0     15      0.0
SQL*Net break/reset to clien            9          0          0      2      0.0
control file sequential read          501          0          0      0      0.3
library cache pin                       7          0          0      2      0.0
log file single write                   4          0          0      1      0.0
library cache load lock                 1          0          0      2      0.0
LGWR wait for redo copy                15          0          0      0      0.0
buffer deadlock                         1          1          0      0      0.0
SQL*Net message from client        60,360          0     17,688    293     38.5
SQL*Net more data from clien       23,639          0          4      0     15.1
SQL*Net message to client          60,358          0          0      0     38.5
          -------------------------------------------------------------


Is there any bug that yall aware of?

Any suggestion to troubleshoot this situation will of great help.

Thanks,
San


--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 12 2006 - 16:53:19 CDT

Original text of this message

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