Oracle10g 自動統計資訊(dbms_stats)

 
 
V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.
==================================================================
session 1
SQL>alter system set  statistics_level=basic;
SQL>select count(*) from dba_objects,dba_objects;
 
session 2
SQL>select cpu_time,elapsed_time,disk_reads from v$sql
where sql_text='select count(*) from dba_objects,dba_objects ';
cpu_time and elapsed_time are updated every 5 seconds
==================================================================
session 1
SQL>alter system set  statistics_level=TYPICAL;
SQL>select count(*) from dba_objects,dba_objects;
 
session 2
SQL>select cpu_time,elapsed_time,disk_reads from v$sql
where sql_text='select count(*) from dba_objects,dba_objects ';
cpu_time and elapsed_time are updated immediately.
 
reference:

 
Oracle10g 自動統計資訊
http://hi.baidu.com/edeed/blog/item/5280b7454c0cc221cffca395.html
Oracle 10g 版本最後不再支援（而不是反對）基於規則的最佳化器 (RBO)。因為 CBO 依靠準確的（或者說
比較準確的）統計資訊來產生最佳化的執行路徑，所以 DBA 需要確保定期收集統計資訊，建立另一個執行核
對清單。在 10g 之前，這一過程可能由於多種原因而徒勞無功。在 10g 中已經消除了很多這類的顧慮，
它能夠自動收集統計資訊。在 Oracle9i 中，您可以透過開啟表監視選項 (ALTER TABLE ...MONITORING)
然後檢查這些表的檢視 DBA_TAB_MODIFICATIONS，檢查表中的資料是否已明顯更改過。
在 10g 中，已不再使用 MONITORING 語句了。而透過設定初始化引數 STATISTIC_LEVEL 為 TYPICAL 或
ALL，就可以自動收集統計資訊。（預設值為 TYPICAL，因此可以隨即啟用自動收集統計資訊的功
能。）Oracle 資料庫 10g 具有一個預定義的排程程式作業，名稱為 GATHER_STATS_JOB，它由
STATISTIC_LEVEL 引數的適當數值所啟用。
SQL> show parameter statistics_
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
statistics_level                     string      TYPICAL
statistics_level 預設是typical，在10g中表監控是啟用的，強烈建議在10g中此引數的值是typical。如
果STATISTICS_LEVEL設定為basic，不僅不能監控表，而且將禁掉如下一些10g的新功能：
ASH(Active Session History)
ASSM(Automatic Shared Memory Management)
AWR(Automatic Workload Repository)
ADDM(Automatic Database Diagnostic Monitor)
統計資訊的收集是資源相當密集的工作，因此您可能希望確保它不影響資料庫的正常操作。在 10g 中，您
可以使這一工作自動完成：可對一個名為 AUTO_TASK_CONSUMER_GROUP 的特定資源使用者組進行預定義，用
於自動執行一些任務，比如收集統計資訊。該使用者組確保這些統計資訊收集作業的優先權低於預設使用者組
，因此減少或消除了自動化任務佔用整個機器的風險。
這個自動任務預設情況下在工作日晚上10:00 - 6:00 和週末全天開啟。呼叫
DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC 收集統計資訊。該過程首先檢測統計資訊缺失和陳舊的對
象。然後確定優先順序，再開始進行統計資訊。
可以透過以下查詢這個JOB的執行情況：
SQL> set lines 256
SQL> col last_start_date format a40
SQL> col last_run_duration format a30
SQL> SELECT owner, enabled, auto_drop, restartable, state, run_count, failure_count,
last_start_date, last_run_duration FROM dba_scheduler_jobs where job_name =
'GATHER_STATS_JOB';
OWNER                          ENABL AUTO_ RESTA STATE            RUN_COUNT FAILURE_COUNT
LAST_START_DATE                          LAST_RUN_DURATION
------------------------------ ----- ----- ----- --------------- ---------- ------------- --
-------------------------------------- ------------------------------
SYS                            TRUE FALSE TRUE SCHEDULED               57             0
27-NOV-08 10.00.02.228110 PM +08:00      +000000000 00:00:25.870394
如果希望將引數 STATISTIC_LEVEL 設為 TYPICAL 卻不希望自動收集統計資訊時該怎麼辦？很簡單。只需
使用以下語句來禁用排程程式作業即可：
SQL> exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
那麼您為什麼要這樣做呢？有很多原因 — 其中一個原因是，雖然表中大部分行發生了變化，但分佈情況可
能沒有改變，這在資料倉儲中很常見。在這種情況下，您不希望再次收集統計資訊，而只是希望重用原來
的統計資訊。另一個原因可能是您正在使用分割槽交換功能來重新整理物化檢視 (MV)，並且不希望收集關於物化
檢視的統計資訊，因為關於被交換表的統計資訊也會被匯入。但是，您也可以將特定的表排除在自動統
資訊收集作業之外，而不需要禁止整個作業。
在最佳化器收集統計資訊時可能出現的複雜情況之一是執行計劃的改變 — 也就是說，原來的最佳化方法在收集
統計資訊之前一直工作良好，但是在此之後，由於新收集的統計資訊產生了不良計劃，導致查詢突然出錯
。這種問題並不少見。
為避免這種災難，統計資訊的收集作業在收集新資訊之前儲存當前的統計資訊。如果出現問題，您總可以
返回到原有的統計資訊，或者至少可以檢查二者之間的不同之處，以便於解決問題。
例如，假設在 5 月 31 日晚上 10:00 開始執行表 REVENUE 上的統計資訊收集作業，而隨後查詢的效能變
差。Oracle 儲存了原有的統計資訊，您可以透過執行以下命令重新獲取這些資訊：
SQL> exec dbms_stats.restore_table_stats ( 'ARUP', 'REVENUE', '31-MAY-04 10.00.00.000000000
PM -04:00');
此命令恢復到 5 月 31 日晚上 10:00 為止的統計資訊,時間資訊是以 TIMESTAMP 資料型別提供。這樣您
就立即還原了由新的統計資訊收集程式所作的更改。
您能夠恢復的時間長度是由保留引數所決定的。要檢視當前的保留引數，可使用以下查詢：
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
                         31
在本示例中表示可以儲存相當於 31 天的統計資訊，但並不能予以保證。要了解統計資訊所覆蓋到的確切
時間和日期，只需使用以下查詢：
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
----------------------------------------------------------------
27-10月-08 11.14.20.357622000 下午 +08:00
該查詢表明可用的最陳舊統計資訊日期為 10 月 27 日下午 11:14。
您可以透過執行內建的函式將保留時間設為不同的值。例如，要將其設為 45 天，可使用：
SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45);