Query parsing/execution plan hangs

From: <pratap.deshmukh_at_gmail.com>
Date: Thu, 14 Jun 2007 14:35:35 -0700
Message-ID: <1181856935.848741.321080@i13g2000prf.googlegroups.com>

Hi,

I have a query that just hangs for hours when I execute the query or do an explain plan. It shows wait event "db file sequential read". The v$ tables show that it waits for 1 block in the SYSTEM tablespace for the cluster C_OBJ#_INTCOL#. After a few hours the block number changes, but I did not track the block numbers.

When I kill the session, it gets marked for kill and it takes time for the session to clear up. So I am not able to try different options like deleting statistics on the table etc. Does anyone have any workaround for this?

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production

Regards,
Pratap Received on Thu Jun 14 2007 - 16:35:35 CDT