前几天在查阅新书<Pro Oracle SQL>介绍的时候，看到作者blog上有提出了undocmented way手工构造SQL profile的例子，确实能像outline一样，强制固定住执行计划，这顿时让我觉得柳暗花明，因为我之前一直对SQL profiles的稳定性大伤脑筋。结合最近的几次项目调优，使用这种办法固定SQL计划，效果相当不错。
手工构造SQL Profiles，可以使用oracle提供的dbms_sqltune.import_sql_profile过程。
SQL> desc dbms_sqltune.import_sql_profile
Parameter Type Mode Default?
———– —————- —- ——–
SQL_TEXT CLOB IN —-sql文本
PROFILE SYS.SQLPROF_ATTR IN —-profile info
NAME VARCHAR2 IN Y —profile名称
DESCRIPTION VARCHAR2 IN Y
CATEGORY VARCHAR2 IN Y —类别
VALIDATE BOOLEAN IN Y
REPLACE BOOLEAN IN Y
FORCE_MATCH BOOLEAN IN Y –是否对sql语句进行匹配，游标共享。
其中Profile参数，我们可以按照既定格式手工填充，因为一般要调优的SQL语句相对比较长，如果手工填充，不是很现实，可以用已生成的执行计划，从v$sql_plan中提取(当然也可以从awr的dba_hist_sql_plan中提取，提取方法可参见后面脚本)
从v$sql_plan中提取sql profiles信息: create_sql_cursor_profiles.sql(该脚本基于Kerry Osborne完善而成，增加了SQL Profile信息调换功能，更方便的为我们选择想要的执行计划)
以下是一个调优案例：
项目中有个模块需要进行日结报表统计，没有使用绑定变量,应用返回超时，涉及语句和生产执行计划如下：
select a.*
b.ofr_create_time as FILE_CREATETIME,
b.ofr_file_name as FILE_name,
b.ofr_completetime as FILE_RECRONTIME,
b.ofr_deal_type as DEAL_TYPE,
c.ubr_last_send as UBR_LAST_SEND,
c.ubr_completetime as UBR_COMPLETETIME,
c.ubr_state as UBR_STATE,
d.ofc_reckonaccount as ofc_reckonaccount,
d.ofc_province as ofc_province
from UNP_PAYRECORD a, unp_orafile b, unp_paybatch c, unp_orarecord d
where a.ubc_ofc_id = d.ofc_id
and a.ubc_ofc_sn = d.ofc_sn
and a.ubc_id = c.ubr_id
and a.ubc_ofc_id = b.ofr_id
and b.ofr_deal_type = ’101003′
and c.ubr_last_send between to_date(’2010-11-12′, ‘yyyy-mm-dd’) and
to_date(’2010-11-12′, ‘yyyy-mm-dd’) + 1
and b.OFR_COMPLETETIME is not null
and a.UBC_OFC_PAY_CHANNEL = ’010501′
order by a.ubc_id desc, a.ubc_sn asc
——————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc
——————————————————————————–
| 0 | SELECT STATEMENT | | | |
| 1 | SORT ORDER BY | | 93172 | 27M| 58M
|* 2 | HASH JOIN | | 93172 | 27M| 23M
|* 3 | HASH JOIN | | 92549 | 21M| 21M
|* 4 | HASH JOIN | | 93486 | 20M| 3656K
| 5 | TABLE ACCESS BY INDEX ROWID| UNP_PAYBATCH | 87005 | 2633K|
|* 6 | INDEX RANGE SCAN | IDX_LAST_SEND | 87005 | |
|* 7 | TABLE ACCESS FULL | UNP_PAYRECORD | 5468K| 1053M|
| 8 | TABLE ACCESS FULL | UNP_ORARECORD | 21M| 322M|
|* 9 | TABLE ACCESS FULL | UNP_ORAFILE | 451K| 23M|
——————————————————————————–
通过添加Hints，我们认为如下优化后的语句和执行计划可以被接受：
select /*+ ordered use_nl(a,b,c,d) */ a.*,
b.ofr_create_time as FILE_CREATETIME,
b.ofr_file_name as FILE_NAME,
b.ofr_completetime as FILE_RECRONTIME,
b.ofr_deal_type as DEAL_TYPE,
c.ubr_last_send as UBR_LAST_SEND,
c.ubr_completetime as UBR_COMPLETETIME,
c.ubr_state as UBR_STATE,
d.ofc_reckonaccount as ofc_reckonaccount,
d.ofc_province as ofc_province
from unp_paybatch c,UNP_PAYRECORD a ,unp_orafile b,unp_orarecord d
where a.ubc_ofc_id = d.ofc_id
and a.ubc_ofc_sn = d.ofc_sn
and a.ubc_id = c.ubr_id
and a.ubc_ofc_id = b.ofr_id
and b.ofr_deal_type = ’101003′
and c.ubr_last_send between to_date(’2010-11-12′, ‘yyyy-mm-dd’) and
to_date(’2010-11-12′, ‘yyyy-mm-dd’) + 1
and b.OFR_COMPLETETIME is not null
and a.UBC_OFC_PAY_CHANNEL = ’010501′
order by a.ubc_id desc, a.ubc_sn asc——————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc
——————————————————————————–
| 0 | SELECT STATEMENT | | | |
| 1 | SORT ORDER BY | | 93172 | 27M| 58M
| 2 | NESTED LOOPS | | 93172 | 27M|
| 3 | NESTED LOOPS | | 94116 | 25M|
| 4 | NESTED LOOPS | | 93486 | 20M|
| 5 | TABLE ACCESS BY INDEX ROWID| UNP_PAYBATCH | 87005 | 2633K|
|* 6 | INDEX RANGE SCAN | IDX_LAST_SEND | 87005 | |
|* 7 | TABLE ACCESS BY INDEX ROWID| UNP_PAYRECORD | 1 | 202 |
|* 8 | INDEX RANGE SCAN | SYS_C003463 | 1 | |
|* 9 | TABLE ACCESS BY INDEX ROWID | UNP_ORAFILE | 1 | 55 |
|* 10 | INDEX UNIQUE SCAN | SYS_C003412 | 1 | |
| 11 | TABLE ACCESS BY INDEX ROWID | UNP_ORARECORD | 1 | 16 |
|* 12 | INDEX UNIQUE SCAN | SYS_C003427 | 1 | |
——————————————————————————–
因为该语句有3个传入参数，没有使用绑定变量，因此不能通过outline的方式进行SQL语句固定，而且，由于亚运期间运维保证封网，无法进行前台应用程序的修改。因此只能寻找别的数据库层上的途径。
尝试去手工去构造SQL Profile。用的就是偷梁换柱的思想，将原始的SQL语句的profiles和优化后的SQL语句的profiles信息调换(这种办法,也常常用在outline的构造中)
查询到原始的SQL语句，其sql_id为’ar6qss94q5m9b’,child_number为0
加过Hints优化后的SQL语句，其sql_id为’4brxrmxzhavtr’,child_number为0
生成SQL profile，其sql文本对应为ar6qss94q5m9b的sqltext(原始文本)，而执行计划信息对应为4brxrmxzhavtr的执行计划(调优后的计划)。
SQL> @create_sql_cursor_profiles.sql
Enter value for sql_id1(used to generate sql_text): ar6qss94q5m9b
Enter value for child_no1 (used to generate sql_text) (0): 0
Enter value for sql_id2(used to generate sql_hints): 4brxrmxzhavtr
Enter value for child_no2(used to generate sql_hints) (0):
Enter value for profile_name (PROF_sqlid_planhash): PROF_1
Enter value for category (DEFAULT):
Enter value for force_matching (FALSE): true
old 9: decode(‘&&profile_name’,'X0X0X0X0′,’PROF_&&sql_id1′||’_'||plan_hash_value,’&&profile_name’)
new 9: decode(‘X0X0X0X0′,’X0X0X0X0′,’PROF_ar6qss94q5m9b’||’_'||plan_hash_value,’X0X0X0X0′)
old 15: sql_id = ‘&&sql_id1′
new 15: sql_id = ‘ar6qss94q5m9b’
old 16: and child_number = &&child_no1;
new 16: and child_number = 0;
old 31: sql_id = ‘&&sql_id2′
new 31: sql_id = ’4brxrmxzhavtr’
old 32: and child_number = &&child_no2
new 32: and child_number = 0
old 40: category => ‘&&category’,
new 40: category => ‘DEFAULT’,
old 42: force_match => &&force_matching
new 42: force_match => true
old 53: dbms_output.put_line(‘ERROR: sql_id: ‘||’&&sql_id1′||’ Child: ‘||’&&child_no1′||’ not found in v$sql.’);
new 53: dbms_output.put_line(‘ERROR: sql_id: ‘||’ar6qss94q5m9b’||’ Child: ‘||’0′||’ not found in v$sql.’);PL/SQL procedure successfully completed.
查看是否有SQL profile生成.
SQL> select name,category,signature,sql_text,created,status,force_matching from dba_sql_profiles;
NAME CATEGORY SIGNATURE SQL_TEXT CREATED STATUS FORCE_MATCHING
—————————— —————————— ———- —————————————————————–
PROF_1 DEFAULT 8.70867244 (略) 2010/12/20 ENABLED YES
查看该PROF_1的outline hints
SQL> select attr_val from dba_sql_profiles p, sys.sqlprof$attr h where p.signature = h.signature and p.category = h.category and a.name=’PROF_1′;
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(’10.2.0.4′)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "C"@"SEL$1" ("UNP_PAYBATCH"."UBR_LAST_SEND"))
INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("UNP_PAYRECORD"."UBC_ID" "UNP_PAYRECORD"."UBC_SN"))
INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("UNP_ORAFILE"."OFR_ID"))
INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("UNP_ORARECORD"."OFC_ID" "UNP_ORARECORD"."OFC_SN"))
LEADING(@"SEL$1" "C"@"SEL$1" "A"@"SEL$1" "B"@"SEL$1" "D"@"SEL$1")
USE_NL(@"SEL$1" "A"@"SEL$1")
USE_NL(@"SEL$1" "B"@"SEL$1")
USE_NL(@"SEL$1" "D"@"SEL$1")
可见，该语句的SQL Profile已经生成，来验证一下这个语句现在使用的执行计划。
SQL> select * from table(dbms_xplan.display_cursor(‘ar6qss94q5m9b’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID ar6qss94q5m9b, child number 0
————————————-
select a.*, b.ofr_create_time as FILE_CREATETIME, b.ofr_file_name
b.ofr_completetime as FILE_RECRONTIME, b.ofr_deal_type as DEAL_TYPE,
c.ubr_last_send as UBR_LAST_SEND, c.ubr_completetime as UBR_COMPLETETIME,
c.ubr_state as UBR_STATE, d.ofc_reckonaccount as ofc_reckonaccount,
as ofc_province from UNP_PAYRECORD a, unp_orafile b, unp_paybatch c, unp_orare
a.ubc_ofc_id = d.ofc_id and a.ubc_ofc_sn = d.ofc_sn and a.ubc_id = c.ubr_i
a.ubc_ofc_id = b.ofr_id and b.ofr_deal_type = ’101003′ and c.ubr_last_send
to_date(’2010-11-12′, ‘yyyy-mm-dd’) and to_date(’2010-11-12′, ‘yyyy-mm-dd
b.OFR_COMPLETETIME is not null and a.UBC_OFC_PAY_CHANNEL = ’010501′ order by
a.ubc_sn asc
Plan hash value: 724325605
——————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc
——————————————————————————–
| 0 | SELECT STATEMENT | | | |
| 1 | SORT ORDER BY | | 93172 | 27M| 58M
| 2 | NESTED LOOPS | | 93172 | 27M|
| 3 | NESTED LOOPS | | 94116 | 25M|
| 4 | NESTED LOOPS | | 93486 | 20M|
| 5 | TABLE ACCESS BY INDEX ROWID| UNP_PAYBATCH | 87005 | 2633K|
|* 6 | INDEX RANGE SCAN | IDX_LAST_SEND | 87005 | |
|* 7 | TABLE ACCESS BY INDEX ROWID| UNP_PAYRECORD | 1 | 202 |
|* 8 | INDEX RANGE SCAN | SYS_C003463 | 1 | |
|* 9 | TABLE ACCESS BY INDEX ROWID | UNP_ORAFILE | 1 | 55 |
|* 10 | INDEX UNIQUE SCAN | SYS_C003412 | 1 | |
| 11 | TABLE ACCESS BY INDEX ROWID | UNP_ORARECORD | 1 | 16 |
|* 12 | INDEX UNIQUE SCAN | SYS_C003427 | 1 | |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
6 – access("C"."UBR_LAST_SEND">=TIMESTAMP’ 2010-11-12 00:00:00′ AND
"C"."UBR_LAST_SEND"<=TIMESTAMP’ 2010-11-13 00:00:00′)
7 – filter("A"."UBC_OFC_PAY_CHANNEL"=’010501′)
8 – access("A"."UBC_ID"="C"."UBR_ID")
9 – filter(("B"."OFR_DEAL_TYPE"=’101003′ AND "B"."OFR_COMPLETETIME" IS NOT NU
PLAN_TABLE_OUTPUT
——————————————————————————–
10 – access("A"."UBC_OFC_ID"="B"."OFR_ID")
12 – access("A"."UBC_OFC_ID"="D"."OFC_ID" AND "A"."UBC_OFC_SN"="D"."OFC_SN")
Note
—–
- SQL profile "PROF_1" used for this statement
执行计划与我们预期的一致，由于SQL Profile的设置了force match，因此，可以替入其他参数值，验证执行计划依然是完美保持的，对执行计划的固定原理上，同outline也就没有太大区别了。
扫一扫
1112
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
