官术网_书友最值得收藏!

首頁 >

數據庫高效優化：架構、規范與SQL技巧

4.1　統計信息分類

統計信息可大致分為系統統計信息、對象統計信息、數據字典統計信息、內部對象統計信息等，下面分別介紹。

4.1.1　系統統計信息

Oracle數據庫從9i開始增加了收集操作系統統計信息的功能。通過收集到的統計信息判斷操作系統的CPU、I/O的處理能力。這為優化器選擇執行計劃提供了額外的判斷依據。通過使用系統統計信息，優化器能夠更加準確地判斷、評價CPU和I/O代價，進而選擇更好的查詢計劃。根據度量I/O子系統的方法不同，可將系統統計信息分為兩類。

·非工作量統計信息（noworkload statistics）

·工作量統計信息（workload statistics）

這兩者的區別是，前者使用人工基準測試，后者使用應用程序基準測試。這里解釋一下，所謂人工基準測試是指并非實際運行的程序產生的工作量。人工基準測試的主要目的是通過執行近似的操作，以模擬應用程序的負載。雖然能夠以輕易可控的方式運行，但通常情況下無法產生像應用程序基準測試那么好的性能數據。所謂應用程序基準測試是指基于實際應用程序正常操作產生的工作量進行的。通常可以很好地提供實際運行系統的性能信息。關于收集方法，后面會有專門說明。下面對系統統計信息項加以說明。

1.指標項說明

非工作量統計信息和工作量統計信息這兩類所包含的指標不同，在計算時會進行一定的換算。

（1）非工作量統計信息

從10g開始，非工作量統計信息總是可用的，主要包含以下指標項。

·CPUSPEEDNW：代表無負載CPU速度。CPU速度為每秒CPU周期數，也就是一個CPU一秒能處理的操作數，單位是百萬次/秒。

·IOSEEKTIM：I/O查找時間，也就是平均尋道時間，其等于查找時間、延遲時間、OS負載時間三者之和，單位為毫秒，默認為10。

·IOTFRSPEED：I/O傳輸速度，也就是平均每毫秒從磁盤傳輸的字節數，單位為字節/毫秒，默認為4096。

（2）工作量統計信息

工作量統計信息只有在顯式地收集以后才可用。要進行顯式收集，就不能使用空閑的系統，因為數據庫引擎要利用正常的數據庫負載來評估I/O子系統。另一方面，衡量CPU速度的方法與進行非工作統計時一樣。工作量統計信息主要包含以下指標項。

·CPUSPEED：代表有負載CPU速度。CPU速度為每秒CPU周期數，也就是一個CPU一秒能處理的操作數，單位為百萬次/秒。

·SREADTIM：隨機讀取單塊的平均時間，單位為毫秒。

·MREADTIM：順序讀取多塊的平均時間，也就是多塊平均讀取時間，單位為毫秒。

·MBRC：平均每次讀取的塊數量，單位為塊數。

·MAXTHR：最大I/O吞吐量，單位為字節/秒。

·SLAVETHR：并行處理中從屬線程的平均I/O吞吐量，單位為字節/秒。

2.數據字典統計信息查詢

系統統計信息保存在aux_stats表里面。Oracle沒有提供數據字典視圖來供外部表訪問。我們可以通過對這個內表的訪問，了解系統統計信息各個方面的情況。例如下面的命令，可查看系統統計信息收集的時間及狀態。

[sys@testdb] SQL> col pval2 format a20
[sys@testdb] SQL> select pname,pval1,pval2
2  fromsys.aux_stats$
3  wheresname='SYSSTATS_INFO';
PNAME                               PVAL1 PVAL2
------------------------------ ---------- --------------------
DSTART                                    08-24-2013 12:04
DSTOP                                     08-24-2013 12:04
FLAGS                                   1
STATUS                                    COMPLETED

如果收集是正確的，則顯示為COMPLETED狀態。下面的命令可查詢系統統計信息的結果集。

[sys@testdb] SQL> select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     3074.07407
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

3.相關操作

針對系統統計信息，可以有多種操作，下面簡單說明一下。

（1）收集統計信息

針對非工作量和工作量的統計信息，收集的方法是不同的。針對非工作量的系統統計信息，可采用如下方法收集：

dbms_stats.gather_system_stats(gathering_mode=>'noworkload');

針對工作量的統計信息，可使用多種方法進行收集。一種方法是，執行兩次收集動作，在兩次快照之間計算其差值。具體方法參考如下：

dbms_stats.gather_system_stats(gathering_mode=>'start');
wait a moment
dbms_stats.gather_system_stats(gathering_mode=>'stop');

這里關于等待時間需要注意，數據庫引擎并不控制數據庫負載，因此必須等待足夠的時間來產生一個有代表性的負載之后再進行另一次快照，一般等待至少30分鐘。

另一種方法是，立即啟動收集一個快照，而第二次收集快照動作在指定時長后執行。這個處理過程并不會一直持續，它會通過系統的調度工具完成。

dbms_stats.gather_system_stats(gathering_mode=>'interval', interval=>N);
*上述過程中，參數interval就是指定收集間隔時長

（2）設置統計信息

除了利用上面的方法收集系統統計信息外，還可以手工設置系統統計信息。但一般不建議這樣做，有一定操作風險。手工設置系統統計信息如下：

begin
dbms_stats.delete_system_stats();
dbms_stats.set_system_stats(pname=>'CPUSPEED',pvalue=>772);
dbms_stats.set_system_stats(pname=>'SREADTIM',pvalue=>5.5);
dbms_stats.set_system_stats(pname=>'MREADTIM',pvalue=>19.4);
dbms_stats.set_system_stats(pname=>'MBRC',pvalue=>53);
dbms_stats.set_system_stats(pname=>'MAXTHR',pvalue=>1243434334);
dbms_stats.set_system_stats(pname=>'SLAVETHR',pvalue=>1212121);

