Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Insert into CLOB field causes enqueue
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-lReceived on Thu Oct 12 2006 - 16:53:19 CDT
![]() |
![]() |