(http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1032431852141 )
Question
~~~~~~~~~
The following SQL running ~ 9min, anyway to speed up?
SQL
~~~
update hiir p
set ytd_anr = (SELECT SUM(h.value_or_amt)
FROM period_table pe,
mfg_dist_dlr_prod_history h
WHERE h.mfg_no = p.mfg_no
AND h.mfg_loc = p.mfg_loc_no
AND h.dist_no = p.dist_no
AND h.dist_loc = p.dist_loc_no
AND h.dlr_no = p.dlr_no
AND h.prod_code = p.prod_code
AND h.program_no = p.program_no
AND h.recv_branch_no = p.recv_branch_no
AND h.rt_mfg_flag = p.rt_mfg_flag
AND h.rt_dist_flag = p.rt_dist_flag
AND h.rt_dlr_flag = p.rt_dlr_flag
AND h.period = pe.period
AND pe.year = :l_current_year
AND h.data_type = :l_data_type)
where rownum < 10000
SQL_TRACE and TKPROF
~~~~~~~~~~~~~~~~~~~~
Notes: issuse the following:
alter system set statistics_level=all
to get the addtional info in the "Row Source Operation" section of the TKPROF
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 0 0
Execute 1 98.06 541.14 735758 1454850 30986 9999
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 98.06 541.14 735758 1454854 30986 9999
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 165
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE (cr=1454772 r=735739 w=0 time=541076424 us)
9999 COUNT STOPKEY (cr=19766 r=3755 w=0 time=968199 us)
9999 TABLE ACCESS FULL HIIR (cr=19766 r=3755 w=0 time=935182 us)
9999 SORT AGGREGATE (cr=1434947 r=731923 w=0 time=537588304 us)
19784 NESTED LOOPS (cr=1434947 r=731923 w=0 time=537385558 us)
290539 TABLE ACCESS BY INDEX ROWID MFG_DIST_DLR_PROD_HISTORY (cr=1144406 r=731922 w=0 time=532734557 us)
290539 INDEX RANGE SCAN MDDP_PK (cr=872544 r=622899 w=0 time=183584801 us)(object id 40318)
19784 TABLE ACCESS BY INDEX ROWID PERIOD_TABLE (cr=290541 r=1 w=0 time=3097982 us)
290539 INDEX UNIQUE SCAN PERIOD_TABLE_PK (cr=2 r=0 w=0 time=1030705 us)(object id 40381)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 735709 0.12 458.79
latch free 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
Table basic info
~~~~~~~~~~~~~~~~~~
period_table - 88 records
hiir - 30,000 records
mfg_dist_dlr_prod_history - 34 million records
Index
~~~~~
The unique key MDDP_PK is:
CREATE UNIQUE INDEX MDDP_PK ON MFG_DIST_DLR_PROD_HISTORY (
MFG_NO,
MFG_LOC,
DIST_NO,
DIST_LOC,
DLR_NO,
PERIOD,
DATA_TYPE,
RECV_BRANCH_NO,
PROD_CODE,
PROGRAM_NO,
RT_MFG_FLAG,
RT_DIST_FLAG,
RT_DLR_FLAG
)
TABLESPACE IMAX_INDEX3
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 2048M
NEXT 10M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
);
Analysis/Response
~~~~~~~~~~~~~~~~~
i) largest physical IO producer:
290539 INDEX RANGE SCAN MDDP_PK (cr=872544 r=622899 w=0 time=183584801 us)(object id 40318)
This step did
- 872544 logical IOs
- 622899 physicaly IOs
- 0 writes (to temp for example...)
ii) look at the ORDERING of the columns in the index MDDP_PK
predicates:
WHERE h.mfg_no = p.mfg_no
AND h.mfg_loc = p.mfg_loc_no
AND h.dist_no = p.dist_no
AND h.dist_loc = p.dist_loc_no
AND h.dlr_no = p.dlr_no
AND h.prod_code = p.prod_code
AND h.program_no = p.program_no
AND h.recv_branch_no = p.recv_branch_no
AND h.rt_mfg_flag = p.rt_mfg_flag
AND h.rt_dist_flag = p.rt_dist_flag
AND h.rt_dlr_flag = p.rt_dlr_flag
Index column order:
MFG_NO,
MFG_LOC,
DIST_NO,
DIST_LOC,
DLR_NO,
PERIOD, -- not in predicates
DATA_TYPE, -- not in predicates
RECV_BRANCH_NO,
PROD_CODE,
PROGRAM_NO,
RT_MFG_FLAG,
RT_DIST_FLAG,
RT_DLR_FLAG
If possible, rewrite it to
MFG_NO,
MFG_LOC,
DIST_NO,
DIST_LOC,
DLR_NO,
RECV_BRANCH_NO,
PROD_CODE,
PROGRAM_NO,
RT_MFG_FLAG,
RT_DIST_FLAG,
RT_DLR_FLAG
PERIOD, -- not in predicates
DATA_TYPE, -- not in predicates
If your key is (a,b,c,d,e) and you do:
where a = :a and b = :b and e = :e
we can range scan the index for all A and B values - looking for E's, if you had a key on
(a,b,e,c,d) - we would go right to a,b,e and stop immediately - no big scan.
No comments:
Post a Comment