（3）刪除統計信息

如果感覺系統收集的統計信息有問題，也可以采用下面的方式進行刪除。

execdbms_stats.delete_system_stats;

4.系統統計信息對優化器的影響

系統統計信息會直接影響優化器的成本計算。如果存在工作量統計，則優化器會使用它而忽略非工作量統計信息。如果工作量統計信息不正確，那么數據庫會使用非工作量統計信息。但要注意，查詢優化器會運行一些健康檢查，可能禁用或部分替換工作量統計信息。

在未引入系統統計信息之前，CBO所計算的成本值全部是基于I/O來計算的；在Oracle引入了系統統計信息之后，實際上就額外地引入了CPU成本計算模型。從此之后，CBO所計算的成本值就不再僅僅包含I/O成本，而是包含I/O成本和CPU成本兩部分。CBO在計算成本的時候就會分別對它們進行計算，并將計算出的I/O成本和CPU成本值的總和作為目標SQL的新成本值。

4.1.2　對象統計信息

1.表統計信息

表是數據庫里最基礎的對象。下面通過一個簡單例子，看看表都有哪些常見的統計信息。

在下面的例子中，我們手工創建了一個表，然后收集了相關統計信息，最后查看數據字典，得到相關的統計信息。

[hf@testdb] SQL> create table t1 as select * from dba_objects;
Table created.

[hf@testdb] SQL> exec dbms_stats.gather_table_stats(user, 't1');
PL/SQL procedure successfully completed.

[hf@testdb] SQL> select table_name,num_rows,blocks,empty_blocks,avg_space,
      chain_cnt,avg_row_len
2  fromuser_tables t
3  wheretable_name='T1';
TABLE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ------------ ---------- ---------- -----------
T1              86273       1260            0          0          0          98

其中，統計信息的含義如下。

·num_rows：數據的行數。

·blocks：高水位線下的數據塊個數。

·empty_blocks：高水位線以上的數據塊個數。dbms_stats不計算這個值，被設置為0。

·avg_space：數據塊中平均空余空間（字節）。dbms_stats不計算這個值，被設置為0。

·chain_cnt：行鏈接和行遷移的數目。dbms_stats不計算這個值，被設置為0。

·avg_row_len：行平均長度（字節）。

（1）高水位線

我們在前面表的統計信息中可以看到一個概念——高水位線（HWM），這是一個比較重要的概念。當我們開始向表插入數據時，第1個塊已經放不下后面新插入的數據。此時，Oracle將高水位線之上的塊用于存儲新增數據；同時，高水位線本身也向上移。也就是說，當不斷插入數據時，高水位線會不斷上移。這樣，在高水位線之下的，就表示使用過的塊；高水位線之上的，就表示已分配但從未使用過的塊。高水位線在插入數據時，當現有空間不足而進行空間的擴展時會向上移，但刪除數據時不會下移。Oracle不會釋放空間以供其他對象使用。

Oracle的全表掃描是讀取高水位線以下的所有塊。當發出一個全表掃描時，Oracle始終必須從段開頭一直掃描到高水位線，即使它什么也沒有發現。該任務延長了全表掃描的時間。下面通過一個示例說明一下。

[hf@testdb] SQL> create table t1 as select * from dba_objects;
Table created.

[hf@testdb] SQL> insert into t1 select * from t1;
86274 rows created.

[hf@testdb] SQL> insert into t1 select * from t1;
172548 rows created.

[hf@testdb] SQL> insert into t1 select * from t1;
345096 rows created.

[hf@testdb] SQL> commit;
Commit complete.

[hf@testdb] SQL> exec dbms_stats.gather_table_stats(user, 't1');
PL/SQL procedure successfully completed.

[hf@testdb] SQL> exec sys.show_space('t1','auto');
Total Blocks............................10240
Total Bytes.............................83886080
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................13312
Last Used Block.........................1024
PL/SQL procedure successfully completed.

這里使用了一個自定義的過程show_space，其具體定義可以在網上搜到，這里不再贅述。通過這個方法可以觀察到一個表的空間使用情況，HWM的計算公式為：HWM=Total Blocks–Unused Blocks。針對上例，其高水位線就是10 240。

下面的語句執行了一個全表掃描。通過之前的說明可知，數據庫會掃描高水位線下的全部數據塊。對應的統計信息consistent gets和physical reads可見。

[hf@testdb] SQL> set autotracetraceonly
[hf@testdb] SQL> select count(*) from t1;
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  2749   (1)| 00:00:33 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   690K|  2749   (1)| 00:00:33 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
9881  consistent gets
9873  physical reads

刪除數據后，重復下面的行為，從輸出可見其consistent gets變化不大，physical reads減少是因為數據塊被緩存的關系。

[hf@testdb] SQL> set autotrace off
[hf@testdb] SQL> delete from t1;
690192 rows deleted.

[hf@testdb] SQL> commit;
Commit complete.

[hf@testdb] SQL> set autotracetraceonly
[hf@testdb] SQL> select count(*) from t1;
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  2749   (1)| 00:00:33 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   690K|  2749   (1)| 00:00:33 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
9888  consistent gets
2484  physical reads

此時，查看其高水位線，沒有變化。也就是說，delete操作不會降低高水位線。

[hf@testdb] SQL> set autotrace off
[hf@testdb] SQL> exec sys.show_space('t1','auto');
Total Blocks............................10240
Total Bytes.............................83886080
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................13312
Last Used Block.........................1024
PL/SQL procedure successfully completed.

Truncate操作后，整體讀取的數據塊減少了。原因就是其高水位線下降了，從原來的10240到現在的（8-5）。因為掃描的數據塊少了，所以其一致性讀、物理讀指標也下降了。

[hf@testdb] SQL> truncate table t1;
Table truncated.

