SQL Profile（第四篇）

  • wei-xh

    2018-05-30 17:49:20
  • SQL Server
  • 原创

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

--------------------------------------------------------------------------------

11.2.0.3

DATA["TEST"]]>4130896540

sh_2">432850053

wrose"]]>

t>

B_VERSION('11.2.0.3')]]>

ing' 10)]]>

')]]>

")]]>

))]]>

输出的信息不容易看懂，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来锁定执行计划，不用担心修正因子过时导致执行计划改变的问题了。

请使用浏览器的分享功能分享到微信等