客戶反映 資料庫無法連線或者直接跟工程師說，因歸檔最近暴增，造成空間緊張，引起資料庫無法使用，需協助檢查歸檔暴增原因。

我們可以透過以下命令檢視：

#### 1.檢查塊改變最多的物件

redo大量產生是由於發生了大量的塊改變，所以檢查塊改變最多的物件. 。 如下示例，是檢測近一小時內容，我們可以根據需求，判斷時間範圍。
``` sql
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語句
``` 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
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
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最佳化》 第五章

【SQL】Oracle 歸檔日誌暴增原因分析

相關文章