[hf@testdb] SQL> set autotracetraceonly
[hf@testdb] SQL> select count(*) from t1;
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  2749   (1)| 00:00:33 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   690K|  2749   (1)| 00:00:33 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
7  consistent gets
0  physical reads

[hf@testdb] SQL> set autotrace off
[hf@testdb] SQL> exec sys.show_space('t1','auto');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................4
Last Used Ext BlockId...................2856
Last Used Block.........................3

PL/SQL procedure successfully completed.

（2）臨時表

臨時表是一類特殊的數據對象，其很多行為與普通表不同。對于數據庫來說，有兩種臨時表：一種是基于會話（on Commit Preserve Row）的臨時表；一種是基于事務（on Commit Delete Row）的臨時表。無論是基于事務還是基于會話的臨時表，對于其他會話都是不可見的。換句話說，數據只存在于當前會話中。基于事務的臨時表，在本會話中只要有提交動作，數據就會立即消失；基于會話的臨時表在SESSION生存期內提交數據仍然存在，并且可以回滾，沒退出會話之前和普通表的操作沒有什么區別。

針對臨時表而言，默認是不收集統計信息的，可以使用dbms_stats.gather_schema_stats這個過程來收集，但是需要修改屬性gather_tmp的值，將其由默認的false，修改為true。在收集統計信息時，最終的統計信息是最后一個執行收集動作的會話所能看到的數據。不過需要注意的是，可以統計基于會話的臨時表，不能統計基于事務的臨時表。

由于臨時表是全局的，但收集的統計信息是在某個會話下做的，不同會話之間看到的數據是不同的。因此在使用臨時表時需要注意，有時收集統計信息反而會產生問題，此時可考慮走默認的動態采樣的方式。

下面創建了一個基于會話的臨時表。

下面通過一個示例加以說明。

[hf@testdb] SQL> create global temporary table t_temp2
2  on commit preserve rows
3  as select * from dba_objects where 1=2;
Table created.

[hf@testdb] SQL> create index idx_object_id on t_temp2(object_id);
Index created.

在一個會話中插入少量數據（10條），后面簡稱為“會話1”。

[hf@testdb] SQL> select sid from v$mystat where rownum=1;
       SID
----------
        20

[hf@testdb] SQL> insert into t_temp2 select * from dba_objects where rownum<=10;
10 rows created.

[hf@testdb] SQL> commit;
Commit complete.

在一個會話中插入大量數據（20萬條），后面簡稱為“會話2”。

[hf@testdb] SQL> select sid from v$mystat where rownum=1;
       SID
----------
        15

[hf@testdb] SQL> insert into t_temp2 select * from dba_objects where rownum<=50000;
50000 rows created.

[hf@testdb] SQL> insert into t_temp2 select * from dba_objects where rownum<=50000;
50000 rows created.

[hf@testdb] SQL> insert into t_temp2 select * from dba_objects where rownum<=50000;
50000 rows created.

[hf@testdb] SQL> insert into t_temp2 select * from dba_objects where rownum<=50000;
50000 rows created.

[hf@testdb] SQL> commit;
Commit complete.

在“會話1”中執行查詢語句，從輸出中可見，這里使用了動態采樣，因為默認情況下臨時表是不收集統計信息的。

[hf@testdb] SQL> select count(*) from t_temp2 where object_id between 10000 and 50000;
--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |             |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN
                         |IDX_OBJECT_ID|     1 |    13 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=10000 AND "OBJECT_ID"<=50000)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
9  recursive calls
0  db block gets
9  consistent gets

在“會話2”中執行查詢語句，從輸出中可見，這里也使用了動態采樣，但下面顯示的“一致性讀”明顯不同，這顯然是由于不同會話動態采樣后分析對象的大小不同。

[hf@testdb] SQL> select count(*) from t_temp2 where object_id between 10000 and 50000;
--------------------------------------------------------------------------------
| Id  | Operation      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT
                       |               |     1 |    13 |   163   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE
                       |               |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN
                       | IDX_OBJECT_ID |   138K|  1761K|   163   (0)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID">=10000 AND "OBJECT_ID"<=50000)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
961  consistent gets

我們在“會話1”中收集了統計信息。從后面的查詢可見，表的記錄數為10。這是準確的，因為“會話1”能看到的記錄就是10條。

[hf@testdb] SQL> exec dbms_stats.gather_schema_stats( 'hf', gather_temp=>TRUE);

PL/SQL procedure successfully completed.

[hf@testdb] SQL> select table_name,num_rows,blocks,chain_cnt,avg_row_len,
      global_stats,user_stats,sample_size,to_char(t.last_analyzed,'yyyy-mm-dd') aly_d
2  fromdba_tables t
3  where owner='HF' and table_name='T_TEMP2';
TABLE_NAME  NUM_ROWS  BLOCKS CHAIN_CNT AVG_ROW_LEN GLO USE SAMPLE_SIZE ALY_D
---------- -------- ------- --------- ----------- --- --- ----------- ----------
T_TEMP2          10       1         0          75 YES  NO          10 2014-11-10

在“會話1”中再次執行上面的SQL，從輸出可見其執行計劃不變，并且沒有動態采樣的字樣了。此外，收集的執行信息部分，一致性讀為1，這比下面采用動態采樣獲得的方式更加精準。對比下面統計信息中的blocks，可以完全對應上。

[hf@testdb] SQL> select count(*) from t_temp2 where object_id between 10000 and 50000;
--------------------------------------------------------------------------------
| Id  | Operation       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT|               |     1 |     3 |     1  (0)| 00:00:01 |
|   1 |  SORT AGGREGATE
                        |               |     1 |     3 |           |          |
|*  2 |   INDEX RANGE SCAN
                        | IDX_OBJECT_ID |     1 |     3 |     1  (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=10000 AND "OBJECT_ID"<=50000)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
1  consistent gets

在“會話2”中重新查看一下統計信息，從中可見看到的還是“會話1”收集的狀態。也就是說，各個會話看到的數據是不同的，但是看到的統計信息是一個。

