日志切換，就是生成的日志太大，數據塊的變化太頻繁。
Snap Id
Snap Time
Sessions
Cursors/Session
Begin Snap:
14560
09-Dec-15 04:00:48
59
4.5
End Snap:
14561
09-Dec-15 05:00:59
54
4.6
Elapsed:
60.19 (mins)
DB Time:
82.47 (mins)
1s產生2M的日志。
Per Second
Per Transaction
Per Exec
Per Call
DB Time(s):
1.4
3.3
0.02
0.47
DB CPU(s):
1.1
2.5
0.01
0.36
Redo size (bytes):
2,315,310.3
5,548,471.3
一個小時日志切換31次。
Statistic
Total
per Hour
log switches (derived)
31
30.90
重點來了，看segments by DB Blocks changes,看是什么對象造成的。
Segments by DB Blocks Changes
% of Capture shows % of DB Block Changes for each top segment compared
with total DB Block Changes for all segments captured by the Snapshot
Owner
Tablespace Name
Object Name
Subobject Name
Obj. Type
DB Block Changes
% of Capture
TC
TBS_TCI
VIEW_TICKET
TABLE
7,758,832
36.44
TC
TBS_TCI
V_DATA_RANGE
TABLE
7,076,112
33.23
TC
TBS_TCI
MV_TCM_WORKFORM
TABLE
3,593,664
16.88
TBM
TBS_TBM
VIEW_DATAAUTH_ROLE_USER
TABLE
2,368,016
11.12
TC
TBS_TC
RPT_ACCRUALS_DETAIL
TABLE
87,648
0.41
通過這些對象找到相應的SQL語句，定位就是它們。對這種業務，有兩種方法，1. delete改為truncate ? 2.加大redo log file的大小
36p9tcbryxnq4 delete from "VIEW_TICKET"
213rsbk7216pc delete from "V_DATA_RANGE"
0mv6h8360myzu delete from "MV_TCM_WORKFORM"
2u4qz8gfqs1z7 INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "MV_TCM_WORKFORM" select t.*, sysdate as s from v_tcm_workform t
)
