客户反映 数据库无法连接或者直接跟工程师说，因归档最近暴增，造成空间紧张，引起数据库无法使用，需协助检查归档暴增原因。
我们可以通过以下命令查看：
1.检查块改变最多的对象
redo大量产生是由于发生了大量的块改变，所以检查块改变最多的对象. 。 如下示例，是检测近一小时内容，我们可以根据需求，判断时间范围。
SQL> select * from (
select to_char(begin_interval_time,'yyyy/mm/dd hh24:mi:ss') snap_time,
dhsso.object_name,sum(db_block_changes_delta)
from dba_hist_seg_stat dhss,dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
where dhs.snap_id=dhss.snap_id
a 2 3 4 5 6 7 nd dhs.instance_number=dhss.instance_number
and dhss.obj#=dhsso.obj#
and dhss.dataobj#=dhsso.dataobj#
and begin_interval_time > sysdate-60/1440
group by to_char(begin_interval_time,'yyyy/mm/dd hh24:mi:ss'),dhsso.object_name
order by 3 desc
) wher 8 9 10 11 12 13 e rownum<=10;
SNAP_TIME OBJECT_NAME SUM(DB_BLOCK_CHANGES_DELTA)
-------------------- ------------------------------ ---------------------------
2021/08/17 14:01:00 TEST_REDO 52000
2021/08/17 14:01:00 WRH$_EVENT_HISTOGRAM_PK 96
2021/08/17 14:01:00 WRH$_SYSMETRIC_HISTORY_INDEX 96
2021/08/17 14:01:00 WRI$_ADV_PARAMETERS 80
2021/08/17 14:01:00 JOB$ 80
2021/08/17 14:01:00 WRH$_PARAMETER_PK 80
2021/08/17 14:01:00 WRM$_SNAPSHOT_DETAILS_INDEX 64
2021/08/17 14:01:00 WRM$_SNAPSHOT_DETAILS 64
2021/08/17 14:01:00 WRI$_ADV_PARAMETERS_PK 64
2021/08/17 14:01:00 SMON_SCN_TIME 48
10 rows selected.
2.根据检查对象结果，确认sql语句
col snap_time for a20
col sql_text for a100
col inst_id for 99
col execs for a6
select to_char(begin_interval_time,'yyyy/mm/dd hh24:mi:ss') snap_time,
dbms_lob.substr(sql_text,4000,1) sql_text,dhss.instance_number inst_id,dhss.sSQL> SQL> SQL> SQL> SQL> 2 ql_id,
executions_delta execs,rows_processed_delta
from dba_hist_sqlstat dhss,dba_hist_snapshot dhs,dba_hist_sqltext dhst
where upper(dhst.sql_text) like '%TEST_REDO%'
and dhss.snap_id=dhs.snap_id
and dhss.instance_number=dhs.instance_number
3 4 5 6 7 8 and dhss.sql_id=dhst.sql_id;
SNAP_TIME SQL_TEXT INST_ID SQL_ID EXECS ROWS_PROCESSED_DELTA
-------------------- ---------------------------------------------------------------------------------------------------- ------- ------------- ----- --------------------
2021/08/17 14:01:00 insert into test_redo select * from test_redo 1 5w8pb7t27c85n 3 651084
2021/08/17 14:01:00 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('paralle 1 2uduyzqnf46yg 1 1
l_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("TEST_REDO") FULL("TEST_REDO") NO_PARALLEL_INDE
X("TEST_REDO") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "MYTEST"."TEST_REDO" SAMPLE BLOCK (:"SYS_B
_4" , :"SYS_B_5") SEED (:"SYS_B_6") "TEST_REDO") SAMPLESUB
3.根据SQL_ID确认是什么程序发起的sql
SQL> col SQL_OPNAME for a20
col MACHINE for a16
col PROGRAM for a28
col SQL_EXEC_START for a20
select SQL_OPNAME,SQL_PLAN_HASH_VALUE,to_char(SQL_EXEC_START,'yyyy/mm/dd hh24:mi:ss') SQL_EXEC_START,MACHINE,PROGRAM
from v$active_session_history where sql_id='5w8pSQL> SQL> SQL> SQL> 2 b7t27c85n';
SQL_OPNAME SQL_PLAN_HASH_VALUE SQL_EXEC_START MACHINE PROGRAM
-------------------- ------------------- -------------------- ---------------- ----------------------------
INSERT 2389758278 2021/08/17 14:48:29 myasm sqlplus@myasm (TNS V1-V3)
INSERT 2389758278 2021/08/17 14:48:29 myasm sqlplus@myasm (TNS V1-V3)
INSERT 2389758278 2021/08/17 14:48:29 myasm sqlplus@myasm (TNS V1-V3)
INSERT 2389758278 2021/08/17 14:48:26 myasm sqlplus@myasm (TNS V1-V3)
INSERT 2389758278 2021/08/17 14:48:16 myasm sqlplus@myasm (TNS V1-V3)
附，环境准备
SQL> create table test_redo as select *from dba_objects;
Table created.
SQL> insert into test_redo select * from test_redo;
93012 rows created.
SQL> /
186024 rows created.
SQL> /
372048 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> /
Commit complete.
参考：
- 《收获不止sql优化》 第五章