Re: Insert into CLOB field causes enqueue

Thank you for the response. select chr(bitand(p1,-16777216)/16777215)|| chr(bitand(p1,16711680)/65535) "Lock" from v$session_wait a where event = 'enqueue'; Lock ------ HW An extract from Don Burleson's article says the following. "HW Enqueue – This type of enqueue is used with the high-water mark of a segment; manually allocating the extents can circumvent this wait. " The tablespace that holds this table is LMT with AUTO space management. Do I have to change this setting? Whats your thought on this? Thanks ----- Original Message ---- From: "Bobak, Mark" <Mark.Bobak_at_il.proquest.com> To: san_oracle_dba_at_yahoo.com; David Sharples <davidsharples_at_gmail.com> Cc: oracle-l <oracle-l_at_freelists.org> Sent: Thursday, October 12, 2006 6:32:16 PM Subject: RE: Insert into CLOB field causes enqueue That's nice, but which one is actually ocurring while the CLOB insert is happening? The data you show is enqueue activity aggregated since instance startup....it's impossible to say which enqueue(s) occurred during your CLOB insert. You should try tracing the session that's doing the insert. That will give you a more definitive picture. -Mark -- Mark J. Bobak Senior Oracle Architect ProQuest Information & Learning There is nothing so useless as doing efficiently that which shouldn’t be done at all. –Peter F. Drucker, 1909-2005 From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of San Sridharan Sent: Thursday, October 12, 2006 6:27 PM To: David Sharples Cc: oracle-l Subject: Re: Insert into CLOB field causes enqueue Here are the enqueue, SQL> select * from v$enqueue_stat where total_wait# > 0; INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME ---------- -- ---------- ----------- ---------- ----------- ------------- 1 CF 219393 1 219392 1 4 1 HW 325116 88417 214359 110757 961794076 1 PS 2353 205 2188 164 32 1 TC 125 25 125 0 11720 1 TX 1472197 4831 1472207 4 228613475 1 US 699237 1 699237 0 1 6 rows selected. SQL> select * from x$ksqst where ksqstwat > 0; 2 ADDR INDX INST_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT -------- ---------- ---------- -- ---------- ---------- ---------- ---------- KSQSTWTM ---------- 3A37A4FC 69 1 CF 219401 1 219400 1 4 3A37B594 246 1 HW 325151 88427 214380 110770 961805663 3A37CD34 498 1 PS 2353 205 2188 164 32 ADDR INDX INST_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT -------- ---------- ---------- -- ---------- ---------- ---------- ---------- KSQSTWTM ---------- 3A37D7B4 610 1 TC 125 25 125 0 11720 3A37D9AC 631 1 TX 1472437 4831 1472447 4 228613475 3A37DC34 658 1 US 699237 1 699237 0 1 Thank you ----- Original Message ---- From: David Sharples <davidsharples_at_gmail.com> To: san_oracle_dba_at_yahoo.com Cc: oracle-l <oracle-l_at_freelists.org> Sent: Thursday, October 12, 2006 5:59:56 PM Subject: Re: Insert into CLOB field causes enqueue what type of enqueues are they On 12/10/06, San Sridharan <san_oracle_dba_at_yahoo.com> wrote: 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.

--
http://www.freelists.org/webpage/oracle-l