[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的說明應該夠清楚也有說服力.

[EDW] Oracle 購併 SUN 對商業智慧&資料倉儲的影響

SUN的購併案在確定由Oracle接收而非IBM後, 市場一片譁然. 多數人還不清楚Oracle為何選擇出手, 因為以硬體而言, 多數的Oracle平台都在HP或IBM的機器，低階的也許是Linux，但是SUN的主力卻是Solaris（Sparc). 既然硬體機會不大，比較可能的著眼點應該就是SUN的Java技術，大家可別忘了SUN才是Java的原創者，雖然目前市場上的Java應用反而以IBM，Oracle或其他應用為主.

Oracle的商業智慧應用有像純以Java開發的Discoverer, 部分應用是用C++和.Net，甚或Hyperion的一些技術. 所以可以想見未來的Oracle商業智慧會越來越往Java平台靠攏和Oracle的WebLogic整合. 還有一個可能是，別忘了，SUN才剛將MySQL納入其麾下，在併入Oracle後，也許一些低階的商業智慧需求，Oracle可以透過MySQL來攻打市場. MySQL的市佔率可不容小覷，因為一堆個人網站因為成本考量，採用MySQL的比率很高.

原文引用： http://www.rittmanmead.com/2009/04/20/oracle-buys-sun-what-does-it-mean-for-oracle-bidw/