[hf@testdb] SQL> select table_name,num_rows,blocks,chain_cnt,avg_row_len,
      global_stats,user_stats,sample_size,to_char(t.last_analyzed,'yyyy-mm-dd') aly_d
2  fromdba_tables t
3  where owner='HF' and table_name='T_TEMP2';
TABLE_NAME NUM_ROWS   BLOCKS  CHAIN_CNT AVG_ROW_LEN GLO USE SAMPLE_SIZE  ALY_D
---------- -------- -------- --------- ----------- --- --- ----------- ----------
T_TEMP2          10        1         0          75 YES NO          10 2014-11-10

“會話2”重新執行下面的SQL語句，發現其執行計劃出現了很大偏差。從原有的“INDEX FAST FULL SCAN”變為“INDEX RANGE SCAN”。這顯然不是我們希望看見的，由于收集了臨時表的統計信息，反而造成執行計劃效率低下。

[hf@testdb] SQL> select count(*) from t_temp2 where object_id between 10000 and 50000;
--------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |    3 |            |          |
|*  2 |   INDEX RANGE SCAN|IDX_OBJECT_ID|     1 |    3 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=10000 AND "OBJECT_ID"<=50000)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
481  consistent gets

在“會話2”中，刪除了統計信息，執行計劃又回到熟悉的“INDEX FAST FULL SCAN”，后面又顯示了動態采樣方式。

[hf@testdb] SQL> exec dbms_stats.delete_table_stats('hf', 't_temp2');
PL/SQL procedure successfully completed.

[hf@testdb] SQL> select count(*) from t_temp2 where object_id between 10000 and 50000;
--------------------------------------------------------------------------------
| Id  | Operation             | Name        |  Rows| Bytes|Cost (%CPU)| Time    |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1|   13 |   163 (0)| 00:00:02 |
|   1 |  SORT AGGREGATE       |             |     1|   13 |          |          |
|*  2 |   INDEX FAST FULL SCAN|IDX_OBJECT_ID|  138K| 1761K|   163 (0)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID">=10000 AND "OBJECT_ID"<=50000)

Note
-----
   - dynamic sampling used for this statement (level=2)

從上面的例子可見，臨時表默認是采用動態采樣的方式，這往往是一種比較合適的方式。由于不同會話看到的數據不同，因此直接收集的方式往往是不可靠的。但臨時表這種“粗粒度”的管理方式，不利于生成精確的執行計劃。因此語句中如果有臨時表，需要關注因統計信息不準確導致的問題。

2.索引統計信息

索引是數據庫里最常見的對象。下面通過一個簡單的例子，看看索引都有哪些常見的統計信息。

下面的代碼創建了一個索引，并查看其統計信息。因為顯示格式問題，這里使用了一個過程print_table，后面將在附錄中列出這個過程。

[hf@testdb] SQL> create table t1 as select * from dba_objects;
Table created.

[hf@testdb] SQL> create index idx_status on t1(status);
Index created.

[hf@testdb] SQL> exec print_table('select index_name,uniqueness,blevel,leaf_blocks,distinct_keys,num_rows,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,global_stats,user_stats,sample_size,to_char(t.last_analyzed,''yyyy-mm-dd'') aly_d from user_indexes t where table_name=''T1'' and index_name=''IDX_STATUS''')
INDEX_NAME                    : IDX_STATUS
UNIQUENESS                    : NONUNIQUE
BLEVEL                        : 1
LEAF_BLOCKS                   : 205
DISTINCT_KEYS                 : 1
NUM_ROWS                      : 86274
AVG_LEAF_BLOCKS_PER_KEY       : 205
AVG_DATA_BLOCKS_PER_KEY       : 1232
CLUSTERING_FACTOR             : 1232
GLOBAL_STATS                  : YES
USER_STATS                    : NO
SAMPLE_SIZE                   : 86274
ALY_D                         : 2014-11-10
-----------------
PL/SQL procedure successfully completed.

下面看一下索引有哪些統計信息。

·num_rows：索引行。

·leaf_blocks：索引葉塊數。

·distinct_keys：索引不同鍵數。

·blevel：索引的blevel分支層數（btree的深度，從root節點到leaf節點的深度。如果root節點也是leaf節點，那么這個深度就是0）。

·avg_leaf_blocks_per_key：每個鍵值的平均索引葉塊數（每個鍵值的平均索引leaf塊數，近似取整），如果是unique index或pk，這個值總是1）。

·avg_data_blocks_per_key：每個鍵值的平均索引數據（表）塊數。

·clustering_factor：索引的聚簇因子（一個度量標準，用于索引的有序度和表混亂度之間的比較）。

我們在上面的索引統計信息中看到一個概念——聚簇因子（clustering_factor），這是一個比較重要的概念，用于標識表中數據的存儲順序和某些索引字段順序的符合程度。Oracle按照索引塊所存儲的rowid來標識相鄰索引記錄在表block中是否為相同塊。如果索引中存在多條記錄a、b、c、d……若b和a是同一個塊，則比較c和b；若不在同一個塊，則clustering_factor+1，然后比較d和c；若還不是同一個塊，則clustering_factor+1……。這樣計算下來，clustering_factor會是介于表塊數量和表記錄數之間的一個值。若clustering_factor接近塊數量，則說明表中數據具有比較好的與索引字段一樣排序順序的存儲，通過索引進行range scan的代價比較小（需要讀取的塊數比較少）；若clustering_factor接近記錄數，則說明數據和索引字段排序順序差異很大，雜亂無章，需要通過索引進行range scan的代價比較大（需要讀取的表塊可能很多）。

下面通過一個示例，顯示聚簇因子的一些使用問題。

[hf@testdb] SQL> create table t1 as select rownum id, object_name name from dba_objects
      whererownum<=50000;
Table created.

[hf@testdb] SQL> create index idx_t1 on t1(id);
Index created.

