[SQL Tune] Hard Parse vs. Soft Parse

Parse 是SQL在執行前的一個重要步驟，也是DBA在調整效能時的一個重要參考指標. Parse可以分為Hard-Parse和Soft-Parse，其介紹如下：

- Hard parse
當 SQL執行時，當Oracle發現在shared pool中找不到相同的SQL時，Oracle就會做Hard-Parse的動作. 他包含以下動作：
* 檢查該SQL的語法
* 檢查執行該SQL的相關權限
* 在shared pool中配置memory給該SQL.
* 做查詢轉換(Query Transformation)：當有用到Materialized View時做Table的轉換.
* 最佳化(Optimization)：就是產生 Execution Plan，這大概是最耗費CPU的動作.
* 產生執行物件（用VB的講法就是執行檔）
由上面的動作可以知道，Hard-Parse是一個昂貴的動作. 所謂昂貴就是只從Latch的使用到CPU時間的耗用等等.

- Soft parse
相較於Hard-Parse，如果該SQL在shared pool中找到相同的SQL時，Oracle就會做Soft-Parse的動作. 它只會包含前述的步驟1-2. 至於何謂相同的SQL？這時就要瞭解何謂Bind Variable?以下兩個SQL對Oracle來說就是不同的SQL.

select * from X1 where A1='1';
select * from X1 where A1='2';

使用Bind Vriable，才可以將兩句SQL的語法統一如下：

select * from X1 where A1=:X;
:X='1'

至於程式是否使用到Bind Variable則端視應用系統的架構而定，本人服務的公司使用的MES系統是3-tier，但是就是中間層，也就是Application Server並未使用Bind Variable，造成DBA在校調系統時碰到很大的困難. 因為整個系統的延展性(Scalable)很差，導因於每個SQL都被Oracle視為不同的SQL，都要做一次編譯，CPU資源的耗費可想而知.

所以Bind Variable雖然是一個簡單的觀念，卻是開發Oracle應用程式時必要的觀念！

Tom有更精闢的解說在此!

[SQL Tune] 用 Oracle Constraint 改善 Query 效能

多數人在 tune SQL 採用的方式多從 Index 或 Join的方式著手. Oracle Optimizer在決定一個 Execution Plan時會有以下的考量:

1. The query to optimize
2. All available database object statistics
3. System statistics(CPU, I/O,..)
4. Initialization parameters
5. Constraints

看到第五項嗎? 原來Constraint也是Oracle在決定Execution Plan時的考慮因素之ㄧ. Constraint對多數人而言是確保資料一致性的最後一道關卡, 但對SQL效能而言也有一定幫助, 且看以下說明:

Constraint --> Check

當SQL遇到 Check constraint時, 若 Check有限制某欄位的值而查詢條件又恰好有該值, 這時Oracle就會根據 Check的限制調整實際SQL的 Execution Plan.

實際範例可參閱Oracle 雜誌 5/6月 Asktom的文章.

http://www.oracle.com/technology/oramag/oracle/09-may/o39asktom.html

這部分在真實狀況下發生的機會較低, 因此不多說明.

Constraint --> NOT NULL

這個情況比較清楚, 就是當某欄位有Index, 但允許空值, 此時空值部份不會被存入 Index中. 但若是該欄位設定為 Not Null, 則當此類SQL(Select Count(*) from Table1 t) 時, Oracle 會很聰明的選取"Index Fast Full Scan"而不用"Full Table Scan", 效能就有一定的提升, 因為該欄位的所有值都對被存入 Index中. 有關"Index Fast Full Scan", 可參閱以下文章.

http://oracle-wei.blogspot.com/2009/03/sql-tune-index-access-methods.html

Constraint --> Primary Key/Foreign Key

這部份對效能有更顯著的提升, 因為 Oracle做的是 Table Elimination, 就是將執行 SQL所需的 Table刪除. 甚麼? 我也是第一次聽到耶! 比較實際的例子用以下說明:

Master Table --> M1 (M_Key, M_Value)

Detail Table --> D1 (M_Key)

有一個SQL如下:
select Sum(M_Value)
from M1, D1
where M1.M_Key=D1.M_Key

可能 Oracle的 Execution Plan是 "Hash Join"就是將 Master和 Detail做 Join後去得到結果.

但如果將 M_Key 加成 D1對M1的 Foreign Key, 而且 M_key為 M1的 Primary Key. 則方才 SQL的 Execution Plan可能會換成只對M1的存取. 因為以實際而言, Ｄ1 的存取並不需要, 只是 Constraint確定了這一件事情. 讓 Oracle 放手去做.

