This discussion is archived
7 Replies Latest reply: Sep 11, 2013 1:55 AM by Nikolay Savvinov RSS

SQL with connect by prior running for a long time

doubtsinora Newbie
Currently Being Moderated

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 :

 

PLAN_IDTIMESTAMPOPERATIONOPTIONSOBJECT_OWNEROBJECT_NAMEOBJECT_ALIASOBJECT_INSTANCEOBJECT_TYPEOPTIMIZERSEARCH_COLUMNSIDPARENT_IDDEPTHPOSITIONCOSTCARDINALITYBYTESCPU_COSTIO_COSTTEMP_SPACEACCESS_PREDICATESFILTER_PREDICATESPROJECTIONTIMEQBLOCK_NAME
11219/10/2013 3:30FILTER 1011 NOT EXISTS (SELECT 0 FROM "TRXNSTRACK" "TRCK" WHERE "TRXNSTRACKREASONID"=1 AND "TRCK"."TRXNSID"=:B1)AL."FROMACCOUNTID"[NUMBER,22], "T"."TRXNSID"[NUMBER,22], "T"."TRXNSTYPEID"[NUMBER,22], "T"."DESCRIPTION"[VARCHAR2,80], "T"."POSTDT"[DATE,7], "T"."TRXNSAMT"[NUMBER,22], "A"."ACCOUNTNUM"[VARCHAR2,19] SEL$5DA710D3
11219/10/2013 3:30FILTER 2121 SYSDATE@!-2<=SYSDATE@!AL."FROMACCOUNTID"[NUMBER,22], "T"."TRXNSID"[NUMBER,22], "T"."TRXNSTYPEID"[NUMBER,22], "T"."DESCRIPTION"[VARCHAR2,80], "T"."POSTDT"[DATE,7], "T"."TRXNSAMT"[NUMBER,22], "A"."ACCOUNTNUM"[VARCHAR2,19]
11219/10/2013 3:30NESTED LOOPS 3231 (#keys=0) "AL"."FROMACCOUNTID"[NUMBER,22], "T"."TRXNSID"[NUMBER,22], "T"."TRXNSTYPEID"[NUMBER,22], "T"."DESCRIPTION"[VARCHAR2,80], "T"."POSTDT"[DATE,7], "T"."TRXNSAMT"[NUMBER,22], "A"."ACCOUNTNUM"[VARCHAR2,19]
11219/10/2013 3:30NESTED LOOPS 43415111939898584 (#keys=0) "AL"."FROMACCOUNTID"[NUMBER,22], "T"."TRXNSID"[NUMBER,22], "T"."TRXNSTYPEID"[NUMBER,22], "T"."DESCRIPTION"[VARCHAR2,80], "T"."POSTDT"[DATE,7], "T"."TRXNSAMT"[NUMBER,22], "A".ROWID[ROWID,10]1
11219/10/2013 3:30NESTED LOOPS 5451419039896903 (#keys=0) "AL"."FROMACCOUNTID"[NUMBER,22], "T"."TRXNSID"[NUMBER,22], "T"."TRXNSTYPEID"[NUMBER,22], "T"."DESCRIPTION"[VARCHAR2,80], "T"."POSTDT"[DATE,7], "T"."TRXNSAMT"[NUMBER,22]1
11219/10/2013 3:30HASH JOINSEMI 6561325439890942 AL."TOACCOUNTID"="TOACCOUNTID" (#keys=1) "AL"."FROMACCOUNTID"[NUMBER,22]1
11219/10/2013 3:30INDEXFULL SCANSPACCOUNTLINK_AK1AL@SEL$1 INDEX (UNIQUE)ANALYZED 76711182521071 AL."FROMACCOUNTID"[NUMBER,22], "AL"."TOACCOUNTID"[NUMBER,22]1SEL$5DA710D3
11219/10/2013 3:30VIEW SYSVW_NSO_1VW_NSO_1@SEL$5DA710D311VIEW 86722182341071 TOACCOUNTID[NUMBER,22]1SEL$683B0107
11219/10/2013 3:30CONNECT BYNO FILTERING WITH START-WITH 9881 TOACCOUNTID=PRIOR "FROMACCOUNTID"TOACCOUNTID=56354162TOACCOUNTID[NUMBER,22], "FROMACCOUNTID"[NUMBER,22], PRIOR NULL[22], LEVEL[4] SEL$683B0107
11219/10/2013 3:30INDEXFULL SCANSPACCOUNTLINK_AK1ACCOUNTLINK@SEL$3 INDEX (UNIQUE)ANALYZED 109911182521071 ACCOUNTLINK.ROWID[ROWID,10], "FROMACCOUNTID"[NUMBER,22], "TOACCOUNTID"[NUMBER,22]1SEL$3
11219/10/2013 3:30TABLE ACCESSBY INDEX ROWIDSPTRXNST@SEL$12TABLEANALYZED 1156211632981 T."TRXNSID"[NUMBER,22], "T"."TRXNSTYPEID"[NUMBER,22], "T"."DESCRIPTION"[VARCHAR2,80], "T"."POSTDT"[DATE,7], "T"."TRXNSAMT"[NUMBER,22]1SEL$5DA710D3
11219/10/2013 3:30INDEXRANGE SCANSPTRXNS_ACCOUNTID_NIDXT@SEL$1 INDEXANALYZED212117111 2241 AL."FROMACCOUNTID"="T"."ACCOUNTID" AND "T"."POSTDT">=SYSDATE@!-2 AND "T"."POSTDT"<=SYSDATE@! T.ROWID[ROWID,10], "T"."POSTDT"[DATE,7]1SEL$5DA710D3
11219/10/2013 3:30INDEXUNIQUE SCANSPACCOUNT_PKA@SEL$1 INDEX (UNIQUE)ANALYZED11345211 901 A."ACCOUNTID"="AL"."FROMACCOUNTID" A.ROWID[ROWID,10]1SEL$5DA710D3
11219/10/2013 3:30TABLE ACCESSBY INDEX ROWIDSPACCOUNTA@SEL$13TABLEANALYZED 1434211291681 A."CLOSEDATE"<SYSDATE@! AND "T"."POSTDT">"A"."CLOSEDATE"A."ACCOUNTNUM"[VARCHAR2,19]1SEL$5DA710D3
11219/10/2013 3:30INDEXRANGE SCANSPTRXNSTRACK_TRXNSID_NIDXTRCK@SEL$6 INDEXANALYZED2151221110731 TRCK."TRXNSID"=:B1 AND "TRXNSTRACKREASONID"=1 TRCK."TRXNSID"[NUMBER,22], "TRXNSTRACKREASONID"[NUMBER,22]1SEL$6

 

 

Please help me in debugging this thanks!

  • 1. Re: SQL with connect by prior running for a long time
    Purvesh K Guru
    Currently Being Moderated

    The Connect By clause does not have START WITH, indicating each row to be the root. Maybe using it might help you.

     

    You have posted the table structure but no data to understand. I would suggest you to simplify the example with two tables and post the data. It might be possible to simplify the query to avoid hitting the ACCOUNTLINK table twice.

  • 2. Re: SQL with connect by prior running for a long time
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    you need to provide some additional diagnostic information -- such as tkprof'ed trace file or dbms_xplan.display_cursor output with 'allstats last' option after enabling rowsource stats.

     

    Best regards,

    Nikolay

  • 3. Re: SQL with connect by prior running for a long time
    doubtsinora Newbie
    Currently Being Moderated

    Hi,

     

    Thanks for your thought on this subject. Below is the trace info that I got from the DBA

     

    SQL ID: d0x879qx2zgtz Plan Hash: 4036333519

     

     

    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 = :B3 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 = :B4 ) AND

      T.POSTDT > A.CLOSEDATE AND T.POSTDT >= :B2 AND T.POSTDT <= :B1

     

     

     

     

    call     count       cpu    elapsed       disk      query    current        rows

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    Parse        0      0.00       0.00          0          0          0           0

    Execute  17160      2.10       1.87          0          0          0           0

    Fetch    17160   7354.61    7390.86     169408    5569856  883366791           0

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    total    34320   7356.71    7392.74     169408    5569856  883366791           0

     

     

    Misses in library cache during parse: 0

    Optimizer mode: CHOOSE

    Parsing user id: 38     (recursive depth: 1)

    ********************************************************************************

     

     

    SQL ID: gs89hpavb4cts Plan Hash: 3415795327

     

     

    SELECT A.ACCOUNTID, C.MEMBERID, A.PROGRAMID, A.ACCOUNTNUM

    FROM

    CUSTOMER C, CUSTOMERACCOUNT CA, ACCOUNT A, PROGRAMPARAMVALUE PPV,

      BATCHPROCESSPROGRAM BP WHERE A.PROGRAMID = BP.PROGRAMID AND A.PROGRAMID =

      PPV.PROGRAMID AND A.ACCOUNTID = CA.ACCOUNTID AND CA.PERSONID = C.PERSONID

      AND PPV.PARAMID = :B2 AND PPV.VALUE = 'Y' AND BP.PROCESSID = :B1 AND BP.RUN

      = 'Y' AND A.ACCOUNTTYPEID = 4 AND A.ACCOUNTSTATUSID = 1 AND C.MEMBERID IS

      NOT NULL

     

     

     

     

    call     count       cpu    elapsed       disk      query    current        rows

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    Parse        0      0.00       0.00          0          0          0           0

    Execute      0      0.00       0.00          0          0          0           0

    Fetch      172     13.14     115.34      80826     278650          0       17200

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    total      172     13.14     115.34      80826     278650          0       17200

     

     

    Misses in library cache during parse: 0

    Parsing user id: 38     (recursive depth: 1)

     

     

     

     

     

     

    ********************************************************************************

     

     

    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

     

     

    call     count       cpu    elapsed       disk      query    current        rows

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    Parse        0      0.00       0.00          0          0          0           0

    Execute      0      0.00       0.00          0          0          0           0

    Fetch        0      0.00       0.00          0          0          0           0

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    total        0      0.00       0.00          0          0          0           0

     

     

    Misses in library cache during parse: 0

     

     

     

     

    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

     

     

    call     count       cpu    elapsed       disk      query    current        rows

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    Parse        0      0.00       0.00          0          0          0           0

    Execute  17160      2.10       1.87          0          0          0           0

    Fetch    17332   7367.75    7506.21     250234    5848506  883366791       17200

    ------- ------  -------- ---------- ---------- ---------- ----------  ----------

    total    34492   7369.85    7508.09     250234    5848506  883366791       17200

     

     

    Misses in library cache during parse: 0

     

     

        2  user  SQL statements in session.

        0  internal SQL statements in session.

        2  SQL statements in session.

    ********************************************************************************

    Trace file: svoprod_ora_12346.trc

    Trace file compatibility: 11.1.0.7

    Sort options: default

     

     

           1  session in tracefile.

           2  user  SQL statements in trace file.

           0  internal SQL statements in trace file.

           2  SQL statements in trace file.

           2  unique SQL statements in trace file.

       66499  lines in trace file.

        7516  elapsed seconds in trace file.

  • 4. Re: SQL with connect by prior running for a long time
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    the trace file you posted doesn't contain execution plans, which makes it almost useless. The reason why the plans are missing from the trace file is probably that the cursors weren't closed by the time tracing stopped -- you can make sure they're closed by exiting the session without disabling tracing, i.e.:

     

    alter session set events='10046 trace name context forever';

    <run your SQL here>

    exit

     

    Best regards,

    Nikolay

  • 5. Re: SQL with connect by prior running for a long time
    doubtsinora Newbie
    Currently Being Moderated

    Hi... Thanks for your reply. Does this help? I dont have enough privilege and people who does have it are not reachable at this moment.

     

    IDCOSTOPERATIONOPTIONSOBJECT_OWNER||'.'||OBJECT_NAME
    ---------------------------------------------------------------------------------------------------------------------------------
    08SELECT STATEMENT.
    1FILTER.
    2FILTER.
    3NESTED LOOPS.
    47NESTED LOOPS.
    56NESTED LOOPS.
    65HASH JOINSEMI.
    72INDEXFULL SCANSP.ACCOUNTLINK_AK1
    86VIEWSYS.VW_NSO_1
    9CONNECT BYNO FILTERING WITH START-WITH.
    102INDEXFULL SCANSP.ACCOUNTLINK_AK1
    111TABLE ACCESSBY INDEX ROWIDSP.TRXNS
    121INDEXRANGE SCANSP.TRXNS_ACCOUNTID_NIDX
    131INDEXUNIQUE SCANSP.ACCOUNT_PK
    141TABLE ACCESSBY INDEX ROWIDSP.ACCOUNT
    151INDEXRANGE SCANSP.TRXNSTRACK_TRXNSID_NIDX

     

    Please advise. Thanks again!

  • 6. Re: SQL with connect by prior running for a long time
    jihuyao Journeyer
    Currently Being Moderated

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

     

    Assume it takes 0.1 sec for each account, total 6 hrs needed.  Do not you run multiple reporting servers?

     

    0.1*200000/60/60

    ----------------

          5.55555556

  • 7. Re: SQL with connect by prior running for a long time
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    no, that doesn't help.

     

    Best regards,

    Nikolay

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points