-
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
-
Post the Index details. That may help to solve your problem.
-
list of indexes
Please find a list of the indexes, if you need any more information then please let me know.
TABLE_NAME INDEX_NAME COLUMNS
FDR_REC_STAGES REC_STAGE_I_ORDER (STAGE_ORDER)
FDR_REC_STAGES REC_STAGE_PK (REC_STAGE)
FDR_REC_HISTS REC_HIST_PK (ACC_ID, DEBT_SEQUENCE, REC_DATE)
FDR_REC_HISTS REC_I_STAGE (REC_STAGE)
FDR_SUSP_HISTS FDR_SUSP_HISTS (ADD_ID, DEBT_SEQUENCE)
Table FDR_REC_HISTS has 1000000 rows.
Table FDR_REC_STAGES has 29.
Table FDR_SUSP_HISTS has 4500 rows.
description of FDR_REC_STAGES :-
FDR_REC_STAGES (
REC_STAGE CHAR (3) NOT NULL,
DESCRIPTION VARCHAR2 (60) NOT NULL,
COMMENTS VARCHAR2 (40) NOT NULL,
PERCENT_COST NUMBER (4,2) NOT NULL,
STAGE_ORDER NUMBER (2) NOT NULL,
DEMINIMIS NUMBER (5,2),
INSTALMENT VARCHAR2 (1) DEFAULT 'N',
FN_IND VARCHAR2 (1),
REM_IND VARCHAR2 (1),
BAILIFF VARCHAR2 (1),
DSS_IND VARCHAR2 (1),
SUM_IND VARCHAR2 (1),
CSUM_IND VARCHAR2 (1),
LO_IND VARCHAR2 (1)
description of FDR_REC_HISTS :-
FDR_REC_HISTS (
ACC_ID VARCHAR2 (12) NOT NULL,
DEBT_SEQUENCE NUMBER (2) NOT NULL,
REC_DATE DATE NOT NULL,
CANCELDATE DATE,
CANCEL_ID CHAR (3),
COSTS NUMBER (10,2) NOT NULL,
REC_STAGE CHAR (3) NOT NULL,
REC_BY VARCHAR2 (8) NOT NULL,
COMMENTS VARCHAR2 (240),
DEBTAGEN NUMBER,
SUMMONS_ID NUMBER,
BALANCE NUMBER (10,2),
SHERDATE DATE,
S_O_CANCELLED VARCHAR2 (1),
RBT_ID VARCHAR2 (9)
description of FDR_SUSP_HISTS :-
FDR_SUSP_HISTS (
SUSP_DATE DATE NOT NULL,
EXPIRYDATE DATE,
ACC_ID VARCHAR2 (12) NOT NULL,
DEBT_SEQUENCE NUMBER (2) NOT NULL,
SUSP_TYPE CHAR (3) NOT NULL,
COMMENTS VARCHAR2 (240),
EXP_BY VARCHAR2 (8)
