--每日归档量
SELECT SUM(BLOCKS *BLOCK_SIZE )/1024/1024/1024 AS "Size(G)",TRUNC(completion_time) completion_time FROM gv$archived_log
where completion_time> to_date('2024-05-20','yyyy-mm-dd')
GROUP BY TRUNC(completion_time) order by completion_time desc;
Size(G) COMPLETION_TIME
---------- -------------------
.161448479 2024-05-26 00:00:00
.317644119 2024-05-25 00:00:00
.235857964 2024-05-24 00:00:00
.320913315 2024-05-23 00:00:00
.229777336 2024-05-22 00:00:00
.309884071 2024-05-21 00:00:00
.317107201 2024-05-20 00:00:00
--每小时归档数量
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",
ROUND (COUNT (1) / 24, 2) "Avg"
FROM gv$log_history
WHERE thread# = inst_id and first_time> to_date('2024-05-20','yyyy-mm-dd')
GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')
ORDER BY 1;
Date Day Total h0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13
------------------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
h14 h15 h16 h17 h18 h19 h20 h21 h22 h23 Avg
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2024-05-20 00:00:00 Mon 4 0 0 0 0 0 0 0 1 0 1 0 0 0 0
0 0 0 1 0 0 0 0 1 0 .17
2024-05-21 00:00:00 Tue 4 0 0 0 0 1 0 0 0 1 0 0 0 0 0
0 1 0 0 0 0 0 0 0 1 .17
2024-05-22 00:00:00 Wed 3 0 0 0 0 0 0 0 1 1 0 0 0 0 0
0 0 0 1 0 0 0 0 0 0 .13
2024-05-23 00:00:00 Thu 4 0 1 0 0 0 0 0 0 1 0 0 0 0 1
0 0 0 0 0 0 0 1 0 0 .17
2024-05-24 00:00:00 Fri 3 0 0 0 0 0 0 1 0 1 0 0 0 0 0
0 0 0 1 0 0 0 0 0 0 .13
2024-05-25 00:00:00 Sat 4 0 1 0 0 0 0 0 0 1 0 0 0 1 0
0 0 0 0 0 0 0 1 0 0 .17
-- 每个归档切换时间
select THREAD#,sequence#,First_change#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') First_time,switch_change# from
gv$loghist where first_time>to_date('2024-05-24','yyyy-mm-dd')
order by 1,2;
THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME SWITCH_CHANGE#
---------- ---------- ------------- ------------------- --------------
1 317 258577080 2024-05-24 06:00:36 258593265
1 318 258593265 2024-05-24 08:23:10 258647633
1 319 258647633 2024-05-24 17:00:23 258701589
1 320 258701589 2024-05-25 01:35:12 258752319
1 321 258752319 2024-05-25 08:23:23 258781782
1 322 258781782 2024-05-25 12:52:41 258845830
1 323 258845830 2024-05-25 21:00:29 258900835
1 324 258900835 2024-05-26 05:46:29 258918630
「欢迎关注我们的公众号，获取更多技术分享与经验交流。」
文章转载自数据库驾驶舱，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
