Oracle 10g 11g 自動分析表格 自動統計資訊收集(Automated Maintenance task)

 說到統計資訊 , 各位應該先了解Oracle CBO的機制 ( cost base optimizer ) , 在SQL語法被執行的時候，是會在Oracle內部轉換成byte code, 並結合SQL執行計畫決定如何做query , 以及是否走full table scan , 或是走某個索引等...


SQL語法的執行其實都是由Oracle CBO 去控制 , 大致上來說 , CBO 是透過表格跟索引的統計資訊而決定SQL Plan的結果，
如果錯誤的統計資訊會影響SQL語法的反應時間.

PS 為維持SQL 效能 , Oracle 10g 以後 , 資料庫便會自動收集統計資訊 ( statistics )。

在Oracle 10g以後就有自動進行收集統計資訊的排程作業了 ,

v 10g中的schedule jobs 叫作 GATHER_STATS_JOB
v 11g中的schedule jobs 叫作 BSLN_MAINTAIN_STATS_JOB

為何會有自動收集統計資訊這東東.是因為在Oracle SQL CBO下(cost based optimizer) , 如果主動分析表格跟索引數
會有較佳的SQL效能. CPU 不必要的耗損也較少.  如果統計資訊是錯的 有可能造成原本該走的索引 沒有走. 或是該走索引卻走full table scan。

另外需注意的是, 資料的自動分析時間窗口(Time Window) , 是否合乎商轉需求 , 也需納入考量!

10g的資料自動維護時間:
在周一到周五的每天晚上十點到隔天六點(Mon - Fri.)
                             
星期六日的持續48 hr (周六凌晨開始，只initial 一次)

--update at 2015 10/4
                           
11g的資料自動維護時間:
在周一到周五的每天晚上10點開始,持續4小時,到隔天早上2點.
周六/日則在早上6點開始,持續20小時,到禮拜天的早上2點.

--
SQL> select client_name,status from Dba_Autotask_Client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

可透過以下view 查詢

SQL>

col ACTUAL_START_DATE for a35
col RUN_DURATION for a13
select ACTUAL_START_DATE, RUN_DURATION, SLAVE_PID from dba_scheduler_job_run_details where job_name = 'BSLN_MAINTAIN_STATS_JOB' order by 1;



ACTUAL_START_DATE                   RUN_DURATION  SLAVE_PID
----------------------------------- ------------- ------------------------------
11-DEC-11 12.00.00.044373 AM +08:00 +000 00:00:00 7032
18-DEC-11 08.06.07.576474 AM +08:00 +000 00:00:10 11019
25-DEC-11 12.00.00.624231 AM +08:00 +000 00:00:00 29807
01-JAN-12 12.00.00.833099 AM +08:00 +000 00:00:12 27391

For Oracle 11g 中 , 

如果表格太大，可自行定義資料變動率到達多少 % 自動分析:

設定:SQL>  exec dbms_stats.set_table_prefs(null,'EMP','STALE_PERCENT',1);
修改為 1％. 範圍從 1-100. 
恢復:SQL> exec dbms_stats.set_table_prefs(null,'EMP','STALE_PERCENT',null);
查詢:SQL>  select dbms_stats.get_prefs('STALE_PERCENT',null,'EMP') from dual;

如何手動分析資料庫:

--分析整個資料庫

begin
dbms_stats.gather_database_stats(options=> 'GATHER AUTO');
end;

--分析特定Schema User
begin
dbms_stats.gather_schema_stats( ownname=> '"USERNAME"' , options=> 'GATHER AUTO');
end;

Update 2016.01.20

如何設定incremental analyze table(11g+以後的feature), 可以縮短分析表格的時間

ref: http://structureddata.org/2008/07/16/oracle-11g-incremental-global-statistics-on-partitioned-tables/

Turning On Incremental Global Stats
SQL> exec DBMS_STATS.SET_TABLE_PREFS(user,'FOO','INCREMENTAL','TRUE')

-- To see the value for INCREMENTAL for a given table:

SQL> select dbms_stats.get_prefs('INCREMENTAL', tabname=>'FOO') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL',TABNAME=>'FOO')
--------------------------------------------------
TRUE