List,
There is a report that I tuned last November. It worked great for months.
Then a couple of weeks ago we started moving some of our indexes to disks
that had been logically striped with Veritas volume manager. I rebuilt
all of the indexes for a couple of partitioned tables and reanalyzed the
partitioned tables and their non-partitioned indexes (using a full
compute).
After that weekend, the performance on this report has fallen back to it's
previous level. It's running in hours what used to run in ten minutes.
I happen to have an old output of a tkprof from that time period. I did a
test using the exact same SQL statement and the same account number.
However, the number of records read was vastly different. The time
required to do the read was also many times larger.
I am confused about why my explain plan is exactly the same but each step
queries such different number of records. Why might it be doing this?
What should I be looking at? Could there be a problem with the stripe
size or the way the striping was done? Would that cause a different
number of records to be read? Might there be a difference in the
statistics, perhaps generated on a partition level as opposed to on a table
level?
I've included the results from tkprof for your edification. The table
REP_DIM and EXP_RPT_HDR are partitioned tables, although their indexes are
non-partitioned. Any thoughts, ideas, directions to go, etc. are vastly
appreciated.
Cherie Machler
Oracle DBA
Gelco Information Network
Current slow tkprof output:
********************************************************************************
SELECT a.MGR_ID,a.MGR_NAME, SUM(a.cnt), SUM(a.tot_amt)
FROM
(SELECT DISTINCT
/*+ USE_HASH(MR) */
TO_CHAR(mr.ACCT_NO) ACCT_NO,MG.MGR_ID
MGR_ID,WHSE_DBO.EDM_REPORT_PKG.F_GET_REP_NM2(R2.REP_KEY)
MGR_NAME,MG.MGMT_GRP_NO MGMT_GRP_NO, R1.REP_ID
REP_ID,WHSE_DBO.EDM_REPORT_PKG.F_GET_REP_NM2(R1.REP_KEY)
REP_NAME,R1.REP_STATUS REP_STATUS, r1.internal_rep_number REP_NO,
erh.cnfr_no, erh.tot_amt, 1 cnt
FROM WHSE_DBO.MGMT_GRP_REP MR,
WHSE_DBO.MGMT_GRP MG,
EDM_DBO.REP_DIM R1,
EDM_DBO.REP_DIM R2,
EDM_DBO.DATE_DIM D,
WHSE_DBO.exp_rpt_hdr erh
where d.oracle_date between trunc (sysdate)
and trunc (sysdate) + 0.99999 and
ERH.ACCT_NO = 50382 AND
erh.intl_rep_no = r1.internal_rep_number AND
erh.exp_rpt_sts <> 'T' AND
erh.arrival_dt <= sysdate - 7 AND
erh.exp_rpt_sts <> '9' AND
erh.exp_rpt_sts <> '4' AND
erh.aprv_sts = 'R' AND
erh.DATA_SRC_MOD_DT_TM =
(select max(erh2.DATA_SRC_MOD_DT_TM)
from exp_rpt_hdr erh2
where erh2.cnfr_no = erh.cnfr_no
) AND
erh.ACCT_NO = MR.ACCT_NO AND
R1.INTERNAL_REP_NUMBER = MR.INTL_REP_NO AND
( (NVL(MR.HIST_FLAG,'I') = 'A' AND
MR.DATA_SRC_MOD_DT_TM =
(SELECT MAX(DATA_SRC_MOD_DT_TM)
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO
)) OR
(MR.DATA_SRC_MOD_DT_TM =
(SELECT MAX(DATA_SRC_MOD_DT_TM)
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
NVL(HIST_FLAG,'I') = 'A' AND
trunc(DATA_SRC_MOD_DT_TM) >= TRUNC(erh.arrival_dt)
)and
not exists
(select mr2.src_chng_batch_win_dt_key
from mgmt_grp_rep mr2
WHERE mr2.ACCT_NO = MR.ACCT_NO AND
mr2.INTL_REP_NO = MR.INTL_REP_NO AND
NVL(mr2.HIST_FLAG,'I') = 'A' and
mr2.data_src_mod_dt_tm =
(SELECT MAX(data_src_mod_dt_tm )
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO
)
) ) OR
(MR.DATA_SRC_MOD_DT_TM =
(SELECT MAX(DATA_SRC_MOD_DT_TM)
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
trunc(DATA_SRC_MOD_DT_TM) <= TRUNC(erh.arrival_dt)
) AND
NOT EXISTS
(SELECT *
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
NVL(HIST_FLAG,'I') = 'A' AND
trunc(DATA_SRC_MOD_DT_TM) >= TRUNC(erh.arrival_dt)
) ) OR
(MR.DATA_SRC_MOD_DT_TM =
(SELECT MAX(DATA_SRC_MOD_DT_TM)
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
TRUNC(DATA_SRC_MOD_DT_TM) <= TRUNC(ERH.APRV_STS_DT)
) AND
NOT EXISTS
(SELECT *
FROM MGMT_GRP_REP
WHERE (ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
TRUNC(DATA_SRC_MOD_DT_TM) <= TRUNC(ERH.ARRIVAL_DT)) OR
(ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
NVL(HIST_FLAG,'I') = 'A') AND
trunc(DATA_SRC_MOD_DT_TM) <= TRUNC(erh.arrival_dt)
) ) ) AND
MG.ACCT_NO = mr.ACCT_NO AND
MG.MGMT_GRP_NO = MR.MGMT_GRP_NO AND
MG.SRC_CHNG_BATCH_WIN_DT_KEY =
(SELECT MAX(SRC_CHNG_BATCH_WIN_DT_KEY)
FROM MGMT_GRP
WHERE ACCT_NO = MG.ACCT_NO AND
MGMT_GRP_NO = MG.MGMT_GRP_NO
) AND
R2.ACCOUNT_NUMBER = mr.ACCT_NO AND
R2.INTERNAL_REP_NUMBER = MG.INTL_REP_NO AND
R2.ORG_STRUC_EFFECTIVE_DATE <= D.ORACLE_DATE AND
R2.BATCH_WINDOW_DATE_KEY =
(SELECT
/*+ INDEX (REP_DIM REP_DIM_UK1) */
MAX(BATCH_WINDOW_DATE_KEY)
FROM REP_DIM
WHERE ACCOUNT_NUMBER = R2.ACCOUNT_NUMBER AND
INTERNAL_REP_NUMBER = R2.INTERNAL_REP_NUMBER AND
BATCH_WINDOW_DATE_KEY <= D.Date_key
) AND
R2.REP_KEY =
(SELECT
/*+ INDEX (REP_DIM REP_DIM_UK1) */
MAX(REP_KEY)
FROM REP_DIM
WHERE ACCOUNT_NUMBER = R2.ACCOUNT_NUMBER AND
INTERNAL_REP_NUMBER = R2.INTERNAL_REP_NUMBER AND
BATCH_WINDOW_DATE_KEY = R2.BATCH_WINDOW_DATE_KEY AND
ORG_STRUC_EFFECTIVE_DATE = R2.ORG_STRUC_EFFECTIVE_DATE
) AND
-- exists
-- (select *
-- from MART_USER_ORG_LVL l
-- WHERE L.ORACLE_UID = UID AND
-- L.CURR_GRP_IND = 'Y' AND
-- NVL(R2.ORG_LEVEL_1_VALUE,'NONE') LIKE L.ORG_LVL1 AND
-- NVL(R2.ORG_LEVEL_2_VALUE,'NONE') LIKE L.ORG_LVL2 AND
-- NVL(R2.ORG_LEVEL_3_VALUE,'NONE') LIKE L.ORG_LVL3 AND
-- NVL(R2.ORG_LEVEL_4_VALUE,'NONE') LIKE L.ORG_LVL4
-- ) AND
R1.ACCOUNT_NUMBER = mr.ACCT_NO AND
R1.ORG_STRUC_EFFECTIVE_DATE <= D.ORACLE_DATE AND
R1.BATCH_WINDOW_DATE_KEY =
(SELECT
/*+ INDEX (REP_DIM REP_DIM_UK1) */
MAX(BATCH_WINDOW_DATE_KEY)
FROM REP_DIM
WHERE ACCOUNT_NUMBER = R1.ACCOUNT_NUMBER AND
INTERNAL_REP_NUMBER = R1.INTERNAL_REP_NUMBER AND
BATCH_WINDOW_DATE_KEY <= d.date_key
) AND
-- exists
-- (select *
-- from MART_USER_ORG_LVL l
-- WHERE L.ORACLE_UID = UID AND
-- L.CURR_GRP_IND = 'Y' AND
-- NVL(R1.ORG_LEVEL_1_VALUE,'NONE') LIKE L.ORG_LVL1 AND
-- NVL(R1.ORG_LEVEL_2_VALUE,'NONE') LIKE L.ORG_LVL2 AND
-- NVL(R1.ORG_LEVEL_3_VALUE,'NONE') LIKE L.ORG_LVL3 AND
-- NVL(R1.ORG_LEVEL_4_VALUE,'NONE') LIKE L.ORG_LVL4
-- ) AND
R1.REP_KEY =
(SELECT
/*+ INDEX (REP_DIM REP_DIM_UK1) */
MAX(REP_KEY)
FROM REP_DIM
WHERE ACCOUNT_NUMBER = R1.ACCOUNT_NUMBER AND
INTERNAL_REP_NUMBER = R1.INTERNAL_REP_NUMBER AND
BATCH_WINDOW_DATE_KEY = R1.BATCH_WINDOW_DATE_KEY AND
ORG_STRUC_EFFECTIVE_DATE = R1.ORG_STRUC_EFFECTIVE_DATE
)
) A
GROUP BY a.MGR_ID, a.MGR_NAME
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.39 0.51 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 16 2135.71 4742.01 198222 56139127 0
214
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 18 2136.10 4742.52 198222 56139127 0
214
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1986 (EDM_DBO)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1542 SORT (GROUP BY)
1542 VIEW
1586 SORT (UNIQUE)
10890141 FILTER
10890141 NESTED LOOPS
10890141 NESTED LOOPS
406276 NESTED LOOPS
59219 NESTED LOOPS
4785 NESTED LOOPS
700 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'MGMT_GRP'
837 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_UK2' (UNIQUE)
836 SORT (AGGREGATE)
1536 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_UK2' (UNIQUE)
4785 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX
ROWID) OF 'REP_DIM'
5485 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'REP_DIM_UK1' (UNIQUE)
59219 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'MGMT_GRP_REP'
64004 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_PK' (UNIQUE)
406276 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX
ROWID) OF 'REP_DIM'
465495 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'REP_DIM_UK1' (UNIQUE)
30070264 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'EXP_RPT_HDR'
30476540 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'EXP_RPT_HDR_IDX2' (NON-UNIQUE)
10890141 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DATE_DIM'
21780282 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'DATE_DIM_UK1' (UNIQUE)
307236 SORT (AGGREGATE)
307236 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'REP_DIM'
351138 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'REP_DIM_UK1'
(UNIQUE)
4242 SORT (AGGREGATE)
4242 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'REP_DIM'
4862 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'REP_DIM_UK1'
(UNIQUE)
13569 SORT (AGGREGATE)
13569 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
17459 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
123621 SORT (AGGREGATE)
282240 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
356683 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
36 FILTER
97 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
127 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
97 SORT (AGGREGATE)
97 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
127 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
148816 SORT (AGGREGATE)
281710 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
355994 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
66661 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
75012 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
155444 SORT (AGGREGATE)
280145 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
346291 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
0 CONCATENATION
70841 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
88743 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
17903 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
17904 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
212642 SORT (AGGREGATE)
212642 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'EXP_RPT_HDR'
248386 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'EXP_RPT_HDR_PK' (UNIQUE)
418 SORT (AGGREGATE)
438 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'REP_DIM'
856 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'REP_DIM_UK1'
(UNIQUE)
224 SORT (AGGREGATE)
233 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'REP_DIM'
457 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'REP_DIM_UK1'
(UNIQUE)
*******************************************
Previous fast tkprof output:
********************************************************************************
SELECT a.MGR_ID,a.MGR_NAME, SUM(a.cnt), SUM(a.tot_amt)
FROM
(SELECT DISTINCT
/*+ USE_HASH(MR) */
TO_CHAR(mr.ACCT_NO) ACCT_NO,MG.MGR_ID
MGR_ID,WHSE_DBO.EDM_REPORT_PKG.F_GET_REP_NM2(R2.REP_KEY)
MGR_NAME,MG.MGMT_GRP_NO MGMT_GRP_NO, R1.REP_ID
REP_ID,WHSE_DBO.EDM_REPORT_PKG.F_GET_REP_NM2(R1.REP_KEY)
REP_NAME,R1.REP_STATUS REP_STATUS, r1.internal_rep_number REP_NO,
erh.cnfr_no, erh.tot_amt, 1 cnt
FROM WHSE_DBO.MGMT_GRP_REP MR,
WHSE_DBO.MGMT_GRP MG,
EDM_DBO.REP_DIM R1,
EDM_DBO.REP_DIM R2,
EDM_DBO.DATE_DIM D,
WHSE_DBO.exp_rpt_hdr erh
where d.oracle_date between trunc (sysdate)
and trunc (sysdate) + 0.99999 and
ERH.ACCT_NO = 50382 AND
erh.intl_rep_no = r1.internal_rep_number AND
erh.exp_rpt_sts <> 'T' AND
erh.arrival_dt <= sysdate - 7 AND
erh.exp_rpt_sts <> '9' AND
erh.exp_rpt_sts <> '4' AND
erh.aprv_sts = 'R' AND
erh.DATA_SRC_MOD_DT_TM =
(select max(erh2.DATA_SRC_MOD_DT_TM)
from exp_rpt_hdr erh2
where erh2.cnfr_no = erh.cnfr_no
) AND
erh.ACCT_NO = MR.ACCT_NO AND
R1.INTERNAL_REP_NUMBER = MR.INTL_REP_NO AND
( (NVL(MR.HIST_FLAG,'I') = 'A' AND
MR.DATA_SRC_MOD_DT_TM =
(SELECT MAX(DATA_SRC_MOD_DT_TM)
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO
)) OR
(MR.DATA_SRC_MOD_DT_TM =
(SELECT MAX(DATA_SRC_MOD_DT_TM)
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
NVL(HIST_FLAG,'I') = 'A' AND
trunc(DATA_SRC_MOD_DT_TM) >= TRUNC(erh.arrival_dt)
)and
not exists
(select mr2.src_chng_batch_win_dt_key
from mgmt_grp_rep mr2
WHERE mr2.ACCT_NO = MR.ACCT_NO AND
mr2.INTL_REP_NO = MR.INTL_REP_NO AND
NVL(mr2.HIST_FLAG,'I') = 'A' and
mr2.data_src_mod_dt_tm =
(SELECT MAX(data_src_mod_dt_tm )
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO
)
) ) OR
(MR.DATA_SRC_MOD_DT_TM =
(SELECT MAX(DATA_SRC_MOD_DT_TM)
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
trunc(DATA_SRC_MOD_DT_TM) <= TRUNC(erh.arrival_dt)
) AND
NOT EXISTS
(SELECT *
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
NVL(HIST_FLAG,'I') = 'A' AND
trunc(DATA_SRC_MOD_DT_TM) >= TRUNC(erh.arrival_dt)
) ) OR
(MR.DATA_SRC_MOD_DT_TM =
(SELECT MAX(DATA_SRC_MOD_DT_TM)
FROM MGMT_GRP_REP
WHERE ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
TRUNC(DATA_SRC_MOD_DT_TM) <= TRUNC(ERH.APRV_STS_DT)
) AND
NOT EXISTS
(SELECT *
FROM MGMT_GRP_REP
WHERE (ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
TRUNC(DATA_SRC_MOD_DT_TM) <= TRUNC(ERH.ARRIVAL_DT)) OR
(ACCT_NO = MR.ACCT_NO AND
INTL_REP_NO = MR.INTL_REP_NO AND
NVL(HIST_FLAG,'I') = 'A') AND
trunc(DATA_SRC_MOD_DT_TM) <= TRUNC(erh.arrival_dt)
) ) ) AND
MG.ACCT_NO = mr.ACCT_NO AND
MG.MGMT_GRP_NO = MR.MGMT_GRP_NO AND
MG.SRC_CHNG_BATCH_WIN_DT_KEY =
(SELECT MAX(SRC_CHNG_BATCH_WIN_DT_KEY)
FROM MGMT_GRP
WHERE ACCT_NO = MG.ACCT_NO AND
MGMT_GRP_NO = MG.MGMT_GRP_NO
) AND
R2.ACCOUNT_NUMBER = mr.ACCT_NO AND
R2.INTERNAL_REP_NUMBER = MG.INTL_REP_NO AND
R2.ORG_STRUC_EFFECTIVE_DATE <= D.ORACLE_DATE AND
R2.BATCH_WINDOW_DATE_KEY =
(SELECT
/*+ INDEX (REP_DIM REP_DIM_UK1) */
MAX(BATCH_WINDOW_DATE_KEY)
FROM REP_DIM
WHERE ACCOUNT_NUMBER = R2.ACCOUNT_NUMBER AND
INTERNAL_REP_NUMBER = R2.INTERNAL_REP_NUMBER AND
BATCH_WINDOW_DATE_KEY <= D.Date_key
) AND
R2.REP_KEY =
(SELECT
/*+ INDEX (REP_DIM REP_DIM_UK1) */
MAX(REP_KEY)
FROM REP_DIM
WHERE ACCOUNT_NUMBER = R2.ACCOUNT_NUMBER AND
INTERNAL_REP_NUMBER = R2.INTERNAL_REP_NUMBER AND
BATCH_WINDOW_DATE_KEY = R2.BATCH_WINDOW_DATE_KEY AND
ORG_STRUC_EFFECTIVE_DATE = R2.ORG_STRUC_EFFECTIVE_DATE
) AND
-- exists
-- (select *
-- from MART_USER_ORG_LVL l
-- WHERE L.ORACLE_UID = UID AND
-- L.CURR_GRP_IND = 'Y' AND
-- NVL(R2.ORG_LEVEL_1_VALUE,'NONE') LIKE L.ORG_LVL1 AND
-- NVL(R2.ORG_LEVEL_2_VALUE,'NONE') LIKE L.ORG_LVL2 AND
-- NVL(R2.ORG_LEVEL_3_VALUE,'NONE') LIKE L.ORG_LVL3 AND
-- NVL(R2.ORG_LEVEL_4_VALUE,'NONE') LIKE L.ORG_LVL4
-- ) AND
R1.ACCOUNT_NUMBER = mr.ACCT_NO AND
R1.ORG_STRUC_EFFECTIVE_DATE <= D.ORACLE_DATE AND
R1.BATCH_WINDOW_DATE_KEY =
(SELECT
/*+ INDEX (REP_DIM REP_DIM_UK1) */
MAX(BATCH_WINDOW_DATE_KEY)
FROM REP_DIM
WHERE ACCOUNT_NUMBER = R1.ACCOUNT_NUMBER AND
INTERNAL_REP_NUMBER = R1.INTERNAL_REP_NUMBER AND
BATCH_WINDOW_DATE_KEY <= d.date_key
) AND
-- exists
-- (select *
-- from MART_USER_ORG_LVL l
-- WHERE L.ORACLE_UID = UID AND
-- L.CURR_GRP_IND = 'Y' AND
-- NVL(R1.ORG_LEVEL_1_VALUE,'NONE') LIKE L.ORG_LVL1 AND
-- NVL(R1.ORG_LEVEL_2_VALUE,'NONE') LIKE L.ORG_LVL2 AND
-- NVL(R1.ORG_LEVEL_3_VALUE,'NONE') LIKE L.ORG_LVL3 AND
-- NVL(R1.ORG_LEVEL_4_VALUE,'NONE') LIKE L.ORG_LVL4
-- ) AND
R1.REP_KEY =
(SELECT
/*+ INDEX (REP_DIM REP_DIM_UK1) */
MAX(REP_KEY)
FROM REP_DIM
WHERE ACCOUNT_NUMBER = R1.ACCOUNT_NUMBER AND
INTERNAL_REP_NUMBER = R1.INTERNAL_REP_NUMBER AND
BATCH_WINDOW_DATE_KEY = R1.BATCH_WINDOW_DATE_KEY AND
ORG_STRUC_EFFECTIVE_DATE = R1.ORG_STRUC_EFFECTIVE_DATE
)
) A
GROUP BY a.MGR_ID, a.MGR_NAME
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.32 0.32 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 1 63.31 173.23 9369 1795781 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 3 63.63 173.55 9369 1795781 0
0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1985 (WHSE_DBO)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (GROUP BY)
0 VIEW
62 SORT (UNIQUE)
355435 FILTER
355435 NESTED LOOPS
355436 NESTED LOOPS
10780 NESTED LOOPS
1754 NESTED LOOPS
154 NESTED LOOPS
29 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'MGMT_GRP'
32 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_UK2' (UNIQUE)
32 SORT (AGGREGATE)
61 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_UK2' (UNIQUE)
154 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX
ROWID) OF 'REP_DIM'
182 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'REP_DIM_UK1' (UNIQUE)
1754 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'MGMT_GRP_REP'
1907 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_PK' (UNIQUE)
10780 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX
ROWID) OF 'REP_DIM'
12533 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'REP_DIM_UK1' (UNIQUE)
946837 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'EXP_RPT_HDR'
957616 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'EXP_RPT_HDR_IDX2' (NON-UNIQUE)
355435 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'DATE_DIM'
710870 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'DATE_DIM_UK1' (UNIQUE)
8280 SORT (AGGREGATE)
8280 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'REP_DIM'
9608 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'REP_DIM_UK1'
(UNIQUE)
125 SORT (AGGREGATE)
125 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'REP_DIM'
148 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'REP_DIM_UK1'
(UNIQUE)
325 SORT (AGGREGATE)
325 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
422 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
4541 SORT (AGGREGATE)
11918 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
14979 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
1 FILTER
3 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
4 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
3 SORT (AGGREGATE)
3 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
4 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
6594 SORT (AGGREGATE)
11903 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
14959 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
2743 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
3146 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
5913 SORT (AGGREGATE)
10462 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
12912 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
0 CONCATENATION
2299 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
2879 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
580 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MGMT_GRP_REP'
580 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MGMT_GRP_REP_IDX2' (NON-UNIQUE)
6070 SORT (AGGREGATE)
6070 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'EXP_RPT_HDR'
7125 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'EXP_RPT_HDR_PK' (UNIQUE)
25 SORT (AGGREGATE)
25 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'REP_DIM'
50 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'REP_DIM_UK1'
(UNIQUE)
13 SORT (AGGREGATE)
13 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
OF 'REP_DIM'
26 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'REP_DIM_UK1'
(UNIQUE)
********************************************************************************
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
