The important thing in life is to have a great aim , and the determination

深入理解Oracle Statistic統(tǒng)計信息

1.統(tǒng)計信息簡介

統(tǒng)計信息主要是描述數(shù)據(jù)庫中表，索引的大小，規(guī)模，數(shù)據(jù)分布狀況等的一類信息。比如，表的行數(shù)，塊數(shù)，平均每行的大小，索引的leaf blocks，索引字段的行數(shù)，不同值的大小等，都屬于統(tǒng)計信息。CBO正是根據(jù)這些統(tǒng)計信息數(shù)據(jù)，計算出不同訪問路徑下，不同join 方式下，各種計劃的成本，最后選擇出成本最小的計劃。

在CBO(基于代價的優(yōu)化器模式)條件下，SQL語句的執(zhí)行計劃由統(tǒng)計信息來決定，若沒有統(tǒng)計信息則會采取動態(tài)采樣的方式?jīng)Q定執(zhí)行計劃！可以說統(tǒng)計信息關(guān)乎sql的執(zhí)行計劃是否正確，屬于sql執(zhí)行的指導(dǎo)思想，oracle的初始化參數(shù)statistics_level控制收集統(tǒng)計信息的級別，有三個參數(shù)值:

BASIC :收集基本的統(tǒng)計信息

TYPICAL：收集大部分統(tǒng)計信息(數(shù)據(jù)庫的默認(rèn)設(shè)置)

ALL：收集全部統(tǒng)計信息

Oracle 10g之后，Query Optimizer就已經(jīng)將CBO作為默認(rèn)優(yōu)化器，并且Oracle官方不再支持RBO服務(wù)。但是，通過優(yōu)化器參數(shù)optimizer_mode，我們可以控制Oracle優(yōu)化器生成不同模式下的執(zhí)行計劃。

關(guān)于優(yōu)化器的請參考：《SQL性能優(yōu)化之optimizer_mode參數(shù)原理滲透解析》

2.如何收集統(tǒng)計信息

2.1 統(tǒng)計信息的內(nèi)容：

1）Table statistics

Number of rows --行數(shù)量

Number of blocks --block數(shù)量

Average row length --平均行的長度.

2）Column statistics

Number of distinct values (NDV) in column --列中distinct的值

Number of nulls in column --列中null的值

Data distribution (histogram)  --數(shù)據(jù)分布

3）Index statistics

Number of leaf blocks --子節(jié)點的塊數(shù)量

Levels --子節(jié)點數(shù)量

Clustering factor --集群因子

4）System statistics

I/O performance and utilization --IO性能和利用率

CPU performance and utilization --CPU的性能和利用率

2.2 收集統(tǒng)計信息

Oracle Statistic 的收集，可以使用analyze 命令，也可以使用DBMS_STATS 包來收集，Oracle 建議使用DBMS_STATS包來收集統(tǒng)計信息，因為DBMS_STATS包收集的更廣，并且更準(zhǔn)確。analyze 在以后的版本中可能會被移除。

DBMS_STATS常用的幾個過程如下：

analyze 命令的語法如下：

2.3 統(tǒng)計信息的分類

Oracle 的Statistic 信息的收集分兩種：自動收集和手工收集。

Oracle 的Automatic Statistics Gathering 是通過Scheduler 來實現(xiàn)收集和維護(hù)的。Job 名稱是GATHER_STATS_JOB, 該Job收集數(shù)據(jù)庫所有對象的2種統(tǒng)計信息：

（1）Missing statistics（統(tǒng)計信息缺失）

（2）Stale statistics（統(tǒng)計信息陳舊）

該Job 是在數(shù)據(jù)庫創(chuàng)建的時候自動創(chuàng)建，并由Scheduler來管理。Scheduler 在maintenance windows open時運(yùn)行g(shù)ather job。 默認(rèn)情況下，job 會在每天晚上10到早上6點和周末全天開啟。該過程首先檢測統(tǒng)計信息缺失和陳舊的對象。然后確定優(yōu)先級，再開始進(jìn)行統(tǒng)計信息。

Scheduler Job的stop_on_window_close 屬性控制GATHER_STATS_JOB 是否繼續(xù)。該屬性默認(rèn)值為True. 如果該值設(shè)置為False，那么GATHER_STATS_JOB 會中斷， 而沒有收集完的對象將在下次啟動時繼續(xù)收集。

