RE: Insert into CLOB field causes enqueue

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Thu, 12 Oct 2006 18:32:16 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF2709EEB96F@AABO-EXCHANGE02.bos.il.pqe>

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

3A37B594 246 1 HW 325151 88427 214380 110770
961805663

3A37CD34 498 1 PS 2353 205 2188 164

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

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 Received on Thu Oct 12 2006 - 17:32:16 CDT