[統計信息系列7] Oracle 11g的自動統計信息收集_如何寫文案

※教你寫出一流的銷售文案?

銷售文案是什麼?A文案是廣告用的文字。舉凡任何宣傳、行銷、販賣商品時所用到的文字都是文案。在網路時代,文案成為行銷中最重要的宣傳方式,好的文案可節省大量宣傳資源,達成行銷目的。

 

(一)統計信息收集概述
在Oracle 11g中,默認有3個自動任務,分別是:自動統計信息收集、SQL調優顧問、段空間調整顧問,查看方法如下:

SQL> SELECT CLIENT_NAME,TASK_NAME,OPERATION_NAME,STATUS FROM dba_autotask_task;

CLIENT_NAME                      TASK_NAME                  OPERATION_NAME             STATUS
-------------------------------- -------------------------- -------------------------- --------
 sql tuning advisor               AUTO_SQL_TUNING_PROG       automatic sql tuning task  ENABLED
 auto optimizer stats collection gather_stats_prog auto optimizer stats job ENABLED
 auto space advisor               auto_space_advisor_prog    auto space advisor job     ENABLED

灰色背景行代表自動統計信息收集,使用的任務為gather_stats_prog。gather_stats_prog調用了DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存儲過程

SQL> SELECT PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION FROM dba_scheduler_programs  WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';

PROGRAM_NAME                   PROGRAM_TYPE      PROGRAM_ACTION
------------------------------ ----------------  --------------------------------------------------------------------------------
 GATHER_STATS_PROG              STORED_PROCEDURE  dbms_stats.gather_database_stats_job_proc

在Oracle 11g中,一共配置了7個自動維護窗口,每天一個窗口

SQL> SELECT WINDOW_NAME,AUTOTASK_STATUS FROM dba_autotask_window_clients  ;

WINDOW_NAME                    AUTOTASK_STATUS
------------------------------ ---------------
MONDAY_WINDOW                  ENABLED
TUESDAY_WINDOW                 ENABLED
WEDNESDAY_WINDOW               ENABLED
THURSDAY_WINDOW                ENABLED
FRIDAY_WINDOW                  ENABLED
SATURDAY_WINDOW                ENABLED
SUNDAY_WINDOW                  ENABLED

每個窗口的運行時間如下:

SQL> SELECT a.WINDOW_NAME,a.REPEAT_INTERVAL,a.duration FROM dba_scheduler_windows a WHERE ENABLED = 'TRUE';

WINDOW_NAME         REPEAT_INTERVAL                                          DURATION         
 ------------------  -------------------------------------------------------  -----------------
 MONDAY_WINDOW       freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0    +000 04:00:00
 TUESDAY_WINDOW      freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0    +000 04:00:00
 WEDNESDAY_WINDOW    freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0    +000 04:00:00
 THURSDAY_WINDOW     freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0    +000 04:00:00
 FRIDAY_WINDOW       freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0    +000 04:00:00
 SATURDAY_WINDOW     freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0     +000 20:00:00
 SUNDAY_WINDOW       freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0     +000 20:00:00

可以看到,從周一到周五,窗口運行時間為晚上22點開始,最多運行4個小時,周六周日從早上6點開始,最多運行20個小時。

在窗口任務啟動時,自動任務GATHER_STATS_PROG每次運行時會先生成ORA$AT_OS_OPT_xxx的作業,然後再執行這個作業。

SQL> SELECT a.JOB_NAME,a.ACTUAL_START_DATE,a.RUN_DURATION,a.STATUS
   2  FROM   dba_scheduler_job_run_details a
   3  WHERE  a.JOB_NAME LIKE 'ORA$AT_OS_OPT%';

