oracle資料庫最佳化之統計資訊

標籤：oracle效能最佳化

1.統計資訊簡介

統計資訊主要是描述資料庫中表，索引的大小，規模，資料分布狀況等的一類資訊。比如，表的行數，塊數，平均每行的大小，索引的leaf blocks，索引欄位的行數，不同值的大小等，都屬於統計資訊。CBO正是根據這些統計資訊資料，計算出不同訪問路徑下，不同join 方式下，各種計劃的成本，最後選擇出成本最小的計劃。

在CBO(基於代價的最佳化器模式)條件下，SQL語句的執行計畫由統計資訊來決定，若沒有統計資訊則會採取動態採樣的方式決定執行計畫！可以說統計資訊關乎sql的執行計畫是否正確，屬於sql執行的指導思想，oracle的初始化參數statistics_level控制收集統計資訊的層級，有三個參數值:

BASIC :收集基本的統計資訊

TYPICAL：收集大部分統計資訊(資料庫的預設設定)

ALL：收集全部統計資訊

Oracle 10g之後，Query Optimizer就已經將CBO作為預設最佳化器，並且Oracle官方不再支援RBO服務。但是，通過最佳化器參數optimizer_mode，我們可以控制Oracle最佳化器產生不同模式下的執行計畫。

2 收集統計資訊

Oracle Statistic 的收集，可以使用analyze 命令，也可以使用DBMS_STATS 包來收集，Oracle 建議使用DBMS_STATS包來收集統計資訊，因為DBMS_STATS包收集的更廣，
並且更準確。analyze 在以後的版本中可能會被移除。

dbms_stats.gather_table_stats 收集表、列和索引的統計資訊；
dbms_stats.gather_schema_stats 收集SCHEMA下所有對象的統計資訊；
dbms_stats.gather_index_stats 收集索引的統計資訊；
dbms_stats.gather_system_stats 收集系統統計資訊
dbms_stats.GATHER_DICTIONARY_STATS：所有字典對象的統計；
DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系統模式的統計
dbms_stats.delete_table_stats 刪除表的統計資訊
dbms_stats.delete_index_stats 刪除索引的統計資訊
dbms_stats.export_table_stats 輸出表的統計資訊
dbms_stats.create_state_table
dbms_stats.set_table_stats 設定表的統計
dbms_stats.auto_sample_size

analyze 命令的文法如下：
SQL>analyze table tablename compute statistics;
SQL>analyze table tablename compute statistics for all indexes;
SQL>analyze table tablename delete statistics

3 統計資訊的分類

Oracle 的Statistic 資訊的收集分兩種：自動收集和手工收集。

Oracle 的Automatic Statistics Gathering 是通過Scheduler 來實現收集和維護的。Job 名稱是GATHER_STATS_JOB, 該Job收集資料庫所有對象的2種統計資訊：

（1）Missing statistics（統計資訊缺失）

（2）Stale statistics（統計資訊陳舊）

該Job 是在資料庫建立的時候自動建立，並由Scheduler來管理。Scheduler 在maintenance windows open時運行gather job。 預設情況下，job 會在每天晚上10到早上6點和周末全天開啟。該過程首先檢測統計資訊缺失和陳舊的對象。然後確定優先順序，再開始進行統計資訊。

Scheduler Job的stop_on_window_close 屬性控制GATHER_STATS_JOB 是否繼續。該屬性預設值為True. 如果該值設定為False，那麼GATHER_STATS_JOB 會中斷， 而沒有收集完的對象將在下次啟動時繼續收集。

Gather_stats_job 調用dbms_stats.gather_database_stats_job_proc過程來收集statistics 的資訊。 該過程收集對象statistics的條件如下：

（1）對象的統計資訊之前沒有收集過。

（2）當對象有超過10%的rows 被修改，此時對象的統計資訊也稱為stale statistics。

select job_name, program_name, enabled, stop_on_window_close
  from dba_scheduler_jobs
 where job_name = ‘gather_stats_job‘;

統計資訊收集如下資料：
（1）表自身的分析： 包括表中的行數，資料區塊數，行長等資訊。
（2）列的分析：包括列值的重複數，列上的空值，資料在列上的分布情況。
（3）索引的分析： 包括索引葉塊的數量，索引的深度，索引的彙總因子等。

這些統計資訊存放在以下的資料字典裡：
DBA_TABLES
DBA_OBJECT_TABLES
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS

包含表行數，使用的塊數，空的塊數，塊的使用率，行遷移和連結的數量，pctfree，pctused的資料，行的平均大小：
SELECT NUM_ROWS, --表中的記錄數
BLOCKS, --表中資料所佔的資料區塊數
EMPTY_BLOCKS, --表中的空塊數
AVG_SPACE, --資料區塊中平均的使用空間
CHAIN_CNT, --表中行串連和行遷移的數量
AVG_ROW_LEN --每條記錄的平均長度
FROM USER_TABLES

