ITPub博客

首页 > 数据库 > Oracle > 根据sql_id生成sql profile

根据sql_id生成sql profile

原创 Oracle 作者:qiuyb 时间:2013-04-09 13:22:01 0 删除 编辑
实际工作中经常出现SQL执行计划总变的状况,在9i及之前版本常用OUTLINE稳定执行计划,10G后可以使用更优的sql profile[@more@]

1、获取某一sql_id的OUTLINE

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('ct47hsv47278w', 0, 'outline'));

这里只贴出了outline的部分,其余略

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1"
"A"@"SEL$1" "IDX_TF_B_PAYLOG_1")
INDEX_RS_ASC(@"SEL$1"
"F"@"SEL$1" ("TD_M_STAFF"."STAFF_ID"))
INDEX_RS_ASC(@"SEL$1"
"C"@"SEL$1" ("TD_B_DISCNT_ACTION"."ACTION_CODE"))
INDEX_RS_ASC(@"SEL$1"
"D"@"SEL$1" ("TD_B_IDTONAME"."PARA_ID" "TD_B_IDTONAME"."INFO_TYPE"))
INDEX_RS_ASC(@"SEL$1"
"B"@"SEL$1" ("TF_B_PAYOTHER_LOG"."CHARGE_ID" "TF_B_PAYOTHER_LOG"."PARTITION_ID"
"TF_B_PAYOTHER_LOG"."CARRIER_ID"))
LEADING(@"SEL$1"
"A"@"SEL$1" "F"@"SEL$1" "C"@"SEL$1" "D"@"SEL$1" "B"@"SEL$1")
USE_NL(@"SEL$1"
"F"@"SEL$1")
USE_NL(@"SEL$1"
"C"@"SEL$1")
USE_NL(@"SEL$1"
"D"@"SEL$1")
USE_NL(@"SEL$1"
"B"@"SEL$1")
END_OUTLINE_DATA
*/

2、创建sql profile
DECLARE
V_HINTS SYS.SQLPROF_ATTR;
CURSOR C1 IS
SELECT *
FROM V$SQL
WHERE SQL_ID = 'ct47hsv47278w'
AND CHILD_NUMBER = 0;
L_C1 C1%ROWTYPE;
BEGIN
V_HINTS := SYS.SQLPROF_ATTR(
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''10.2.0.5'')',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'INDEX_RS_ASC(@"SEL$1"
"A"@"SEL$1" "IDX_TF_B_PAYLOG_1")',
'INDEX_RS_ASC(@"SEL$1"
"F"@"SEL$1" ("TD_M_STAFF"."STAFF_ID"))',
'INDEX_RS_ASC(@"SEL$1"
"C"@"SEL$1" ("TD_B_DISCNT_ACTION"."ACTION_CODE"))',
'INDEX_RS_ASC(@"SEL$1"
"D"@"SEL$1" ("TD_B_IDTONAME"."PARA_ID" "TD_B_IDTONAME"."INFO_TYPE"))',
'INDEX_RS_ASC(@"SEL$1"
"B"@"SEL$1" ("TF_B_PAYOTHER_LOG"."CHARGE_ID" "TF_B_PAYOTHER_LOG"."PARTITION_ID"
"TF_B_PAYOTHER_LOG"."CARRIER_ID"))',
'LEADING(@"SEL$1"
"A"@"SEL$1" "F"@"SEL$1" "C"@"SEL$1" "D"@"SEL$1" "B"@"SEL$1")',
'USE_NL(@"SEL$1"
"F"@"SEL$1")',
'USE_NL(@"SEL$1"
"C"@"SEL$1")',
'USE_NL(@"SEL$1"
"D"@"SEL$1")',
'USE_NL(@"SEL$1"
"B"@"SEL$1")',
'END_OUTLINE_DATA'
);

OPEN C1;
FETCH C1
INTO L_C1;

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(L_C1.SQL_FULLTEXT,
V_HINTS,
'sqlprofile_ct47hsv47278w', --sql profile名字
FORCE_MATCH => TRUE,
REPLACE => TRUE);
CLOSE C1;
END;
/

3、相关视图
<1>SYS.SQLPROF$ATTR
<2>DBA_SQL_PROFILES

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/717880/viewspace-1060378/,如需转载,请注明出处,否则将追究法律责任。

上一篇: hp-ux UDP 优化
全部评论
  • 博文量
    75
  • 访问量
    651555