【DB筆試面試608】在Oracle中，如何使用STA來生成SQL Profile？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中，如何使用STA來生成SQL Profile？
♣
答案部分
利用STA對語句進行優化後，STA會對語句進行分析，採用最優的優化策略，並給出優化後的查詢計劃。可以按照STA給出的建議重寫語句。但是，有些情況下，你可能無法重寫語句（比如在生產環境中，SQL語句又在一個包中）。這個時候就可以利用Sql Profile，將優化策略存儲在Profile中，Oracle在構建這條語句的查詢計劃時，就不會使用已有相關統計數據，而使用Profile的策略，生成新的查詢計劃。
第一步：給用戶賦許可權
[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 16:47:29 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SYS@dlhr> SYS@dlhr> SYS@dlhr> SYS@dlhr> GRANT CREATE ANY SQL PROFILE TO LHR; Grant succeeded. SYS@dlhr> GRANT DROP ANY SQL PROFILE TO LHR; Grant succeeded. SYS@dlhr> GRANT ALTER ANY SQL PROFILE TO LHR; Grant succeeded. SYS@dlhr> conn lhr/lhr Connected. LHR@dlhr> LHR@dlhr> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production LHR@dlhr> create table lhr.TB_LHR_20160525_01 as select * from dba_objects; Table created. LHR@dlhr> create index lhr.TB_LHR_20160525_01_idx on TB_LHR_20160525_01(object_id); Index created. LHR@dlhr> exec dbms_stats.gather_table_stats('lhr','TB_LHR_20160525_01',cascade=>true,degree=>4); PL/SQL procedure successfully completed. LHR@dlhr> set autot on LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; COUNT(*) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 3612989399 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"=100) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1249 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed LHR@dlhr> set autot off LHR@dlhr> SELECT v.SQL_ID, v.SQL_TEXT FROM v$sql v WHERE v.SQL_TEXT like '%no_index(TB_LHR_20160525_01%' and v.SQL_TEXT not like '%v$sql%' ; SQL_ID ------------- SQL_TEXT ------------------------------------------------------------------------------------------------------------------------------------ 7jt1btjkcczb8 select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 7suktf0w95cry EXPLAIN PLAN SET STATEMENT_ID='PLUS150249' FOR select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_L HR_20160525_01 where object_id = 100
第二步：創建、執行優化任務
LHR@dlhr> DECLARE 2 my_task_name VARCHAR2(30); 3 my_sqltext CLOB; 4 BEGIN 5 my_sqltext := 'select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100'; 6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 7 sql_text => my_sqltext, 8 user_name => 'LHR', 9 scope => 'COMPREHENSIVE', 10 time_limit => 60, 11 task_name => 'sql_profile_test', 12 description => 'Task to tune a query on a specified table'); 13 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test'); 14 END; 15 / PL/SQL procedure successfully completed.
或者也可以使用sqlid來生成優化任務，如下：
LHR@dlhr> DECLARE 2 a_tuning_task VARCHAR2(30); 3 BEGIN 4 a_tuning_task := dbms_sqltune.create_tuning_task(sql_id => '7jt1btjkcczb8', 5 task_name => 'sql_profile_test_SQLID'); 6 dbms_sqltune.execute_tuning_task(a_tuning_task); 7 END; 8 / PL/SQL procedure successfully completed.
第三步：查看優化建議
LHR@dlhr> set autot off LHR@dlhr> set long 10000 LHR@dlhr> set longchunksize 1000 LHR@dlhr> set linesize 100 LHR@dlhr> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL; DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : sql_profile_test Tuning Task Owner : LHR Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 60 Completion Status : COMPLETED Started at : 05/25/2016 16:58:31 Completed at : 05/25/2016 16:58:32 ------------------------------------------------------------------------------- Schema Name: LHR SQL ID : 9kzm8scz6t92z SQL Text : select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 99.83%) ------------------------------------------ - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test', task_owner => 'LHR', replace => TRUE); Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .006278 .00004 99.36 % CPU Time (s): .003397 .000021 99.38 % User I/O Time (s): 0 0 Buffer Gets: 1249 2 99.83 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') ---------------------------------------------------------------------------------------------------- Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- 1. Statistics for the original plan were averaged over 10 executions. 2. Statistics for the SQL profile plan were averaged over 10 executions. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 3612989399 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"=100) 2- Using SQL Profile -------------------- Plan hash value: 661515879 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | INDEX RANGE SCAN| TB_LHR_20160525_01_IDX | 1 | 5 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') ---------------------------------------------------------------------------------------------------- 2 - access("OBJECT_ID"=100) -------------------------------------------------------------------------------
這裡可以看到，在優化建議中給出了新的查詢計劃。現在，我們決定接受這個建議，並且不重寫語句。
第四步：接受profile
LHR@dlhr> set autot on LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; COUNT(*) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 3612989399 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"=100) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1249 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed LHR@dlhr> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_test_SQLID', task_owner => 'LHR', replace => TRUE); PL/SQL procedure successfully completed. LHR@dlhr> set autot off LHR@dlhr> SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints 2 FROM dba_sql_profiles d, 3 dba_advisor_tasks e, 4 SYS.SQLOBJ$DATA A, 5 SYS.SQLOBJ$ B, 6 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA), 7 '/outline_data/hint'))) h 8 where a.signature = b.signature 9 and a.category = b.category 10 and a.obj_type = b.obj_type 11 and a.plan_id = b.plan_id 12 and a.signature = d.signature 13 and d.task_id=e.task_id 14 and d.name = 'SYS_SQLPROF_0154e728ad3f0000' 15 ; TASK_NAME NAME ------------------------------ ------------------------------ SQL_TEXT ---------------------------------------------------------------------------------------------------- HINTS ---------------------------------------------------------------------------------------------------- sql_profile_test SYS_SQLPROF_0154e728ad3f0000 select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 OPTIMIZER_FEATURES_ENABLE(default) sql_profile_test SYS_SQLPROF_0154e728ad3f0000 select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 IGNORE_OPTIM_EMBEDDED_HINTS
在這裡用了包DBMS_SQLTUNE的另一個函數：ACCEPT_SQL_PROFILE。其中，參數task_name即我們創建的優化建議任務的名稱，name是profile的名字，可以是任意合法名稱。此外這個函數還有其他一些函數，下面是這個函數的原型：
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name IN VARCHAR2, object_id IN NUMBER := NULL, name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL; task_owner IN VARCHAR2 := NULL, replace IN BOOLEAN := FALSE, force_match IN BOOLEAN := FALSE) RETURN VARCHAR2;
Description是profile的描述資訊；task_owner是優化建議任務的所有者；replace為TRUE時，如果這個profile已經存在，就代替它；force_match為TURE時，表示與語句強制匹配，即強制使用綁定變數，和系統參數cursor_sharing設置為FORCE時類似，為FALSE時，與cursor_sharing設置為EXACT時類似，即完全匹配。
這裡要特別提到的是category這個參數，你可以通過設置這個參數，制定特定會話使用這個profile。在10g中，每個會話都有一個新參數SQLTUNE_CATEGORY，他的默認值是DEFAULT。而我們在調用這個函數時，如果沒有指定這個參數，那它的值也是DEFAULT，而如果我們給這個profile指定了一個其它的CATEGORY值，如FOR_TUNING，那麼只有會話參SQLTUNE_CATEGORY也為FOR_TUNING時，才會使用這個porfile。為什麼說這個參數很有用呢？試想一個這樣的環境：你在一個生產系統上利用STA調優一條語句，STA已經給出了優化建議，但是你又不敢貿然實施它給出的建議（畢竟它只是機器嘛，不能完全信任），你就可以創建一個有特殊CATEGORY的profile，然後在你自己的會話中制定SQLTUNE_CATEGORY為這個特殊的CATEGORY，那就既可以看優化建議的實際效果又不影響生產環境。
此外可以通過視圖DBA_SQL_PROFILES來查看已經創建的profile。
第五步：查看profile的效果
LHR@dlhr> set autot on LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; COUNT(*) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 661515879 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | INDEX RANGE SCAN| TB_LHR_20160525_01_IDX | 1 | 5 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=100) Note ----- - SQL profile "SYS_SQLPROF_0154e728ad3f0000" used for this statement Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
從NOTE部分可以看到，語句採用了profile中的數據，創建了新的查詢計劃。並且在查詢計劃中還有一些附加資訊，表明這個語句是採用了「SYS_SQLPROF_0154e728ad3f0000」這個Profile，而不是根據對象上面的統計數據來生成的查詢計劃。
但上述方法主要是依賴sql tuning advisor,如果它無法生成你想要的執行計劃.你還可以通過手動的方式,通過sql profile把hint加進去.複雜的SQL的hint可以採用腳本coe_xfr_sql_profile.sql來產生原語句的outline data和加hint語句的outline data，然後替換對應的SYS.SQLPROF_ATTR，最後執行生成的sql就可以了。
使用PLSQL DEVELOPER 11查看執行計劃，如下圖，新版本的好處：
本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。
