隨筆-86  評論-33  文章-0  trackbacks-0
          dbms_stats能良好地估計統計數據(尤其是針對較大的分區表),并能獲得更好的統計結果,最終制定出速度更快的SQL執行計劃。
          exec dbms_stats.gather_schema_stats(
          ownname          
          => 'SCOTT',
          options          
          => 'GATHER AUTO',
          estimate_percent 
          => dbms_stats.auto_sample_size,
          method_opt       
          => 'for all columns size repeat',
          degree           
          => 15
          )
                 為了充分認識dbms_stats的好處,需要仔細體會每一條主要的預編譯指令(directive)。下面讓我們研究每一條指令,并體會如何用它為基于代價的SQL優化器收集最高質量的統計數據。
          options參數

          使用4個預設的方法之一,這個選項能控制Oracle統計的刷新方式:

          gather——重新分析整個架構(Schema)。
          gather empty——只分析目前還沒有統計的表。
          gather stale——只重新分析修改量超過10%的表(這些修改包括插入、更新和刪除)。
          gather auto——重新分析當前沒有統計的對象,以及統計數據過期(變臟)的對象。注意,使用gather auto類似于組合使用gather stale和gather empty。
                 注意,無論gather stale還是gather auto,都要求進行監視。如果你執行一個alter table xxx monitoring命令,Oracle會用dba_tab_modifications視圖來跟蹤發生變動的表。這樣一來,你就確切地知道,自從上一次分析統計數據以來,發生了多少次插入、更新和刪除操作。

          estimate_percent選項

          estimate_percent參數是一種比較新的設計,它允許Oracle的dbms_stats在收集統計數據時,自動估計要采樣的一個segment的最佳百分比:
          estimate_percent => dbms_stats.auto_sample_size

                 要驗證自動統計采樣的準確性,你可檢視dba_tables sample_size列。一個有趣的地方是,在使用自動采樣時,Oracle會為一個樣本尺寸選擇5到20的百分比。記住,統計數據質量越好,CBO做出的決定越好。

          method_opt選項
          method_opt:for table --只統計表 
                                for all indexed columns --只統計有索引的表列 
                                for all indexes --只分析統計相關索引 
                                for all columns

          dbms_stats的method_opt參數尤其適合在表和索引數據發生變化時刷新統計數據。method_opt參數也適合用于判斷哪些列需要直方圖(histograms)。

                某些情況下,索引內的各個值的分布會影響CBO是使用一個索引還是執行一次全表掃描的決策。例如,假如在where子句中指定的值的數量不對稱,全表掃描就顯得比索引訪問更經濟。

                 如果你有一個高度傾斜的索引(某些值的行數不對稱),就可創建Oracle直方圖統計。但在現實世界中,出現這種情況的機率相當小。使用CBO時,最常見的錯誤之一就是在CBO統計中不必要地引入直方圖。根據經驗,只有在列值要求必須修改執行計劃時,才應使用直方圖。

          為了智能地生成直方圖,Oracle為dbms_stats準備了method_opt參數。在method_opt子句中,還有一些重要的新選項,包括skewonly,repeat和auto:
          method_opt=>'for all columns size skewonly'
          method_opt=>'for all columns size repeat'
          method_opt=>'for all columns size auto'

               skewonly選項會耗費大量處理時間,因為它要檢查每個索引中的每個列的值的分布情況。

               假如dbms_stat發現一個索引的各個列分布得不均勻,就會為那個索引創建直方圖,幫助基于代價的SQL優化器決定是進行索引訪問,還是進行全表掃描訪問。例如,在一個索引中,假定有一個列在50%的行中,如清單B所示,那么為了檢索這些行,全表掃描的速度會快于索引掃描。

          --*************************************************************
          --
           SKEWONLY option—Detailed analysis
          --
          --
           Use this method for a first-time analysis for skewed indexes
          --
           This runs a long time because all indexes are examined
          --
          *************************************************************
           
          begin
            dbms_stats.gather_schema_stats(
               ownname          
          => 'SCOTT',
               estimate_percent 
          => dbms_stats.auto_sample_size,
               method_opt       
          => 'for all columns size skewonly',
                degree           
          => 7
             );
          end;


                  重新分析統計數據時,使用repeat選項,重新分析任務所消耗的資源就會少一些。使用repeat選項(清單C)時,只會為現有的直方圖重新分析索引,不再搜索其他直方圖機會。定期重新分析統計數據時,你應該采取這種方式。

          --**************************************************************
          --
           REPEAT OPTION - Only reanalyze histograms for indexes
          --
           that have histograms
          --
          --
           Following the initial analysis, the weekly analysis
          --
           job will use the “repeat” option. The repeat option
          --
           tells dbms_stats that no indexes have changed, and
          --
           it will only reanalyze histograms for
          --
           indexes that have histograms.
          --
          **************************************************************
          begin
             dbms_stats.gather_schema_stats(
                ownname          
          => 'SCOTT',
                estimate_percent 
          => dbms_stats.auto_sample_size,
                method_opt       
          => 'for all columns size repeat',
                degree           
          => 7
             );
          end;

                 使用alter table xxx monitoring;命令來實現Oracle表監視時,需要使用dbms_stats中的auto選項。如清單D所示,auto選項根據數據分布以及應用程序訪問列的方式(例如通過監視而確定的一個列的工作量)來創建直方圖。使用method_opt=>’auto’類似于在dbms_stats的option參數中使用gather auto。

          begin
            dbms_stats.gather_schema_stats(
               ownname          
          => 'SCOTT',
                estimate_percent 
          => dbms_stats.auto_sample_size,
                method_opt       
          => 'for all columns size auto',
                degree           
          => 7
             );
          end;


          并行統計收集degree參數

          Oracle推薦設置DBMS_STATS的DEGREE參數為DBMS_STATS.AUTO_DEGREE,該參數允許Oracle根據對象的大小和并行性初始化參數的設置選擇恰當的并行度。
          聚簇索引,域索引,位圖連接索引不能并行收集。

           

          如何使用dbms_stats分析統計信息?
          --創建統計信息歷史保留表

          sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ; 


          --導出整個scheme的統計信息

          sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ; 


          --分析scheme

          Exec dbms_stats.gather_schema_stats( 
          ownname 
          => 'scott'
          options 
          => 'GATHER AUTO'
          estimate_percent 
          => dbms_stats.auto_sample_size, 
          method_opt 
          => 'for all indexed columns '
          degree 
          => 6 ) 


          --分析表

          sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ; 


          --分析索引

          SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;


          --如果發現執行計劃走錯,刪除表的統計信息

          SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;


          --導入表的歷史統計信息

          sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ; 


          --如果進行分析后,大部分表的執行計劃都走錯,需要導回整個scheme的統計信息

          sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');


          --導入索引的統計信息

          SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table'


          --檢查是否導入成功

          SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST'


          分析數據庫(包括所有的用戶對象和系統對象):gather_database_stats
          分析用戶所有的對象(包括表、索引、簇):gather_schema_stats
          分析表:gather_table_stats
          分析索引:gather_index_stats
          刪除數據庫統計信息:delete_database_stats
          刪除用戶方案統計信息:delete_schema_stats
          刪除表統計信息:delete_table_stats
          刪除索引統計信息:delete_index_stats
          刪除列統計信息:delete_column_stats
          設置表統計信息:set_table_stats
          設置索引統計信息:set_index_stats
          設置列統計信息:set_column_stats



          從Oracle Database 10g開始,Oracle在建庫后就默認創建了一個名為GATHER_STATS_JOB的定時任務,用于自動收集CBO的統計信息。

          這個自動任務默認情況下在工作日晚上10:00-6:00和周末全天開啟。調用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統計信息。
          該過程首先檢測統計信息缺失和陳舊的對象。然后確定優先級,再開始進行統計信息。

          可以通過以下查詢這個JOB的運行情況:

          select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

          其實同在10點運行的Job還有一個AUTO_SPACE_ADVISOR_JOB:

           

          SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;

          JOB_NAME                      LAST_START_DATE
          ------------------------------ ----------------------------------------
          AUTO_SPACE_ADVISOR_JOB        04-DEC-07 10.00.00.692269 PM +08:00
          GATHER_STATS_JOB              
          04-DEC-07 10.00.00.701152 PM +08:00
          FGR$AUTOPURGE_JOB
          PURGE_LOG                      
          05-DEC-07 03.00.00.169059 AM PRC


          然而這個自動化功能已經影響了很多系統的正常運行,晚上10點對于大部分生產系統也并非空閑時段。
          而自動分析可能導致極為嚴重的閂鎖競爭,進而可能導致數據庫Hang或者Crash。

          所以建議最好關閉這個自動統計信息收集功能

          方法之一:
          exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
          恢復自動分析:
          exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');


          方法二:
          alter system set "_optimizer_autostats_job"=false scope=spfile;
          alter system set "_optimizer_autostats_job"=true scope=spfile;
          Pfile可以直接修改初始化參數文件,重新啟動數據庫。



          posted on 2009-02-07 15:11 Derek.Guo 閱讀(18912) 評論(0)  編輯  收藏

          只有注冊用戶登錄后才能發表評論。


          網站導航:
           
          MSN:envoydada@hotmail.com QQ:34935442
          主站蜘蛛池模板: 武川县| 贡觉县| 新安县| 民和| 招远市| 厦门市| 越西县| 梁河县| 法库县| 晋江市| 光泽县| 临邑县| 西乌珠穆沁旗| 当雄县| 安顺市| 临朐县| 临城县| 怀安县| 万山特区| 泸溪县| 龙江县| 永昌县| 汨罗市| 兴国县| 高陵县| 卢湾区| 丘北县| 安图县| 鄂州市| 札达县| 上虞市| 隆安县| 丹凤县| 唐山市| 开化县| 龙游县| 鄂托克前旗| 漳州市| 绥化市| 遵化市| 石河子市|