导航菜单

ORACLE数据库日志切换次数优化--禁止定时任务后少切换了250次

优德w88软件下载

In the past month, a lot of hidden problems have arisen due to database storage space problems. The observation time period is caused by the early morning, because the space is less than 100G, and the archive log generated in the early morning has 100G, so it triggers a series. Question, the process of troubleshooting positioning is recorded below~

SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH: MI: SS'),1,5) Day,

SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24: MI: SS'),10,2),'00',1,0)) H00,

SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24: MI: SS'),10,2),'01',1,0)) H01,

SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24: MI: SS'),10,2),'02',1,0)) H02,

SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24: MI: SS'),10,2),'03',1,0)) H03,

xxSUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24: MI: SS'),10,2),'04',1,0)) H04,

SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24: MI: SS'),10,2),'05',1,0)) H05,

SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24: MI: SS'),10,2),'06',1,0)) H06,

SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24: MI: SS'),10,2),'07',1,0)) H07,

SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24: MI: SS'),10,2),'08',1,0)) H08,

SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24: MI: SS'),10,2),'09',1,0)) H09,

xxxxSUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'10',1,0))H10,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'11',1,0))H11,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'12',1,0))H12,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'13',1,0))H13,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'14',1,0))H14,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'15',1,0))H15,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'16',1,0))H16,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'17',1,0))H17,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'18',1,0))H18,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'19',1,0))H19,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'20',1,0))H20,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'21',1,0))H21,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'22',1,0))H22,

SUM(解码(SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH24: MI: SS'),10,2),'23',1,0))H23,

COUNT(*)TOTAL

FROM v $ log_history a

WHERE first_time>=to_char(sysdate-10)

GROUP BY SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH: MI: SS'),1,5)

ORBS BY SUBSTR(TO_CHAR(first_time,'MM/DD/RR HH: MI: SS'),1,5)DESC;

b04147257bfb4c7cb072589c26667fb1

可以看出,1点钟和2点钟的日志开关将达到约130倍。原本以为界面平台每天都在这个时间间隔运行,但界面平台基本上每十分钟或一小时运行一次。很少有固定时间运行1-2点左右,具体问题在哪里? sql导致了什么?

选择b。*

来自(

选择t.sql_id,

T.module,

(从sys.wrh $ _sqltext中选择to_char(dbms_lob.substr(sql_text,20,1)),其中sql_id=t.sql_id和rownum<=1)sql_text1,

To_char(s.begin_interval_time,'yyyy-mm-dd hh24')begin_interval_time1,

to_char(t.rows_processed_delta,'9999,9999,9999')rows_processed_delta,

Round((ratio_to_report(rows_processed_delta)over())* 100,2)rtr_row,

T.executions_delta,

To_char(t.rows_processed_delta /(t.executions_delta + 1),'9999,999999')row_exec

来自sys.wrh $ _sqlstat t,wrm $ _snapshot s

其中t.dbid=s.dbid

并且t.snap_id=s.snap_id

And s.begin_interval_time >=to_date(to_char(trunc(sysdate),'yyyy-mm-dd') ||'00: 00: 00','yyyy-mm-dd hh24: mi: ss')

And s.begin_interval_time <=to_date(to_char(trunc(sysdate) ,'yyyy-mm-dd')||'08: 00: 00','yyyy-mm-dd hh24: mi: ss')

Order by t.snap_id desc

) b

Where (upper(sql_text1) like '%INSERT%' or

Upper(sql_text1) like '%DELETE%' or

Upper(sql_text1) like '%UPDATE%')

Order by rows_processed_delta

e8ae2a397c7b4d63bfaac5f8e5927311

It can be observed that the timing task with 4 databases generates the most transaction volume. The four timing tasks are mainly for some large tables, only the last 30 days of data are retained, but the timing is performed at 1:30 every day. Once, it's a bit too frequent.

Begin

xxDBMS_SCHEDULER.DISABLE( 'SYSTEM.ARCHIVE_120_TABLES');

端;

/

开始

DBMS_SCHEDULER.DISABLE( 'SYSTEM.ARCHIVE_LOG_TABLES');

端;

/

开始

DBMS_SCHEDULER.DISABLE( 'SYSTEM.AUTO_ARCHIVE_DPCA');

端;

/

开始

DBMS_SCHEDULER.DISABLE( 'SYSTEM.AUTO_ARCHIVE_202');

端;

/

8fb1b75666b14ca99d446936e7ea22fe

408a819588d341dbb8708e8b557aa146

可以看到日志切换次数从250多次切换到现在10次切换,很明显这4个定时任务就是罪魁祸首了。

a8257ffed60540efb4dfc1ed78aa492c

dc1b4c5d4f094070b93f7fe133d19b80

与业务商定后修改这4个定时任务分别为每周六1点,2点,5点,10点进行。

修改定时任务时间这块前面介绍了,所以这里就不多说了.

后面会分享更多DEVOPS和DBA方面的内容,感兴趣的朋友可以关注一下

258d825db1ca4b6b99aa89592a942404

XX