RE: Urgent: db file sequential read

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Wed, 31 Mar 2004 19:49:35 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNCENKCBAA.lex.de.haan@naturaljoin.nl>

Hi Stefan,

not exactly giving you an answer, but this DELETE statement with a correlated subquery to the table itself is pretty expensive. maybe you can rewrite it into a DELETE on an updatable view instead? something like:

SQL> DELETE FROM ( some smart self-join expression goes here ) WHERE ... ;

that might kill two birds with a single stone: better performance, and wait problem solved ...

Cheers,
Lex.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Stefan Jahnke Sent: Wednesday, March 31, 2004 18:34
To: Oracle DBA List (E-Mail)
Subject: Urgent: db file sequential read - wait

Hi list

I have a session waiting forever on a "db file sequential read" wait event during a mass delete.
The statement looks like this:

DELETE
FROM tcdhispersversnr a
WHERE EXISTS

         (SELECT idvistamaster
            FROM tcdhispersversnr b
           WHERE b.pkfk          =  a.pkfk
             AND b.idvistamaster =  a.idvistamaster
             AND a.idvistadetail IS NULL
             AND b.idvistadetail IS NOT NULL
             AND (


                   (CAST(A.ZHI_SYSEINDAT AS TIMESTAMP(0)) >=

CAST(B.ZHI_SYSEINDAT AS TIMESTAMP(0))
                    AND
                    CAST(A.ZHI_SYSEINDAT AS TIMESTAMP(0)) <
CAST(B.ZHI_SYSERSDAT AS TIMESTAMP(0)))
                   OR


                   (CAST(B.ZHI_SYSEINDAT AS TIMESTAMP(0)) >=

CAST(A.ZHI_SYSEINDAT AS TIMESTAMP(0))
                    AND
                    CAST(B.ZHI_SYSEINDAT AS TIMESTAMP(0)) <
CAST(A.ZHI_SYSERSDAT AS TIMESTAMP(0)))
                 )
             AND (


                   (A.ZHI_SYSGUADAT >= B.ZHI_SYSGUADAT

                    AND
                    A.ZHI_SYSGUADAT <  B.ZHI_SYSGUBDAT)
                   OR


                   (B.ZHI_SYSGUADAT >= A.ZHI_SYSGUADAT

                    AND
                    B.ZHI_SYSGUADAT <  A.ZHI_SYSGUBDAT)
                 )
         )

The block the session is waiting for belongs to an index on table tcdhispersversnr. I was thinking it might be a problem that the index is updated by the delete part of the statement while the sub-select part of the statement uses the index. Both the table in the delete-part and the table being queried in the sub-select are the same. NOTHING else is happening on this machine. No locks or latches or memory or undo problems whatsoever.
Anybody any idea what might cause that problem?

At totally frustrated yours,
Stefan

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Mar 31 2004 - 11:51:11 CST

RE: Urgent: db file sequential read - wait