不好意思, 偷懶一下這次沒有用自己跑的實例說明, AskTom的說明應該夠清楚也有說服力.

[SQL Tune] Pivot/CrossTab SQL Query

開發商業智慧系統時, Crosstab的分析是很重要的技術, 或稱為Pivot. 將資料轉置成欄和列都有各自代表的維度, 而相對應的值就放置在中間, 如下表所示:

外銷總數量
USA China Japan
Jan 100 150 100
Feb 50 20 10
Mar 20 40 50
Apr 10 50 60
...

這對Excel而言是一件簡單的事情，因為樞紐分析大家都會，但是對關聯式資料庫而言，是一個
很大的挑戰， 因為關聯式資料庫的資料擺放型態是 Column-Value, 如下表:

月份        外銷國             數量
Jan         USA                10
Feb         China              30
....................................

這時在 Oracle怎麼解決呢? 有些人會在呈現端-Client想辦法, 不管是VB, ASP,
JSP將資料從Oracle讀進ResultSet後，聰明的程式設計師總有辦法將資料作CrossTab
的處理, 但問題是通常程式寫完後如果換做是其他人接手，應該會看不懂程式碼在寫
什麼？原因無他，因為太複雜！還有一個辦法是買 Solution, 像 Hyperion,
Oracle Discoverer, Business Object等應該都內建一些報表工具可以做CrossTab,
缺點不用說就是貴，不用個幾百萬，案子應該起不來!

如果可以在 Oracle端就將資料處理好, 再搭配前端的資料呈現工具，有一些比較簡單
的作法.常見的像是以下的SQL, 用 Decode 或是 Case, 用兩層SQL兜出想要的結果.但
是缺點呢? 一個是寫法還是有點小複雜, 如再要多個法國的外銷數量, 程式還要改.
當然後者的問題好解決，可將此邏輯寫成一個Function, 將可能的國別傳入Function後,
再透過該Function兜出SQL後執行.

=========================================================================
select Month,China,USA,Japan,China+USA+Japan
from (
select
Month,
max(case when Country='China' then qty else 0 end) China,
max(case when Country='USA' then qty else 0 end) USA,
max(case when Country='Japan' then qty else 0end)  Japan
from Sales_Value
group by Month
);
=========================================================================

