11G 中的V$ACTIVE_SESSION_HISTORY 檢視沒有資料

kewin發表於2012-02-14
Session
ASH 試圖沒有資料
2012-2-14
Kevin Zou
在一個11G的環境中，發現V$ACTIVE_SESSION_HISTORY沒有任何資料。
SQL> select * from v$version where rownum < 2;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL> select count(*) from v$active_session_history;

  COUNT(*)
----------
         0
SQL> show parameter control_management_pack_access 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      NONE
SQL> show parameter  statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
statistics_level 引數已經設定為TYPICAL，怎麼還是沒有收集到SESSION的活動資訊呢？
原來開啟會話的收集，除了大家知道的引數外，還有一個新的引數CONTROL_MANAGEMENT_PACK_ACCESS 。它的值可以為NONE | DIAGNOSTIC | DIAGNOSTIC+TUNING，
預設值為DIAGNOSTIC+TUNING。
每個數值代表的意義：
The DIAGNOSTIC pack includes AWR, ADDM, and so on.
The TUNING pack includes SQL Tuning Advisor, SQLAccess Advisor, and so on.
A license for DIAGNOSTIC is required for enabling the TUNING pack.

設定該引數為DIAGNOSTIC+TUNING，看下效果：
SQL> select count(*) from test;

  COUNT(*)
----------
     63595

Elapsed: 00:00:00.16
SQL> select count(*) from v$active_session_history;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.00
SQL>
SQL> alter system set control_management_pack_access="DIAGNOSTIC+TUNING" scope=s
pfile;

System altered.

Elapsed: 00:00:00.01
SQL> startup force
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size                  2179856 bytes
Variable Size            1845497072 bytes
Database Buffers         1275068416 bytes
Redo Buffers               17281024 bytes
Database mounted.
Database opened.
SQL> select count(*) from  v$actice_session_history;
select count(*) from  v$actice_session_history
                      *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.05
SQL> select count(*) from  v$active_session_history;

  COUNT(*)
----------
        30

Elapsed: 00:00:00.06

-THE END-

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

相關文章