ORACLE資料分析及動態採樣

oracle資料庫效能調優最需要重視的也最常遇到的就是SQL執行效率，而反映SQL效率最直觀的工具就是CBO產生的執行計畫，那麼如何讓CBO產生最精準的效率最高的執行計畫成為我們當前需要研究的課題。同一條語句，好的執行計畫能帶來飛一樣的速度，壞的執行計畫讓我們痛苦不堪，下面我們從原理到實踐來把如何產生高效計劃的方法教給大家。

一  CBO介紹

CBO全稱叫Cost Based optimization基於代價最佳化器，它是一個數學模型，同一個SQL語句在不同的oracle版本中計算出來的代價結果也是不一樣的，因為每個版本CBO最佳化器的設計結構有很大不同，現在還不是很完善很智能很通人性，因此我們不能完全依賴它，只能輔助我們。

如何產生精確的執行計畫：公式資料+CBO=執行計畫，傳入CBO的資料越精確得到結果越精確，我們能做的保證輸入資料更準確，通過精確資料計算出精確執行計畫

二示範一個表分析後執行計畫比動態採樣更準確的例子

動態採樣：顧名思義就是oracle自動為你進行的初步資料分析，由於是隨機在表上取一些資料，因此並不能保證得出的執行計畫很準確，只能作為一種輔助分析手段，在不得已的情況下來分析資料，有一定的局限性。

情境：當表沒有分析資訊時，oracle會使用動態採樣技術，而且動態採樣是在SQL硬解析的時候發生的，傳入->CBO參數->產生執行計畫。

層級：oracle 10g   oracle 11g預設動態採樣層級是2，它有level1-10，設定的層級越高採集的資料區塊越多，結果越精確，已耗用時間越長，level10對所有資料進行採樣分析。

實驗

LEO1@LEO1> drop table leo1 purge;清理環境

Table dropped.

LEO1@LEO1> drop table leo2 purge;

Table dropped.

LEO1@LEO1> create table leo1 as select * from dba_objects;建立leo1表

Table created.

LEO1@LEO1> create table leo2 as select * from leo1;建立leo2表，採用leo1一樣資料和結構

Table created.

LEO1@LEO1> col segment_name for a10

LEO1@LEO1> select segment_name,extents,blocks from dba_segments where segment_name in ('LEO1','LEO2');

SEGMENT_NA    EXTENTS     BLOCKS

---------- ---------- -------------- -------------- ----

LEO1               24       1152

LEO2               24       1152

查詢leo1和leo2表這兩個段Object Storage Service參數，都是佔用24個區，1152個塊，2個表一模一樣嘛

LEO1@LEO1> col table_name for a10

LEO1@LEO1> select table_name,num_rows,blocks,status from dba_tables wheretable_name in ('LEO1','LEO2');

TABLE_NAME   NUM_ROWS     BLOCKS STATUS

---------- ---------- ---------- ------------ ---------- ------------

LEO1                             VALID

LEO2                             VALID

在這個資料字典裡只顯示表名和目前狀態（有效），沒有行資訊和塊資訊，這是為神馬呢，嗯從上面的操作可以看出，我們只是建立了表，但沒有分析表統計資訊，現在我們分析一下後看看效果

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO1');對LEO1表進行統計分析

PL/SQL procedure successfully completed.

LEO1@LEO1> select table_name,num_rows,blocks,status from dba_tables wheretable_name in ('LEO1','LEO2');

TABLE_NAME   NUM_ROWS     BLOCKS STATUS

---------- ---------- ---------- ------------ ---------- ------------

LEO1                    71968                 1051      VALID

LEO2                                                              VALID

現在leo1表已經有行資訊和塊資訊了，leo2由於沒有進行表分析現在還什麼都沒有

LEO1@LEO1> set autotrace trace exp

LEO1@LEO1> select * from leo1 where object_id=10000;

Execution Plan

----------------------------------------------------------

Plan hash value: 2716644435

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------