客户反映 数据库无法连接或者直接跟工程师说，因归档最近暴增，造成空间紧张，引起数据库无法使用，需协助检查归档暴增原因。 我们可以通过以下命令查看： #### 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优化》 第五章