JOB_NAME                ACTUAL_START_DATE                    RUN_DURATION        STATUS
---------------------   ----------------------------------   ----------------    ------------
 ORA$AT_OS_OPT_SY_1      25-MAY-20 10.00.02.042065 PM PRC     +000 00:01:24       SUCCEEDED
 ORA$AT_OS_OPT_SY_21     30-MAY-20 09.25.57.005710 AM PRC     +000 00:00:37       SUCCEEDED
 ORA$AT_OS_OPT_SY_41     30-MAY-20 01.26.30.842460 PM PRC     +000 00:00:43       SUCCEEDED
 ORA$AT_OS_OPT_SY_61     30-MAY-20 05.26.41.292037 PM PRC     +000 00:00:31       SUCCEEDED

總結:Oracle 11g自動統計信息收集是通過每天執行自動任務gather_stats_prog來實現的,它每天會自動生成ORA$AT_OS_OPT_xxx的作業,然後執行作業來收集統計信息,其本質也是執行了DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存儲過程。

 

(二)統計信息收集策略
每次自動收集統計信息,並不是對所有表都進行收集,Oracle只對那些已經統計信息失效的對象進行收集,那麼Oracle如何判斷哪些對象的統計信息失效了呢?
在Oracle 11g中,如果參數STATISTICS_LEVEL的值為TYPICAL(默認)或者ALL,則DBA_TAB_MODIFICATIONS會記錄自上次自動統計信息收集完成之後對目標表的insert、update、delete的操作影響行數,並且還會記錄自從上次自動收集統計信息之後是否發生過truncate。需要注意的是DBA_TAB_MODIFICATIONS並不會實時更新,如果需要查看最新信息,可以手動更新該表的信息:

EXEC dbms_stats.flush_database_monitoring_info();

Oracle收集失效的統計信息的策略:自上次自動統計信息收集作業完成之後,如果DBA_TAB_MODIFICATIONS中記錄的INSERT+UPDATE+DELETE所影響的行記錄之和超過了DBA_TABLES中目標表記錄數的10%,或者是自上次統計信息收集完成之後目標表執行過truncate操作,那麼Oracle會認為目標表的統計信息已經失效,自動統計信息收集作業就會對目標表重新收集統計信息。

 

(三)禁用/啟用自動統計信息收集

在某些情況下,需要禁用自動統計信息的收集,可以使用以下3種方法,每種方法禁用範圍不同。

(3.1)使用以下方法可以禁用/啟用自動統計信息收集

SQL> EXEC dbms_auto_task_admin.disable(client_name=> 'auto optimizer stats collection',operation=> NULL,window_name=> NULL);

確認是否已經關閉:

SELECT WINDOW_NAME,AUTOTASK_STATUS,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR FROM DBA_AUTOTASK_WINDOW_CLIENTS

 

如果要啟用,可以使用如下方法重新打開自動統計信息收集:

SQL> EXEC dbms_auto_task_admin.enable(client_name=> 'auto optimizer stats collection',operation=> NULL,window_name=> NULL);

再次查詢,確認已經開啟:

 

※別再煩惱如何寫文案,掌握八大原則!

什麼是銷售文案服務?A就是幫你撰寫適合的廣告文案。當您需要販售商品、宣傳活動、建立個人品牌,撰寫廣告文案都是必須的工作。

(3.2)使用DBMS_SCHEDULER.DISABLE可以禁用維護窗口,從而禁用統計信息收集
例子1:禁掉周一的自動維護作業,包括統計信息收集、段顧問、sql調優顧問

EXEC dbms_scheduler.disable(NAME=> 'SYS.MONDAY_WINDOW',FORCE=> TRUE)

結果如下:

SELECT a.WINDOW_NAME,a.enabled FROM dba_scheduler_windows a where a.window_name = 'MONDAY_WINDOW';

啟用周一的自動維護作業,包括統計信息收集、段顧問、sql調優顧問

EXEC dbms_scheduler.enable(NAME=>'SYS.MONDAY_WINDOW');

(3.3)使用DBMS_SCHEDULER.DISABLE可以禁用維護窗口中的統計信息收集
例子2:禁掉周二的自動統計信息收集,段顧問、sql調優顧問保持開啟

EXEC dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>'TUESDAY_WINDOW');

查詢結果如下:

