RE: DML on dual?
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <Andrew.Kerber@xxxxxxx>, <Dave.Herring@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 Apr 2007 14:20:33 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Kerber, Andrew W.
Sent: Thursday, April 12, 2007 11:07 AM
To: Dave.Herring@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Herring Dave - dherri
Sent: Thursday, April 12, 2007 9:42 AM
To: oracle-l@xxxxxxxxxxxxx
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.
****************************************************************************
--
//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.
============================================================================
==
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
