Oracle查找引起归档日志暴增的SQL语句
一、新建表，模拟数据变更
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);
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 = 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 sysdate - nvl(180,1)/1440 and PARSING_SCHEMA_NAME'SYS')
WHERE TOP_D = to_date('2023-10-23 21:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND dhs.begin_interval_time dbms_logmnr.new，其他不需要添加
SELECT 'execute dbms_logmnr.add_logfile(logfilename=>''' || name || ''', options=>dbms_logmnr.new);' as ddl
FROM v$archived_log
WHERE completion_time >= TO_DATE('2023-10-22 20:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND completion_time '/data/oracle/archivelog/1_167_1106703090.dbf', options=>dbms_logmnr.new);
--使用本地的在线数据字典分析归档日志
execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
--增加日志
execute dbms_logmnr.add_logfile(logfilename=>'/data/oracle/archivelog/1_168_1106703090.dbf');
execute dbms_logmnr.add_logfile(logfilename=>'/data/oracle/archivelog/1_169_1106703090.dbf');
execute dbms_logmnr.add_logfile(logfilename=>'/data/oracle/archivelog/1_170_1106703090.dbf');
execute dbms_logmnr.add_logfile(logfilename=>'/data/oracle/archivelog/1_171_1106703090.dbf');
execute dbms_logmnr.add_logfile(logfilename=>'/data/oracle/archivelog/1_172_1106703090.dbf');
execute dbms_logmnr.add_logfile(logfilename=>'/data/oracle/archivelog/1_173_1106703090.dbf');
execute dbms_logmnr.add_logfile(logfilename=>'/data/oracle/archivelog/1_174_1106703090.dbf');
--创建临时表记录变更信息
create table test.logmnr_contents_1023 as select * from v$logmnr_contents;
-- 最后释放pga
execute dbms_logmnr.end_logmnr;
--查询临时表找出哪个表变更的比较频繁
select to_char(TIMESTAMP,'YYYY-MM-DD HH24') TIME_1,
seg_owner,
table_name,
operation,
count(*)
from test.logmnr_contents_1023
where seg_owner not in ('SYS')
group by to_char(TIMESTAMP,'YYYY-MM-DD HH24'),seg_owner,table_name,operation
order by seg_owner,table_name,to_char(TIMESTAMP,'YYYY-MM-DD HH24'),operation;