OTN有一篇文章
(http://kr.forums.oracle.com/forums/thread.jspa?messageID=2487130)
就是談這一段，下面是節錄自該文章的Source Code. 原則上就是將要做
CrossTab的相關資訊傳入後，將SQL組合出來，有些語法會有點看不懂，因為程式裡
兜出來的SQL是用11G最新的Pivot SQL.
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
create or replace function getPivotSql(

p_sql            in varchar2,

p_x_col          in varchar2,

p_x_col_type     in varchar2 default 'DATE',

p_x_col_start    in varchar2,

p_x_col_interval in varchar2,

p_x_col_int_unit in varchar2 default 'DAY',

p_x_col_count    in number,

p_y_col          in varchar2,

p_cell_col       in varchar2,

p_cell_col_aggr  in varchar2 default NULL

) return varchar2

is

v_sql varchar2(32767);

begin

v_sql := '';

v_sql := v_sql || 'with data as ('||chr(10);

v_sql := v_sql || '  '||p_sql||chr(10);

v_sql := v_sql || ')';

if p_x_col_type = 'VARCHAR2' then

  v_sql := v_sql ||', x_dist_values as ('||chr(10);

  v_sql := v_sql ||'  select distinct '||p_x_col||' val from data order by 1'||chr(10);

  v_sql := v_sql ||'), x_values_rownum as ('||chr(10);

  v_sql := v_sql ||'  select rownum zeile, val from x_dist_values where rownum <= '||p_x_col_count||chr(10);     v_sql := v_sql ||')'||chr(10);   else     v_sql := v_sql || chr(10);   end if;   v_sql := v_sql || 'select distinct '||chr(10);   v_sql := v_sql || '  data.'||p_y_col||','||chr(10);   for i in 1..p_x_col_count loop     if p_cell_col_aggr is not null then       v_sql := v_sql || p_cell_col_aggr||'(';     end if;     v_sql := v_sql || '  case when ';     if p_x_col_type = 'VARCHAR2' then       v_sql := v_sql || 'x.zeile = '||i;     elsif p_x_col_type = 'NUMBER' then       v_sql := v_sql || ' data.'||p_x_col||' between '||         p_x_col_start||' + ('||(i - 1)||' * '||         p_x_col_interval || ') and '||p_x_col_start||' + ('||         i||' * '||p_x_col_interval||') ';     elsif p_x_col_type = 'DATE' then       v_sql := v_sql || ' data.'||p_x_col||' between '||        p_x_col_start||' + interval '''||((i - 1) * p_x_col_interval )||        ''' '||p_x_col_int_unit||' and '||p_x_col_start||        ' + interval '''||i * p_x_col_interval ||''' '||p_x_col_int_unit;     end if;     v_sql := v_sql ||' then '||p_cell_col|| ' else null end';     if p_cell_col_aggr is not null then       v_sql := v_sql || ')';     end if;     v_sql := v_sql || ' as "VALUE"';     if i < p_x_col_type =" 'VARCHAR2'">
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

下面就來細究Pivot SQL的語法 吧！將前面介紹的第一種方式改寫成新版本會向如下!
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
select * from (
select Month, Country
from Sales_Value t
)
pivot
(
count(Country)
for Month in ('USA','China','Japan')
)
order by Month;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
看起來，簡單多了吧！ 結合起第二步驟的Function，CrossTab是不是變成比較簡單有條理
而且彈性變大了？ 不用花錢買BI Solution也可以讓公司使用者享有Excel樞紐分析的功能
喔！

OTN原文如下：
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html

[SQL Tune] 評估 Index 存取方式(Access Methods)

Index 的建立不論是B-tree, Bitmap還是 function-based index, 都是加快資料存取的手法. 簡單的說, Index 有點像是建立資料的捷徑, 而這個捷徑就是RowID.所以 Index存取的目的就是去蒐集RowID, 藉此捷徑快速的取得資料.

常見的Index存取有以下幾個方式

• Index Range Scan
  這是最常見的存取方式, 從以下SQL可以清楚瞭解.
  
  select
  employee_name
  from employee
  where Birth_date >sysdate-100;
  
  Birth_date這個欄位若有建立Index, Oracle透過 B-Tree Index 找到Rowid後, 即可快速找到資料.
  
  
• Fast Full-index Scan
  Full index scan 乍聽之下會不清楚Oracle在搞甚麼? 和 Full Table Scan有何不同? 其實是因為有些SQL只需要Index的資料根本不需要碰到 Table的資料, 像 Count(*), 如以下SQL.
  
  select distinct country, count(*)
  from employee
  group by country;
  
  如同 Full-Table Scan, Fast Full-Index Scan也會參考 db_file_multiblock_read_count的參數. 也就是當Fast Full-Index Scan發生時, Oracle也會一次根據此參數設定讀取多個Block.至於 Oracle 是否執行 Fast Full-Index Scan, 前提是 所有在 select 和 where中指定的欄位都 必須存在於 Index中.另外必須有多於 10%回傳的資料是位於Index中, Optimizer才會選擇使 用. 也可以用這個 Hint(/*+ index_ffs() /*)去強迫 Oracle執行Fast Full-Index Scan.
  

• Index Full Scan
  通常發生在Optimizer認為SQL回傳的結果會按照Index排序，也就是有 'Order By'的指令. 它會用 Temp space料對Index做排序. 和 Fast Full-Index Scan不同的是, Fast Full-Index Scan是對整個Index做Scan並不會做排序. Index Full Scan使用的是DB Sequential Read而Fast Full-Index Scan使用的是DB Scattered Read.
  

參考資料
http://www.oracle-training.cc/oracle_tips_index_access.htm
http://www.dbanotes.net/Oracle/Index_full_scan_vs_index_fast_full_scan.htm

[SQL Tune] 新工具(SPM)- 避免 SQL Execution Plan 迷路

很多Oracle的使用者都曾經經歷過以下痛苦經驗:

• Database升級完之後，某些SQL突然變的慢得不行.
• Table加了Partition後，本來跑3秒的SQL變成30分鍾也跑不完.

諸如此類不勝枚舉，大致和系統改變有關. Oracle optimizer是Oracle得以強過其他關連式資料庫的利器，但也因為它太過強大, 太過聰明，偶而會秀逗. 當然SQL的Execution plan跑掉不見得是壞事，因為大部分可能變得比較好，例如，資料內容有大幅變動後反映在Statistics上時，這時Execution Plan當然要跟著改變. 這種改變可能是好的改變. 但是如果不是，那就不好了，正式環境的SQL那裡會允許SQL的效能一下子掉得天差地遠. 所以針對Optimizer對Execution Plan的改變，當然只能接受變好不能變差.

在11G之前, 管理 Execution plan的方式是用 stored outline或 SQL profile. 但是這兩個工具相對對DBA而言, 比較需要手動的介入. 相對而言, SPM是smart得多了.

Oracle瞭解大家的痛苦，在11g 出現了一個新功能叫做：SQL Plan Management (SPM)，SPM允許使用者針對指定SQL維持一個穩定的效能. 有了SPM後，SQL變成 'Managed SQL' . 所謂'Managed SQL'就是說SPM會針對'Managed SQL'去偵測Execution Plan的改變，為了這個目的，SPM會維護所有'Managed SQL'的Execution Plan歷史紀錄. 這時又有一個 'SPM aware optimizer'負責存取，使用和管理SQL Management Base (SMB)的資訊.

而SMB是負責儲存一組被接受的Plan，而何謂可接受(Accepted)當然要透過SPM去判斷，確定效能沒有問題才能加入SMB. 這樣大概可以瞭解，SPM就是透過將特定SQL的所有Execution Plan儲存起來後，在執行階段去判斷哪一個Plan才是效能最好的. 這時若有一組新的Plan產生，就不會被 'SPM aware optimizer'所考慮因為他還沒有機會進入SMB當中.

下面這個圖說明3個SQL的Plan歷史如何被儲存和被SPM使用.

再來就是要維護一個正確的SMB，有以下幾個方式. 基本上，Oracle在SPM啟動後，它會自動偵測重複性(Repeated)的SQL，將其Execution plan記錄下來，並決定接受與否. 至於非重複性也就是Ad-hoc的SQL就不會被紀錄.

• 自動偵測： 將參數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES設為True，此時所有SQL的Plan歷史會記錄Optimizer執行時的相關資訊，例如SQL本身，SQL compile時的環境等. 記錄的資訊未來就可以用來重新產生Execution Plan. 所有該SQL執行產生Plan的歷史紀錄都會被紀錄下來，如果效能被認可，就會被視為可接受.
• 手動偵測：如果有使用SQL Tuning Set (STS)，則SPM也可以手動更新SMB使用以下功能 ： dbms_spm.load_plans_from_sqlset 去指定特定的SQL給SMB.
• 手動偵測：將Cursor cache手動更新SMB，使用以下功能:dbms_spm.load_plans_from_cursor_cache

在SMB建立之後, 每一個SQL在執行之後, 當Optimizer產生一個新的 Execution plan後, 就會和SMB裡面的 Execution plan作比較, 如果有一樣的就直接採用. 如果沒有, 則在SMB裡找一個Cost最低的Execution plan來執行. 而針對未被使用的Execution plan則會被放置到Un-accepted區域. 一直到未來也許環境改變後, 該Execution plan有機會被選取.

至於Execution plan被記錄下來後, 如何管理這裡被記錄且驗證過的Execution plan. Oracle 提供了package DBMS_SPM和 view dba_sql_plan_baseline 供DBA管理之用.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optplanmgmt.htm

[SQL Tune] Star Transformation

Star transformation 是在EDW(Enterprise Data Warehouse)中用來Join Table的常見方式. Star Transformation 和傳統的 Nested Loop/Hash Join/Merge Join 在運算邏輯上有很大的差異. 簡單的說, Star Transformation 利用 Bitmap Index 將 Dimension Table先做Join和Filter後在去存取 Fact Table, 藉以大量的減少IO.

但如同 Oracle 的很多功能, 在使用這些功能前, 相關的需求和系統設定, 也必須先研究清楚以避免執行時出現問題, 等程式上線後再踩到地雷可別怪ORACLE沒有先跟你講喔!

0. 準備工作
-- Fact Table
-- 此 Table 筆數大約是220,000筆
CREATE TABLE STAR_FACT
(
QTY Number(6),
Package Varchar2(40),
Customer VARCHAR2(20 BYTE),
Stage VARCHAR2(25 BYTE) NOT NULL
);

-- Dimension Table 1
-- 此 Table 筆數大約是30筆
CREATE TABLE STAR_DIM_1
(
Stage VARCHAR2(20 BYTE),
section VARCHAR2(25 BYTE) NOT NULL
);
-- Dimension Table 2
-- 此 Table 筆數大約是300筆
CREATE TABLE STAR_DIM_2
(
Cus_code VARCHAR2(20 BYTE),
Cus_locationVARCHAR2(25 BYTE) NOT NULL
);
-- Dimension Table 3
-- 此 Table 筆數大約是300筆
CREATE TABLE STAR_DIM_3
(
Pkg_PkgcodeVARCHAR2(20 BYTE),
Pkg_Lead_CountVARCHAR2(25 BYTE) NOT NULL
);
-- Gather statistics
exec dbms_stats.gather_table_stats('RPT','Star_Fact');
exec dbms_stats.gather_table_stats('RPT','Star_DIM_1');
exec dbms_stats.gather_table_stats('RPT','Star_DIM_2');
exec dbms_stats.gather_table_stats('RPT','Star_DIM_3');

-- Add constraint/Primary key at Dimension Table
alter table RPT.Star_Dim_1 add constraint Star_dim_1_pk primary key (Stage);
alter table RPT.Star_Dim_2 add constraint Star_Dim_2_pk primary key (Cus_Code);
alter table RPT.Star_Dim_3 add constraint Star_Dim_3_pk primary key (Pkg_Pkgcode);

-- Create Bitmap index on dimension key at Fact Table
create bitmap index Star_Fact_Dim_1 on RPT.Star_Fact(Stage);
create bitmap index Star_Fact_Dim_2 on RPT.Star_Fact(Customer);
create bitmap index Star_Fact_Dim_3 on RPT.Star_Fact(Package);

-- Analyze Table & Index
analyze table Star_Fact compute statistics for table for all indexes for all indexed columns;
analyze table Star_DIM_1 compute statistics for table for all indexes for all indexed columns;
analyze table Star_DIM_2 compute statistics for table for all indexes for all indexed columns;
analyze table Star_DIM_3 compute statistics for table for all indexes for all indexed columns;

1. 使用 非Star Transformations

* 我們就先用非 Star Transformation看看Oracle 會用什麼方是去處理?
先將用以下指令確認Oracle 不會使用 Star Transformation
alter session set star_transformation_enabled='false';

* 接下來執行以下SQL, 此SQL從客戶, 產品和站點中指定一些屬性在去Fact Table中計算數量.


select D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE,sum(F.qty)
from star_fact F, star_DIm_1 D1, star_DIM_2 D2, star_DIM_3 D3
where F.customer=D2.cus_code
and F.stage=D1.stage
and F.package=D3.PKG_PKGCODE
and D1.section='F'
and D3.PKG_LEAD_COUNT=96
group by D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE ;


* 執行結果如下


-- Execution Plan
Plan
SELECT STATEMENT CHOOSE Cost: 131 Bytes: 636 Cardinality: 12
8 SORT GROUP BY Cost: 131 Bytes: 636 Cardinality: 12
7 HASH JOIN Cost: 120 Bytes: 77,592 Cardinality: 1,464
1 TABLE ACCESS FULL RPT.STAR_DIM_2 Cost: 2 Bytes: 5,915 Cardinality: 455
6 HASH JOIN Cost: 117 Bytes: 58,560 Cardinality: 1,464
2 TABLE ACCESS FULL RPT.STAR_DIM_3 Cost: 2 Bytes: 60 Cardinality: 6
5 HASH JOIN Cost: 114 Bytes: 1,558,890 Cardinality: 51,963
3 TABLE ACCESS FULL RPT.STAR_DIM_1 Cost: 2 Bytes: 40 Cardinality: 5
4 TABLE ACCESS FULL RPT.STAR_FACT Cost: 108 Bytes: 4,836,590 Cardinality: 219,845

-- AutoTrace
Description Value
sorts (memory) 3
sorts (disk) 0
redo size 0
recursive calls 0
physical reads 509
db block gets 0
consistent gets 1104
bytes sent via SQL*Net to client 1122
bytes received via SQL*Net from client 1036
SQL*Net roundtrips to/from client 4

從結果來看, 最終的筆數是6筆, Consistent Get 是 1104, 大概花了兩秒鍾. 而看看Execution Plan, 它用了三個 Hash Join, 先從Star_Dim_1去連接 Star_Fact, 後再接續連接 Star_Dim_2, Star_Dim3. 沒有意外, 這是正常 Oracle 處理此類SQL的做法.

2. Star Transformation 的原理和步驟

如前文所說, Star Transformation 可以有效的將 Dimension Table先做Join和Filter後再去存取 Fact Table, 其步驟如下:

* 為每一個 Dimension Table建立 Fact Table的 RowID的列表

- 先根據查詢的限制條件在每個 Dimension Table得到RowID列表.

- 利用前一步驟得到列表透過 Bitmap Index(Fact Table)去存取的Fact Table, 再將得到的
ROWID存成列表.

- 再來做的就是合併的動作. 假設SQL包含三個 Dimension key, 就重複前兩個步驟三次後,
將三個結果的 RowID作合併的動作.

* 執行過濾的動作, 所謂過濾就是說從前述合併的結果中判讀所有條件都成立的RowID,所謂所有條件就是說符合三的條件的資料.
* 將前述步驟的 RowID得到實際的資料.
* 在連接回 Dimension Table去得到SQL要的一些屬性值 .
* 對結果做加總.


3. Star Transformation 的實例演練

* 先將用以下指令確認Oracle 使用 Star Transformation

alter session set star_transformation_enabled='false';

* 執行同樣的SQL

select D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE,sum(F.qty)
from star_fact F, star_DIm_1 D1, star_DIM_2 D2, star_DIM_3 D3
where F.customer=D2.cus_code
and F.stage=D1.stage
and F.package=D3.PKG_PKGCODE
and D1.section='F'
and D3.PKG_LEAD_COUNT=96
group by D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE ;


-- Execution Plan
Plan
SELECT STATEMENT CHOOSE Cost: 54 Bytes: 53 Cardinality: 1
18 SORT GROUP BY Cost: 54 Bytes: 53 Cardinality: 1
17 HASH JOIN Cost: 53 Bytes: 53 Cardinality: 1
15 HASH JOIN Cost: 50 Bytes: 40 Cardinality: 1
13 HASH JOIN Cost: 47 Bytes: 1,320 Cardinality: 44
1 TABLE ACCESS FULL RPT.STAR_DIM_1 Cost: 2 Bytes: 40 Cardinality: 5
12 TABLE ACCESS BY INDEX ROWID RPT.STAR_FACT Cost: 43 Bytes: 4,092 Cardinality: 186
11 BITMAP CONVERSION TO ROWIDS
10 BITMAP AND
5 BITMAP MERGE
4 BITMAP KEY ITERATION
2 TABLE ACCESS FULL RPT.STAR_DIM_3 Cost: 2 Bytes: 60 Cardinality: 6
3 BITMAP INDEX RANGE SCAN RPT.STAR_FACT_03
9 BITMAP MERGE
8 BITMAP KEY ITERATION
6 TABLE ACCESS FULL RPT.STAR_DIM_1 Cost: 2 Bytes: 40 Cardinality: 5
7 BITMAP INDEX RANGE SCAN RPT.STAR_FACT_02
14 TABLE ACCESS FULL RPT.STAR_DIM_3 Cost: 2 Bytes: 60 Cardinality: 6
16 TABLE ACCESS FULL RPT.STAR_DIM_2 Cost: 2 Bytes: 5,915 Cardinality: 455

-- AutoTrace
Description Value
sorts (memory) 2
sorts (disk) 0
redo size 0
recursive calls 0
physical reads 0
db block gets 0
consistent gets 0
bytes sent via SQL*Net to client 695
bytes received via SQL*Net from client 734
SQL*Net roundtrips to/from client 3

從結果來看, 最終的筆數是6筆, Consistent Get 是 695,比前例少了一半 時間也是. 而看看Execution Plan, 如同前文所說, 它將Dimension Table 儘可能先用 Bitmap Index 串聯後, 在去存取 Fact Table. 從 Consistent Get 大幅下降就知道效果相當明顯.

4. Star Transformation 的需求

* Fact & Dimensional Table建立 Index. 通常來說, Fact Table上的Diemension key會建立Bitmap Index. 不像前述的 Nested Loop/Hash Join/Merge Join 一次只會使用單一Index(同一 Table), Star Transformation 會儘可能使用所有可用的 Index.
* Fact & Dimensional Table的 primary & foreign key關係, 這不是必要 , 但是建議要有. 針對資料量較大的 Table, 建立 primary & foreign key關係對資料 Load一定會有影響, 要自行斟酌影響程度做適當動作(如 Load 資料時, disable primary & foreign key關係)
* Database 設定
- Metalink 有一些 Star Transformation 的 Bug 說明. 這一點倒是蠻令人失望, 因為一堆 Oracle的新功能總是隱藏一堆BUG, 而這些BUG都要歷經一段時間被發現後, 在待 Oracle原廠出 Patch解決.
- 設定參數: Star_Transformation_Enabled, 設為 True後, optimizer才會考慮 Star Transformation.
- 設定參數: Hash_Join_Enabled, Hash Join會是 Star Transformation常用的 Join.
- 調整 PGA 參數讓 Star Transformation 儘量執行在 PGA 中.
* 還有一個參數 alter session set "_always_star_transformation"=true; 是 Oracle 的內部參數, 是要 Oracle Support 建議才能使用, 如果 Optimizer 不聽話堅持不run Star Transformation, 可以試試此參數.
* 總之, Star Transformation 是做 EDW一定要具備的技巧, 有這個工具, 許多大量資料存取的SQL變得簡單. 當然水可以載舟亦可覆舟, 在使用任何新功能別忘了周全的驗證一定是需要的.


參考文件

http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/schemas.htm
So How Do Star Transformations Actually Works

[SQL Tune] 三個常見的JOIN簡介

談到SQL tune大概不能不談Join. 開發人員若在SQL語法中用了Join, 對ExecutionPlan中TABLE如何被Join一定要有基本的認識和瞭解, 否則Tune就只是空談而已. 本文就會針對Join的三種方式作一些介紹.

實例演練

我用的DATABASE是 Oracle XE 10G.

-- 以 HR 連線

Connect hr/hr;

-- 建立一個 Driving table

create table Driving_Emp as select * from EMPLOYEES;

-- 建立一個 Driven table
create table Driven_Dept as select * from DEPARTMENTS;

-- 建立 Index

create index e_deptno on Driven_Dept(DEPARTMENT_ID);
-- 收集相關 Statistics
exec dbms_stats.gather_table_stats('HR','Driving_Emp');

exec dbms_stats.gather_table_stats('HR','Driven_Dept');

1.Nested loop

這大概是最常見的Join方式, 以Join兩個TABLE為例, 基本上他的運作方式就像用VB寫兩個迴圈,各自抓取一個TABLE,一個是Driving TABLE(驅動端)一個是Driven TABLE(被驅動端). 前者是外迴圈後者是內迴圈. 如以下程式範例:

寫成SQL會是這樣:

Select Driving.*, Driven.* from Driving, Driven where Driving.col1=Driven.col2;

若寫成PL/SQL 應該會像這樣:

For i in (select * from Driving)

loop
For j in (select * from Driven where col2=i.col1)
loop
Display results;
End loop;

End loop;

這時ORACLE內部會做的是:

a) 找出外迴圈的driving table

b) 找出內迴圈的 driven table 指定給外迴圈

c) 根據每次外迴圈取得的資料, 連接內迴圈去取得相關資料.

使用時機

• 外迴圈最好有一些條件去限制回傳的筆數, 若外迴圈的筆數很多則Nested Loop的Cost可想 而知會相當高.
  
• 內迴圈連接外迴圈的欄位(Col2)最好有合適的INDEX, 否則內迴圈用Full Table Scan對效率可能會有影響.
• 原則上, Hash Join 的效率會較 Nested Loop 來得好.

實例演練
--利用以下指令虛擬出會產生 Nested Loop的資料筆數. 還蠻有趣的, 當筆數資料一調整就可以看到 Execution Plan從 Nested Loop 和 Hash Join之間跳來跳去.
exec dbms_stats.set_table_stats(ownname => 'HR', tabname => 'Driven_Dept', numrows => 40,numblks => 100 , avgrlen => 124);

exec dbms_stats.set_table_stats(ownname => 'HR', tabname => 'Driving_Emp', numrows => 100, numblks => 100, avgrlen => 124);

exec dbms_stats.set_index_stats(ownname => 'HR', indname => 'E_DEPTNO', numrows => 100, numlblks => 10);

-- Run 看看以下SQL
select
e.EMPLOYEE_ID,d.DEPARTMENT_NAME from Driving_Emp e, Driven_Dept d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID;

-- 果然跑出 Nested Loop
Plan
SELECT STATEMENT ALL_ROWSCost: 41 Bytes: 13,320 Cardinality: 360
4 TABLE ACCESS BY INDEX ROWID TABLE HR.DRIVEN_DEPT Cost: 1 Bytes: 120 Cardinality: 4
3 NESTED LOOPS Cost: 41 Bytes: 13,320 Cardinality: 360
1 TABLE ACCESS FULL TABLE HR.DRIVING_EMP Cost: 29 Bytes: 700 Cardinality: 100
2 INDEX RANGE SCAN INDEX HR.E_DEPTNO Cost: 0 Cardinality: 9

2. Hash join

Hash joins 用來串接較大的TABLE, 可以把它想成改良版的Nested Loop, 有人稱它為Blocked Nested Loop, 可參閱 Wiki.

他的作法可分為兩個階段, 如下,

1. 建置 - Build : 建立一個 in-memory 的 hash table, 先將 Driving table 的一部分資料讀進Hash table(若 Memory 足夠則放在Memory, 若不夠則放在 Temporary space)透過 Hash Function 計算 Join Key. Hash的 Memory是屬於 Private memory, 也就是這個SQL所獨享的, 不像其他 Logic I/O 會用到 Latch. 相對於 Nested Loop 整個 Logic I/O 會大幅下降, 這也是 Hash Join 會優於 Nested Loop的原因之ㄧ.
   
2. 偵測 - Probe : 偵測第一階段的hash table的hash value去掃瞄Driven table, 若得到Output則輸出結果.
   

這時要看DBA 設定 Hash Join的相關參數, 若Memory設定沒有大到足以一次驟完成上述步驟, 則ORACLE會分多次完成. 和Nested Loop的差異在於前者是每次的外迴圈都要連接一次內迴圈而後者則是Hash Table讀滿後才連結一次內迴圈. 相較之下對CPU的需求也較高.

使用時機

• Driving table筆數少而Driven table 筆數多時, 此方式會較Nested Loop 來得有效率.
• 若是SQL的結果是要呈現在分頁的REPORT上面, Nested Loop 會好一些. 若是呈現全部的結果則 Hash Join 會強一些.

實例演練

-- 適著調大一下筆數囉
exec dbms_stats.set_table_stats(ownname => 'HR', tabname => 'Driving_Emp', numrows => 100000000, numblks => 100000, avgrlen => 124);

exec dbms_stats.set_index_stats(ownname => 'HR', indname => 'E_DEPTNO', numrows => 100000000, numlblks => 10000);

exec dbms_stats.set_table_stats(ownname => 'HR', tabname => 'Driven_Dept', numrows => 100000000,numblks => 10000 , avgrlen => 124);

-- Run SQL
select e.EMPLOYEE_ID,d.DEPARTMENT_NAME from Driving_Emp e, Driven_Dept d where e.DEPARTMENT_ID=d.DEPARTMENT_ID;

-- 變成 Hash Join啦
Plan
SELECT STATEMENT ALL_ROWSCost: 15,127,941,348 Bytes: 33,636,363,300,000,000 Cardinality: 909,090,900,000,000
3 HASH JOIN Cost: 15,127,941,348 Bytes: 33,636,363,300,000,000 Cardinality: 909,090,900,000,000
1 TABLE ACCESS FULL TABLE HR.DRIVING_EMP Cost: 33,028 Bytes: 700,000,000 Cardinality: 100,000,000
2 TABLE ACCESS FULL TABLE HR.DRIVEN_DEPT Cost: 5,551 Bytes: 3,000,000,000 Cardinality: 100,000,000

3. Sort merge join

Driving/Driven Table的觀唸到 Sort Merge Join 就不復見了. 原則上它的作法就是將兩個TABLE的資料各自排序後做合併的動作.原則上整個動作可以分為兩個部份.

• 排序 (Sort join operation)

從兩個TABLE中將資料各自做排序.

• 合併 (Merge join operation)

將排序完後的結果合併並篩選出結果.

使用時機

大概只有在join大概只有在join條件用到不等於(<, <=, >=, <>)時, 有機會期望用到此方式. 原則上,這是一個吃IO很重的動作.

實例演練

-- 如前所說 Merge Join常出現於'不等於', 那就來個不等於吧.

select *
from Driving_Emp e, Driving_Emp f
where E.EMPLOYEE_ID<>F.EMPLOYEE_ID
and E.HIRE_DATE<=F.hire_date

--是不是變成 Merge Join

Plan
SELECT STATEMENT ALL_ROWSCost: 11,824 Bytes: 71,327,102,832 Cardinality: 495,327,103
6 MERGE JOIN Cost: 11,824 Bytes: 71,327,102,832 Cardinality: 495,327,103
2 SORT JOIN Cost: 1,753 Bytes: 7,200,000 Cardinality: 100,000
1 TABLE ACCESS FULL TABLE HR.DRIVING_EMP Cost: 35 Bytes: 7,200,000 Cardinality: 100,000
5 FILTER
4 SORT JOIN Cost: 1,753 Bytes: 7,200,000 Cardinality: 100,000
3 TABLE ACCESS FULL TABLE HR.DRIVING_EMP Cost: 35 Bytes: 7,200,000 Cardinality: 100,000

相關連結

1.Expert one-on-one Oracle

2.Nested loops, Hash join and Sort Merge joins – difference?(Sachin Arora )

3. Wiki (Merge Join, Hash Join)