Query tuning help

  • From: Ram K <lambu999@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 5 Feb 2013 17:51:24 -0600

Hello
We have a SQL is running slow in one of our OLTP applications:

SELECT A1.PERSONNUM ,  A1.FULLNM , A5.PPENDDATEDTM, A2.NAME,
SUM(A7.DURATIONSECSQTY/3600)
FROM TKCSOWNER.WFCTOTAL A7, TKCSOWNER.WTKEMPLOYEE A6, TKCSOWNER.MYPAYPERIOD
A5,
   TKCSOWNER.LABORACCT A4, TKCSOWNER.PAYCODE1MMFLAT A3,
   TKCSOWNER.PAYCODE A2, TKCSOWNER.PERSON A1
WHERE A3.GRANDPAYCODEID=A2.PAYCODEID
AND A6.EMPLOYEEID      =A7.EMPLOYEEID
AND A3.PAYCODEID       =A7.PAYCODEID
AND A3.EFFECTIVEDTM   <=A7.APPLYDTM
AND A3.EXPIRATIONDTM   >A7.APPLYDTM
AND A1.PERSONID        =A6.PERSONID
AND A7.LABORACCTID     =A4.LABORACCTID
AND A5.PAYRULEID       =A6.PAYRULEID
AND A2.TYPE            ='P'
AND A7.NOTPAIDSW       =0
AND A7.APPLYDTM       >=A5.PPSTARTDATEDTM
AND A7.APPLYDTM       <=A5.PPENDDATEDTM
AND A2.NAME            ='XXX'
GROUP BY A1.PERSONNUM, A1.FULLNM, A5.PPENDDATEDTM, A4.LABORLEV3NM,
   DECODE(A7.APPLYDTM-A7.ADJAPPLYDTM,0,' ','Y') ,  A2.NAME

I did a traceonly in sqlplus and i got this result:

Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------------------
| Id  | Operation                          | Name              | Rows  |
Bytes | Cost  |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                   |     1 |
189 | 16056 |
|   1 |  HASH GROUP BY                     |                   |     1 |
189 | 16056 |
|   2 |   NESTED LOOPS                     |                   |     1 |
189 | 16055 |
|   3 |    NESTED LOOPS                    |                   |     1 |
175 | 16054 |
|   4 |     NESTED LOOPS                   |                   |     1 |
123 | 16053 |
|   5 |      NESTED LOOPS                  |                   |     1 |
99 | 16051 |
|   6 |       HASH JOIN                    |                   | 15945 |
965K|    92 |
|   7 |        NESTED LOOPS                |                   |    54 |
2484 |    10 |
|   8 |         TABLE ACCESS BY INDEX ROWID| PAYCODE           |     1 |
26 |     1 |
|   9 |          INDEX UNIQUE SCAN         | XU1_PAYCODE       |     1
|       |     0 |
|  10 |         TABLE ACCESS FULL          | MYPAYPERIOD       |    54 |
1080 |     9 |
|  11 |        VIEW                        | index$_join$_002  | 15971 |
249K|    82 |
|  12 |         HASH JOIN                  |                   |
|       |       |
|  13 |          INDEX FAST FULL SCAN      | XU2_WTKEMPLOYEE   | 15971 |
249K|    57 |
|  14 |          INDEX FAST FULL SCAN      | XU1_WTKEMPLOYEE   | 15971 |
249K|    44 |
|  15 |       TABLE ACCESS BY INDEX ROWID  | WFCTOTAL          |     1 |
37 |     1 |
|  16 |        INDEX RANGE SCAN            | X3_WFCTOTAL       |     1
|       |     1 |
|  17 |      TABLE ACCESS BY INDEX ROWID   | PAYCODE1MMFLAT    |     1 |
24 |     2 |
|  18 |       INDEX RANGE SCAN             | PK_PAYCODE1MMFLAT |     1
|       |     1 |
|  19 |     TABLE ACCESS BY INDEX ROWID    | PERSON            |     1 |
52 |     1 |
|  20 |      INDEX UNIQUE SCAN             | PK_PERSON         |     1
|       |     0 |
|  21 |    INDEX RANGE SCAN                | ZZZ_PK_LABORACCT |     1 |
14 |     1 |
----------------------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
  435711393  consistent gets
         15  physical reads
         72  redo size
       5300  bytes sent via SQL*Net to client
        568  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         70  rows processed

Query takes more than an hour to run. 435M consistent gets!

The stats on the tables are mostly ok, except the big wfctotal which now
has 31.5 million rows as opposed to the 30.7 as shown in the user tables. I
think that should not make a big difference as it is only 2.5% off.

TABLE_NAME                     LAST_ANALYZED     SAMPLE_SIZE
------------------------------ ----------------- -----------
LABORACCT                      26-JAN-13 06:02am       28701
MYPAYPERIOD                    04-FEB-13 12:00am          54
PAYCODE                        26-JAN-13 06:01am         191
PAYCODE1MMFLAT                 26-JAN-13 06:01am         592
PERSON                         26-JAN-13 06:02am       15971
WFCTOTAL                       29-NOV-12 10:44pm    30701988
WTKEMPLOYEE                    26-JAN-13 06:07am       15971
v11.2 db. I would like to make it run faster. Not sure how to do it.Any
help is appreciated.

-- 
Thanks,
Ram.


--
//www.freelists.org/webpage/oracle-l


Other related posts: