Help with suddenly long running query, 7.3.4.3/AIX

From: James <james_lorenzen_at_allianzlife.com>
Date: Wed, 03 Mar 1999 16:41:41 GMT
Message-ID: <7bjoo3$a8c$1@nnrp1.dejanews.com>

I applied the patchset to our 7.3.4 version of Oracle to bring it up to 7.3.4.3. I then set b_tree_bitmap_plans = TRUE. A query that is built in Envision (sp?) has started taking over an hour, according to the user, last week it had been taking 5 minutes. The explain plan for this query shows no full table scans.

This query has three subqueries in it, two "exists (select ...)" and one "effdt = (select max(dffdt) ..". I am trying to understand why it has started taking so long.

The query (and part of the tkprof from the trace) follow. All of the tables are analyzed and the optimizer is set at choose. Is there something that I can do to improve this query?

TIA SELECT C.TREE_NODE_NUM,
   SUM(A.POSTED_TOTAL_AMT * DECODE( A.CURRENCY_CD,'USD',1,'CAD',.6535))  FROM PS_LEDGER A, PS_GL_ACCOUNT_TBL B, PSTREESELECT06 C  WHERE EXISTS (SELECT 'X' FROM PSTREESELECT05 A3

               WHERE A3.SELECTOR_NUM = 1201
                 AND A.BUSINESS_UNIT = A3.RANGE_FROM_05
                 AND A3.TREE_NODE_NUM BETWEEN 1 AND 2000000000)

 AND A.LEDGER = 'ACTUALS' AND A.AZL_BASIS <> '1'  AND B.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL

                WHERE B.SETID = SETID
                  AND B.ACCOUNT = ACCOUNT
                  AND EFFDT <= SYSDATE)
 AND B.SETID = 'AZL'       AND B.ACCOUNT = A.ACCOUNT

 AND A.FISCAL_YEAR = 1998 AND A.ACCOUNTING_PERIOD BETWEEN 0 AND 12  AND EXISTS (SELECT 'X' FROM PSTREESELECT05 A3

             WHERE A3.SELECTOR_NUM = 201
               AND A.DEPTID >= A3.RANGE_FROM_05
               AND A.DEPTID <= A3.RANGE_TO_05
               AND A3.TREE_NODE_NUM BETWEEN 1 AND 2000000000)
 AND A.BUSINESS_UNIT IN ('AZL','NALU', 'ULTRA')
 AND C.SELECTOR_NUM = 1192
 AND A.ACCOUNT >= C.RANGE_FROM_06
 AND A.ACCOUNT <= C.RANGE_TO_06
 AND C.TREE_NODE_NUM BETWEEN 68965518 AND 2000000000
 AND C.SELECTOR_NUM = 1192
 AND B.ACCOUNT >= C.RANGE_FROM_06

 AND B.ACCOUNT <= C.RANGE_TO_06
 AND C.TREE_NODE_NUM BETWEEN 68965518 AND 2000000000 GROUP BY C.TREE_NODE_NUM

call  count  cpu  elapsed  disk  query	current  rows ------- ------ 
-------- ---------- ---------- ---------- ----------  ---------- Parse	1 
0.13  0.13  0  0  0  0 Execute	1  0.00  0.00  0  0  0	0 Fetch  1  955.77 
1071.59  21612	6922475  0  27 ------- ------  -------- ---------- ----------

---------- ---------- ---------- total 3 955.90 1071.72 21612 6922475 0 27

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 10 (SYSADM)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
 488541   SORT (GROUP BY)
      0    CONCATENATION
  10199     FILTER
  10199      NESTED LOOPS
  10868       NESTED LOOPS
  10830        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF


                   'PS_LEDGER'


  10831         INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PSDLEDGER'
                    (NON-UNIQUE)
      3          INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                     'PS_PSTREESELECT05' (UNIQUE)
  10975        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF


                   'PS_GL_ACCOUNT_TBL'


  21803         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'PSAGL_ACCOUNT_TBL' (NON-UNIQUE)
1054196       INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                  'PS_PSTREESELECT06' (UNIQUE)
   3708      SORT (AGGREGATE)
   3708       TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF
                  'PS_GL_ACCOUNT_TBL'
   7383        INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                   'PSAGL_ACCOUNT_TBL' (NON-UNIQUE)


 121270      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PS_PSTREESELECT05'
                 (UNIQUE)
   7186     FILTER
   7186      NESTED LOOPS
   7936       NESTED LOOPS
   7756        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF


                   'PS_LEDGER'


   7757         INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PSDLEDGER'
                    (NON-UNIQUE)
      3          INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                     'PS_PSTREESELECT05' (UNIQUE)
   7967        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF


                   'PS_GL_ACCOUNT_TBL'


  15723         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'PSAGL_ACCOUNT_TBL' (NON-UNIQUE)
 769792       INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                  'PS_PSTREESELECT06' (UNIQUE)
   3708      SORT (AGGREGATE)
   3708       TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF
                  'PS_GL_ACCOUNT_TBL'
   7383        INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                   'PSAGL_ACCOUNT_TBL' (NON-UNIQUE)


 121270      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PS_PSTREESELECT05'
                 (UNIQUE)
 472337     FILTER
 472337      NESTED LOOPS
 505409       NESTED LOOPS
 507647        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF


                   'PS_LEDGER'


 507648         INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PSDLEDGER'
                    (NON-UNIQUE)
      3          INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                     'PS_PSTREESELECT05' (UNIQUE)
 505738        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF


                   'PS_GL_ACCOUNT_TBL'


1009558         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'PSAGL_ACCOUNT_TBL' (NON-UNIQUE)
49024673       INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                  'PS_PSTREESELECT06' (UNIQUE)
   3708      SORT (AGGREGATE)
   3708       TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF
                  'PS_GL_ACCOUNT_TBL'
   7383        INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                   'PSAGL_ACCOUNT_TBL' (NON-UNIQUE)


 121270      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PS_PSTREESELECT05'
                 (UNIQUE)

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

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    

Received on Wed Mar 03 1999 - 10:41:41 CST