一、前言
目前一套混合数据库研发在进行改造，希望将其迁移至ClickHouse数据库，这套库本身体量比较大，好几个T，日常在凌晨时段，会从其它库将数据同步至该库，以往每日的归档量也都比较大，平均每日会产生好几百G的归档量。
研发这次在改造过程中，为方便改造，业务人员在该数据库服务器上申请新创建一个用户，避免和旧项目混淆，便于管理，另外可为后端接口开发提供便利，减少修改量，带该Oracle库CK改造完成后，将逐步下线Oracle任务，释放资源。
但在Oracle业务改造CK的过程中，导致每日产生2T的归档量，为该数据库分配的ASM存储空间非常紧张，归档日志保留天数从原来的3天改为一天，备库因为归档量太多导致空间不足，经常要手工删除已应用的主库归档日志才能继续同步数据，后来将备库的删除归档日志脚本调整为每隔5分钟就要删除一次。
以下是从zabbix监控观测到近一个月主库ASM剩余空间量情况。
近期该库每日归档量产生量如下图所示：
每日不同时段谷归档日志切换次数，如下图所示:
从上述两幅图可以看到每天产生的归档量都很多，而且经常集中在晚上8点后和凌晨3点前这一时段。
这套库上部署两个数据库实例，多个业务，经常我在凌晨收到告警短信提示主库ASM空间已超过告警阈值，经常我要手工进行清理操作。
和项目组沟通，项目组希望排查到底是哪些对象导致产生归档量多，以下是本次排查记录。
二、排查过程
通过如下语句查询近一周每天日志切换次数
WITH T
AS ( SELECT TO_CHAR (FIRST_TIME, 'YYYY-MM-DD') AS LOG_GEN_DAY,
TO_CHAR (
SUM (
DECODE (TO_CHAR (FIRST_TIME, 'YYYY-MM-DD'),
TO_CHAR (FIRST_TIME, 'YYYY-MM-DD'), 1,
0)),
'999')
AS "LOG_SWITCH_NUM"
FROM V$LOG_HISTORY
WHERE FIRST_TIME < TRUNC (SYSDATE)
GROUP BY TO_CHAR (FIRST_TIME, 'YYYY-MM-DD'))
SELECT T.LOG_GEN_DAY,
T.LOG_SWITCH_NUM,
M.AVG_LOG_SWITCH_NUM,
(T.LOG_SWITCH_NUM - M.AVG_LOG_SWITCH_NUM) AS DIFF_SWITCH_NUM
FROM T
CROSS JOIN
(SELECT TO_CHAR (AVG (T.LOG_SWITCH_NUM), '999')
AS AVG_LOG_SWITCH_NUM
FROM T) M
ORDER BY T.LOG_GEN_DAY DESC;
LOG_GEN_DA LOG_SWITCH_NUM AVG_LOG_SWITCH_NUM DIFF_SWITCH_NUM
---日期-- ---日志切换次数-- --以往平均日志切换次数 两者对比
---------- -------------- ------------------ ---------------
2023-08-24 5528 4542.75 985.25
2023-08-23 5534 4542.75 991.25
2023-08-22 5034 4542.75 491.25
2023-08-21 4332 4542.75 -210.75
2023-08-20 4679 4542.75 136.25
2023-08-19 4260 4542.75 -282.75
2023-08-18 4279 4542.75 -263.75
2023-08-17 2696 4542.75 -1846.75
通过如下语句查询近一段时间redo log 历史信息，可以通过如下一条SQL查询
------------------------------------------------------------------------------------------------
REM Author: Riyaj Shamsudeen @OraInternals, LLC
REM www.orainternals.com
REM
REM Functionality: This script is to print redo size rates in a RAC claster
REM **************
REM
REM Source : AWR tables
REM
REM Exectution type: Execute from sqlplus or any other tool.
REM
REM Parameters: No parameters. Uses Last snapshot and the one prior snap
REM No implied or explicit warranty
REM
REM Please send me an email to rshamsud@orainternals.com, if you enhance this script :-)
REM This is a open Source code and it is free to use and modify.
REM Version 1.20
REM
------------------------------------------------------------------------------------------------
set colsep '|'
set lines 220
alter session set nls_date_format='YYYY-MM-DD HH24:MI';
set pagesize 10000
with redo_data as (
SELECT instance_number,
to_date(to_char(redo_date,'DD-MON-YY-HH24:MI'), 'DD-MON-YY-HH24:MI') redo_dt,
trunc(redo_size/(1024 * 1024),2) redo_size_mb
FROM (
SELECT dbid, instance_number, redo_date, redo_size , startup_time FROM (
SELECT sysst.dbid,sysst.instance_number, begin_interval_time redo_date, startup_time,
VALUE -
lag (VALUE) OVER
( PARTITION BY sysst.dbid, sysst.instance_number, startup_time
ORDER BY begin_interval_time ,sysst.instance_number
) redo_size
FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE sysst.stat_id =
( SELECT stat_id FROM sys.wrh$_stat_name WHERE stat_name='redo size' )
AND snaps.snap_id = sysst.snap_id
AND snaps.dbid =sysst.dbid
AND sysst.instance_number = snaps.instance_number
AND snaps.begin_interval_time> sysdate-30
ORDER BY snaps.snap_id )
)
)
select instance_number, redo_dt, redo_size_mb,
sum (redo_size_mb) over (partition by trunc(redo_dt)) total_daily,
trunc(sum (redo_size_mb) over (partition by trunc(redo_dt))/24,2) hourly_rate
from redo_Data
order by redo_dt, instance_number
/
-- 结果如下
INSTANCE_NUMBER|REDO_DT |REDO_SIZE_MB|TOTAL_DAILY|HOURLY_RATE
--数据库实例号 日志切换时间 产生的日志量 当天日志总量 每小时日志量
---------------|----------------|------------|-----------|-----------
1|2023-08-16 01:19| | 1152420.89| 48017.53
2|2023-08-16 01:19| | 1152420.89| 48017.53
1|2023-08-16 02:19| 4.04| 1152420.89| 48017.53
2|2023-08-16 02:19| 913.75| 1152420.89| 48017.53
1|2023-08-16 03:19| 4.41| 1152420.89| 48017.53
2|2023-08-16 03:19| 4.12| 1152420.89| 48017.53
1|2023-08-16 04:19| .32| 1152420.89| 48017.53
2|2023-08-16 04:19| .07| 1152420.89| 48017.53
1|2023-08-16 05:19| .24| 1152420.89| 48017.53
2|2023-08-16 05:19| .15| 1152420.89| 48017.53
1|2023-08-16 06:19| .19| 1152420.89| 48017.53
2|2023-08-16 06:19| .18| 1152420.89| 48017.53
1|2023-08-16 07:19| .32| 1152420.89| 48017.53
2|2023-08-16 07:19| .39| 1152420.89| 48017.53
1|2023-08-16 08:19| .64| 1152420.89| 48017.53
2|2023-08-16 08:19| .38| 1152420.89| 48017.53
1|2023-08-16 09:19| .68| 1152420.89| 48017.53
2|2023-08-16 09:19| .47| 1152420.89| 48017.53
1|2023-08-16 10:20| 7.34| 1152420.89| 48017.53
2|2023-08-16 10:20| .4| 1152420.89| 48017.53
1|2023-08-16 11:19| 20756.49| 1152420.89| 48017.53
2|2023-08-16 11:19| 20271.88| 1152420.89| 48017.53
1|2023-08-16 12:19| .64| 1152420.89| 48017.53
2|2023-08-16 12:19| .51| 1152420.89| 48017.53
1|2023-08-16 13:19| .55| 1152420.89| 48017.53
2|2023-08-16 13:19| .29| 1152420.89| 48017.53
1|2023-08-16 14:19| 4014.64| 1152420.89| 48017.53
2|2023-08-16 14:19| 4476.3| 1152420.89| 48017.53
1|2023-08-16 15:20| 34759.8| 1152420.89| 48017.53
2|2023-08-16 15:20| 23164.05| 1152420.89| 48017.53
1|2023-08-16 16:19| 1648.57| 1152420.89| 48017.53
2|2023-08-16 16:19| 47854.5| 1152420.89| 48017.53
1|2023-08-16 17:19| 3| 1152420.89| 48017.53
2|2023-08-16 17:19| 49160.89| 1152420.89| 48017.53
1|2023-08-16 18:19| 1941.52| 1152420.89| 48017.53
2|2023-08-16 18:19| 2297.18| 1152420.89| 48017.53
1|2023-08-16 19:19| 29938.97| 1152420.89| 48017.53
2|2023-08-16 19:19| 98063.67| 1152420.89| 48017.53
1|2023-08-16 20:20| 138491.73| 1152420.89| 48017.53
2|2023-08-16 20:20| 182871.43| 1152420.89| 48017.53
1|2023-08-16 21:19| 116346.39| 1152420.89| 48017.53
2|2023-08-16 21:19| 166364.5| 1152420.89| 48017.53
1|2023-08-16 22:19| 43692.51| 1152420.89| 48017.53
2|2023-08-16 22:19| 124527.91| 1152420.89| 48017.53
1|2023-08-16 23:20| 15.63| 1152420.89| 48017.53
2|2023-08-16 23:20| 40819.25| 1152420.89| 48017.53
1|2023-08-17 00:20| .37| 1338636.68| 55776.52
2|2023-08-17 00:20| 24104.49| 1338636.68| 55776.52
1|2023-08-17 01:19| .46| 1338636.68| 55776.52
2|2023-08-17 01:19| 5078.51| 1338636.68| 55776.52
1|2023-08-17 02:19| 907.77| 1338636.68| 55776.52
2|2023-08-17 02:19| .39| 1338636.68| 55776.52
1|2023-08-17 03:19| .4| 1338636.68| 55776.52
2|2023-08-17 03:19| .63| 1338636.68| 55776.52
1|2023-08-17 04:19| .18| 1338636.68| 55776.52
2|2023-08-17 04:19| .23| 1338636.68| 55776.52
1|2023-08-17 05:20| 2.27| 1338636.68| 55776.52
2|2023-08-17 05:20| .62| 1338636.68| 55776.52
1|2023-08-17 06:19| 11.79| 1338636.68| 55776.52
2|2023-08-17 06:19| 2.93| 1338636.68| 55776.52
1|2023-08-17 07:19| 5.81| 1338636.68| 55776.52
2|2023-08-17 07:19| 10.42| 1338636.68| 55776.52
1|2023-08-17 08:19| 3.85| 1338636.68| 55776.52
2|2023-08-17 08:19| 3.9| 1338636.68| 55776.52
1|2023-08-17 09:19| 3.34| 1338636.68| 55776.52
2|2023-08-17 09:19| 4.51| 1338636.68| 55776.52
1|2023-08-17 10:19| .71| 1338636.68| 55776.52
2|2023-08-17 10:19| 1.21| 1338636.68| 55776.52
1|2023-08-17 11:19| .85| 1338636.68| 55776.52
2|2023-08-17 11:19| 115.65| 1338636.68| 55776.52
1|2023-08-17 12:19| .58| 1338636.68| 55776.52
2|2023-08-17 12:19| .56| 1338636.68| 55776.52
1|2023-08-17 13:19| .74| 1338636.68| 55776.52
2|2023-08-17 13:19| 5436.84| 1338636.68| 55776.52
1|2023-08-17 14:19| 35237.18| 1338636.68| 55776.52
2|2023-08-17 14:19| 5182.46| 1338636.68| 55776.52
1|2023-08-17 15:19| 51749.07| 1338636.68| 55776.52
2|2023-08-17 15:19| 23078.98| 1338636.68| 55776.52
1|2023-08-17 16:19| 27831.43| 1338636.68| 55776.52
2|2023-08-17 16:19| 38593.15| 1338636.68| 55776.52
1|2023-08-17 17:19| 2356.26| 1338636.68| 55776.52
2|2023-08-17 17:19| 67383.4| 1338636.68| 55776.52
1|2023-08-17 18:20| .68| 1338636.68| 55776.52
2|2023-08-17 18:20| 17278.17| 1338636.68| 55776.52
1|2023-08-17 19:19| 17883.54| 1338636.68| 55776.52
2|2023-08-17 19:19| 92089.46| 1338636.68| 55776.52
1|2023-08-17 20:19| 169194.39| 1338636.68| 55776.52
2|2023-08-17 20:19| 196925.92| 1338636.68| 55776.52
1|2023-08-17 21:19| 190696.84| 1338636.68| 55776.52
2|2023-08-17 21:19| 132122.64| 1338636.68| 55776.52
1|2023-08-17 22:19| 79821.21| 1338636.68| 55776.52
2|2023-08-17 22:19| 98485.05| 1338636.68| 55776.52
1|2023-08-17 23:19| 18814.23| 1338636.68| 55776.52
2|2023-08-17 23:19| 38212.61| 1338636.68| 55776.52
1|2023-08-18 00:19| 47.58| 1425002.35| 59375.09
2|2023-08-18 00:19| 26257.42| 1425002.35| 59375.09
1|2023-08-18 01:20| 8.2| 1425002.35| 59375.09
2|2023-08-18 01:20| 38704.02| 1425002.35| 59375.09
1|2023-08-18 02:20| .37| 1425002.35| 59375.09
2|2023-08-18 02:20| 67371.26| 1425002.35| 59375.09
1|2023-08-18 03:19| .2| 1425002.35| 59375.09
2|2023-08-18 03:19| .62| 1425002.35| 59375.09
1|2023-08-18 04:19| .13| 1425002.35| 59375.09
2|2023-08-18 04:19| .39| 1425002.35| 59375.09
1|2023-08-18 05:19| .22| 1425002.35| 59375.09
2|2023-08-18 05:19| .41| 1425002.35| 59375.09
1|2023-08-18 06:19| .13| 1425002.35| 59375.09
2|2023-08-18 06:19| .36| 1425002.35| 59375.09
1|2023-08-18 07:20| .37| 1425002.35| 59375.09
2|2023-08-18 07:20| .55| 1425002.35| 59375.09
1|2023-08-18 08:19| 1.06| 1425002.35| 59375.09
2|2023-08-18 08:19| .97| 1425002.35| 59375.09
1|2023-08-18 09:19| 2377.03| 1425002.35| 59375.09
2|2023-08-18 09:19| 3683.25| 1425002.35| 59375.09
1|2023-08-18 10:19| 5446.4| 1425002.35| 59375.09
2|2023-08-18 10:19| 3353.81| 1425002.35| 59375.09
1|2023-08-18 11:19| 43393.5| 1425002.35| 59375.09
2|2023-08-18 11:19| 4202.62| 1425002.35| 59375.09
1|2023-08-18 12:19| 1.37| 1425002.35| 59375.09
2|2023-08-18 12:19| 1.27| 1425002.35| 59375.09
1|2023-08-18 13:19| 37713.84| 1425002.35| 59375.09
2|2023-08-18 13:19| 13.43| 1425002.35| 59375.09
1|2023-08-18 14:19| 79879.65| 1425002.35| 59375.09
2|2023-08-18 14:19| 5211.79| 1425002.35| 59375.09
1|2023-08-18 15:19| 38138.9| 1425002.35| 59375.09
2|2023-08-18 15:19| 25199.97| 1425002.35| 59375.09
1|2023-08-18 16:19| 63483.71| 1425002.35| 59375.09
2|2023-08-18 16:19| .9| 1425002.35| 59375.09
1|2023-08-18 17:19| 32013.06| 1425002.35| 59375.09
2|2023-08-18 17:19| 2.27| 1425002.35| 59375.09
1|2023-08-18 18:19| 3358.63| 1425002.35| 59375.09
2|2023-08-18 18:19| 940.87| 1425002.35| 59375.09
1|2023-08-18 19:19| 63273.13| 1425002.35| 59375.09
2|2023-08-18 19:19| 61759.43| 1425002.35| 59375.09
1|2023-08-18 20:19| 190250.84| 1425002.35| 59375.09
2|2023-08-18 20:19| 123742.07| 1425002.35| 59375.09
1|2023-08-18 21:19| 148004.18| 1425002.35| 59375.09
2|2023-08-18 21:19| 121757.51| 1425002.35| 59375.09
1|2023-08-18 22:19| 97055.44| 1425002.35| 59375.09
2|2023-08-18 22:19| 94255.69| 1425002.35| 59375.09
1|2023-08-18 23:20| 1865.81| 1425002.35| 59375.09
2|2023-08-18 23:20| 42227.72| 1425002.35| 59375.09
1|2023-08-19 00:19| 55.4| 1562183.6| 65090.98
2|2023-08-19 00:19| 26262.6| 1562183.6| 65090.98
1|2023-08-19 01:19| 15.08| 1562183.6| 65090.98
2|2023-08-19 01:19| 34927.71| 1562183.6| 65090.98
1|2023-08-19 02:19| 1.34| 1562183.6| 65090.98
2|2023-08-19 02:19| 59092.42| 1562183.6| 65090.98
1|2023-08-19 03:19| 2.1| 1562183.6| 65090.98
2|2023-08-19 03:19| 2.21| 1562183.6| 65090.98
1|2023-08-19 04:20| 10.55| 1562183.6| 65090.98
2|2023-08-19 04:20| 12.95| 1562183.6| 65090.98
1|2023-08-19 05:20| 25.18| 1562183.6| 65090.98
2|2023-08-19 05:20| 3.67| 1562183.6| 65090.98
1|2023-08-19 06:19| 76.21| 1562183.6| 65090.98
2|2023-08-19 06:19| 3.59| 1562183.6| 65090.98
1|2023-08-19 07:19| 66.56| 1562183.6| 65090.98
2|2023-08-19 07:19| 1.55| 1562183.6| 65090.98
1|2023-08-19 08:19| .59| 1562183.6| 65090.98
2|2023-08-19 08:19| .65| 1562183.6| 65090.98
1|2023-08-19 09:19| 42.9| 1562183.6| 65090.98
2|2023-08-19 09:19| .71| 1562183.6| 65090.98
1|2023-08-19 10:19| .63| 1562183.6| 65090.98
2|2023-08-19 10:19| .44| 1562183.6| 65090.98
1|2023-08-19 11:19| .45| 1562183.6| 65090.98
2|2023-08-19 11:19| 16.7| 1562183.6| 65090.98
1|2023-08-19 12:19| 100.47| 1562183.6| 65090.98
2|2023-08-19 12:19| .39| 1562183.6| 65090.98
1|2023-08-19 13:19| 23.02| 1562183.6| 65090.98
2|2023-08-19 13:19| .38| 1562183.6| 65090.98
1|2023-08-19 14:19| .36| 1562183.6| 65090.98
2|2023-08-19 14:19| .45| 1562183.6| 65090.98
1|2023-08-19 15:19| 29355.41| 1562183.6| 65090.98
2|2023-08-19 15:19| 10528.14| 1562183.6| 65090.98
1|2023-08-19 16:19| 16765.88| 1562183.6| 65090.98
2|2023-08-19 16:19| 33340.27| 1562183.6| 65090.98
1|2023-08-19 17:19| 17.12| 1562183.6| 65090.98
2|2023-08-19 17:19| 56011.82| 1562183.6| 65090.98
1|2023-08-19 18:19| 61.16| 1562183.6| 65090.98
2|2023-08-19 18:19| 26561.9| 1562183.6| 65090.98
1|2023-08-19 19:19| 65604.06| 1562183.6| 65090.98
2|2023-08-19 19:19| 61783.17| 1562183.6| 65090.98
1|2023-08-19 20:19| 211205.14| 1562183.6| 65090.98
2|2023-08-19 20:19| 173221.87| 1562183.6| 65090.98
1|2023-08-19 21:20| 189277.13| 1562183.6| 65090.98
2|2023-08-19 21:20| 137040.21| 1562183.6| 65090.98
1|2023-08-19 22:19| 134138.25| 1562183.6| 65090.98
2|2023-08-19 22:19| 134958.6| 1562183.6| 65090.98
1|2023-08-19 23:19| 94765.92| 1562183.6| 65090.98
2|2023-08-19 23:19| 66800.29| 1562183.6| 65090.98
1|2023-08-20 00:20| 83413.34| 1679174.62| 69965.6
2|2023-08-20 00:20| 76710.2| 1679174.62| 69965.6
1|2023-08-20 01:19| 8664.52| 1679174.62| 69965.6
2|2023-08-20 01:19| 60369.95| 1679174.62| 69965.6
1|2023-08-20 02:19| .53| 1679174.62| 69965.6
2|2023-08-20 02:19| 59161.8| 1679174.62| 69965.6
1|2023-08-20 03:19| .57| 1679174.62| 69965.6
2|2023-08-20 03:19| .48| 1679174.62| 69965.6
1|2023-08-20 04:20| .21| 1679174.62| 69965.6
2|2023-08-20 04:20| .22| 1679174.62| 69965.6
1|2023-08-20 05:20| .15| 1679174.62| 69965.6
2|2023-08-20 05:20| 57.08| 1679174.62| 69965.6
1|2023-08-20 06:19| 17.04| 1679174.62| 69965.6
2|2023-08-20 06:19| 40.53| 1679174.62| 69965.6
1|2023-08-20 07:19| .21| 1679174.62| 69965.6
2|2023-08-20 07:19| .57| 1679174.62| 69965.6
1|2023-08-20 08:19| .2| 1679174.62| 69965.6
2|2023-08-20 08:19| .31| 1679174.62| 69965.6
1|2023-08-20 09:19| 58.41| 1679174.62| 69965.6
2|2023-08-20 09:19| 101.46| 1679174.62| 69965.6
1|2023-08-20 10:20| 6432.93| 1679174.62| 69965.6
2|2023-08-20 10:20| 5297.36| 1679174.62| 69965.6
1|2023-08-20 11:19| 43210.62| 1679174.62| 69965.6
2|2023-08-20 11:19| 22171.59| 1679174.62| 69965.6
1|2023-08-20 12:19| 49937.16| 1679174.62| 69965.6
2|2023-08-20 12:19| 4.84| 1679174.62| 69965.6
1|2023-08-20 13:19| 48495.73| 1679174.62| 69965.6
2|2023-08-20 13:19| 4.47| 1679174.62| 69965.6
1|2023-08-20 14:19| 2151.15| 1679174.62| 69965.6
2|2023-08-20 14:19| 2280.13| 1679174.62| 69965.6
1|2023-08-20 15:19| 13.86| 1679174.62| 69965.6
2|2023-08-20 15:19| .53| 1679174.62| 69965.6
1|2023-08-20 16:19| .54| 1679174.62| 69965.6
2|2023-08-20 16:19| .26| 1679174.62| 69965.6
1|2023-08-20 17:19| 32.18| 1679174.62| 69965.6
2|2023-08-20 17:19| .27| 1679174.62| 69965.6
1|2023-08-20 18:19| .29| 1679174.62| 69965.6
2|2023-08-20 18:19| .33| 1679174.62| 69965.6
1|2023-08-20 19:19| 41873.94| 1679174.62| 69965.6
2|2023-08-20 19:19| 81779.65| 1679174.62| 69965.6
1|2023-08-20 20:19| 113931.74| 1679174.62| 69965.6
2|2023-08-20 20:19| 232933.1| 1679174.62| 69965.6
1|2023-08-20 21:19| 147415.82| 1679174.62| 69965.6
2|2023-08-20 21:19| 135966.76| 1679174.62| 69965.6
1|2023-08-20 22:19| 118217.87| 1679174.62| 69965.6
2|2023-08-20 22:19| 133536.91| 1679174.62| 69965.6
1|2023-08-20 23:19| 77319.54| 1679174.62| 69965.6
2|2023-08-20 23:19| 127567.27| 1679174.62| 69965.6
1|2023-08-21 00:19| 43658.59| 1567327.87| 65305.32
2|2023-08-21 00:19| 14166.61| 1567327.87| 65305.32
1|2023-08-21 01:20| 11037.49| 1567327.87| 65305.32
2|2023-08-21 01:20| 34031.9| 1567327.87| 65305.32
1|2023-08-21 02:19| 3.18| 1567327.87| 65305.32
2|2023-08-21 02:19| 64020.71| 1567327.87| 65305.32
1|2023-08-21 03:19| 8.64| 1567327.87| 65305.32
2|2023-08-21 03:19| 9.35| 1567327.87| 65305.32
1|2023-08-21 04:19| 3.04| 1567327.87| 65305.32
2|2023-08-21 04:19| 2.75| 1567327.87| 65305.32
1|2023-08-21 05:19| 2.23| 1567327.87| 65305.32
2|2023-08-21 05:19| 2.76| 1567327.87| 65305.32
1|2023-08-21 06:20| .06| 1567327.87| 65305.32
2|2023-08-21 06:20| 16.83| 1567327.87| 65305.32
1|2023-08-21 07:19| .55| 1567327.87| 65305.32
2|2023-08-21 07:19| .98| 1567327.87| 65305.32
1|2023-08-21 08:19| 1.97| 1567327.87| 65305.32
2|2023-08-21 08:19| 2.46| 1567327.87| 65305.32
1|2023-08-21 09:19| 102.19| 1567327.87| 65305.32
2|2023-08-21 09:19| 1.62| 1567327.87| 65305.32
1|2023-08-21 10:19| 5938.51| 1567327.87| 65305.32
2|2023-08-21 10:19| 6864.28| 1567327.87| 65305.32
1|2023-08-21 11:20| 24386.15| 1567327.87| 65305.32
2|2023-08-21 11:20| 41243.75| 1567327.87| 65305.32
1|2023-08-21 12:19| .67| 1567327.87| 65305.32
2|2023-08-21 12:19| 57194.47| 1567327.87| 65305.32
1|2023-08-21 13:19| 2.79| 1567327.87| 65305.32
2|2023-08-21 13:19| 39730.88| 1567327.87| 65305.32
1|2023-08-21 14:19| 4150.86| 1567327.87| 65305.32
2|2023-08-21 14:19| 254.41| 1567327.87| 65305.32
1|2023-08-21 15:19| .69| 1567327.87| 65305.32
2|2023-08-21 15:19| .86| 1567327.87| 65305.32
1|2023-08-21 16:19| .98| 1567327.87| 65305.32
2|2023-08-21 16:19| 1.15| 1567327.87| 65305.32
1|2023-08-21 17:19| 1.5| 1567327.87| 65305.32
2|2023-08-21 17:19| 1.39| 1567327.87| 65305.32
1|2023-08-21 18:19| 11.32| 1567327.87| 65305.32
2|2023-08-21 18:19| 14.33| 1567327.87| 65305.32
1|2023-08-21 19:19| 56481.89| 1567327.87| 65305.32
2|2023-08-21 19:19| 68239.38| 1567327.87| 65305.32
1|2023-08-21 20:19| 176650.48| 1567327.87| 65305.32
2|2023-08-21 20:19| 167236.12| 1567327.87| 65305.32
1|2023-08-21 21:19| 150272.36| 1567327.87| 65305.32
2|2023-08-21 21:19| 124321.11| 1567327.87| 65305.32
1|2023-08-21 22:19| 140541.88| 1567327.87| 65305.32
2|2023-08-21 22:19| 104863.56| 1567327.87| 65305.32
1|2023-08-21 23:19| 75123.23| 1567327.87| 65305.32
2|2023-08-21 23:19| 156724.96| 1567327.87| 65305.32
1|2023-08-22 00:19| 76267.08| 1802963.81| 75123.49
2|2023-08-22 00:19| 99088| 1802963.81| 75123.49
1|2023-08-22 01:20| 37559.62| 1802963.81| 75123.49
2|2023-08-22 01:20| 58811.51| 1802963.81| 75123.49
1|2023-08-22 02:19| 58303.27| 1802963.81| 75123.49
2|2023-08-22 02:19| 6113.9| 1802963.81| 75123.49
1|2023-08-22 03:19| .35| 1802963.81| 75123.49
2|2023-08-22 03:19| .58| 1802963.81| 75123.49
1|2023-08-22 04:19| .14| 1802963.81| 75123.49
2|2023-08-22 04:19| .27| 1802963.81| 75123.49
1|2023-08-22 05:19| .13| 1802963.81| 75123.49
2|2023-08-22 05:19| .28| 1802963.81| 75123.49
1|2023-08-22 06:19| .2| 1802963.81| 75123.49
2|2023-08-22 06:19| .27| 1802963.81| 75123.49
1|2023-08-22 07:19| .54| 1802963.81| 75123.49
2|2023-08-22 07:19| 17.52| 1802963.81| 75123.49
1|2023-08-22 08:19| 5.04| 1802963.81| 75123.49
2|2023-08-22 08:19| 50.5| 1802963.81| 75123.49
1|2023-08-22 09:19| 13.46| 1802963.81| 75123.49
2|2023-08-22 09:19| 11.52| 1802963.81| 75123.49
1|2023-08-22 10:19| 2.31| 1802963.81| 75123.49
2|2023-08-22 10:19| 2.77| 1802963.81| 75123.49
1|2023-08-22 11:19| 4161.4| 1802963.81| 75123.49
2|2023-08-22 11:19| 25464.44| 1802963.81| 75123.49
1|2023-08-22 12:19| 23730.19| 1802963.81| 75123.49
2|2023-08-22 12:19| 32263.52| 1802963.81| 75123.49
1|2023-08-22 13:19| 1.06| 1802963.81| 75123.49
2|2023-08-22 13:19| 47777.54| 1802963.81| 75123.49
1|2023-08-22 14:20| 3582.7| 1802963.81| 75123.49
2|2023-08-22 14:20| 50690.46| 1802963.81| 75123.49
1|2023-08-22 15:19| 2158.52| 1802963.81| 75123.49
2|2023-08-22 15:19| 2282.49| 1802963.81| 75123.49
1|2023-08-22 16:19| .85| 1802963.81| 75123.49
2|2023-08-22 16:19| .5| 1802963.81| 75123.49
1|2023-08-22 17:19| .61| 1802963.81| 75123.49
2|2023-08-22 17:19| 12257.47| 1802963.81| 75123.49
1|2023-08-22 18:19| .48| 1802963.81| 75123.49
2|2023-08-22 18:19| 550.67| 1802963.81| 75123.49
1|2023-08-22 19:20| 47341.26| 1802963.81| 75123.49
2|2023-08-22 19:20| 73200.4| 1802963.81| 75123.49
1|2023-08-22 20:19| 139324.53| 1802963.81| 75123.49
2|2023-08-22 20:19| 212522.68| 1802963.81| 75123.49
1|2023-08-22 21:19| 142770.68| 1802963.81| 75123.49
2|2023-08-22 21:19| 139896.85| 1802963.81| 75123.49
1|2023-08-22 22:19| 122309.23| 1802963.81| 75123.49
2|2023-08-22 22:19| 139527.51| 1802963.81| 75123.49
1|2023-08-22 23:19| 122890.2| 1802963.81| 75123.49
2|2023-08-22 23:19| 122008.31| 1802963.81| 75123.49
1|2023-08-23 00:19| 79560.29| 1930297.19| 80429.04
2|2023-08-23 00:19| 122690.79| 1930297.19| 80429.04
1|2023-08-23 01:19| 35727.15| 1930297.19| 80429.04
2|2023-08-23 01:19| 110199.79| 1930297.19| 80429.04
1|2023-08-23 02:19| 19977.94| 1930297.19| 80429.04
2|2023-08-23 02:19| 65605.77| 1930297.19| 80429.04
1|2023-08-23 03:19| 4.42| 1930297.19| 80429.04
2|2023-08-23 03:19| 3.22| 1930297.19| 80429.04
1|2023-08-23 04:19| .1| 1930297.19| 80429.04
2|2023-08-23 04:19| .36| 1930297.19| 80429.04
1|2023-08-23 05:19| .11| 1930297.19| 80429.04
2|2023-08-23 05:19| .34| 1930297.19| 80429.04
1|2023-08-23 06:19| 16.76| 1930297.19| 80429.04
2|2023-08-23 06:19| .37| 1930297.19| 80429.04
1|2023-08-23 07:19| 1.01| 1930297.19| 80429.04
2|2023-08-23 07:19| 1.06| 1930297.19| 80429.04
1|2023-08-23 08:19| 2.16| 1930297.19| 80429.04
2|2023-08-23 08:19| 2.58| 1930297.19| 80429.04
1|2023-08-23 09:20| 1.4| 1930297.19| 80429.04
2|2023-08-23 09:20| 1.26| 1930297.19| 80429.04
1|2023-08-23 10:19| 39999.65| 1930297.19| 80429.04
2|2023-08-23 10:19| 393.08| 1930297.19| 80429.04
1|2023-08-23 11:19| 84517.27| 1930297.19| 80429.04
2|2023-08-23 11:19| 25767.91| 1930297.19| 80429.04
1|2023-08-23 12:19| 60696.8| 1930297.19| 80429.04
2|2023-08-23 12:19| 39028.15| 1930297.19| 80429.04
1|2023-08-23 13:19| 109.92| 1930297.19| 80429.04
2|2023-08-23 13:19| 67642.07| 1930297.19| 80429.04
1|2023-08-23 14:20| 20777.47| 1930297.19| 80429.04
2|2023-08-23 14:20| 9403.11| 1930297.19| 80429.04
1|2023-08-23 15:19| 2873.88| 1930297.19| 80429.04
2|2023-08-23 15:19| 2461.21| 1930297.19| 80429.04
1|2023-08-23 16:19| 108.44| 1930297.19| 80429.04
2|2023-08-23 16:19| 10.38| 1930297.19| 80429.04
1|2023-08-23 17:19| 2.36| 1930297.19| 80429.04
2|2023-08-23 17:19| 2.58| 1930297.19| 80429.04
1|2023-08-23 18:19| 2.19| 1930297.19| 80429.04
2|2023-08-23 18:19| 2.61| 1930297.19| 80429.04
1|2023-08-23 19:20| 65253.69| 1930297.19| 80429.04
2|2023-08-23 19:20| 59524.2| 1930297.19| 80429.04
1|2023-08-23 20:19| 248198.78| 1930297.19| 80429.04
2|2023-08-23 20:19| 150924.99| 1930297.19| 80429.04
1|2023-08-23 21:19| 147151.71| 1930297.19| 80429.04
2|2023-08-23 21:19| 126127.1| 1930297.19| 80429.04
1|2023-08-23 22:19| 86125.46| 1930297.19| 80429.04
2|2023-08-23 22:19| 104470.74| 1930297.19| 80429.04
1|2023-08-23 23:19| 43631.96| 1930297.19| 80429.04
2|2023-08-23 23:19| 111292.6| 1930297.19| 80429.04
1|2023-08-24 00:19| 38014.91| 1875994.22| 78166.42
2|2023-08-24 00:19| 77684.61| 1875994.22| 78166.42
1|2023-08-24 01:19| 8572.47| 1875994.22| 78166.42
2|2023-08-24 01:19| 84571.92| 1875994.22| 78166.42
1|2023-08-24 02:19| 6033.5| 1875994.22| 78166.42
2|2023-08-24 02:19| 129777.87| 1875994.22| 78166.42
1|2023-08-24 03:19| .54| 1875994.22| 78166.42
2|2023-08-24 03:19| .47| 1875994.22| 78166.42
1|2023-08-24 04:19| .08| 1875994.22| 78166.42
2|2023-08-24 04:19| .2| 1875994.22| 78166.42
1|2023-08-24 05:19| .49| 1875994.22| 78166.42
2|2023-08-24 05:19| .73| 1875994.22| 78166.42
1|2023-08-24 06:19| 20.08| 1875994.22| 78166.42
2|2023-08-24 06:19| 3.97| 1875994.22| 78166.42
1|2023-08-24 07:19| 10.75| 1875994.22| 78166.42
2|2023-08-24 07:19| 11.09| 1875994.22| 78166.42
1|2023-08-24 08:19| 117.61| 1875994.22| 78166.42
2|2023-08-24 08:19| 3.03| 1875994.22| 78166.42
1|2023-08-24 09:19| 55674.17| 1875994.22| 78166.42
2|2023-08-24 09:19| 90.9| 1875994.22| 78166.42
1|2023-08-24 10:19| 42754.41| 1875994.22| 78166.42
2|2023-08-24 10:19| 29127.09| 1875994.22| 78166.42
1|2023-08-24 11:19| 26417.65| 1875994.22| 78166.42
2|2023-08-24 11:19| 40354.89| 1875994.22| 78166.42
1|2023-08-24 12:19| 22895.39| 1875994.22| 78166.42
2|2023-08-24 12:19| 42468.22| 1875994.22| 78166.42
1|2023-08-24 13:19| 220.16| 1875994.22| 78166.42
2|2023-08-24 13:19| 65719.47| 1875994.22| 78166.42
1|2023-08-24 14:20| 682.19| 1875994.22| 78166.42
2|2023-08-24 14:20| 8140.3| 1875994.22| 78166.42
1|2023-08-24 15:19| 2803.07| 1875994.22| 78166.42
2|2023-08-24 15:19| 1266.33| 1875994.22| 78166.42
1|2023-08-24 16:19| 105.55| 1875994.22| 78166.42
2|2023-08-24 16:19| 1988.2| 1875994.22| 78166.42
1|2023-08-24 17:19| 93.61| 1875994.22| 78166.42
2|2023-08-24 17:19| 212.3| 1875994.22| 78166.42
1|2023-08-24 18:19| 184.95| 1875994.22| 78166.42
2|2023-08-24 18:19| 181.5| 1875994.22| 78166.42
1|2023-08-24 19:19| 64323.23| 1875994.22| 78166.42
2|2023-08-24 19:19| 59384.87| 1875994.22| 78166.42
1|2023-08-24 20:20| 207133.9| 1875994.22| 78166.42
2|2023-08-24 20:20| 170924.24| 1875994.22| 78166.42
1|2023-08-24 21:19| 146239.89| 1875994.22| 78166.42
2|2023-08-24 21:19| 167973.31| 1875994.22| 78166.42
1|2023-08-24 22:19| 115838.35| 1875994.22| 78166.42
2|2023-08-24 22:19| 124410.62| 1875994.22| 78166.42
1|2023-08-24 23:19| 52838.45| 1875994.22| 78166.42
2|2023-08-24 23:19| 80722.69| 1875994.22| 78166.42
1|2023-08-25 00:19| 133.7| 265155.35| 11048.13
2|2023-08-25 00:19| 40263.04| 265155.35| 11048.13
1|2023-08-25 01:19| 51.95| 265155.35| 11048.13
2|2023-08-25 01:19| 56701.73| 265155.35| 11048.13
1|2023-08-25 02:19| 908.3| 265155.35| 11048.13
2|2023-08-25 02:19| 114879.48| 265155.35| 11048.13
1|2023-08-25 03:19| .6| 265155.35| 11048.13
2|2023-08-25 03:19| .44| 265155.35| 11048.13
1|2023-08-25 04:19| .16| 265155.35| 11048.13
2|2023-08-25 04:19| .13| 265155.35| 11048.13
1|2023-08-25 05:19| .42| 265155.35| 11048.13
2|2023-08-25 05:19| .36| 265155.35| 11048.13
1|2023-08-25 06:19| 17.08| 265155.35| 11048.13
2|2023-08-25 06:19| .12| 265155.35| 11048.13
1|2023-08-25 07:19| .84| 265155.35| 11048.13
2|2023-08-25 07:19| .56| 265155.35| 11048.13
1|2023-08-25 08:19| 4.9| 265155.35| 11048.13
2|2023-08-25 08:19| 1.81| 265155.35| 11048.13
1|2023-08-25 09:20| 14993.34| 265155.35| 11048.13
2|2023-08-25 09:20| 37196.39| 265155.35| 11048.13
研发同事希望我帮忙查询到具体每张表产生多少归档量，我表示这个很难，我只能通过提供一些对象，结合AWR报告的语句，查找对应的表。
数据库产生redo 日志，基本上来源于DML的增、删、改这三类操作，这三类操作中，一般来说delete产生日志量更大，因为delete在进行删除操作时，是将整行记录删除，在删除前还要记录删除前的记录，以便进行回滚和恢复操作。update很多时候其实是修改的一行记录中的某个字段，相对较少。
但在和研发沟通后，他们表示，他们的数据很多是从其它地方插入过来后，然后再对整行字段基本都做update操作。
这套库因为有备库，开启了force logging,项目组希望通过在语句里使用nologging也是没法实现减少日志的产生。
我希望通过查询哪些数据块的改变结合AWR中的表来为研发提供相应的表对象。
可以通过如下SQL，结合产生归档日志量多的那段时间
SELECT TO_CHAR (BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24') SNAP_TIME,
DHSO.OBJECT_NAME,
SUM (DB_BLOCK_CHANGES_DELTA) BLOCK_CHANGED
FROM DBA_HIST_SEG_STAT DHSS,
DBA_HIST_SEG_STAT_OBJ DHSO,
DBA_HIST_SNAPSHOT DHS
WHERE DHS.SNAP_ID = DHSS.SNAP_ID
AND DHS.INSTANCE_NUMBER = DHSS.INSTANCE_NUMBER
AND DHSS.OBJ# = DHSO.OBJ#
AND DHSS.DATAOBJ# = DHSO.DATAOBJ#
AND BEGIN_INTERVAL_TIME BETWEEN TO_DATE ('2023-08-24 21:00',
'YYYY-MM-DD HH24:MI')
AND TO_DATE ('2023-08-25 03:00',
'YYYY-MM-DD HH24:MI')
GROUP BY TO_CHAR (BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24'), DHSO.OBJECT_NAME
HAVING SUM (DB_BLOCK_CHANGES_DELTA) > 0
ORDER BY SUM (DB_BLOCK_CHANGES_DELTA) DESC;
-- 查询到的结果如下
---- 根据块改编查找频繁被修改的对象
SNAP_TIME |OBJECT_NAME |BLOCK_CHANGED
-------------|--------------------------------------------------------------------------------------------------------------------------------|-------------
2023-08-24 21|XX_XXXX_COMPETE_KEYWORD_M | 329098048
2023-08-24 21|XX_XXXX_CAT_SUPP_C_D | 305831040
2023-08-24 22|XX_XXXX_CAT_SUPP_C_D | 302265088
2023-08-24 23|XX_XXXX_PROD_MSG_C_D | 222788448
2023-08-24 20|IDX_XX_XXXX_BUYER_MSG_SUPP_3 | 217657152
2023-08-24 20|IDX_BUYER_EMAIL | 216507200
2023-08-24 20|XX_XXXX_VISIT_DIS_HOUR_LM | 183289728
2023-08-24 23|XX_XXXX_CAT_SUPP_C_D | 167283296
2023-08-24 21|XX_XXXX_RELATED_KEYWORD_M | 158026976
2023-08-24 20|XX_XXXX_CAT_SUPP_C_D | 151973792
2023-08-24 20|IDX_XX_XXXX_PROD_MSG_C_D_3 | 149850016
2023-08-24 20|IDX_CCW | 146744416
2023-08-24 22|XX_XXXX_COMPETE_KEYWORD_M | 145073024
2023-08-24 22|XX_XXXX_CAT_PROD_C_D_PK | 143598976
2023-08-24 20|IDEX_CPW | 141802240
2023-08-24 21|XX_XXXX_PROD_VISIT_R_D | 136638560
2023-08-24 22|XX_XXXX_PROD_MSG_C_D | 135317600
2023-08-24 21|XX_XXXX_ALL_PROD_TOP20_D | 130186560
2023-08-24 22|XX_XXXX_CAT_PROD_C_D | 126755712
2023-08-25 02|IDX_XX_XXXX_KEYWORD_M_1 | 118489664
2023-08-24 21|IDX_XX_XXXX_PROD_MSG_C_D_3 | 118224768
2023-08-25 02|IDX_XX_XXXX_RELATED_KEYWORD_M_4 | 117911424
2023-08-25 02|IDX_XX_XXXX_RELATED_KEYWORD_M_2 | 117739072
2023-08-25 02|IDX_XX_XXXX_KEYWORD_M_2 | 116816128
2023-08-25 02|IDX_XX_XXXX_KEYWORD_M_3 | 116251584
2023-08-25 01|IDX_XX_XXXX_RELATED_KEYWORD_M_3 | 116174144
2023-08-24 20|XX_XXXX_PROD_MSG_C_D | 108694528
2023-08-24 21|XX_XXXX_CAT_PROD_C_D | 108185664
2023-08-24 22|IDX_XX_XXXX_PROD_VISIT_C_D_2 | 103447072
2023-08-24 22|XX_XXXX_USER_INF | 102804672
2023-08-24 22|IDX_XX_XXXX_PROD_MSG_C_D_3 | 102543104
2023-08-24 21|IDX_XX_XXXX_CAT_PROD_C_D_2 | 97861088
2023-08-24 21|IDX_XX_XXXX_CAT_PROD_C_D_1 | 97603040
2023-08-24 23|IDX_XX_XXXX_KEYWORD_M_2 | 93805056
2023-08-24 21|IDX_XX_XXXX_PROD_FLOW_W_2 | 91958432
2023-08-24 21|IDX_XX_XXXX_RELATED_KEYWORD_M_2 | 88445408
2023-08-24 22|IDX_XX_XXXX_RELATED_KEYWORD_M_2 | 87032640
2023-08-24 21|IDX_XX_XXXX_PROD_VISIT_C_D_2 | 85940960
2023-08-24 22|IDX_DCPKM3 | 84483072
2023-08-24 22|XX_XXXX_PROD_VISIT_C_D | 82929152
2023-08-24 22|IDX_XX_XXXX_PROD_VISIT_C_D_1 | 82460960
2023-08-24 22|IDX_XX_XXXX_PROD_VISIT_C_D_5 | 82220032
2023-08-24 22|IDX_XX_XXXX_PROD_VISIT_C_D_3 | 82094624
2023-08-24 22|IDX_XX_XXXX_PROD_VISIT_C_D_6 | 81907040
2023-08-24 22|IDX_XX_XXXX_PROD_VISIT_C_D_4 | 81537152
2023-08-25 02|IDX_XX_XXXX_RELATED_KEYWORD_M_1 | 78890400
2023-08-24 21|XX_XXXX_PROD_MSG_C_D | 74725664
2023-08-24 20|XX_XXXX_PROD_VISIT_R_D | 73649120
2023-08-24 22|IDX_XX_XXXX_KEYWORD_M_2 | 73580000
2023-08-24 20|IDX_XX_XXXX_PROD_VISIT_C_D_2 | 72155584
2023-08-24 21|XX_XXXX_PROD_VISIT_C_D | 67113728
2023-08-24 21|IDX_XX_XXXX_PROD_VISIT_C_D_5 | 65247776
2023-08-24 21|IDX_XX_XXXX_PROD_VISIT_C_D_1 | 65169568
2023-08-24 21|IDX_XX_XXXX_PROD_VISIT_C_D_3 | 65110688
2023-08-24 21|IDX_XX_XXXX_PROD_VISIT_C_D_6 | 64997472
2023-08-24 21|IDX_XX_XXXX_PROD_VISIT_C_D_4 | 64934048
2023-08-24 21|IDX_XX_XXXX_KEYWORD_M_2 | 61563648
2023-08-24 22|XX_XXXX_RELATED_KEYWORD_M | 55655072
2023-08-24 23|IDX_XX_XXXX_PROD_VISIT_C_D_2 | 55652736
2023-08-24 21|IDX_XX_XXXX_ALL_PROD_TOP20_D_3 | 53937216
2023-08-24 20|IDX_XX_XXXX_PROD_TOP20_D_2 | 53048320
2023-08-24 22|IDX_XX_XXXX_CAT_PROD_C_D_2 | 51422080
2023-08-24 22|IDX_XX_XXXX_RELATED_KEYWORD_M_1 | 49955488
2023-08-24 22|IDX_XX_XXXX_RELATED_KEYWORD_M_4 | 49470080
2023-08-24 22|IDX_XX_XXXX_RELATED_KEYWORD_M_3 | 48619136
2023-08-24 23|IDX_XX_XXXX_KEYWORD_M_3 | 47766976
2023-08-24 21|XX_XXXX_PROD_FLOW_W | 47638784
2023-08-24 21|IDX_XX_XXXX_PROD_FLOW_W_3 | 47420288
2023-08-24 21|IDX_XX_XXXX_PROD_FLOW_W_1 | 47406688
2023-08-24 23|XX_XXXX_KEYWORD_M | 47399264
2023-08-24 22|XX_XXXX_PROD_VISIT_R_D | 47318720
2023-08-24 21|IDX_XX_XXXX_PROD_FLOW_W_4 | 47161664
2023-08-24 23|IDX_XX_XXXX_KEYWORD_M_1 | 47036864
2023-08-24 21|IDX_XX_XXXX_ALL_PROD_TOP20_D_2 | 46794144
2023-08-24 20|IDX_XX_XXXX_PROD_MSG_R_D_2 | 46787264
2023-08-24 23|XX_XXXX_PROD_VISIT_C_D | 45652416
2023-08-24 23|IDX_XX_XXXX_PROD_VISIT_C_D_4 | 45374720
2023-08-24 23|IDX_XX_XXXX_PROD_VISIT_C_D_5 | 45227456
2023-08-24 23|IDX_XX_XXXX_PROD_VISIT_C_D_3 | 45166496
2023-08-24 23|IDX_XX_XXXX_PROD_VISIT_C_D_6 | 45070880
2023-08-24 23|IDX_XX_XXXX_PROD_VISIT_C_D_1 | 44861376
2023-08-24 22|IDX_XX_XXXX_PROD_MSG_C_D_2 | 44749888
2023-08-24 20|IDX_XX_XXXX_PROD_TOP20_D_3 | 44430720
2023-08-24 21|DCPVC3M | 41876288
2023-08-24 20|IDX_XX_XXXX_CAT_SUPP_R_D_3 | 41869376
2023-08-24 20|XX_XXXX_CAT_HOT_PROPVPD_D | 41309952
2023-08-24 21|IDX_XX_XXXX_RELATED_KEYWORD_M_1 | 40134432
2023-08-25 01|IDX_XX_XXXX_RELATED_KEYWORD_M_1 | 39529824
2023-08-24 21|IDX_XX_XXXX_RELATED_KEYWORD_M_4 | 39410592
然后获取了这段时间的AWR报告：
通过这一时段的AWR报告，可以看到很多是采用merge into 批量的插入数据操作。
将这些信息汇总发给研发，让他们进行排查并进行业务改造，尽量减少日志的产生。
三、附录
附上几个查询日志信息的SQL 语句：
3.1 查询一周每天总的日志切换次数
select round(avg(LOG_SWITCHES)) LOG_SWITCHES, DAY
from (select to_char(trunc(first_time), 'Day') DAY,
TRUNC(FIRST_TIME, 'DDD'),
count(*) LOG_SWITCHES
from v$log_history
group by TRUNC(FIRST_TIME, 'DDD'),
to_char(trunc(first_time), 'Day')
order by 2)
group by day;
3.2 查询一段时间每天总的日志切换次数
select round(avg(LOG_SWITCHES)) LOG_SWITCHES, DAY
from (select TRUNC(FIRST_TIME, 'DDD') DAY, count(*) LOG_SWITCHES
from v$log_history
where first_time between sysdate - 30 and sysdate
group by TRUNC(FIRST_TIME, 'DDD'),
to_char(trunc(first_time), 'Day')
order by 1)
group by day
order by 2;
或者
select a.*, round(a.count# * b.avg# / 1024 / 1024) daily_avg_mb
from (select to_char(first_time, 'yyyy-mm-dd') day,
count(1) count#,
min(recid) min#,
max(recid) max#
from v$log_history
group by to_char(first_time, 'yyyy-mm-dd')
order by 1 desc) a,
(select avg(bytes) avg#,
count(1) count#,
max(bytes) max_bytes,
min(bytes) min_bytes
from v$log) b;
3.3 查询一天每小时总的日志切换次数
select Hour, round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (select to_char(trunc(first_time, 'HH'), 'HH24') Hour,
TRUNC(FIRST_TIME, 'DDD'),
count(*) LOG_SWITCHES
from v$log_history
group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
order by 1)
group by Hour
order by Hour;
3.5 监控当前重做日志文件使用情况
set linesize 200 pagesize 1400;
select le.leseq "Current log sequence No",
100 * cp.cpodr_bno / le.lesiz "Percent Full",
(cpodr_bno - 1) * 512 "bytes used exclude header",
le.lesiz * 512 - cpodr_bno * 512 "Left space",
le.lesiz * 512 "logfile size"
from x$kcccp cp, x$kccle le
where LE.leseq = CP.cpodr_seq
and bitand(le.leflg, 24) = 8;
3.6 LogMiner挖掘日志
cd /home/oracle
sqlplus / as sysdba
exec dbms_logmnr.add_logfile('/data/arch/1_7043_992628960.dbf',dbms_logmnr.new);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
set linesize 200
set pagesize 0
spool 20191025.txt
create table log_miner_1025 as select * from v$logmnr_contents;
execute dbms_logmnr.end_logmnr;
spool off
##然后查询log_miner_1025信息
3.7 查询日志异常增高的DML操作SQL
set line 200
col sql_text1 format a20
select b.*
from (select t.sql_id,
t.module,
(select to_char(dbms_lob.substr(sql_text, 20, 1))
from sys.wrh$_sqltext
where sql_id = t.sql_id
and 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
from sys.wrh$_sqlstat t, wrm$_snapshot s
where t.dbid = s.dbid
and t.snap_id = s.snap_id
and s.begin_interval_time >=
to_date(to_char(trunc(sysdate), 'yyyy-mm-dd') || '00:10:10',
'yyyy-mm-dd hh24:mi:ss')
and s.begin_interval_time <=
to_date(to_char(trunc(sysdate), 'yyyy-mm-dd') || '09:20:20',
'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
最后修改时间：2024-04-15 21:12:53
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
