Query tuning help
From: Ram K <lambu999_at_gmail.com>
Date: Tue, 5 Feb 2013 17:51:24 -0600
Message-ID: <CAAKoEZ9aetnYJ64vzxVM2cc1V+-hW+hW539tJqDyD6RwSJSGqQ_at_mail.gmail.com>
Hello
We have a SQL is running slow in one of our OLTP applications:
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:
| 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 |
Date: Tue, 5 Feb 2013 17:51:24 -0600
Message-ID: <CAAKoEZ9aetnYJ64vzxVM2cc1V+-hW+hW539tJqDyD6RwSJSGqQ_at_mail.gmail.com>
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 |Note
| 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 | ----------------------------------------------------------------------------------------
- '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 15971v11.2 db. I would like to make it run faster. Not sure how to do it.Any help is appreciated.
-- Thanks, Ram. -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 06 2013 - 00:51:24 CET