Re: Query tuning help
- From: Ram K <lambu999@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Wed, 6 Feb 2013 18:50:05 -0600
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_NAME
COLUMN_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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
