一、性能數(shù)據(jù)的存儲過程：

性能數(shù)據(jù)的收集包含這樣幾個存儲過程：

GATHER_INDEX_STATS：分析索引信息 
GATHER_TABLE_STATS：分析表信息，當cascade為true時，分析表、列（索引）信息 
GATHER_SCHEMA_STATS：分析方案信息 
GATHER_DATABASE_STATS：分析數(shù)據(jù)庫信息 
GATHER_SYSTEM_STATS：分析系統(tǒng)信息

二、GATHER_TABLE_STATS： 分析表、字段和索引：

我們分析時最常用到的就是GATHER_TABLE_STATS，dbms_stats能良好地估計統(tǒng)計數(shù)據(jù)（尤其是針對較大的分區(qū)表），并能獲得更好的統(tǒng)計結(jié)果，最終制定出速度更快的SQL執(zhí)行計劃。

procedure gather_table_stats
    (ownname varchar2, tabname varchar2, partname varchar2 default null,
     estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
     block_sample boolean default FALSE,
     method_opt varchar2 default DEFAULT_METHOD_OPT,
     degree number default to_degree_type(get_param('DEGREE')),
     granularity varchar2 default  DEFAULT_GRANULARITY,
     cascade boolean default DEFAULT_CASCADE,
     stattab varchar2 default null, statid varchar2 default null,
     statown varchar2 default null,
     no_invalidate boolean default
       to_no_invalidate_type(get_param('NO_INVALIDATE')),
     stattype varchar2 default 'DATA',
     force boolean default FALSE,
     -- the context is intended for internal use only.
     context dbms_stats.CContext default null);

method_opt:決定histograms直方圖信息是怎樣被統(tǒng)計的。method_opt的取值如下(默認值為FOR ALL COLUMNS SIZE AUTO):

• FOR ALL COLUMNS:統(tǒng)計所有列的HISTOGRAMS.
• FOR ALL INDEXED COLUMNS:統(tǒng)計所有INDEXED列的HISTOGRAMS.
• FOR ALL HIDDEN COLUMNS:統(tǒng)計你看不到列的HISTOGRAMS
• FOR COLUMNS <LIST> SIZE <INTEGER> | REPEAT | AUTO |
• INTEGER指的直方圖的BUCKETS數(shù)量，取值范圍為[1,254]。
• REPEAT上次統(tǒng)計過的HISTOGRAMS。
• AUTO:ORACLE根據(jù)列數(shù)據(jù)的分布及相關(guān)列的訪問量來決定收集直方圖的列。
• SKEWONLY:ORACLE 根據(jù)列的數(shù)據(jù)分布來決定哪些列收集直方圖

在 gather_table_stats 存儲過程的所有參數(shù)中，除了 ownname 和 tabname，其他的參數(shù)都有默認值。

dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1');

三、DBMS_STATS 包管理功能

1、查找表最后執(zhí)行分析的日期

select table_name,num_rows,blocks,last_analyzed from all_tables where table_name='WORK_LIST';

2、其他存儲過程

• CREATE_STAT_TABLE ：創(chuàng)建分析數(shù)據(jù)表
• DROP_STAT_TABLE： 刪除分析數(shù)據(jù)表
• GATHER_TABLE_STATS：執(zhí)行分析表
• GET_TABLE_STATS ：獲取分析數(shù)據(jù)
• SET_TABLE_STATS ： 設置分析數(shù)據(jù)
• EXPORT_TABLE_STATS： 導出分析數(shù)據(jù)
• IMPORT_TABLE_STATS： 導入分析數(shù)據(jù)
• LOCK_TABLE_STATS ：鎖定分析數(shù)據(jù)
• UNLOCK_TABLE_STATS ：解鎖分析數(shù)據(jù)
• RESTORE_TABLE_STATS： 恢復分析數(shù)據(jù)
• delete_table_stats ：刪除表統(tǒng)計信息

四、自動收集CBO的統(tǒng)計信息

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

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

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

select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

其實同在10點運行的Job還有一個AUTO_SPACE_ADVISOR_JOB，然而這個自動化功能已經(jīng)影響了很多系統(tǒng)的正常運行，晚上10點對于大部分生產(chǎn)系統(tǒng)也并非空閑時段。 
而自動分析可能導致極為嚴重的閂鎖競爭，進而可能導致數(shù)據(jù)庫Hang或者Crash。

所以建議最好關(guān)閉這個自動統(tǒng)計信息收集功能

方法之一:

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可以直接修改初始化參數(shù)文件，重新啟動數(shù)據(jù)庫。

到此這篇關(guān)于ORACLE數(shù)據(jù)表分析的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持腳本之家。

最新評論