[hf@testdb] SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade => true);
PL/SQL procedure successfully completed.

[hf@testdb] SQL> select blocks,num_rows from user_tables where table_name = 'T1';
    BLOCKS   NUM_ROWS
---------- ----------
       252      50000

[hf@testdb] SQL> select index_name, blevel, leaf_blocks, clustering_factor
      fromuser_indexes where table_name = 'T1';
INDEX_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
IDX_T1                                  1         110               240

上例中聚簇因子接近表的塊數，性能很好。原因是創建表時指定的ID列是一個遞增的順序，所以索引順序與表的存放順序高度一致。

下面的代碼使用了一個反轉索引的方法。所謂反轉索引，就是將每個列的字節順序反轉。這樣做的目的是將順序值打亂為隨機散布的索引項。由這個例子可見，其聚簇因子劇增，接近了表的記錄數，性能很差。

[hf@testdb] SQL> alter index idx_t1 rebuild reverse;
Index altered.

[hf@testdb] SQL> select blocks,num_rows from user_tables where table_name = 'T1';
    BLOCKS   NUM_ROWS
---------- ----------
       252      50000

[hf@testdb] SQL> select index_name, blevel, leaf_blocks, clustering_factor
      fromuser_indexes where table_name = 'T1';
INDEX_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
IDX_T1                                  1         111             49994

3.字段統計信息

數據庫除了表、索引外，也會收集字段的統計信息。字段的統計信息分兩類：一類是基本信息，另外一類是柱狀圖信息。我們后面會有專門的章節介紹柱狀圖，所以這里只談基本信息。下面通過一個例子進行說明。

[hf@testdb] SQL> create table t1 as select * from dba_objects;
Table created.

[hf@testdb] SQL> exec dbms_stats.gather_table_stats(user,'T1');
[hf@testdb] SQL> l
  1  select column_name,
  2         decode(t.data_type,
  3             'NUMBER',t.data_type||'('||decode(t.data_precision,null,t.data_length||')',t.data_precision||','||t.data_scale||')'),
  4             'DATE',t.data_type,
  5             'LONG',t.data_type,
  6             'LONG RAW',t.data_type,
  7             'ROWID',t.data_type,
  8             'MLSLABEL',t.data_type,
  9             t.data_type||'('||t.data_length||')')||' '||
 10         decode(t.nullable,
 11             'N','NOT NULL',
 12             'n','NOT NULL',
 13             NULL) col,
 14         num_distinct,num_nulls,density,avg_col_len,histogram,num_buckets
 15  from user_tab_cols t
 16* where table_name='T1'

 [hf@testdb] SQL> /

COLUMN_NAME     COL          NUM_DISTINCT NUM_NULLS    DENSITY AVG_COL_LEN HISTO  NUM_BUCKETS
-------------- ------------ ---------- ------- --------- ---------------- ----------
OWNER          VARCHAR2(30)         24       0 .041666667          6 NONE          1
OBJECT_NAME    VARCHAR2(128)     51744       0 .000019326         25 NONE          1
SUBOBJECT_NAME VARCHAR2(30)         89   86009 .011235955          2 NONE          1
OBJECT_ID      NUMBER(22)        86274       0 .000011591          5 NONE          1
DATA_OBJECT_ID NUMBER(22)         8583   77651 .000116509          2 NONE          1
OBJECT_TYPE    VARCHAR2(19)         44       0 .022727273          9 NONE          1
CREATED        DATE                889       0 .001124859          8 NONE          1
LAST_DDL_TIME  DATE               1004       0 .000996016          8 NONE          1
TIMESTAMP      VARCHAR2(19)       1044       0 .000957854         20 NONE          1
STATUS         VARCHAR2(7)           1       0          1          6 NONE          1
TEMPORARY      VARCHAR2(1)           2       0         .5          2 NONE          1
GENERATED      VARCHAR2(1)           2       0         .5          2 NONE          1
SECONDARY      VARCHAR2(1)           2       0         .5          2 NONE          1
NAMESPACE      NUMBER(22)           20       0        .05          3 NONE          1
EDITION_NAME  VARCHAR2(30)           0   86274          0          0 NONE          0

默認的情況下，數據庫會為列收集基本信息，但不會收集柱狀圖信息。在使用dbms_stats.gather_table_stats收集表的統計信息時，未指定method_opt，則Oracle將采用FOR ALL COLUMNS SIZE AUTO選型。從10g開始，有一個內置的參數_column_tracking_level，可以通過它來控制是否監控列的使用。默認這個參數是打開的，此時如果某些傾斜列被頻繁使用，則Oracle會在Auto模式下，自動為該列收集柱狀圖。

下面看看列統計信息的主要項。

·num_distinct：不同值的數目。

·num_nulls：字段值為null的數目。

·density：選擇率。

·low_value：最小值，顯示為內部存儲的格式。注意，字符串列只存儲前32字節。

·high_value：最大值，顯示為內部存儲的格式。注意，字符串列只存儲前32字節。

·avg_col_len：列平均長度（字節）。

·histogram：是否有直方圖統計信息。如果有，則是哪種類型。10g以后的版本才提供。

·NONE：沒有直方圖。

·FREQUENCY：基于頻率類型。

·HEIGHT BALANCED：基于高度類型。

·num_buckets：直方圖的桶數。

這里引入了一個很重要的概念——選擇率。這個指標反映了字段的選擇性。優化器通過選擇率與記錄數的乘積來獲得基數。這是作為執行路徑選擇的一個重要依據。選擇率的計算方法與字段是否存在柱狀圖有關，這里只介紹在字典不存在柱狀圖的情況下的計算方法，也就是沒有柱狀圖的情況；有柱狀圖的情況后面的章節將單獨介紹。對于沒有柱狀圖的情況，字段選擇率為1/num_distinct。下面通過一個示例說明。

