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 有何建議吧。