RE: DML on dual?

From: Ken Naim <kennaim_at_gmail.com>
Date: Thu, 12 Apr 2007 14:20:33 -0400
Message-ID: <00ab01c77d2f$4353aa80$0300a8c0@kenlaptop>

I have not seen this situation, but I have a thought on it. At one site, a client had all their sequences with a cache size 0 to "enforce" a nogap policy which obviously didn't work instead it caused a sql loader job to run extremly slow. After tracing the session I saw a ton of recursive sql on the internal sequnence tables which is a serial operation (to prevent duplicate sequence values) using locks.

While the sql loader job does not, afaik, select from dual the lock might be caused by underlying sequence refreshing its values too often.

Ken
-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kerber, Andrew W.
Sent: Thursday, April 12, 2007 11:07 AM
To: Dave.Herring_at_acxiom.com; oracle-l_at_freelists.org Subject: RE: DML on dual?

Is perhaps doing a select from dual to bump a sequence as part of a transaction? I suppose that might get a lock...

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Herring Dave - dherri
Sent: Thursday, April 12, 2007 9:42 AM
To: oracle-l_at_freelists.org
Subject: DML on dual?

Folks,

I've got an odd problem on our production database where it seems physical writes and row lock waits are happening on SYS.DUAL. These are not causing any major performance problems, but it concerns me that some sort of DML (I'm assuming) is being performed against SYS.DUAL, which in my mind should never happen.

I came to the conclusion about DML against SYS.DUAL based on data from STATS$SEG_STAT for that segment, where I see physical writes and row lock waits, at most 100 or so per day.

I've set auditing on for INSERT, UPDATE, LOCK, DELETE on SYS.DUAL, but nothing has shown up. I've search V$SQL for any SQL statement referencing DUAL/SYS.DUAL but have found nothing beyond SELECT statements.

Anyone else see this behavior and/or have any clues on how to track what's causing this activity?

Database is 9.2.0.6, on Tru64 5.1b, 10 x 40GB GS1280 server.

Thanks.

Dave

David C. Herring, DBA | A c x i o m Delivery Center Organization 630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.

--

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

--

NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 12 2007 - 13:20:33 CDT