[hf@testdb] SQL> create table t1 as select rownumid ,object_name,status from dba_objects where rownum<=50000;
Table created.

[hf@testdb] SQL> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.

[hf@testdb] SQL> select min(id),max(id) from t1;
MIN(ID)    MAX(ID)
---------- ----------
         1      50000

這里創建了一個測試表，表的ID字段范圍是1～50 000。

[hf@testdb] SQL> select column_name,num_distinct,num_nulls,density,histogram,num_buckets
2  from user_tab_cols t
3  where table_name='T1';
COLUMN_NAME     NUM_DISTINCT  NUM_NULLS    DENSITY HISTO NUM_BUCKETS
--------------- ------------ ---------- ---------- ----- -----------
ID                     50000          0     .00002 NONE            1
OBJECT_NAME            28810          0  .00003471 NONE            1
STATUS                     1          0          1 NONE            1

從統計信息可見，ID字段的選擇率為1/num_distinct=1/50000。

[hf@testdb] SQL> select * from t1 where id between 1 and 10000;
10000 rows selected.
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |   351K|    82   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   | 10000 |   351K|    82   (0)| 00:00:01 |
--------------------------------------------------------------------------

對ID字段執行了一個范圍查詢，這里評估出的基數為10 000。這是在全表5萬條記錄中選擇出的1萬條，選擇率為1/50 000計算得來的。具體計算方法是：選擇率×選擇范圍×記錄數＝評估基數，即1/50 000×10 000×50 000＝10 000。

4.直方圖信息

當數據字段的數據分布不均勻時，通過之前的字段統計信息優化器往往很難做出正確的估算。為了解決這一問題，Oracle引入了一種新的統計信息類型——直方圖。簡單來說，它就是來反映數據分布情況的一種統計信息。從原理上來講，就是假定存在n個桶（Buckets），每個桶代表一個取值或者一個取值范圍，將列中不同的值放入與之對應的桶中，通過這些桶的統計來得到列上數據分布的情況。

根據唯一值的數量和桶的個數，可以將直方圖分為兩種類型（在12c中，又細分為4種）：基于頻率的直方圖和基于高度的直方圖。下面針對這兩種直方圖分別加以說明。

（1）基于頻率的直方圖

當列的唯一值數量小于或等于桶允許的最大值（254）時，數據庫會使用基于頻率的直方圖。每個值將會占據一個桶。每個桶的高低代表每個值出現的次數。下面通過一個圖簡單說明，如圖4-1所示。

圖4-1　基于頻率的直方圖

下面通過一個實際的例子，幫大家體會一下基于頻率的直方圖。

[hf@testdb] SQL> execute dbms_random.seed(0);
PL/SQL procedure successfully completed.

[hf@testdb] SQL> create table t1 
2  as
3  select trunc(dbms_random.value(1,10))val
4  from dual
5  connect by rownum<= 10000;
Table created.

這里創建了一個測試表，共插入了10 000條記錄，其中的VAL字段為1～9之間的隨機整數。

[hf@testdb] SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt => 'for columns valsize 254');
PL/SQL procedure successfully completed.

[hf@testdb] SQL> select num_distinct,num_buckets,histogram
2  from user_tab_columns
3  where table_name='T1' and column_name='VAL';
NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ ----------- ---------------
           9           9 FREQUENCY

這里收集了統計信息，注意收集統計的選型使用了“for columns val size 254”。這表示采用254個桶來收集VAL字段的統計信息。因為這個字段的不同值只有9個，所以會使用基于頻率的直方圖。從后面的查詢也可以看出，這個表有9個不同的值，使用了9個桶，整個直方圖的類型為基于頻率的直方圖。

[hf@testdb] SQL> select endpoint_number, endpoint_value
2  from user_tab_histograms
3  where column_name = 'VAL' and table_name = 'T1'
4  order by endpoint_number;
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
           1160              1
           2333              2
           3398              3
           4506              4
           5674              5
           6718              6
           7826              7
           8907              8
          10000              9
9 rows selected.

可以從user_tab_histograms視圖中查看直方圖的具體信息。這里有兩個主要字段，其含義分別如下。

·ENDPOINT_VALUE：該值本身。如果字段是NUMBER類型，可以直接顯示；對于非數字類型（VARCHAR2、CHAR、NVARCHAR2、NCHAR和RAW）必須要進行轉換。

·ENDPOINT_NUMBER：取值的累計出現次數。當前endpoint_number減去上一個endpoint_number就是當前行出現的次數。

[hf@testdb] SQL> select val,count(*) from t1 group by val order by val;
       VAL   COUNT(*)
---------- ----------
         1       1160
         2       1173
         3       1065
         4       1108
         5       1168
         6       1044
         7       1108
         8       1081
         9       1093

對照這個查詢與上面直方圖的輸出，就很容易理解了。VAL＝1的記錄，共有1160個；VAL＝2的記錄，共有1173（即2333-1160）；以此類推。

（2）基于高度的直方圖

當列的唯一值數量大于桶數時，數據庫會基于高度的直方圖反映數據分布，每個桶容納相同數量的值。下面通過一個圖簡單說明，如圖4-2所示。

圖4-2　基于高度的直方圖

下面通過一個實際的例子，幫大家體會一下基于高度的直方圖。

[hf@testdb] SQL> execute dbms_random.seed(0);
PL/SQL procedure successfully completed.

[hf@testdb] SQL> create table t1 
2  as
3  select trunc(dbms_random.value(1,100))val
4  from dual
5  connect by rownum<= 10000;
Table created.

這里創建了一個測試表，共插入了10 000條記錄。其中的VAL字段為1～99之間的隨機整數。

[hf@testdb] SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt => 'for columns val size 50');
PL/SQL procedure successfully completed.

[hf@testdb] SQL> select num_distinct,num_buckets,histogram
2  from user_tab_columns
3  where table_name='T1' and column_name='VAL';
NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ ----------- ---------------
          99          50 HEIGHT BALANCED