SELECT WINDOW_NAME,AUTOTASK_STATUS,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR FROM DBA_AUTOTASK_WINDOW_CLIENTS ;

再次開啟:

EXEC dbms_auto_task_admin.enable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>'TUESDAY_WINDOW');

(四)調整自動統計信息收集
默認的統計信息如下,從周一到周五,窗口運行時間為晚上22點開始,最多運行4個小時,周六周日從早上6點開始,最多運行20個小時。

我們可以對其進行修改,修改的方法如下:
1.先禁用窗口:DBMS_SCHEDULER.DISABLE()
2.修改窗口的屬性:DBMS_SCHEDULER.SET_ATTRIBUTE()
3.啟用窗口:DBMS_SCHEDULER.ENABLE()

例子1:將周二的起始執行時間調整到23點

-- 1.禁用窗口
EXEC dbms_scheduler.disable(NAME=> 'SYS.MONDAY_WINDOW',FORCE=> TRUE)

-- 2.修改啟動時間為23點
EXEC dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW',attribute => 'REPEAT_INTERVAL',value => 'freq=daily;byday=TUE;byhour=23;byminute=0; bysecond=0');

-- 3.啟用窗口
EXEC dbms_scheduler.enable(NAME=>'SYS.MONDAY_WINDOW');

查看結果:

 

(五)列的直方圖統計信息收集方式修改
在Oracle 11g中,Oracle默認直方圖的統計信息收集方式是AUTO,即Oracle會根據負載以及列的使用情況來確定對哪些列收集直方圖信息,為了更好地利用直方圖統計信息的同時保持執行計劃的穩定,推薦對直方圖統計信息的收集策略是對已經存在直方圖的列才收集直方圖統計信息,即以REPEAT方式收集。
查看默認的直方圖收集策略:

SQL> SELECT dbms_stats.get_prefs('METHOD_OPT') FROM dual;

DBMS_STATS.GET_PREFS('METHOD_O
--------------------------------------------------------------------------------
 FOR ALL COLUMNS SIZE AUTO

修改直方圖策略:

SQL> EXEC dbms_stats.set_global_prefs(pname => 'METHOD_OPT',pvalue => 'FOR ALL COLUMNS SIZE REPEAT');
 PL/SQL procedure successfully completed

查看修改后的默認的直方圖收集策略:

SQL> SELECT dbms_stats.get_prefs('METHOD_OPT') FROM dual;

DBMS_STATS.GET_PREFS('METHOD_O
--------------------------------------------------------------------------------
 FOR ALL COLUMNS SIZE REPEAT

(六)統計信息閾值修改
在Oracle 11g中,默認統計信息的收集閾值為10%,即10%的行數據發生變化或者執行了truncate,才會再次收集統計信息。我們可以使用下面的方法針對單個表修改閾值。

例子1:修改test01表的統計信息收集閾值為5%。

查看初始的閾值:

SQL> SELECT dbms_stats.get_prefs(pname => 'STALE_PERCENT',ownname => 'LIJIAMAN',tabname => 'TEST01') FROM dual;

DBMS_STATS.GET_PREFS(PNAME=>'S
 --------------------------------------------------------------------------------
 10

修改閾值為5:

SQL> EXEC dbms_stats.set_table_prefs(ownname => 'LIJIAMAN',tabname => 'TEST01',pname   => 'STALE_PERCENT',pvalue  => 5);
 PL/SQL procedure successfully completed

確認修改后的閾值:

SQL> SELECT dbms_stats.get_prefs(pname => 'STALE_PERCENT',ownname => 'LIJIAMAN',tabname => 'TEST01') FROM dual;

DBMS_STATS.GET_PREFS(PNAME=>'S
--------------------------------------------------------------------------------
5

需要注意的是:當閾值為0時,不管數據如何變化,每天都會自動收集統計信息。

【完】

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

※廣告預算用在刀口上,台北網頁設計公司幫您達到更多曝光效益

擁有後台管理系統的網站,將擁有強大的資料管理與更新功能,幫助您隨時新增網站的內容並節省網站開發的成本。

您可能也會喜歡…