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

Search Discussions

  • Kerber, Andrew W. at Apr 12, 2007 at 3:06 pm
    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 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
  • Herring Dave - dherri at Apr 12, 2007 at 4:57 pm
    Could be. It just seems odd that the object DUAL would incur physical
    writes and row lock waits.

    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
    -----Original Message-----
    From: Kerber, Andrew W.
    Sent: Thursday, April 12, 2007 10:07 AM
    To: Herring Dave - dherri; 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...
    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.
  • Ken Naim at Apr 12, 2007 at 6:20 pm
    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
    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
    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
  • Ghassan Salem at Apr 12, 2007 at 5:44 pm
    David,
    try to capture which session is doing this (looking into v$sql), and then
    you'll have at least the program, ...
    rgds
    On 4/12/07, Herring Dave - dherri wrote:

    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-4762office |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

    --
    http://www.freelists.org/webpage/oracle-l
  • Herring Dave - dherri at Apr 12, 2007 at 5:54 pm
    That's part of the problem. I don't know who is doing this, even after
    trolling through V$SQL. All statements that reference DUAL are SELECTs.
    I guess I could set 10046 system-wide for a short period, until I find a
    .trc file with an event of row lock wait, but that seems to involve a
    little too much brute force.



    I could also check V$SEGSTAT every minute, checking for an increase in
    the appropriate segment event for DUAL, then when an increase is found
    scan V$SQL for a LAST_LOAD_TIME greater or equal to a minute ago. Maybe
    a little less brute force, but still seems to be a rather large hammer
    for such a small nail.



    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

    From: Ghassan Salem
    Sent: Thursday, April 12, 2007 12:45 PM
    To: Herring Dave - dherri
    Cc: oracle-l_at_freelists.org
    Subject: Re: DML on dual?



    David,
    try to capture which session is doing this (looking into v$sql), and
    then you'll have at least the program, ...
    rgds

    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.
  • John Darrah at Apr 12, 2007 at 6:19 pm
    I've seen several oracle trace files that have the statement "SELECT 1 FROM
    DUAL FOR UPDATE NOWAIT" I have no idea what the purpose of that statement is
    but it probably explains what you are seeing.
    On 4/12/07, Herring Dave - dherri wrote:

    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-4762office |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

    --
    http://www.freelists.org/webpage/oracle-l
  • Herring Dave - dherri at Apr 12, 2007 at 6:37 pm
    Good call! I didn't see this previously, but then again I didn't search for it specifically:

    LAST_LOAD_TIME LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS FIRST_LOAD_TIME CPU_TIME

    ------------------- ------------------ ------------------ ------------------ ------------------- ------------------
    ELAPSED_TIME
    ------------------

    SQL_TEXT

    2007-04-12/08:59:30 1 1 927 2007-04-06/20:30:02 352600
    539648

    SELECT NULL FROM DUAL FOR UPDATE NOWAIT

    I also now have the userid that generates these. At least now I know. Thanks again.

    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

    From: John Darrah
    Sent: Thursday, April 12, 2007 1:20 PM
    To: Herring Dave - dherri
    Cc: oracle-l_at_freelists.org
    Subject: Re: DML on dual?

    I've seen several oracle trace files that have the statement "SELECT 1 FROM DUAL FOR UPDATE NOWAIT" I have no idea what the purpose of that statement is but it probably explains what you are seeing.

    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.
  • John Darrah at Apr 12, 2007 at 6:44 pm
    Looks like this was something sqlplus did until version 10.2 to check if the
    database was read only or not. From a note I found on metalink:

    In 9.0 SQLPLUS was altered to issue an "select null from dual for
    update nowait"
    statement to check if the target database is READ ONLY. This is no longer
    required for 9.2 database and so SQLPLUS should no longer issue this SQL.

    The doc says it was fixed in 10.2

    Thanks
    John
    On 4/12/07, Herring Dave - dherri wrote:

    Good call! I didn't see this previously, but then again I didn't search
    for it specifically:

    LAST_LOAD_TIME LOADED_VERSIONS OPEN_VERSIONS
    EXECUTIONS FIRST_LOAD_TIME CPU_TIME
    ------------------- ------------------ ------------------
    ------------------ ------------------- ------------------
    ELAPSED_TIME
    ------------------
    SQL_TEXT
    ------------------------------------------------------------------------------------------------------------------------------------
    2007-04-12/08:59:30 1 1 927
    2007-04-06/20:30:02 352600
    539648
    SELECT NULL FROM DUAL FOR UPDATE NOWAIT
    I also now have the userid that generates these. At least now I
    know. Thanks again.

    Dave


    ___________________________________
    David C. Herring, DBA| A c x i o m Delivery Center Organization
    630-944-4762office |630-430-5988 wireless|630-944-4989 fax
    ________________________________________
    From: John Darrah
    Sent: Thursday, April 12, 2007 1:20 PM
    To: Herring Dave - dherri
    Cc: oracle-l_at_freelists.org
    Subject: Re: DML on dual?

    I've seen several oracle trace files that have the statement "SELECT 1
    FROM DUAL FOR UPDATE NOWAIT" I have no idea what the purpose of that
    statement is but it probably explains what you are seeing.

    ***************************************************************************
    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
  • Tanel Poder at Apr 14, 2007 at 5:07 am
    Good finding...


    This sqlplus'es "select from dual for update" never made sense to me..


    Tanel.

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of John Darrah
    Sent: Friday, April 13, 2007 02:45
    To: Herring Dave - dherri
    Cc: oracle-l_at_freelists.org
    Subject: Re: DML on dual?

    Looks like this was something sqlplus did until version 10.2 to check if the
    database was read only or not. From a note I found on metalink:
    In 9.0 SQLPLUS was altered to issue an "select null from dual for update
    nowait"

    statement to check if the target database is READ ONLY. This is no longer

    required for 9.2 database and so SQLPLUS should no longer issue this SQL.


    The doc says it was fixed in 10.2


    Thanks
    John
  • Tim Hall at Apr 12, 2007 at 8:44 pm
    I'm sure I saw this problem with SELECT 1 FROM DUAL FOR UPDATE NOWAIT on an
    older version of Oracle a few years ago. If I recall correctly, we traced
    it to an internal Oracle process... either the OCI interface or DBMS_PIPE
    or ??? I just can't remember for sure which one it was. We eventually
    realized it was some internal application's lame way of making sure that
    there was only one instance of a certain process running at any point in
    time.

    I realize that's not much to go on, but maybe it will trigger someone else's
    memory... Tim
    On 4/12/07, John Darrah wrote:

    I've seen several oracle trace files that have the statement "SELECT 1
    FROM DUAL FOR UPDATE NOWAIT" I have no idea what the purpose of that
    statement is but it probably explains what you are seeing.
    On 4/12/07, Herring Dave - dherri wrote:

    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-4762office |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

    --
    http://www.freelists.org/webpage/oracle-l
  • Taft, David at Apr 12, 2007 at 7:29 pm
    John,


    Hmmm, my memory may not be serving me too well here, but this reminds me of
    something I read in Tom Kyte's newest book. I don't have the book with me,
    so I can't look it up, but I think he had an example of some sort of
    home-grown concurrency control mechanism. Probably an example of how not to
    code. Anyway, I believe the example was such that a session had to hold
    this lock for the code to proceed. Darn, the one day I don't have that book
    on hand.


    Cheers,


    David Taft

    From: John Darrah

    I've seen several oracle trace files that have the statement "SELECT 1 FROM
    DUAL FOR UPDATE NOWAIT" I have no idea what the purpose of that statement is
    but it probably explains what you are seeing.
  • Taft, David at Apr 12, 2007 at 7:48 pm
    John,


    Since I didn't have Tom's book with me, I decided to check his website.
    Probably had my facts confused. Right author, wrong example? Anyway, it
    appears that SQL*Plus connections generate this same statement.


    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15628139
    56388
    <http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813
    956388>
    SELECT NULL FROM DUAL FOR UPDATE NOWAIT; December 27, 2005


    David

    -----Original Message-----
    From: Taft, David
    Sent: Thursday, April 12, 2007 3:30 PM
    To: 'darrah.john_at_gmail.com'; Dave.Herring_at_acxiom.com
    Cc: oracle-l_at_freelists.org
    Subject: RE: DML on dual?

    John,


    Hmmm, my memory may not be serving me too well here, but this reminds me of
    something I read in Tom Kyte's newest book. I don't have the book with me,
    so I can't look it up, but I think he had an example of some sort of
    home-grown concurrency control mechanism. Probably an example of how not to
    code. Anyway, I believe the example was such that a session had to hold
    this lock for the code to proceed. Darn, the one day I don't have that book
    on hand.


    Cheers,


    David Taft

    From: John Darrah

    I've seen several oracle trace files that have the statement "SELECT 1 FROM
    DUAL FOR UPDATE NOWAIT" I have no idea what the purpose of that statement is
    but it probably explains what you are seeing.

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 12, '07 at 2:41p
activeApr 14, '07 at 5:07a
posts13
users8
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase