oracle 日志切换太频繁,诊断一次Oracle日志切换频繁的问题

日志切换，就是生成的日志太大，数据块的变化太频繁。

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