Gather_stats_job 調(diào)用dbms_stats.gather_database_stats_job_proc過程來收集statistics 的信息。 該過程收集對象statistics的條件如下：

（1）對象的統(tǒng)計信息之前沒有收集過。

（2）當(dāng)對象有超過10%的rows 被修改，此時對象的統(tǒng)計信息也稱為stale statistics。

但是對于高度變化的表在白天的活動期間被TRUNCATE/DROP并重建或者塊加載超過本身總大小10%的對象；我們可以將這些表上的統(tǒng)計設(shè)置為NULL

可以通過以下SQL來查看：

為了決定是否對對象進(jìn)行監(jiān)控，Oracle 提供了一個參數(shù)STATISTICS_LEVEL。通過設(shè)置初始化參數(shù)STATISTIC_LEVEL 為TYPICAL 或ALL，就可以自動收集統(tǒng)計信息(默認(rèn)值為TYPICAL，因此可以隨即啟用自動收集統(tǒng)計信息的功能)。STATISTIC_LEVEL 參數(shù)的值可以激活GATHER_STATS_JOB。

在10g中表監(jiān)控默認(rèn)是激活的，如果STATISTICS_LEVEL設(shè)置為basic，不僅不能監(jiān)控表，而且將禁掉如下一些10g的新功能：

（1）ASH(Active Session History)

（2）ASSM(Automatic Shared Memory Management)

（3）AWR(Automatic Workload Repository)

（4）ADDM(Automatic Database Diagnostic Monitor)

當(dāng)啟動對象的監(jiān)控后，從上次統(tǒng)計信息收集之后的的信息，如inserts，updates，deletes 等，這些改變的信息會記錄到user_tab_modifications 視圖。

當(dāng)對象的數(shù)據(jù)發(fā)生改變之后， 經(jīng)過幾分鐘的延時，這些信息寫入到user_tab_modifications視圖，然后dbms_stats.flush_database_monitoring_info過程就會發(fā)現(xiàn)這些信息，并講這些信息保存在內(nèi)存中。

當(dāng)監(jiān)控的對象被修改的部分超過10%時，gather_database_stats 或者gather_schema_stats 過程就會去收集這些stale statistics

3.統(tǒng)計信息的存儲位置以及常用數(shù)據(jù)字典

3.1 統(tǒng)計信息常用數(shù)據(jù)字典

統(tǒng)計信息收集如下數(shù)據(jù)：

（1）表自身的分析： 包括表中的行數(shù)，數(shù)據(jù)塊數(shù)，行長等信息。

（2）列的分析：包括列值的重復(fù)數(shù)，列上的空值，數(shù)據(jù)在列上的分布情況。

（3）索引的分析： 包括索引葉塊的數(shù)量，索引的深度，索引的聚合因子等。

這些統(tǒng)計信息存放在以下的數(shù)據(jù)字典里：

3.2 表的統(tǒng)計信息

包含表行數(shù)，使用的塊數(shù)，空的塊數(shù)，塊的使用率，行遷移和鏈接的數(shù)量，pctfree，pctused的數(shù)據(jù)，行的平均大小：

3.3索引列的統(tǒng)計信息

包含索引的深度（B-Tree的級別），索引葉級的塊數(shù)量，集群因子（clustering_factor), 唯一值的個數(shù)。

3.4 列的統(tǒng)計信息

包含唯一的值個數(shù)，列最大小值，密度（選擇率），數(shù)據(jù)分布（直方圖信息），NUll值個數(shù)

在OCP 10g考試中會有個跟統(tǒng)計信息相關(guān)的考試題：

1. In your Oracle 10g database , you have scheduled a job to update the optimizer statistics at 05:00 pm

every Friday. The job has successfully completed. Which three pieces of information would you check to

confirm that the statistics have been collected? (Choose three.)

A. average row size

B. last analyzed date

C. size of table in bytes

D. size of table in database blocks

E. number of free blocks in the free list

F. number of extents present in the table

Answer: ABD

posted on 2014-05-16 19:08 鴻雁 閱讀(300) 評論(0)  編輯  收藏 所屬分類: 數(shù)據(jù)庫