Oracle查找引起归档日志暴增的SQL语句转载
原文：https://www.modb.pro/db/1717539062243467264
参考：https://www.cnblogs.com/lei-z/p/16467177.html
一、新建表，模拟数据变更
1、新建三个表
CREATE TABLE t1 (
id NUMBER,
name VARCHAR2(200),
info VARCHAR2(200),
row_create_time TIMESTAMP,
row_lastupdate_time TIMESTAMP
);
CREATE TABLE t2 (
id NUMBER,
name VARCHAR2(200),
info VARCHAR2(200),
row_create_time TIMESTAMP,
row_lastupdate_time TIMESTAMP
);
CREATE TABLE t3 (
id NUMBER,
name VARCHAR2(200),
info VARCHAR2(200),
row_create_time TIMESTAMP,
row_lastupdate_time TIMESTAMP
);
2、初始化数据
-- 使用循环插入数据，每10000条数据提交一次
DECLARE
commit_count NUMBER := 0;
BEGIN
FOR i IN 1..50000 LOOP
INSERT INTO t1 (id, name, info, row_create_time, row_lastupdate_time)
VALUES (i, 't1_' || i, 'info ' || i, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO t2 (id, name, info, row_create_time, row_lastupdate_time)
VALUES (i, 't2_' || i, 'info ' || i, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO t3 (id, name, info, row_create_time, row_lastupdate_time)
VALUES (i, 't3_' || i, 'info ' || i, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
commit_count := commit_count + 1;
IF commit_count = 10000 THEN
COMMIT;
commit_count := 0;
END IF;
END LOOP;
COMMIT;
END;
3、编写存储过程执行update 或者 delete
CREATE OR REPLACE PROCEDURE update_or_delete_data (
p_action VARCHAR2,
p_table_name VARCHAR2,
p_total_rows NUMBER,
p_commit_interval NUMBER
) AS
v_counter NUMBER := 0;
v_start_time TIMESTAMP := CURRENT_TIMESTAMP;
BEGIN
IF p_action = 'update' THEN
-- 执行更新操作
WHILE v_counter < p_total_rows LOOP
EXECUTE IMMEDIATE 'UPDATE ' || p_table_name || '
SET name = ''Updated Name '' || DBMS_RANDOM.VALUE,
info = ''Updated Info '' || DBMS_RANDOM.VALUE,
row_lastupdate_time = CURRENT_TIMESTAMP
WHERE id = :id' USING v_counter + 1;
v_counter := v_counter + 1;
IF MOD(v_counter, p_commit_interval) = 0 THEN
COMMIT;
END IF;
END LOOP;
ELSIF p_action = 'delete' THEN
-- 执行删除操作
WHILE v_counter < p_total_rows LOOP
EXECUTE IMMEDIATE 'DELETE FROM ' || p_table_name || '
WHERE id = :id' USING v_counter + 1;
v_counter := v_counter + 1;
IF MOD(v_counter, p_commit_interval) = 0 THEN
COMMIT;
END IF;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('Invalid action. Use "update" or "delete" as the first parameter.');
END IF;
COMMIT;
-- 计算总耗时并输出信息
DBMS_OUTPUT.PUT_LINE('Total Elapsed Time: ' || (CURRENT_TIMESTAMP - v_start_time));
DBMS_OUTPUT.PUT_LINE('Table: ' || p_table_name || ', Action: ' || p_action || ', Total Rows Changed: ' || p_total_rows);
END;
/
4、执行存储过程，模拟数据变更
-- 调用存储过程来执行更新操作，更新表t1中的10000行，每1000行提交一次
call update_or_delete_data('update', 't1', 10000, 1000);
-- 调用存储过程来执行删除操作，删除表t2中的20000行，每2000行提交一次
call update_or_delete_data('delete', 't2', 20000, 2000);
二、查询归档日志暴增的三种方法
- 根据SQL查询
- 根据AWR定位
- 根据dbms_logmnr挖掘归档日志
--第一种方法，根据SQL查询
1、查询最近三个小时的DML变更
with aa as
(SELECT IID,
USERNAME,
to_char(BEGIN_TIME,'mm/dd hh24:mi') begin_time,
SQL_ID,
decode(COMMAND_TYPE,3,'SELECT',2,'INSERT',6,'UPDATE',7,'DELETE',189,'MERGE INTO','OTH') "SQL_TYPE",
executions "EXEC_NUM",
rows_processed "Change_NUM"
FROM (SELECT s.INSTANCE_NUMBER IID,
PARSING_SCHEMA_NAME USERNAME,COMMAND_TYPE,
cast(BEGIN_INTERVAL_TIME as date) BEGIN_TIME,
s.SQL_ID,
executions_DELTA executions,
rows_processed_DELTA rows_processed,
(IOWAIT_DELTA) /
1000000 io_time,
100*ratio_to_report(rows_processed_DELTA) over(partition by s.INSTANCE_NUMBER, BEGIN_INTERVAL_TIME) RATIO,
sum(rows_processed_DELTA) over(partition by s.INSTANCE_NUMBER, BEGIN_INTERVAL_TIME) totetime,
elapsed_time_DELTA / 1000000 ETIME,
CPU_TIME_DELTA / 1000000 CPU_TIME,
(CLWAIT_DELTA+APWAIT_DELTA+CCWAIT_DELTA+PLSEXEC_TIME_DELTA+JAVEXEC_TIME_DELTA)/1000000 OTIME,
row_number() over(partition by s.INSTANCE_NUMBER,BEGIN_INTERVAL_TIME order by rows_processed_DELTA desc) TOP_D
FROM dba_hist_sqlstat s, dba_hist_snapshot sn,dba_hist_sqltext s2
where s.snap_id = sn.snap_id
and s.INSTANCE_NUMBER = sn.INSTANCE_NUMBER
and rows_processed_DELTA is not null
and s.sql_id = s2.sql_id and COMMAND_TYPE in (2,6,7,189)
and sn.BEGIN_INTERVAL_TIME > sysdate - nvl(180,1)/1440 and PARSING_SCHEMA_NAME<>'SYS')
WHERE TOP_D <= nvl(20,1)
)
select aa.*,s.sql_fulltext "FULL_SQL" from aa left join v$sql s on aa.sql_id=s.sql_id ORDER BY IID, BEGIN_TIME desc,"Change_NUM" desc;
--如果sql_id从共享缓存清除，可以从dba_hist_sqltext表查询完整的SQL文本信息
select * from dba_hist_sqltext where sql_id='baak64bs12085';
2、查询指定时间段的DML变更
with aa as
(SELECT IID,
USERNAME,
to_char(BEGIN_TIME,'mm/dd hh24:mi') begin_time,
SQL_ID,
decode(COMMAND_TYPE,3,'SELECT',2,'INSERT',6,'UPDATE',7,'DELETE',189,'MERGE INTO','OTH') "SQL_TYPE",
executions "EXEC_NUM",
rows_processed "Change_NUM"
FROM (SELECT s.INSTANCE_NUMBER IID,
PARSING_SCHEMA_NAME USERNAME,COMMAND_TYPE,
cast(BEGIN_INTERVAL_TIME as date) BEGIN_TIME,
s.SQL_ID,
executions_DELTA executions,
rows_processed_DELTA rows_processed,
(IOWAIT_DELTA) /
1000000 io_time,
100*ratio_to_report(rows_processed_DELTA) over(partition by s.INSTANCE_NUMBER, BEGIN_INTERVAL_TIME) RATIO,
sum(rows_processed_DELTA) over(partition by s.INSTANCE_NUMBER, BEGIN_INTERVAL_TIME) totetime,
elapsed_time_DELTA / 1000000 ETIME,
CPU_TIME_DELTA / 1000000 CPU_TIME,
(CLWAIT_DELTA+APWAIT_DELTA+CCWAIT_DELTA+PLSEXEC_TIME_DELTA+JAVEXEC_TIME_DELTA)/1000000 OTIME,
row_number() over(partition by s.INSTANCE_NUMBER,BEGIN_INTERVAL_TIME order by rows_processed_DELTA desc) TOP_D
FROM dba_hist_sqlstat s, dba_hist_snapshot sn,dba_hist_sqltext s2
where s.snap_id = sn.snap_id
and s.INSTANCE_NUMBER = sn.INSTANCE_NUMBER
and rows_processed_DELTA is not null
and s.sql_id = s2.sql_id and COMMAND_TYPE in (2,6,7,189)
and sn.BEGIN_INTERVAL_TIME >= TO_DATE('2023-10-23 21:00:00', 'YYYY-MM-DD HH24:MI:SS')
and sn.BEGIN_INTERVAL_TIME < TO_DATE('2023-10-23 22:30:00', 'YYYY-MM-DD HH24:MI:SS')
and PARSING_SCHEMA_NAME<>'SYS')
WHERE TOP_D <= nvl(20,1)
)
select aa.*,s.sql_fulltext "FULL_SQL" from aa left join v$sql s on aa.sql_id=s.sql_id ORDER BY IID, BEGIN_TIME desc,"Change_NUM" desc;
3、查询dml变更示例：
with aa as
(SELECT IID,
USERNAME,
to_char(BEGIN_TIME,'mm/dd hh24:mi') begin_time,
SQL_ID,
decode(COMMAND_TYPE,3,'SELECT',2,'INSERT',6,'UPDATE',7,'DELETE',189,'MERGE INTO','OTH') "SQL_TYPE",
executions "EXEC_NUM",
rows_processed "Change_NUM"
FROM (SELECT s.INSTANCE_NUMBER IID,
PARSING_SCHEMA_NAME USERNAME,COMMAND_TYPE,
cast(BEGIN_INTERVAL_TIME as date) BEGIN_TIME,
s.SQL_ID,
executions_DELTA executions,
rows_processed_DELTA rows_processed,
(IOWAIT_DELTA) /
1000000 io_time,
100*ratio_to_report(rows_processed_DELTA) over(partition by s.INSTANCE_NUMBER, BEGIN_INTERVAL_TIME) RATIO,
sum(rows_processed_DELTA) over(partition by s.INSTANCE_NUMBER, BEGIN_INTERVAL_TIME) totetime,
elapsed_time_DELTA / 1000000 ETIME,
CPU_TIME_DELTA / 1000000 CPU_TIME,
(CLWAIT_DELTA+APWAIT_DELTA+CCWAIT_DELTA+PLSEXEC_TIME_DELTA+JAVEXEC_TIME_DELTA)/1000000 OTIME,
row_number() over(partition by s.INSTANCE_NUMBER,BEGIN_INTERVAL_TIME order by rows_processed_DELTA desc) TOP_D
FROM dba_hist_sqlstat s, dba_hist_snapshot sn,dba_hist_sqltext s2
where s.snap_id = sn.snap_id
and s.INSTANCE_NUMBER = sn.INSTANCE_NUMBER
and rows_processed_DELTA is not null
and s.sql_id = s2.sql_id and COMMAND_TYPE in (2,6,7,189)
and sn.BEGIN_INTERVAL_TIME > sysdate - nvl(180,1)/1440 and PARSING_SCHEMA_NAME<>'SYS')
WHERE TOP_D <= nvl(20,1)
)
7 8 9 10 11 12 13 14 15 16 17 18 19