18. 10053 事件

10053 事件可以幫助我們了解 CBO 優化器是如何選擇出 SQL 當前最佳的執行計畫， 10053 事件總共有兩個層級可以設定， Level 2 可以蒐集出欄位的統計資訊 、 SQL 的 access path ， join 方式的選擇以及相對應的 cost ； Level 1 可以蒐集出 Level 2 所有的資訊再加上優化器使用的參數設定，以及 Index 的統計資訊。

使用 10053 事件的前提是 SQL 語法必須進行 Hard Parsing ，因為只有經過 Hard Parsing 才能捕捉到優化器是如何運算出 Cost ，否則 10053 事件蒐集出來是沒有任何內容的。 10053 有如下的方式可以進行蒐集 :

• 使用 explain plan 

針對要蒐集 10053 的 SQL 語法使用 explain plan 進行 Hard Parsing 來蒐集 :

SQL> alter session set events '10053 trace name context forever, level 1'; SQL> explain plan for <--SQL STATEMENT-->; SQL> alter session set events '10053 trace name context off'; SQL> select value from v$diag_info where name='Default Trace File';  (查詢 trace file 名稱)

• 使用 oradebug 進行蒐集

利用 oradebug 語法來蒐集 10053 事件 :

SQL> oradebug setmypid; SQL> oradebug event 10053 trace name context forever, level 1; SQL> <--SQL STATEMENT-->; SQL> oradebug event 10053 trace name context off; SQL> select value from v$diag_info where name='Default Trace File';  (查詢 trace file 名稱)

• 使用 DBMS_SQLDIAG 進行蒐集

利用 dbms_sqldiag 可以蒐集特定 SQL_ID 的 10053 事件，例如蒐集 SQL_ID 為 847df3vkcbsvh 的 10053 :

SQL> exec dbms_sqldiag.dump_trace(p_sql_id=>'847df3vkcbsvh',p_child_number=>0,p_component=>'Compiler'); SQL> select value from v$diag_info where name='Default Trace File';  (查詢 trace file 名稱)

在一個完整的 10053 trace 文件中，會有以下幾個部分 :

• 原始的 SQL 語法 :

• 優化器所使用的參數 :

• 表格相關的各項統計資訊 :

• 執行計畫的產生過程 :

從這個階段的資訊我們可以看到各項 access path 以及 join 方式所計算出來的 cost ，並且選擇最低的 cost 作為最終的執行計畫 :

經由 10053 的幫助，我們可以了解為何優化器會選擇這個 Plan ，並且從中可以找出問題所在，我們以下列這個查詢來說，優化器選擇的是 Hash Join :

SQL>  select t1.n1, t2.n1 from t1,t2         where t1.n2 = t2.n2 and t1.c1 = 'Q' and t2.c1 = 'P';

針對這句 SQL 來蒐集 10053 事件 :

10053 trace 首先揭露了 T1 、 T2 這兩個表格以及系統的統計資訊 :

接下來是 Table 存取路徑的選擇 :

由上述資訊可以得知，使用 Table Scan 的 Cost 為 380.17 ，使用 Index 的 Cost 為 1502.56 ，所以最後會選擇使用 Table Scan 。

最後一個區塊是 Join 方式的選擇 :

由這個區塊我們可以截取到，使用 Nest Loop 的 Cost 為 4207826.48 、使用 Sort Merge Join 的 Cost 為 1303.69 ，使用 Hash Join 的 Cost 為 936.34 ，綜合上述分析， 10053 最後就可以得到結論，這句 SQL 所選用的執行計畫為使用 Hash Join 並且做 Table Scan :

最後我們再利用 10053 來分析一句 SQL 如下 :

SQL> select e.employee_id,d.department_name,e.last_name        from emp e, dep d  where e.department_id=d.department_id and e.first_name='James';

雖然 emp 與 dep 這兩個 Table 都有 Index ，但優化器最終選擇的仍然是 Full Table Scan :

透過 10053 trace 可以發現，針對 EMP 這個 Table 使用 Table Scan 的 Cost 為 3 ，而使用 Index 的 Cost 高達 18700.40 :

由這個現象我們懷疑，是否這個 Index 所掃描的 Block 過多導致 Cost 增加太多，有可能是因為 Index 破碎導致 Block 數量過多。

檢視另一個 Table DEP ， DEP 總共有 27 筆資料，但 10053 顯示使用 IX_DEP 這個 Index 的估算 (Cardinality) 卻只有 1 筆資料 :

由這邊可以發現優化器對於 Index 的估算錯誤，那是不是我們提供了錯誤的資訊給優化器了呢 ? 

綜合兩點，我們將 Index IX_EMP1 進行 Rebuild ，然後重新蒐集統計資訊來修正統計錯誤 :

修正過後，優化器所選擇的 Plan 就變成了使用 Index 了 :

當我們對一段 SQL 的執行計畫有所疑問時，透過 10053 事件的分析就可以找到問題所在。

17. SQL Profile

