环境
首先讨论11g的环境
知识点
1.snapshot自动采样的开启和关闭
开启exec dbms_workload_repository.modify_snapshot_settings(interval => 30);
关闭exec dbms_workload_repository.modify_snapshot_settings(interval => 0);
2.exec dbms_workload_repository.modify_snapshot_settings(interval => );snapshot采样间隔最小设置为10分钟
3.exec dbms_workload_repository.modify_snapshot_settings(retention => );snapshot采样保留时间最小设置为8天
snapshot信息视图
1.dba_hist_snapshot snapshot详细细信息
2.dba_hist_wr_control snapshot采样设置信息
这两个视图长得就是这个样子:
1.dba_hist_snapshot
SQL> set wrap off
set linesize 1000
set pagesize 1000
column startup_time for a30
column begin_interval for a30
select to_char(startup_time,'yyyy-mm-dd hh24:mi:ss') as "startup_time",to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') as "begin_interval",snap_id,snap_level,instance_number from dba_hist_snapshot t where instance_number=1 order by t.snap_id,t.instance_number;
SQL> SQL> SQL> SQL> SQL>
startup_time begin_interval SNAP_ID SNAP_LEVEL INSTANCE_NUMBER
------------------------------ ------------------------------ ---------- ---------- ---------------
2022-09-28 17:49:57 2022-09-28 17:49:57 21 1 1
2022-09-28 17:49:57 2022-09-28 18:01:03 22