7 Replies Latest reply: Sep 11, 2013 3:55 AM by Nikolay Savvinov RSS

    SQL with connect by prior running for a long time

    doubtsinora

      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

          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

            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

              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

                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

                  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

                    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

                      Hi,

                       

                      no, that doesn't help.

                       

                      Best regards,

                      Nikolay