這里收集了統計信息，注意收集統計的選型使用了“for columns val size 50”。這表示采用50個桶來收集VAL字段的統計信息。因為這個字段的不同值只有99個，因此會使用基于高度的直方圖。從后面的查詢也可以看出，這個表有99個不同的值，使用了50個桶，整個直方圖的類型為基于高度的直方圖。

[hf@testdb] SQL> select endpoint_number, endpoint_value
2  from user_tab_histograms
3  where column_name = 'VAL' and table_name = 'T1'
4  order by endpoint_number;
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0              1
              1              2
              2              4
              3              6
              4              8
              5             10
              6             12
              7             14
              8             16
              9             18
             10             19
...
             50             99
51 rows selected.

可以從user_tab_histograms視圖中，查看直方圖的具體信息。這里有兩個主要字段，其含義分別如下：

·ENDPOINT_VALUE：列的數值。該列是NUMBER類型，如果直方圖列是非數字類型則需要轉換，且只取字段的前六個字節（不是字符并記錄到數據字典中）。

·ENDPOINT_NUMBER：桶號。

如何解讀上面的輸出呢？上面輸出代表VAL＝1，占據了0號桶；VAL＝2，占據了1號桶；VAL＝3、4，占據了2號桶；VAL＝5、6占據了3號桶；以此類推。

（3）直方圖對執行計劃的影響

如果字段存在傾斜，且也分析了直方圖，則在生成執行計劃時與沒有直方圖不同。如果有直方圖，會影響計算成本中的選擇因子density。在user_tab_columns里有這樣的兩個列num_distinct和density。在計算基數時，如果沒有直方圖則基數為num_rows/num_distinct；如果有直方圖則為num_rows*density（此時的density<>1/num_distinct）。

下面通過一個案例，說明在有沒有直方圖的情況下執行計劃的不同。

構建的表中，數據嚴重不均衡，OWNER='HF'的有1條，OWNER='PUBLIC'的有3萬多條。

[hf@testdb] SQL> create table t1 as select * from dba_objects;
Table created.

[hf@testdb] SQL> create index idx_owner on t1(owner);
Index created.

[hf@testdb] SQL> select owner,count(*) from t1 group by owner;
OWNER                            COUNT(*)
------------------------------ ----------
OWBSYS_AUDIT                           12
MDSYS                                2011
HF                                      1
PUBLIC                              33996
...
24 rows selected.

收集了一個直方圖，這是一個基于頻率的直方圖。

[hf@testdb] SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade =>true,method_opt => 'for columns owner size 254');
PL/SQL procedure successfully completed.

[hf@testdb] SQL> select num_distinct,num_buckets,histogram
2  from user_tab_columns
3  where table_name='T1' and column_name='OWNER';
NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ ----------- ---------------
          24          20 FREQUENCY

對于返回較少數據的情況，例如下面的OWNER='HF'的情況，優化器選擇使用了索引掃描。

[hf@testdb] SQL> select * from t1 where owner='HF';
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     8 |   784 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID
                           | T1        |     8 |   784 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN | IDX_OWNER |     8 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

對于返回較多數據的情況，例如下面的OWNER='PUBLIC'的情況，優化器選擇使用了全表掃描。這顯然是個不錯的選擇。根據數據分布不同，選擇了更為高效的處理方式。

[hf@testdb] SQL> select * from t1 where owner='PUBLIC';
33996 rows selected.
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 33513 |  3207K|   344   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T1   | 33513 |  3207K|   344   (1)| 00:00:05 |
--------------------------------------------------------------------------

后面我們去掉了直方圖，即忽視了數據的不均衡問題。

[hf@testdb] SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade =>true,method_opt => 'for columns owner size 1');
PL/SQL procedure successfully completed.
[hf@testdb] SQL> select num_distinct,num_buckets,histogram
2  from user_tab_columns
3  where table_name='T1' and column_name='OWNER';
NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ ----------- ---------------
          24           1 NONE

在去掉了直方圖之后，查詢均使用了索引掃描的方式，即使是返回大量數據的owner='PUBLIC'的查詢。可見，這并不是一個很好的選擇。

[hf@testdb] SQL> alter system flush shared_pool;
System altered.
[hf@testdb] SQL> select * from t1 where owner='HF';
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  3595 |   344K|   105   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID
                           | T1        |  3595 |   344K|   105   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN | IDX_OWNER |  3595 |       |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------------

[hf@testdb] SQL> select * from t1 where owner='PUBLIC';
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  3595 |   344K|   105   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID
                           | T1        |  3595 |   344K|   105   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN | IDX_OWNER |  3595 |       |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------------

5.擴展統計信息

除了針對表、索引、字段外，從11g開始也提供了針對多列的統計信息。所謂多列的統計信息是指對多個存在關聯關系的列（作為一個組合列）收集的統計信息。如果查詢語句的WHERE條件中出現這個組合列所涉及的關聯列的過濾條件，則優化器在判斷時會使用多列統計信息進行估算，而不再使用原始多列組合進行估算。

下面通過一個案例說明統計信息的具體用法。

下面的代碼構造了一表t1，并插入了1萬條記錄。要注意的是，表中字段n1和n2的值完全一樣，也就是說這兩個字段是有關聯的。

[hf@testdb] SQL> create table t1 ( n1 number,n2 number);
Table created.

[hf@testdb] SQL> insert into t1 select 1,trunc(dbms_random.value(0,100)) from dba_objects where rownum<10001;
10000 rows created.

[hf@testdb] SQL> update t1 set n1=n2;
10000 rows updated.

[hf@testdb] SQL> commit;
Commit complete.

[hf@testdb] SQL> exec dbms_stats.gather_table_stats(ownname=>'HF',tabname=>'T1',cascade=>true,estimate_percent=>100);
PL/SQL procedure successfully completed.

