SQL概要文件--转自老熊的BLOG

转帖，原文见：http://www.laoxiong.net/sql-profiles-partii.html#comment-1098

Oracle 11g从发布到现在，也有几个年头了。而在国内来说，Oracle 10g仍然是主流，甚至一些电信运营商的核心系统仍然在使用9i。作为Oracle 10g的一项新特性，SQL Profiles被使用得并不太多。不管是在论坛、个人的BLOG还是其他一些地方，SQL Profiles的介绍也相对较少

使用SQL Profiles无非是两个目的：

• 锁定或者说是稳定执行计划。
• 在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。

那么SQL Profile到底是什么？在我看来，SQL Profile就是为某一SQL语句提供除了系统统计信息、对象（表和索引等）统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器为SQL语句选择更适合的执行计划。这些说法显得比较枯燥，还是来看看下面的测试。

create table t1 as select object_id,object_name from dba_objects where rownum<=50000;

create table t2 as select * from dba_objects;

create index t2_idx on t2(object_id);

exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');

exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');

然后看看下面这一条SQL：

Set autot traceonly

select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

这里省略了SELECT出来的具体数据，但是我们关心的是返回的结果行数、执行计划以及逻辑读这些信息。首先从执行计划可以看到，这条SQL语句在2个表上都是全表扫描。在第1个表T1上，有 like ‘%T1%’这样的条件，导致只能全表扫描，这没有问题。但是第2个表，也是全表扫描，这里有没有问题呢？或者说是有没有优化的余地，答案显然是肯定的。这里的问题在于执行计划ID=1的那一行，Oracle优化器评估T1 like ‘%T1%’返回的结果行数为2500行，即T1表总行数的5%，如果2个表采用index range scan+nested loop连接，oracle评估的成本会高于full table scan+hash join。

select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner

from t1,t2

where t1.object_name like '%T1%'

and t1.object_id=t2.object_id;

从执行计划可以看到Oracle优化器评估的成本为5061，远远高于原来的219。但是实际的逻辑读是多少呢？

加了HINT之后实际的逻辑读只有290，低于原始SQL的932。所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，也就过高地估计了nest loop的成本，最终也就选择了不是最优的执行计划。

下面我们用Oracle的SQL Tuning Advisor来尝试这条SQL：

conn / as sysdba

select sql_text,SQL_ID from v$sql where sql_text like'%T1%';

var tuning_task varchar2(100);

DECLARE

l_sql_id v$session.prev_sql_id%TYPE;

l_tuning_task VARCHAR2(30);

BEGIN

l_sql_id:='4zbqykx89yc8v';

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

print tuning_task;

set long 100000000;

SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;

上面代码中的sql_id是从v$sql来，对应的是没有加hint的SQL。结果看起来非常棒，SQL Tuning Advisor为我们找到了理想的执行计划，T1表上经过谓词过滤后返回的行数评估为26，相当地精确。我们要做的就是Accept SQL Profile，接受这个SQL Profile。

execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>true);

那么我们再执行其他的类似SQL看看

select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' and t1.object_id=t2.object_id;

这一次，尽管我们更改了LIKE 后面的值，但是执行计划与SQL Tuning Advisor产生的执行计划完全一样。从执行计划的”Note“一节也可以看到，SQL Profile起作用了。SQL Profile的名字为”SYS_SQLPROF_014b39f084c88000″。

select name,category,signature,type,status,force_matching from dba_sql_profiles

一些复杂的SQL，我经常会先通过SQL Tuning Advisor来分析一下，看能不能让Oracle自已找出一个更好的执行计划。

我们来看看，SQL Profiles实际上是些什么

select * from sys.sqlprof$attr;

signature是什么？可以理解为与sql_id、sql_hash_value类似的值，用来标识SQL。在10g以上的版本中，查看v$sql的定义就可以发现2列：exact_matching_signature、force_matching_signature。通过下面的数据可以看出区别：

对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量）后，如果SQL相同，那么SQL语句的exact_matching_signature就是相同的。对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么SQL语句的force_matching_signature就是相同的。但是例外的情况是：如果SQL中有绑定变量，force_matching_signature就会与exact_matching_signature一样的生成标准。

回到前面，accept_sql_profile这个过程，force_match参数设为TRUE，那么dba_sql_profiles中的signature则是由SQL的force_matching_signature而来，否则便是exact_matching_signature。对于Outlines来说，则只能是exact_matching_signature。从这个角度上讲，Sql Profiles比Outlines的使用范围更广，因为Sql profiles对没有使用绑定变量的SQL也支持得很好。值得注意的是，Sql profiles的force_match属性是不能更改的，只能在创建时指定，如果要更改，则只能重新创建改Sql Profile。

下面来看看sys.sqlprof$attr数据字典。这里面没有SQL Profile的名字，而是用的sql的signature。大家从attr_val的结果发现了什么？

OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0104)

