Re: Insert into CLOB field causes enqueue

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Thu, 12 Oct 2006 17:07:29 -0700 (PDT)
Message-ID: <20061013000729.26725.qmail@web31902.mail.mud.yahoo.com>

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_at_yahoo.com> 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

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

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

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.

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

Received on Thu Oct 12 2006 - 19:07:29 CDT