Re: Insert into CLOB field causes enqueue

Ok So u are waiting on HW enqueue.
But are the number of extents in the segment increasing?

  Reason I ask is becoz I recently hit the below bug
at a site recently 
   
  4062438 EXPERIENCING LONG HW ENQUEUE WAITS WHEN INSERTING LOBS 

  the dup of which is fixed in 11
   
  Can you tell me if you also delete from this table?
  
The way we got around this problem was to implement rolling partitions on the 
table and truncating partitions instead of deleting data
   
  HTH
-Fairlie

San Sridharan <san_oracle_dba@xxxxxxxxx> wrote:
          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@xxxxxxxxxxxxxxx>
To: san_oracle_dba@xxxxxxxxx; David Sharples <davidsharples@xxxxxxxxx>
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, October 12, 2006 6:32:16 PM
Subject: RE: Insert into CLOB field causes enqueue

  DIV {  MARGIN:0px;}      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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxx>
To: san_oracle_dba@xxxxxxxxx
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx> 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.









  






          Fairlie Rego
Senior Oracle Consultant
  
  http://el-caro.blogspot.com/
  M: +61 402 792 405
   






                                
---------------------------------
Want to be your own boss? Learn how on  Yahoo! Small Business.

Other related posts: