Re: Query tuning help
From: Ram K <lambu999_at_gmail.com>
Date: Wed, 6 Feb 2013 18:50:05 -0600
Message-ID: <CAAKoEZ8Sv=kGeDUKKwWxUuYDRu5VdX54+iyLjsjnRK+v1VSMMA_at_mail.gmail.com>
Thanks to everyone who responded.
I tried a few things:
After I ran the SQL in sqlplus:
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
| Id | Operation | Name | Starts |
E-Rows | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 |
17 - filter("A3"."EXPIRATIONDTM">"A7"."APPLYDTM") 18 - access("A3"."PAYCODEID"="A7"."PAYCODEID" AND "A3"."GRANDPAYCODEID"="A2"."PAYCODEID" AND
Date: Wed, 6 Feb 2013 18:50:05 -0600
Message-ID: <CAAKoEZ8Sv=kGeDUKKwWxUuYDRu5VdX54+iyLjsjnRK+v1VSMMA_at_mail.gmail.com>
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
select count(*), NOTPAIDSW from wfctotal
group by NOTPAIDSW
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-lReceived on Thu Feb 07 2013 - 01:50:05 CET