可以看到，SQL Profiles的attr_val实际上就是一些Hints。只是SQL Profiles中的Hint，没有指定SQL使用哪个索引，也没有指定表的连接方法和连接顺序。这里只指定了T1表评估返回的行数，与原始的评估返回的行数的放大缩小的倍数。2500*0.0104正好为26。这里就是告诉Oracle优化器，T1表经过谓语过滤后返回行数应该为评估的0.01161091426倍。从这里可以看出，SQL Profiles并不会锁定SQL的执行计划，只是提供了更多、更准确的统计信息给优化器。看下面的测试：

exec dbms_stats.set_table_stats('sh','T1',numrows=>5000000);

将T1表的统计信息中的表行数改为500万，Oracle就会评估为返回select 5000000*0.05*0.0104 from dual =2600

这里执行计划又变回为full scan+hash join。可以看到，虽然SQL Profile起作用了，但是并没有锁定执行计划。

小结：本文简单介绍了什么是SQL Profiles及其作用，如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature

在上一篇《SQL Profiles-Part I》，我向大家介绍了什么是SQL Profiles及其作用，如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。那么在今天，将向大家介绍如何手工创建SQL Profiles（即不通过SQL Tuning Advisor)来达成2个目的：锁定或者说稳定SQL执行计划。 在不能修改应用的SQL的情况下，来改变或者说是强制使SQL使用我们指定的执行计划，即使原始的SQL包含了Hints。 那么，这里最关键的一点是，如何来手工创建SQL Profiles? 答案是，正如上一篇中有朋友的留言，使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程。

SQL> desc dbms_sqltune

这个过程其名字与实际功能有所差异，其实可以理解为CREATE OR REPLACE SQL_PROFILE。过程中的PROFILE参数为SYS.SQLPROF_ATTR，这种类型其实就是VARCHAR2的集合类型(COLLECTION)：

SQL> select text from dba_source where name='SQLPROF_ATTR' and owner='SYS';

下面我们就用这个过程来创建SQL PROFILE：
为避免干扰，将上一篇测试中生成的SQL Profile删除掉，同时恢复T1表的统计信息中的表行数：

SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014b39f084c88000');

SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>49953);

现在我们手工创建一个SQL Profile：

declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr('USE_NL(T1 T2)','INDEX(T2)');
dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
v_hints,'SQLPROFILE_NAME1',force_match=>true);
end;
/

select attr_val from dba_sql_profiles a, sys.sqlprof$attr b
where a.signature = b.signature
and a.name='SQLPROFILE_NAME1';

下面执行SQL Profiles对应的SQL：

set autot traceolny

select t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id;

可以看到，SQL使用了SQL Profile，不过没有达到我们预期的效果。

看起来是SQL Profile使用的Hints有问题。我们重新设置SQL Profile的Hints，在Hints中加上“Query Block Name”。这一次在执行IMPORT_SQL_PROFILE过程时，将REPLACE参数设置为TRUE，以替换现有的SQL Profile：

declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr('USE_NL(T1@SEL$1 T2@SEL$1)','INDEX(T2@SEL$1)');
dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
v_hints,'SQLPROFILE_NAME1',force_match=>true,replace=>true);
end;
/

再次执行下面的SQL：

select t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id;

这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。那么我们再一次手工修改T1表的统计信息，看看结果如何：

SQL> exec dbms_stats.set_table_stats('sh','T1',numrows=>5000000);

select t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id;

可以看到，Oracle优化器评估表T1经过Like条件过滤后返回的行数虽然很大，但是这里的执行计划仍然与未修改统计信息之前一样，使用range scan+ nested loop join。

通过以上的测试，我们明白了DBMS_SQLTUNE.IMPORT_SQL_PROFILE的使用，同时也验证了这种方式的有效性，SQL Profiles能够像Outlines一样，能够稳定SQL的执行计划。

接下来我们需要完成两个任务。
任务一：对现有的SQL稳定其执行计划。
这里的问题是：稳定一条SQL语句的Hints从哪里来？简单的sql，没问题，我们可以手工构造，但是复杂的SQL，手工构造相对比较复杂，同时手工构造的Hints不一定能够保证SQL的执行计划就会稳定。从10g开始，v$sql_plan中就包括了SQL语句OUTLINE数据，也就是稳定执行计划的Hints。从下面可以看到

select t1.*,t2.owner
from t1,t2
where t1.object_name like '%T1%'
and t1.object_id=t2.object_id;

select * from table(dbms_xplan.display_cursor(null,null,'outline'));

上面所显示的“Outline Data”即是我们稳定SQL执行计划需要的Hints(我们甚至可以将这些Hints直接写到我们的SQL中）。对需要稳定执行计划的SQL，我们所要做的就是如前面所示，将Hints与SQL文本一起创建一个SQL Profile

之后老熊使用了一个编写好的脚本进行了实验，我将在另外一篇blog中介绍这种方法。

[@more@]

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

转载于:http://blog.itpub.net/23216740/viewspace-1044782/