執行了一個查詢語句，從Rows可見，其評估出的記錄數為1。這是因為優化器按照兩個字段的選擇率相乘作為整體的選擇率。因為n1和n2字段都是隨機插入的1～100之間的數據，所以單字段的選擇率為1/100，組合在一起就是1/10 000。表共有10 000條記錄，因此估算選擇出的記錄數為1條。

[hf@testdb] SQL> select * from t1 where n1=1 and n2=1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     6 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=1 AND "N2"=1)

由下頁的代碼可知，實際返回的記錄數為94，這與估計的值差異很大。原因就是優化器無法得知n1、n2兩個列的值是有關聯的。

[hf@testdb] SQL> select count(*) from t1 where n1=1 and n2=1;
COUNT(*)
----------
        94

下面的代碼創建了一個擴展統計信息，從視圖中可見，這個擴展是包含了（n1,n2）列。

[hf@testdb] SQL> declare
  2    cg_namevarchar2(30);
3  begin
  4    cg_name:=sys.dbms_stats.create_extended_stats('HF','T1','(n1,n2)');
  5    dbms_output.put_line(cg_name);
6  end;
7  /
SYS_STUBZH0IHA7K$KEBJVXO5LOHAS
PL/SQL procedure successfully completed.

[hf@testdb] SQL> select extension_name,extension from dba_stat_extensions where table_name='T1' and owner='HF';
EXTENSION_NAME                 EXTENSION
-------------------------------------------------------------------------------
SYS_STUBZH0IHA7K$KEBJVXO5LOHAS ("N1","N2")

從下面的代碼中的Rows輸出可見，估算的行數是100，這顯然是充分考慮到n1=n2的情況，且與實際的94條記錄差異不大。

[hf@testdb] SQL> exec dbms_stats.gather_table_stats(ownname=>'HF',tabname=>'T1',method_opt=>'for columns(n1,n2) size auto',estimate_percent=>100);
PL/SQL procedure successfully completed.

[hf@testdb] SQL> select * from t1 where n1=1 and n2=1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |   600 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |   600 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

6.動態采樣

隨著Oracle數據庫逐步淘汰了RBO的優化器方式，CBO成為優化器的唯一選擇。而CBO需要依賴于準確的統計信息，如果對象沒有收集統計信息，則會造成很大的問題。此時，就需要一種機制避免因為統計信息缺失可能導致的產生低效執行計劃的問題。動態采樣正是為了幫助優化器獲得盡可能多的信息，可以把它視為對象統計信息的必要補充。

一般而言，在下列情況下可能會使用動態采樣。

·當表、索引等對象缺乏統計信息的時候，優化器可采用動態采樣。

·臨時表。一般來說，臨時表沒有統計信息，多采用動態采樣的手段收集。

·對于復雜邏輯，優化器可能無法準確評估，可以采用動態采樣。

針對動態采樣，可以采用不同的層次。層次越高，其收集的信息越準確，當然其開銷也越大。在不同的數據庫版本中，其對應的動態采樣的默認層次也不一樣。如果采樣的層次設置為3，則查詢優化器通過測量樣本中記錄的選擇性來估算語句中條件的選擇性，而不是使用數據字典中的統計信息或者手工設置的值。如果采樣的層次設置為4或者更高，則除了層級的操作外，還可以動態采樣同一張表在WHERE子句中引用的兩個或者更多的字段。當字段間有關系的時候，這將非常有助于提高估算的性能。表4-1所示是動態采樣的層次及含義說明。

表4-1　動態采樣的層次及含義說明

下面通過一個例子說明動態采樣的使用。

[hf@testdb] SQL> create table t1( a int,bvarchar(100));
Table created.

[hf@testdb] SQL> insert into t1 select object_id,object_name from dba_objects;
86282 rows created.

[hf@testdb] SQL> commit;
Commit complete.
*上面創建了一個示例表，然后插入了8萬多條記錄。[hf@testdb] SQL> create index idx_t1_a on t1(a);
Index created.

[hf@testdb] SQL> select * from t1 where a=100;
--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    65 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID
                            | T1       |     1 |    65 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN  | IDX_T1_A |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)

從Note可以看出，這里使用了動態采樣，層次是2。

4.1.3　數據字典統計信息

數據字典統計信息是用來描述數據字典基表、索引等的詳細信息。這同普通表、索引等沒有什么區別。唯一的區別就是管理的方法不同，需要專門的語句進行操作，關于統計信息操作后面會詳細說明。

另外有一點需要注意，那就是從10g開始，數據字典統計信息可以自動收集。

4.1.4　內部對象統計信息

內部對象統計信息是用來描述內部表（例如X$系統表）的詳細信息。從本質來說，X$表是基于內存數據結構的。如果它的統計信息不準確，會造成低效的執行計劃。因為X$本身就是內存結構，低效的執行計劃可能會造成訪問內存結構所持有的Latch或Mutex長時間得不到釋放。如果出現大規模爭用的話，數據庫會出現CPU使用超高甚至全庫掛起的情況。

與普通對象對比，內部對象的統計信息管理方法不同。此外，如果內部對象缺少統計信息，數據庫是不會采用動態采樣機制的。一般只有在確定是內部對象統計信息不準的情況下，才額外收集它。

主站蜘蛛池模板： 正镶白旗| 宁晋县| 缙云县| 澎湖县| 凤阳县| 大厂| 电白县| 怀来县| 永丰县| 永登县| 隆子县| 金门县| 闻喜县| 江西省| 绍兴市| 巴马| 武定县| 黄骅市| 克拉玛依市| 湛江市| 兴海县| 莱芜市| 乐平市| 文安县| 虎林市| 新营市| 开阳县| 安新县| 乾安县| 乌兰县| 平谷区| 丽水市| 阿坝县| 黄骅市| 海安县| 万源市| 开鲁县| 中西区| 五常市| 七台河市| 淅川县|