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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: