sql:
UPDATE /*+ BYPASS_UJVC*/
(
select /*+ ordered*/
a.DayNewAddUserFlag,a.MonNewAddUserFlag,
CASE WHEN TO_CHAR(b.FstActDate,'YYYYMMDD') = '20070811' THEN '1' ELSE '0' END DayNewAddUserFlag_v,
CASE WHEN b.FstActDate>=to_date('20070801','yyyymmdd') AND b.FstActDate< (TO_DATE('20070811','YYYYMMDD') + 1) THEN '1' ELSE '0' END MonNewAddUserFlag_v
from etl.t_etl_H023_pre_userinfo b,
T_FACT_PF_TD_SERV_INFO_hjl_1 a
WHERE a.days = substr('20070811',7,2)
and a.subscrbid = b.subscrbid
) rr
set rr.DayNewAddUserFlag = DayNewAddUserFlag_v,
rr.MonNewAddUserFlag = MonNewAddUserFlag_v
执行计划:
5 UPDATE STATEMENT
4 UPDATE
3 HASH JOIN
1 ETL.T_ETL_H023_PRE_USERINFO TABLE ACCESS [FULL]
2 FACT.T_FACT_PF_TD_SERV_INFO_HJL_1 TABLE ACCESS [FULL]
表 FACT.T_FACT_PF_TD_SERV_INFO_HJL_1 2000万行
表 ETL.T_ETL_H023_PRE_USERINFO 1000万行
trace的信息:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 1125.15 4207.15 2345917 297786 10529359 10398247
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 1125.15 4207.15 2345917 297786 10529359 10398247
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 39
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 378232 0.75 541.82
direct path read temp 334 0.04 1.22
latch: object queue header operation 18 0.00 0.00
latch free 5 0.00 0.00
rdbms ipc reply 11 0.00 0.01
latch: cache buffers chains 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.05 0.05
********************************************************************************
问题:前几天在20分钟执行完,现在要2个小时以上。且在 第三步
HASH JOIN 是发生大量的db file sequential read ,查看v$session_event 发现发生 大量的 cache buffers chains事件。
请各位高手不吝赐教,谢谢!