RE: Query tuning help
Date: Wed, 6 Feb 2013 22:48:11 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01FEE53B_at_WIN02.hotsos.com>
Looks like the index X3_WFCTOTAL is the issue, that's were things really go badly. The optimizer things it's getting 1 row and it's getting over 2 billion. I think that is a major problem. The next bit with the NOTPAIDSW isn't good, but start with the index scan, why is that so far off the mark?
Maybe try extended stats on the combination of the columns in this index. I suspect a bad cardinality calculation might be because of the default behavior is to treat the predicates independently.
What is the selectivity of the two columns in the index? (the DENSITY column in *_INDEXES)
BTW - Those are good setting for index caching and index cost adjust, they are the defaults which tend to be good. As always you actual mileage will very.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram K
Sent: Wednesday, February 06, 2013 7:50 PM
To: jonathan_at_jlcomp.demon.co.uk
Cc: oracle-l
Subject: Re: Query tuning help
Thanks to everyone who responded.
I tried a few things:
After I ran the SQL in sqlplus:
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')); SQL_ID 2bbazrqwkr4um, child number 1
SELECT /*+ GATHER_PLAN_STATISTICS */ 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='XXXX' GROUP BY A1.PERSONNUM,A1.FULLNM, A5.PPENDDATEDTM, A4.LABORLEV3NM,DECODE(A7.APPLYDTM-A7.ADJAPPLYDTM,0,' ','Y'), A2.NAME Plan hash value: 2356839483
| Id | Operation | Name | Starts
|
E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------ ------------------------------------------
| 0 | SELECT STATEMENT | | 1
|
| 1 |00:09:48.22 | 300M|
| 1 | HASH GROUP BY | | 1
|
1 | 1 |00:09:48.22 | 300M|
| 2 | NESTED LOOPS | | 1
|
1 | 1 |00:19:59.99 | 300M|
| 3 | NESTED LOOPS | | 1
|
1 | 1 |00:19:59.99 | 300M|
| 4 | NESTED LOOPS | | 1
|
1 | 1 |00:19:59.99 | 300M|
| 5 | NESTED LOOPS | | 1
|
1 | 133K|00:42:12.51 | 300M|
|* 6 | HASH JOIN | | 1
|
15945 | 15949 |00:00:00.49 | 129 |
| 7 | NESTED LOOPS | | 1
|
54 | 54 |00:00:00.01 | 30 |
|* 8 | TABLE ACCESS BY INDEX ROWID| PAYCODE | 1
|
1 | 1 |00:00:00.01 | 2 |
|* 9 | INDEX UNIQUE SCAN | XU1_PAYCODE | 1
|
1 | 1 |00:00:00.01 | 1 |
| 10 | TABLE ACCESS FULL | MYPAYPERIOD | 1
|
54 | 54 |00:00:00.01 | 28 |
| 11 | VIEW | index$_join$_002 | 1
|
15971 | 15975 |00:00:00.40 | 99 |
|* 12 | HASH JOIN | | 1
|
| 15975 |00:00:00.35 | 99 |
| 13 | INDEX FAST FULL SCAN | XU2_WTKEMPLOYEE | 1
|
15971 | 15975 |00:00:00.01 | 61 |
| 14 | INDEX FAST FULL SCAN | XU1_WTKEMPLOYEE | 1
|
15971 | 15975 |00:00:00.10 | 38 |
|* 15 | TABLE ACCESS BY INDEX ROWID | WFCTOTAL | 15949
|
1 | 133K|00:40:28.38 | 300M|
|* 16 | INDEX RANGE SCAN | X3_WFCTOTAL | 15949
|
1 | 2122M|00:13:45.07 | 12M|
|* 17 | TABLE ACCESS BY INDEX ROWID | PAYCODE1MMFLAT |
133K| 1 | 1 |00:00:01.83 | 20590 |
|* 18 | INDEX RANGE SCAN | PK_PAYCODE1MMFLAT |
133K| 1 | 1 |00:00:01.06 | 20589 |
| 19 | TABLE ACCESS BY INDEX ROWID | PERSON | 1
|
1 | 1 |00:00:00.01 | 3 |
|* 20 | INDEX UNIQUE SCAN | PK_PERSON | 1
|
1 | 1 |00:00:00.01 | 2 |
|* 21 | INDEX RANGE SCAN | ZZZZ_PK_LABORACCT | 1
|
1 | 1 |00:00:00.01 | 2 | ------------------------------------------------------------------------ ------------------------------------------
Predicate Information (identified by operation id):
6 - access("A5"."PAYRULEID"="A6"."PAYRULEID")
8 - filter("A2"."TYPE"=U'P')
9 - access("A2"."NAME"=U'STIIP-INDICATOR')
12 - access(ROWID=ROWID)
15 - filter(("A7"."NOTPAIDSW"=0 AND
"A6"."EMPLOYEEID"="A7"."EMPLOYEEID"))
16 - access("A7"."APPLYDTM">="A5"."PPSTARTDATEDTM" AND
"A7"."APPLYDTM"<="A5"."PPENDDATEDTM")
17 - filter("A3"."EXPIRATIONDTM">"A7"."APPLYDTM")
18 - access("A3"."PAYCODEID"="A7"."PAYCODEID" AND
"A3"."GRANDPAYCODEID"="A2"."PAYCODEID" AND
"A3"."EFFECTIVEDTM"<="A7"."APPLYDTM")
20 - access("A1"."PERSONID"="A6"."PERSONID")
21 - access("A7"."LABORACCTID"="A4"."LABORACCTID")
Note
- cardinality feedback used for this statement
64 rows selected.
I have changed name predicate value and index name slightly in the above. As you guys have mentioned the A-rows is way off in line 16.
Index defn:
TABLE_NAME INDEX_NAME COLUMN_NAMECOLUMN_POSITION
--------------------- ------------------------------
----------------------
---------------
WFCTOTAL X3_WFCTOTAL APPLYDTM
1
WFCTOTAL X3_WFCTOTAL PAYCODEID
2
I re analyzed the table at this point, including NOTPAIDSW column (only).
Column stats on the NOTPAIDSW column:
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE ------------------------------ ------------------------------
- --------------------
DENSITY LAST_ANALYZED NUM_NULLS
SAMPLE_SIZE
HISTOGRAM
--------------------------------- ----------------- ----------
WFCTOTAL NOTPAIDSW 2 80 C102 .000000015890049501635600 06-FEB-13 05:11pm 0 31466233 FREQUENCY
COUNT(*) NOTPAIDSW
---------- ----------
18142 1
31449074 0
I dont get how the low and hi value are 80 and C102 in tab_cols while the querying the table shows something different
Parameters:
optimizer_capture_sql_plan_baselines boolean FALSE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.2.0.1 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean TRUE optimizer_use_invisible_indexes boolean FALSE optimizer_use_pending_statistics boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE ======================================================================== =======================================
OICaching and OICAdj recommended by the vendors, not us.
-- Thanks, Ram. -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 07 2013 - 05:48:11 CET