Query Block Name
可能很多人用手工创建SQL Profile的一个最大的难处是不知道该如何写出让SQL Profile接受的hint，因为传统的hint是不包含Query Block Name的，把Query Block Name增加到hint里会显得这个技术有点复杂。其实Query Block Name并不是复杂的技术，我们搞清楚了它的由来、它的技术细节后，就会豁然开朗。
| n Note ：初始化参数类的hint并不需要Query Block Name，他们的作用域是全局的，初始化类的参数有很多：all_rows、dynamic_sampling、gather_plan_statistics、result_cache等等。 |
我们先看下如下的一个SQL：
| select count(*) from test where status in (select status from test where status='Inactive'); |
如果我们想让子查询select status from test where status='Inactive'走索引扫描而不要走全索引扫描，这个hint该如何写？
| select count(*) from test where status in (select /*+ index_rs(test t_ind) */ status from test where status='Inactive'); |
但是如果我们使用的是SQL Profile等技术，不是直接在SQL语句里添加hint，这个hint要在全局起作用，如果仅仅是通过index_rs(test t_ind)来表达你的意图并不够，因为这个SQL里有2处都引用到了test表，index(test t_ind)到底是对SQL的哪个地方的test起作用并不明确，因此才会引出Query Block Name的概念，一个select（update/delete/merge等也都是）就是一个Query Block。系统默认产生的查询块是以一个由字母组成的前缀加数字组成的，前缀是基于语句的类型。查询块的数字编号是按照SQL语句解析阶段查询块出现的位置从左到右来进行的。看下表：
| 前缀 | 语句类型 |
| CRI$ | CREATE INDEX 语句 |
| DEL$ | DELETE 语句 |
| INS$ | INSERT 语句 |
| MRG$ | MERGE语句 |
| SEL$ | SELECT 语句 |
| SET$ | 集合操作如UNION |
| UPD$ | UPDATE 语句 |
例如我们上面的语句select count(*) from test where status in (select status from test where status='Inactive')可以拆解为2个查询块，select count(*) from test为SEL$1，select status from test where status='Inactive'为SEL$2，但是在SQL语句解析阶段，SQL语句会做查询转换，这些带有子查询的SQL最终会被展开为只包含一个select的单一查询块的SQL，转换后的SQL语句的查询块命名会与其他部分的命名不一样，它是包含前缀加一个八个字符的哈希值。我们可以通过dbms_xplan.display_cursor增加outline参数来获得SQL PLAN的outline data数据，这些hint里包含了查询块的命名。
| SQL>select * from table(dbms_xplan.display_cursor(null,null,'outline'));
Outline Data -------------
/*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_hintS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('optimizer_dynamic_sampling' 10) OPT_PARAM('_optimizer_skip_scan_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$5DA710D3") UNNEST(@"SEL$2") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") INDEX_FFS(@"SEL$5DA710D3" "TEST"@"SEL$1" ("TEST"."STATUS")) INDEX(@"SEL$5DA710D3" "TEST"@"SEL$2" ("TEST"."STATUS")) LEADING(@"SEL$5DA710D3" "TEST"@"SEL$1" "TEST"@"SEL$2") USE_HASH(@"SEL$5DA710D3" "TEST"@"SEL$2") END_OUTLINE_DATA */ |
如上输出的hint，我们在了解上面所做的描述后，应该大体能够看懂，SEL$1查询块对应的作用域是select count(*) from test为SEL$1，SEL$2查询块对应的作用域是select status from test where status='Inactive'，而SEL$5DA710D3查询块是优化器对SQL做展开后为新SQL生成的查询块，SQL展开后都只会有一层，不包含子查询、嵌套查询等，因此展开后的SQL只会包含一个查询块。我们来对上面的outline data部分的关键hint做出一些解释。INDEX(@"SEL$5DA710D3" "TEST"@"SEL$2" ("TEST"."STATUS"))：@"SEL$5DA710D3"代表整个查询块的名称，说明这个hint是全局hint，"TEST"@"SEL$2"代表引用的对象是在SEL$2查询块内的test，("TEST"."STATUS")代表了索引所引用的字段，这里也可以直接写索引名。
| n Note：通过dbms_xplan.display_cursor增加outline参数来获得SQL PLAN的outline data数据是一种可以快速获得查询块、获得提示的方式，用这些hint可以非常容易获得、构造出我们需要的hint，也是我本人最喜欢使用的一种方式。其实这些hint都是存储在v$sql_plan的other_xml字段中，我们也可以通过转换函数直接从这个字段中查询到这些hint。
SQL>select 2 extractvalue(value(d), '/hint') as outline_hints 3 from 4 xmltable('/*/outline_data/hint' 5 passing ( 6 select 7 xmltype(other_xml) as xmlval 8 from 9 v$sql_plan 10 where 11 sql_id = '4ujkuvfura9ys' 12 and child_number = 0 13 and other_xml is not null 14 ) 15 ) d;
OUTLINE_hintS ------------------------------------------------------------------------ IGNORE_OPTIM_EMBEDDED_hintS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('optimizer_dynamic_sampling' 10) OPT_PARAM('_optimizer_skip_scan_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$5DA710D3") UNNEST(@"SEL$2") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") INDEX_FFS(@"SEL$5DA710D3" "TEST"@"SEL$1" ("TEST"."STATUS")) INDEX(@"SEL$5DA710D3" "TEST"@"SEL$2" ("TEST"."STATUS")) LEADING(@"SEL$5DA710D3" "TEST"@"SEL$1" "TEST"@"SEL$2") USE_HASH(@"SEL$5DA710D3" "TEST"@"SEL$2")
|
我们也可以自己给查询块来命名，这是依靠qb_name这个hint来实现：
| SQL>select /*+ qb_name(wxh) */count(*) from test where status in (select /*+ qb_name(wxb) index_rs(test t_ind)*/status from test where status='Inactive');
COUNT(*) ---------- 100
1 row selected.
SQL>select * from table(dbms_xplan.display_cursor(null,null,'outline'));
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------
Outline Data -------------
/*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_hintS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('optimizer_dynamic_sampling' 10) OPT_PARAM('_optimizer_skip_scan_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$AA13F0C1") UNNEST(@"WXB") OUTLINE(@"WXH") OUTLINE(@"WXB") INDEX_FFS(@"SEL$AA13F0C1" "TEST"@"WXH" ("TEST"."STATUS")) INDEX(@"SEL$AA13F0C1" "TEST"@"WXB" ("TEST"."STATUS")) LEADING(@"SEL$AA13F0C1" "TEST"@"WXH" "TEST"@"WXB") USE_HASH(@"SEL$AA13F0C1" "TEST"@"WXB") END_OUTLINE_DATA */ |
Outline Data输出的hint里，相关的查询块的名称已经变成我们自己命名的查询块的名称了。
最佳实践
默认通过SQL Tuning Advisor创建的SQL Profile，是通过一些修正因子来达到修正执行计划的目的，但是随着数据的变化，时间的推移，这些SQL Profile里包含的修正因子可能也已经不再准确，因此在某些使用了SQL Profile的SQL上可能会发现刚开始这些SQL Profile工作的很好，但是不久就会产生一些问题。虽然SQL Tuning Advisor创建的SQL Profile有着这些缺点，但是用它来为一个SQL产生比优化器更好的执行计划也是一个非常好的事，我本人非常喜欢用SQL Tuning Advisor来优化一个SQL，然后会去查看SQL Profile到底提供了什么建议，然后测试这些建议，如果性能确实有提升，我会考虑接受这些SQL Profile，然后通过本文后面介绍飞方式来锁定这个SQL的执行计划，让SQL Profile达到锁定执行计划的目的。既然SQL Profile也可以使用常见的hint，例如index、full等，那么我们就可以想个办法以常见的hint替换掉修正因子类的hint(OPT_ESTIMATE(@SEL$1, TABLE, TEST@SEL$1, SCALE_ROWS=10)）来达到锁定执行计划的目的。如何把通过SQL Tuning Advisor方式创建的SQL Profile转变为可以直接锁定执行计划的SQL Profile？我们再倒回到本章的第一节：使用SQL Tuning Advisor创建了一个SQL Profile，我们看看如何来锁定这个执行计划。10GR2后，任何SQL解析后，都会在v$sql_plan的other_xml中存储outline需要的hint信息。如：
| SQL>select other_xml from v$sql_plan where sql_id='c37q7z5qjnwwf' and other_xml is not null and child_number = 0;
OTHER_XML -------------------------------------------------------------------------------- <other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![C DATA["TEST"]]></info><info type="plan_hash">4130896540</info><info type="plan_ha sh_2">432850053</info><info type="sql_profile"><![CDATA["profile_c37q7z5qjnwwf_d wrose"]]></info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_hintS]]></hin t><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]></hint><hint><![CDATA[D B_VERSION('11.2.0.3')]]></hint><hint><![CDATA[OPT_PARAM('optimizer_dynamic_sampl ing' 10)]]></hint><hint><![CDATA[OPT_PARAM('_optimizer_skip_scan_enabled' 'false ')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1 ")]]></hint><hint><![CDATA[INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."STATUS" ))]]></hint></outline_data></other_xml> |
输出的信息不容易看懂，10GR2后也在display_cursor中增加了outline参数来获取这一块的信息：
| SQL>select * from table(dbms_xplan.display_cursor('c37q7z5qjnwwf',0,'outline'));
Outline Data -------------
/*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_hintS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('optimizer_dynamic_sampling' 10) OPT_PARAM('_optimizer_skip_scan_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."STATUS")) END_OUTLINE_DATA */
|
执行计划的输出Outline Data部分的数据就是从v$sql_plan的other_xml里取出来的，我们也可以直接通过一些转换函数来把v$sql_plan的other_xml中的值变为我们可以阅读的方式：
| SQL>select 2 extractvalue(value(d), '/hint') as outline_hints 3 from 4 xmltable('/*/outline_data/hint' 5 passing ( 6 select 7 xmltype(other_xml) as xmlval 8 from 9 v$sql_plan 10 where 11 sql_id = 'c37q7z5qjnwwf' 12 and child_number = 0 13 and other_xml is not null 14 ) 15 ) d;
OUTLINE_hintS ------------------------------------------------------------------------------- IGNORE_OPTIM_EMBEDDED_hintS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('optimizer_dynamic_sampling' 10) OPT_PARAM('_optimizer_skip_scan_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."STATUS")) |
因此我们可以在通过SQL Tuning Advisor方式使用SQL Profile 后，通过把other_xml中的hint取出来，replace参数设置为true，然后通过dbms_sqltune.import_sql_profile包把这些hint植入，替换之前由SQL Tuning Advisor产生的SQL Profile，这样就达到了锁定执行计划的目的。我本人已经采用这种方式优化过很多复杂的SQL，屡试不爽！
| SQL>declare 2 l_profile_name varchar2(30); 3 cl_sql_text clob; 4 ar_profile_hints sys.sqlprof_attr; 5 begin 6 select -----从other_xml里取出hint 7 extractvalue(value(d), '/hint') as outline_hints 8 bulk collect 9 into 10 ar_profile_hints 11 from 12 xmltable('/*/outline_data/hint' 13 passing ( 14 select 15 xmltype(other_xml) as xmlval 16 from 17 v$sql_plan 18 where 19 sql_id = 'c37q7z5qjnwwf' 20 and child_number = 0 21 and other_xml is not null 22 ) 23 ) d; 24 25 select -----取出sql文本 26 sql_fulltext 27 into 28 cl_sql_text 29 from 30 v$sqlarea 31 where 32 sql_id = 'c37q7z5qjnwwf'; 33 34 35 select 'profile_'||'c37q7z5qjnwwf'||'_dwrose' 36 into l_profile_name -----构造profile的name 37 from dual; 38 39 dbms_sqltune.import_sql_profile( 40 sql_text => cl_sql_text, 41 profile =>ar_profile_hints, 42 category => '', 43 name => l_profile_name, 44 force_match =>FALSE, 45 replace => true ------取代之前由SQL Tuning Advisor产生的Profile 46 ); 47 48 dbms_output.put_line(' '); 49 dbms_output.put_line('Profile '||l_profile_name||' created.'); 50 dbms_output.put_line(' '); 51 52 end; 53 /
PL/SQL procedure successfully completed. |
上面的代码通过包dbms_sqltune的函数import_sql_profile创建了一个SQL Profile，接受的hint为ar_profile_hints对象，这个对象是从other_xml里解析出来的，replace参数设置为了true，代表替换之前由SQL Tuning Advisor产生的SQL Profile。我们看看新创建的SQL Profile是否起作用了：
| SQL>select count(name) from test where status='Inactive';
COUNT(NAME) ----------- 100
SQL>SQL>select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------ SQL_ID c37q7z5qjnwwf, child number 0 ------------------------------------- select count(name) from test where status='Inactive' Plan hash value: 4130896540
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 218 (100)| | | 1 | SORT AGGREGATE | | 1 | 21 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 25000 | 512K| 218 (1)| 00:00:03 | |* 3 | INDEX RANGE SCAN | T_IND | 25000 | | 63 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Note ----- - SQL Profile profile_c37q7z5qjnwwf_dwrose used for this statement |
根据执行计划的Note部分显示，刚才创建的SQL Profile已经起作用了，而且SQL Profile的名字也是按照我们命名的格式。我们来看看后台存储的hint是什么样子的，如果符合预期的话，应该跟other_xml中的hint一致。
| SQL>SELECT extractValue(value(h),'.') AS hint 2 FROM sys.sqlobj$data od, sys.sqlobj$ so, 3 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h 4 WHERE so.name = 'profile_98p6bqwfau56j_dwrose' 5 AND so.signature = od.signature 6 AND so.category = od.category 7 AND so.obj_type = od.obj_type 8 AND so.plan_id = od.plan_id;
hint ------------------------------------------------------------------------------ IGNORE_OPTIM_EMBEDDED_hintS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."STATUS")) |
不出所料，SQL Profile里存储的hint与当时我们查询v$sql_plan的other_xml里的hint一致。
这种方式非常棒，即使用了SQL Tuning Advisor的优点，可以为SQL提供非常好的优化建议，又使用到了SQL Profile来锁定执行计划，不用担心修正因子过时导致执行计划改变的问题了。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-2155365/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-2155365/
375
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
