awr收集snapshot时的级别

和statspack相比awr收集快照的级别只有2种[@more@]

SQL> select distinct snap_level from dba_hist_snapshot;

SNAP_LEVEL
----------
1
2

2 rows selected.

SQL> show parameter statistics_level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string ALL
SQL>

其中dba_hist_snapshot中字段snap_level=1表示该快照要么是在参数statistics_level=typical自动生成的，要么是用下面命令：

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select snap_id, snap_level from dba_hist_snapshot where snap_id=(select max(snap_id) from dba_hist_snapshot);

SNAP_ID SNAP_LEVEL
---------- ----------
94 1

SQL> show parameter statistics_level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string ALL
SQL>

--==============================

执行exec dbms_workload_repository.create_snapshot;时由于过程create_snapshot的参数flush_level的default值是TYPICAL,所以如果不加参数，那么不管参数statistics_level的值是否是ALL,生成的snapshot对应的SNAP_LEVEL都是1；如果想让oracle自动搜集的snapshot的SNAP_LEVEL是2，那么可以修改参数statistics_level为all或者手动执行下面命令：

SQL> exec dbms_workload_repository.create_snapshot(flush_level=>'ALL');

PL/SQL procedure successfully completed.

SQL> select snap_id, snap_level from dba_hist_snapshot where snap_id=(select max(snap_id) from dba_hist_snapshot);

SNAP_ID SNAP_LEVEL
---------- ----------
95 2

SQL>

注意参数flush_level对应的参数值TYPICAL和ALL都要求大写。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1047782/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1047782/