虽然AWR功能已经完全可以替代STATSPACK的功能，但是目前存在的9i的数据库还有不少，而且却是发现有不少人不了解如何更改STATSPACK的级别。
STATSPACK默认是LEVEL 5收集，而LEVEL 5收集的数据是不包括执行计划的，因此对于关注SQL性能的情况，至少需要调整到LEVEL 6，下面是检查和调整STATSPACK收集等级的方法，比较简单，就不多解释了：
SQL> EXEC statspack.snap PL/SQL PROCEDURE successfully completed. SQL> SELECT snap_id, dbid, snap_level FROM stats$snapshot; SNAP_ID DBID SNAP_LEVEL ---------- ---------- ---------- 1 1258229964 5 1 ROW selected. SQL> EXEC statspack.snap(i_snap_level => 6) PL/SQL PROCEDURE successfully completed. SQL> SELECT snap_id, dbid, snap_level FROM stats$snapshot; SNAP_ID DBID SNAP_LEVEL ---------- ---------- ---------- 1 1258229964 5 2 1258229964 6 2 ROWS selected. SQL> EXEC statspack.snap PL/SQL PROCEDURE successfully completed. SQL> SELECT snap_id, dbid, snap_level FROM stats$snapshot; SNAP_ID DBID SNAP_LEVEL ---------- ---------- ---------- 1 1258229964 5 2 1258229964 6 3 1258229964 5 3 ROWS selected. SQL> EXEC statspack.snap(i_snap_level => 6, i_modify_parameter => 'true') PL/SQL PROCEDURE successfully completed. SQL> SELECT snap_id, dbid, snap_level FROM stats$snapshot; SNAP_ID DBID SNAP_LEVEL ---------- ---------- ---------- 1 1258229964 5 2 1258229964 6 3 1258229964 5 4 1258229964 6 4 ROWS selected. SQL> EXEC statspack.snap PL/SQL PROCEDURE successfully completed. SQL> SELECT snap_id, dbid, snap_level FROM stats$snapshot; SNAP_ID DBID SNAP_LEVEL ---------- ---------- ---------- 1 1258229964 5 2 1258229964 6 3 1258229964 5 4 1258229964 6 5 1258229964 6 5 ROWS selected. SQL> EXEC statspack.modify_statspack_parameter(i_snap_level => 10) PL/SQL PROCEDURE successfully completed. SQL> EXEC statspack.snap PL/SQL PROCEDURE successfully completed. SQL> SELECT snap_id, dbid, snap_level FROM stats$snapshot; SNAP_ID DBID SNAP_LEVEL ---------- ---------- ---------- 1 1258229964 5 2 1258229964 6 3 1258229964 5 4 1258229964 6 5 1258229964 6 6 1258229964 10 6 ROWS selected. |