RE: Query tuning help
Date: Tue, 5 Feb 2013 21:19:23 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01FEE4A9_at_WIN02.hotsos.com>
Stats off even one percent (or less) can make a difference, but might not.
It likely would be easier to figure out where to start with stat lines from a 10046 trace or a query on the V$SQL_PLAN_STATISTICS_ALL view after a run so you can see at which line most of the work and time is going. The optimizer certainly thinks it's going to deal with few rows for much of the plan which I'm thinking is why it's choosing all those nested loops, it could be that there are more rows then it thinks and Hash or Sort Merge would be better. It would likely be very revealing to see the actual rows for each line in the plan.
Ric Van Dyke
Education Director
Hotsos Enterprises LTD.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram K
Sent: Tuesday, February 05, 2013 6:51 PM
To: oracle-l
Subject: Query tuning help
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 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-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 06 2013 - 04:19:23 CET