诊断一次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 


你可能感兴趣的