I have an application running on my database with a bit of code that takes forever to run. I have the code showing below with an explain plan of what it is doing. Can anyone help me by giving me pointers on how to speed this SQL up.
My database is running in Rule based mode as the application can only use this mode. I am running on oracle 7.3.4.5 on a UNix box.


SQL> explain plan set statement_id = 'A' for
2 SELECT RS.DESCRIPTION,RS.STAGE_ORDER FROM FDR_REC_HISTS R,FDR_SUSP_HISTS
S,FDR_REC_STAGES RS WHERE R.ACC_ID = S.ACC_ID AND
3 R.ACC_ID = :b1 AND R.DEBT_SEQUENCE = S.DEBT_SEQUENCE AND
4 R.REC_STAGE = RS.REC_STAGE AND RS.STAGE_ORDER = (SELECT
5 MAX(RS1.STAGE_ORDER) FROM FDR_REC_STAGES RS1,FDR_REC_HISTS R1 WHERE
6 R1.ACC_ID = :b1 AND R1.REC_STAGE = RS1.REC_STAGE AND R1.CANCELDATE
7 IS NULL AND R1.CANCEL_ID IS NULL ) AND (S.EXPIRYDATE IS NULL
8 OR S.EXPIRYDATE > SYSDATE ) AND R.REC_STAGE = RS.REC_STAGE
9 ;
SQL> @xplread
SQL> set pages 5000
SQL> set lines 132
SQL> set echo off

Execution Plan

--------------------------------------------------------------------------------
----------------------------------------------------
SELECT STATEMENT

NESTED LOOPS

NESTED LOOPS

TABLE ACCESS BY ROWID FDR_REC_STAGES

INDEX RANGE SCAN REC_STAGE_I_ORDER NON-UNIQUE

SORT AGGREGATE

NESTED LOOPS

TABLE ACCESS BY ROWID FDR_REC_HISTS

INDEX RANGE SCAN REC_HIST_PK UNIQUE

TABLE ACCESS BY ROWID FDR_REC_STAGES

INDEX UNIQUE SCAN REC_STAGE_PK UNIQUE

TABLE ACCESS BY ROWID FDR_REC_HISTS

INDEX RANGE SCAN REC_I_STAGE NON-UNIQUE

TABLE ACCESS BY ROWID FDR_SUSP_HISTS

INDEX RANGE SCAN FDR_SUSP_6 NON-UNIQUE