SQL Profile 為 Oracle 10g 開始有的功能，可以說是 Oracle 9i 以前 Outlines 的進化版， SQL Profiles 針對特定的 SQL ，透過一連串的 Hint 生成執行計畫並把它保存下來，當下次這句 SQL 再次進行解析時，便可以參考 SQL Profiles 的內容來產生執行計畫，與 Outlines 不同的是， Outlines 是直接鎖定 SQL 的執行計畫，而 SQL Profiles 只是做為 Cost Based 的統計資訊供優化器參考，對於優化器來說只是一個引導的作用。雖然說 SQL Profiles 只是個引導，但多數時候 SQL 解析出來的 Plan 也不會與 SQL Profiles 的 Plan 不同，除非 SQL Profiles 所使用的 Hint 有失效的情況才有可能解出不同的 Plan ，也因此 SQL Profiles 常常是用來固定 SQL 執行計畫的一種手段。

透過建立 SQL Profiles ，可以用來固定或是改變特定 SQL 的執行計畫，例如一段 SQL 如下 :

SQL> select s.*,b.owner from sobj s,bobj b        where s.object_name like '%EMP%' and s.object_id=b.object_id;

執行計畫為兩個 Table 的 Hash Join :

從執行計劃顯示， BOBJ 這個 Table 的筆數以及 Bytes 顯然是較大的，針對這個 Table 使用 Full Table Scan 可能不太合適 :

這個時候就可以透過建立 SQL Profiles 來改變這句 SQL 的執行計畫，建立 SQL Profiles 有以下的方式 :

• 使用 SQL Tuning Advisor 

針對執行計畫不好的 SQL 使用 SQL Tuning Advisor 來調教是最直接的方式，如果有更好的 Plan ，透過 SQL Tuning Advisor 的建議來建立 SQL Profiles 是最方便的方式，以 dbms_sqltune 來執行 SQL Tuning Advisor :

SQL> set serveroutput on SQL> var tuning_task varchar2(100); SQL> declare       l_sql_id v$session.prev_sql_id%type;       l_tuning_task varchar2(30);       begin        l_sql_id := '95wam753v65d8';        l_tuing_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);        :tuning_task := l_tuning_task;        dbms_sqltune.execute_tuning_task(l_tuning_task);        dbms_output.put_line(l_tuning_task);       end; /

執行完畢後使用 dbms_sqltune.report_tuning_task 檢視結果 :

SQL> set long 5000 SQL> set linesize 120 SQL> select dbms_sqltune.report_tuning_task('TASK_1271') from dual;

由報告來看，系統提供了一個較優的執行計畫給我們，針對大 Table BOBJ 改用了 Index Scan :

接下來我們要做的，就是接受這個建議，接受之後就會替這句 SQL 建立  SQL PROFILES 了 :

SQL> begin        dbms_sqltune.accept_sql_profile        (task_name => 'TASK_1271', replace => TRUE);       end; /

建立了 SQL PROFILES 之後，再重複執行一次同樣的 SQL ，可以發現執行計畫已經改用 Index 並且註記這個計畫使用的是 SQL Profile :

• 手工建立 SQL Profiles

利用 DBMS_SQLTUNE.IMPORT_SQL_PROFILE 自行定義 Hint 來建立 SQL Profiles ，將原本使用 Hash Join 改用 USE_NL 來建立使用 Nest Loop 的執行計畫 :

手工建立 SQL Profiles 之後，同樣的再次執行 SQL 也會套用此計畫 :

• 使用 coe_xfr_sql_profile.sql

由於手工創建 SQL Profiles 需要先知道較好的執行計畫，以及此執行計畫所需要的 Hint ，自行撰寫 dbms_sqltune.import_sql_profile 實在不太容易， coe_xfr_sql_profile.sql 這個腳本就是幫助我們產生 dbms_sqltune.import_sql_profile 的語法，只要透過 coe_xfr_sql_profile.sql 來產生語法，就可以輕鬆的手工建立 SQL Profiles 。 coe_xfr_sql_profile.sql 的執行方式為 coe_xfr_sql_profile.sql + SQL_ID + Plan_Hash_Value ，以上述例子來創建 SQL Profiles 只需執行 :

SQL> @coe_xfr_sql_profile.sql 95wam753v65d8 3020495344

執行完畢後就會產生出建立 SQL Profiles 的腳本 coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql，執行它就可以建立出 SQL Profiles 了 :

SQL> @coe_xfr_sql_profile_95wam753v65d8_3020495344.sql

coe_xfr_sql_profile.sql 為官方工具 SQLT 裡面的其中一個腳本，必須由 Oracle Support 的文檔 SQLT Usage Instructions (Doc ID 1614107.1) 下載，裡面的 utl 目錄下就有 coe_xfr_sql_profile.sql 這個腳本了 :

有了這個工具之後，就可以再透過 dba_hist_sqlstat 來查詢 SQL 在過去當中是否有較佳的 Plan 可用，如果能找到，那麼就可以依照過去較佳的 Plan 來建立 SQL Profiles ，如果過去都沒有較佳的 Plan ，那麼針對這句 SQL 就是一個全新的 Tuning ，不如先問問 SQL Tuning Advisor 有何建議吧。