【statspack級別設定】

sub8412發表於2012-11-15
我們根據統計資訊的詳細程度可以設定不同的級別，每種級別見STATS$LEVEL_DESCRIPTION ;
SQL> col DESCRIPTION for a60
SQL> select * from STATS$LEVEL_DESCRIPTION;

SNAP_LEVEL DESCRIPTION
---------- ------------------------------------------------------------
0 This level captures general statistics, including rollback s
  egment, row cache, SGA, system events, background events, se
  ssion events, system statistics, wait statistics, lock stati
  stics, and Latch information

5 This level includes capturing high resource usage SQL Statem
  ents, along with all data captured by lower levels

6 This level includes capturing SQL plan and SQL plan usage in
  formation for high resource usage SQL Statements, along with
   all data captured by lower levels

7 This level captures segment level statistics, including logi
  cal and physical reads, row lock, itl and buffer busy waits,
   along with all data captured by lower levels

10 This level includes capturing Child Latch statistics, along
  with all data captured by lower levels

0： 一性效能統計:包含回退段狀態、字典快取、SGA、系統事件、後臺事件、會話事件、系統統計、等待統計、鎖統計、閂鎖統計。

5： 增加了收集SQL的資訊、幷包括0級收集的資訊。

6： 增強了在SQL收集資訊方面的功能(列出佔用資源較高的SQL),幷包所有低階別的資訊。 

7： 增加了收集段級別的統計資訊(如段的邏輯讀與物理讀、行鎖、ITL及buffer busy waits), 幷包括所有低階別的資訊。  

10：增加了收集子LATCH鎖的資訊,幷包括所有低階別的資訊。  

　　如果你使用statspack確定熱表及熱索引,那就需要使用7/10的級別來收集快照。 預設是5級。

我們可以手工修改這個級別： 

永久修改收集級別
SQL> EXEC STATSPACK.MODIFY_STATSPACK_PARAMETER(I_SNAP_LEVEL=>10,I_MODIFY_PARAMETER=>'TRUE');

PL/SQL procedure successfully completed.

臨時修改 
SQL> EXECUTE STATSPACK.SNAP(I_SNAP_LEVEL=>10);

PL/SQL procedure successfully completed.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20553601/viewspace-749268/，如需轉載，請註明出處，否則將追究法律責任。

相關文章