首页 > oracle知识 > oracle调优 >

数据库redolog切换频率统计分析

作者: 初见博客 分类: oracle调优 发布时间: 2021-06-23 16:49

edolog切换频率分析,一方面可以辅助评估数据库运行的繁忙程度,得出数据库忙闲时段的具体分布,另一方面结合redolog的大小可以评估开启归档所需预留的磁盘空间。下面分享笔者使用的一条sql语句,使用该语句可以得出redolog的切换频率分布结果。

SELECT TRUNC (first_time) “Date”, TO_CHAR (first_time, ‘Dy’) “Day”, COUNT (1) “Total”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’00’, 1, 0)) h0,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’01’, 1, 0)) “h1”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’02’, 1, 0)) “h2”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’03’, 1, 0)) “h3”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’04’, 1, 0)) “h4”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’05’, 1, 0)) “h5”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’06’, 1, 0)) “h6”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’07’, 1, 0)) “h7”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’08’, 1, 0)) “h8”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’09’, 1, 0)) “h9”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’10’, 1, 0)) “h10”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’11’, 1, 0)) “h11”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’12’, 1, 0)) “h12”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’13’, 1, 0)) “h13”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’14’, 1, 0)) “h14”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’15’, 1, 0)) “h15”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’16’, 1, 0)) “h16”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’17’, 1, 0)) “h17”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’18’, 1, 0)) “h18”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’19’, 1, 0)) “h19”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’20’, 1, 0)) “h20”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’21’, 1, 0)) “h21”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’22’, 1, 0)) “h22”,
SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’23’, 1, 0)) “h23”, to_char(ROUND (COUNT (1) / 24, 2),’fm99999999990.00′) “Avg”
FROM gv$log_history
WHERE first_time >= trunc(SYSDATE) – 30
and thread# = inst_id
GROUP BY TRUNC (first_time), TO_CHAR (first_time, ‘Dy’)
ORDER BY 1 DESC;

491f698448b1c3af

使用toad的Log Switch Frequency Map功能也可以直接统计出切换热图,结果如下:

0c211d23c6f328e2

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注