SQL & PL/SQL

Hi,

We are using Oracle 10g. Below is a cursor sql which is having performance issues. The pAccountid is being passed from the output of a different cursor. But this cursor sql is running for a long time. Could you please help me in tuning this sql. I believe the subquery with connect by prior is causing the trouble.

The TRXNS is a huge table which is not partitioned. The query is forced to use the index on the accountid of the TRXNS table.

The accountlink table has 20,000 records and the TRXNStrack table has 10,000 records in total.

This sql executes for 200,000 pAccountids and runs for more than 8 hours.

SELECT /*+ INDEX(T TRXNS_ACCOUNTID_NIDX) */ AL.FROMACCOUNTID oldaccountid ,

                                A.ACCOUNTNUM  oldaccountnum,

                               T.TRXNSID,

                               T.TRXNSTYPEID,

                               T.DESCRIPTION ,

                               T.postdt,

                               T.TRXNSAMT

                    FROM

                    ACCOUNTLINK AL,

                    TRXNS T,

                    ACCOUNT A

                   WHERE AL.TOACCOUNTID IN

                                                         (SELECT TOACCOUNTID FROM ACCOUNTLINK START WITH TOACCOUNTID = pAccountid

                                                                                                                     CONNECT BY PRIOR FROMACCOUNTID  = TOACCOUNTID)

                        AND AL.FROMACCOUNTID = T.ACCOUNTID

                        AND A.ACCOUNTID = AL.FROMACCOUNTID

AND NOT EXISTS (select 1 from TRXNStrack trck where trck.TRXNSid = t.TRXNSid AND TRXNSTrackReasonid = 1)

                        AND T.postdt > A.CLOSEDATE

                        AND T.postdt >= sysdate-2

                        AND T.postdt <= sysdate;

Create script for trxn table:

CREATE TABLE SP.TRXNS

(

  TRXNSID      NUMBER(15) CONSTRAINT "BIN$rpIQEeyLDfbgRAAUT4DEnQ==$0" NOT NULL,

  ACCOUNTID    NUMBER(15) CONSTRAINT "BIN$rpIQEeyMDfbgRAAUT4DEnQ==$0" NOT NULL,

  STATEMENTID  NUMBER(15),

  TRXNSTYPEID  NUMBER(15),

  DESCRIPTION  VARCHAR2(80 BYTE),

  postdt     DATE,

  TRXNSAMT     NUMBER(12,2),

  TRXNSREQID   NUMBER(15),

  LASTUPDATE   DATE,

  SOURCEID     NUMBER(15),

  HIDE         VARCHAR2(1 BYTE)

)

TABLESPACE SO_TRXN_DATA

RESULT_CACHE (MODE DEFAULT)

PCTUSED    40

PCTFREE    10

INITRXNS   2

MAXTRXNS   255

STORAGE    (

            INITIAL          50M

            NEXT             1M

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            FREELISTS        8

            FREELIST GROUPS  1

            BUFFER_POOL      DEFAULT

            FLASH_CACHE      DEFAULT

            CELL_FLASH_CACHE DEFAULT

           )

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

CREATE INDEX SP.TRXNS_ACCOUNTID_NIDX ON SP.TRXNS

(ACCOUNTID, postdt)

LOGGING

TABLESPACE SO_TRXN_INDEX

PCTFREE    10

INITRXNS   2

MAXTRXNS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            FREELISTS        1

            FREELIST GROUPS  1

            BUFFER_POOL      DEFAULT

            FLASH_CACHE      DEFAULT

            CELL_FLASH_CACHE DEFAULT

           )

NOPARALLEL;

below is the executing plan for this sql taken from toad :

Please help me in debugging this thanks!