[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

[Oracle News] Oracle 有趣的東東

幾個和Oracle相關而且有趣的東東和大家分享. Firefox兼Oracle的喜愛者有福了.

• Oracle文件搜尋的Firefox plugin, 可以在Firefox上新增搜尋Oracle文件的搜尋引擎
• Oracle 新聞搜尋, 可以讓你隨時了解Oracle的最新新聞.
• Oracle Bookmarklets, 這是一個Javascript, 只要將網址加入Bookmark後, 連結該網址就可以搜尋文件.
• 測試你的 Oracle功力的小遊戲, 看看你過幾關?
  

[PL/SQL] Oracle SQL 批次Update，如何確保完整性?

Oracle SQL 批次Update如何確保完整性，對很多人來說是一件很麻煩的事情.當一個Update指令失敗，程式會立即跳離並Rollback已經更新的資料. 但多數的狀況是，造成失敗的資料只是上千筆資料中的一兩筆而已，其他沒有問題的更新應該還是要繼續執行. 這時要如何處理呢？Oracle magazine 3-4月號提供了三個Solution供大家參考. 大家看看吧！

1. Nested Block: 這是一般人做常用的作法，就是將Update指令寫成Cursor，一筆一筆擷取處理 後再做單筆的更新，並將整個過程用Begin-Exception-End包裹起來，當有Update失敗的情形 時用Exception處理，以確保後續的更新得以繼續進行. 這樣的好處是邏輯很清楚，有問題時很好處理但是問題來啦，當資料量大時，效能是必有問題. 另一個好處是多數的Oracle版本都可支援此作法.

2. ForAll Save Exceptions: 這個作法就是針對第一個方式的效能問題作改善.
利用 Bulk Collect Into的功能將資料從Cursor放置入變數後， 再做批次資料更新. 更新時多一個 'Save Exceptions'的指令，這時程式會將過程中的更新錯誤資料存入於宣告區的Exception變數中，之後再將其存入Table以供後續Trouble shooting之用. 使用'Save Exceptions'指令，Oracle會將已執行的DML Rollback並將錯誤訊息存入SQL％Bulk_Exceptions中，並繼續執行下一個指令. 但是這時被Rollback的資料會包含同一個Statement已經執行的Update.
這一段比較複雜，節錄原文裡的部分Source code以供說明.
從Code裡面看到作者是用他自行開發的QEM(PL/SQL Error Management Framework) 來記錄Exception中的詳細資訊.

======================================================================
CREATE OR REPLACE PROCEDURE change_salary_for (
dept_in IN employees.department_id%TYPE
, pct_increase_in IN NUMBER
, fetch_limit_in IN PLS_INTEGER
)
IS
bulk_errors exception; PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

CURSOR employees_cur
IS
SELECT employee_id, salary
FROM employees
WHERE department_id = dept_in;

TYPE employee_tt
IS
TABLE OF employees.employee_id%TYPE
INDEX BY BINARY_INTEGER;

employee_ids employee_tt;

TYPE salary_tt
IS
TABLE OF employees.salary%TYPE
INDEX BY BINARY_INTEGER;

salaries salary_tt;
BEGIN
OPEN employees_cur;

LOOP
FETCH employees_cur
BULK COLLECT INTO employee_ids, salaries
LIMIT fetch_limit_in;

FOR indx IN 1 .. employee_ids.COUNT
LOOP
salaries (indx) := compensation_rules.adjusted_compensation (
employee_id_in => employee_ids (indx)
, pct_increase_in => pct_increase_in
);
END LOOP;

FORALL indx IN 1 .. employee_ids.COUNT
SAVE EXCEPTIONS
UPDATE employees
SET salary = salaries (indx)
WHERE employee_id = employee_ids (indx);
EXIT WHEN employees_cur%NOTFOUND;
END LOOP;

EXCEPTION
WHEN bulk_errors
THEN
FOR indx IN 1 .. sql%BULK_EXCEPTIONS.COUNT
LOOP
q$error_manager.register_error (
error_code_in => sql%BULK_EXCEPTIONS (indx).ERROR_CODE
, name1_in => 'EMPLOYEEE_ID'
, value1_in => employee_ids (sql%BULK_EXCEPTIONS (indx).ERROR_INDEX)
, name2_in => 'PCT_INCREASE'
, value2_in => pct_increase_in
, name3_in => 'NEW_SALARY'
, value3_in => salaries (sql%BULK_EXCEPTIONS (indx).ERROR_INDEX)
);
END LOOP;

========================================================================

3.DML Error Logging: 這一段是一個新的作法，先用以下Package DBMS_ERRORLOG為你要處理的Table建立一個相對應的Error Log Table.

BEGIN
 DBMS_ERRLOG.create_error_log (
    dml_table_name   => 'EMPLOYEES'
  , skip_unsupported => TRUE);
END;

執行完後會有一個新Table出現 "ERR$_EMPLOYEES". 這時程式如第二個方式的Bulk Collect
Into的方式但是Exception的處理使用不同的作法. 如以下的程式碼，當有Exception發生於
Update的時候，程式碼裡面將上"LOG ERRORS REJECT LIMIT UNLIMITED;"指令，再呼叫副程式
Log_Error，這時就可以使用方才建立的Error Table將錯誤輸出而且不會影響後續的DML處理.
這裡的Rollback就是根據每一筆記錄不同於第二個作法的是每一個Statement.

======================================================================
PROCEDURE change_salary_for (
 dept_in  IN employees.department_id%TYPE
  , pct_increase_in  IN NUMBER
 , fetch_limit_in    IN PLS_INTEGER
)
IS
 bulk_errors exception;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

 CURSOR employees_cur
 IS
    SELECT employee_id, salary FROM employees WHERE department_id = dept_in;

 TYPE employee_tt
 IS
    TABLE OF employees.employee_id%TYPE INDEX BY BINARY_INTEGER;

 employee_ids   employee_tt;

 TYPE salary_tt
 IS
    TABLE OF employees.salary%TYPE INDEX BY BINARY_INTEGER;

 salaries       salary_tt;

 PROCEDURE log_errors
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      FOR error_rec IN (SELECT * FROM err$_employees)
      LOOP
         q$error_manager.register_error (
            error_code_in   => error_rec.ora_err_number$
          , name1_in        => 'EMPLOYEEE_ID'
          , value1_in        => error_rec.employee_id
          , name2_in        => 'PCT_INCREASE'
          , value2_in        => pct_increase_in
          , name3_in        => 'NEW_SALARY'
          , value3_in        => error_rec.salary
         );
      END LOOP;

      DELETE FROM err$_employees;

      COMMIT;
   END log_errors;
BEGIN
 OPEN employees_cur;

 LOOP
    FETCH employees_cur
       BULK COLLECT INTO employee_ids, salaries LIMIT fetch_limit_in;

    FOR indx IN 1 .. employee_ids.COUNT
    LOOP
       salaries (indx) := compensation_rules.adjusted_compensation (
          employee_id_in    => employee_ids (indx)
        , pct_increase_in    => pct_increase_in
       );
    END LOOP;

    FORALL indx IN 1 .. employee_ids.COUNT()
           UPDATE employees SET salary = salaries (indx) WHERE employee_id = employee_ids (indx)
       LOG ERRORS REJECT LIMIT UNLIMITED;

    log_errors ();

    EXIT WHEN employee_ids.COUNT() <>======================================================================

原文引用，請參照Link.

[APEX] 讀書心得 - Part I (Application Builder Concepts)

花了三天時間終於完成了第一個應用系統, 內含收貨資料維護, 出貨資料維護和出貨庫存報表. 其中出貨資料維護是用 Master-Detail的 Form去設計. 使用後的感覺是, Schema也就是Table/Index/...的建立還是要透過APEX的畫面自行設計&建立, 懂關聯式資料的人都可以上手. 之後進到WEB畫面的建構部份才是真正的挑戰. 如前文所提, 我也K了不少文件和安裝範例程式來看, 總算完成第一個應用程式(正請老婆大人驗收中).
感覺是APEX的底層設計考慮不少Layer的觀念很像Java 的 MVC(Model & View & Control)觀念, 所以有些設定要想一下他的原理才能夠瞭解, 知道如何修改.
下面是我K了官網上的文件後, 先將整個WEB產生的架構整理如下, 看一下在去K厚厚的原文書會比較有幫助.

. Item # Item Sub Item Description . 1.1 Page Page "the basic building block of an application,contain user interface elements, such as tabs, lists, buttons, items, and regions." . 1.2 Page Page Rendering "lists user interface controls and logic that are executed when a page is rendered. Page Rendering is the process of generating a page from the database Show Page is the page rendering process. It assembles all the page attributes (including regions, items, and buttons) into a viewable HTML page." . 1.3 Page Page Processing "Lists logic controls (such as computations and processes) that are evaluated and executed when the page is processed. Accept Page performs page processing. It performs any computations, validations, processes, and branching. When you request a page using a URL, the engine is running Show Page. When you submit a page, the Application Express engine is running Accept Page or performing page processing during which it saves the submitted values in the session cache and then performs any computations, validations, or processes." . 1.4 Condition Condition A small unit of logic that helps you control the display of regions, items, buttons, and tabs as well as the execution of processes, computations, and validations. . 1.4.1 Condition Type Current page in Expression 1 "3,100,203 If the current page is 100, then this condition evaluates to true and the condition passes." . 1.4.2 Condition Type Exists (SQL query returns at least one row) "SELECT 1 FROM employees WHERE department_id = :P101_DEPTNO" . 1.4.3 Condition Type PL/SQL Expression NVL(:MY_ITEM,'NO') = 'YES' . 1.5 Session State Management Session A session is a logical construct that establishes persistence (or stateful behavior) across page views . 1.5.1 Session State Management Referencing Session State "1. SQL --> :MY_ITEM --> Standard bind variable syntax for items whose names are no longer than 30 characters. Use this syntax for references within a SQL query and within PL/SQL. 2. PL/SQL --> V('MY_ITEM') --> PL/SQL syntax referencing the item value using the V function. 3. PL/SQL --> NV('MY_NUMERIC_ITEM') --> Standard PL/SQL syntax referencing the numeric item value using the NV function. 4. Static text (exact) --> &MY_ITEM. --> Static text. Exact substitution." . 1.5.2 Session State Management Setting Session State When a user submits a page, the Application Express engine automatically stores values typed into fields (items) in session state. . 1.5.3 Session State Management Clearing Session State "Uses standard f?p syntax to clear the cache 1. Clearing Cache by Item 2. Clearing Cache by Page 3. Clearing Cache for an Entire Application 4. Clearing Cache for the Current User Session" . 1.6 Understanding URL Syntax Understanding URL Syntax "The URL that displays for each page identifies the location of Oracle Application Express, the address of Oracle Application Express, the application ID, the page number, and the session ID. Using f?p Syntax: f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly . 1.7 Substitution Strings Substitution Strings "You can use substitution strings within a page template or region source to replace a character string with another value. As you design your application and enable users to edit items, you use substitution strings to pass information. You can use substitution strings in Application Builder in the following ways: * Include a substitution string within a template to reference component values * Reference page or application items using &ITEM. syntax * Use built-in substitution strings to achieve a specific type of functionality .