一点一滴努力,满仓满谷收成

做为DBA很想让每一个SQL的执行计划都是优的，且不变化。在实际工作中有一类应用，用到的一些表是按月分区的，每次帐期切换一下，下一个帐期在使用时这些表的当月分区数据量会由0条记录变为数万条。因ORACLE自动收集统计信息的滞后性，不准确的统计信息引发了执行计划的改变，因此会引发性能的问题。

稳定执行计划大体有三种方法：
1)锁定统计信息，这比较适合参数类的表。
2)使用HINT方式，这需要开发程序变更程序，这样的方式对开发人员要求较高，而且死板，索引重建改个名就麻烦了。
3）使用类似sql profile这样的方式。9i的时侯用outline,10时推荐用sql profile,11g时推荐用SQL Plan Baselines。

本文着重描述一下10g的sql profile的使用,手册上关于sql profile的用法基本都包含在SQL Tuning Advisor的使用中，这样的方式在实际中很不合DBA的工作方式。
如下以通过sql_id的生成sql profile的方式简要描述下sql profile的使用。

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;
/

现在看一下相应SQL的执行计划是不是按预想的变过来了。
3、相关视图

1)SYS.SQLPROF$ATTR
2)DBA_SQL_PROFILES