ITPub博客

首页 > 数据库 > SQL Server > SQL Profile(第四篇)

SQL Profile(第四篇)

原创 SQL Server 作者:wei-xh 时间:2018-05-30 17:49:20 0 删除 编辑

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的概念,一个selectupdate/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 testSEL$1select 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 testSEL$1SEL$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_planother_xml里的hint一致。

这种方式非常棒,即使用了SQL Tuning Advisor的优点,可以为SQL提供非常好的优化建议,又使用到了SQL Profile来锁定执行计划,不用担心修正因子过时导致执行计划改变的问题了。

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

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2304719