包含索引的深度（B-Tree的層級），索引葉級的塊數量，叢集因子（clustering_factor), 唯一值的個數。
SELECT BLEVEL, --索引的層數
LEAF_BLOCKS, --葉子結點的個數
DISTINCT_KEYS, --唯一值的個數
AVG_LEAF_BLOCKS_PER_KEY, --每個KEY的平均葉塊個數
AVG_DATA_BLOCKS_PER_KEY, --每個KEY的平均資料區塊個數
CLUSTERING_FACTOR --群集因子
FROM USER_INDEXES

包含唯一的值個數，列最大小值，密度（選擇率），資料分布（長條圖資訊），NUll值個數
SELECT NUM_DISTINCT, --唯一值的個數
LOW_VALUE, --列上的最小值
HIGH_VALUE, --列上的最大值
DENSITY, --選擇率因子（密度）
NUM_NULLS, --空值的個數
NUM_BUCKETS, --長條圖的BUCKET個數
HISTOGRAM --長條圖的類型
FROM USER_TAB_COLUMNS

 

1，SQL/PLUS的視窗運行以下命令
 
set time on;                 (說明：開啟時間顯示）（可選）
set autotrace on;            (說明：開啟自動分析統計，並顯示SQL語句的運行結果）
set autotrace traceonly;     (說明：開啟自動分析統計，不顯示SQL語句的運行結果）
 
4，接下來你就運行需要查看執行計畫的SQL語句，看到其分析統計結果了。一般來講，我們的SQL語句應該避免對大表的全表掃描。
 
5，關閉以上功能，在SQL/PLUS的視窗運行以下命令
 
set time off;                      (說明：關閉時間顯示）
set autotrace off;       

附：相關的set autotrace命令：



2.需求分析：

通過分析發現，某些大表的統計日期已經有2,3個月沒有更新了。
select table_name, num_rows, last_analyzed from user_tables ;1

call dbms_stats.gather_table_stats(‘使用者名稱‘,‘表名‘);

select ‘alter system kill session ‘‘‘|| sid ||‘‘||‘,‘|| serial# ||‘‘‘;‘ from v$session where username=‘JINRILOG‘

3.自動化指令碼

將如下兩個指令碼存放到同一目錄下，注意修改資料庫配置參數，然後使用windows計劃任務定期執行動態更新；

指令碼1：自動更新Oracle統計資訊.bat

title 自動更新Oracle統計資訊[%date%%time%]

sqlplus userName/[email protected]/db_name @gen_sqls.sql | find "call dbms_stats.gather_table_stats">stats_sqls.sql
echo exit>>stats_sqls.sql
sqlplus userName/[email protected]/db_name @stats_sqls.sql
echo 指令碼執行完成1

指令碼2：gen_sqls.sql

select
‘call dbms_stats.gather_table_stats(‘‘使用者名稱‘‘,‘‘‘|| TABLE_NAME ||‘‘‘);‘ as sqls
from user_tables a
where a.last_analyzed <sysdate-7
and num_rows > 1000
order by num_rows ;
exit;





分析某個使用者下所有表
select ‘call dbms_stats.gather_table_stats(‘‘‘|| OWNER ||‘‘‘,‘‘‘|| TABLE_NAME ||‘‘‘);‘ as sqls from dba_tables where owner=‘SCOTT‘


select ‘exec dbms_stats.gather_table_stats(‘‘‘|| OWNER ||‘‘‘,‘‘‘|| TABLE_NAME ||‘‘‘);‘ from dba_tables where owner=‘HR‘

select ‘exec dbms_stats.gather_table_stats(‘‘‘|| OWNER ||‘‘‘,‘‘‘|| TABLE_NAME ||‘‘‘)‘ from dba_tables where owner=‘HR‘


方式二

CREATE OR REPLACE PROCEDURE AnalyzeAllTable  
IS  
--2009-10-18 wallimn   
--分析所有表及索引。便於有效使用CBO最佳化器  
BEGIN  
    --分析所有表：analyze table TABLENAME compute statistics  
    for cur_item in (select table_name from user_tables) loop  
        begin  
             execute immediate ‘analyze table ‘|| cur_item.table_name   
                               || ‘ compute statistics‘;  
         exception   
            when others then  
                dbms_output.put_line(‘分析表異常：‘||sqlerrm);  
         end;  
     end loop;  
      
    --分析所有索引：analyze index INDEXNAME estimate statistics  
     for cur_item in (select index_name from user_indexes) loop  
         begin  
              execute immediate ‘analyze index ‘|| cur_item.index_name   
                                || ‘ estimate statistics‘;  
         exception   
            when others then  
                dbms_output.put_line(‘分析索引異常：‘||sqlerrm);  
         end;  
     end loop;     
END AnalyzeAllTable;  
/

本文出自 “11300506” 部落格，請務必保留此出處http://11310506.blog.51cto.com/11300506/1963264

oracle資料庫最佳化之統計資訊