Database Administration (MOSC)

MOSC Banner

FTS takes forever...

edited Sep 26, 2011 10:21PM in Database Administration (MOSC) 5 commentsAnswered ✓
Hi!
I have this query that have taken over 7 hours! Its stuck on "db file sequential read" on table ORDLOC, its doing a FTS...

The statistics are up to date and no new plan.

The table involved is 3.1 million rows and analysed the 9th of September.

The SQL:

SELECT 'X'
  FROM DUAL
 WHERE EXISTS
          (SELECT DISTINCT 'X'
             FROM ORDLOC OL,
                  STORE S,
                  ORDHEAD OH,
                  BLA_WH_MARKET LWM
            WHERE     S.DEFAULT_WH = LWM.WH
                  AND S.STORE = :B1
                  AND (OL.SKU IN (SELECT :B2 FROM DUAL
                                  UNION ALL
                                  SELECT PACK_NO
                                    FROM PACKSKU PS
                                   WHERE PS.SKU = :B2))
                  AND OH.ORDER_NO = OL.ORDER_NO

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center