Chinaunix首页 | 论坛 | 博客
  • 博客访问: 705561
  • 博文数量: 102
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3059
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(102)

文章存档

2020年(67)

2014年(3)

2013年(32)

分类: Oracle

2020-05-25 15:42:56

接上一篇 http://blog.chinaunix.net/uid-7655508-id-5833181.html

1.2   宝剑出鞘之SQLT快速绑定执行计划

SQL PROFILE可以使用SQLT工具快速绑定,SQL PROFILE就是对SQL增加了一系列HINTS,好处是不需要改写SQL,可以在数据库里直接管理。

对于COE工具SQL PROFILE绑定有两类:

1)直接绑定:针对执行计划经常突变的,历史中有好的执行计划,当前走的执行计划差,直接绑定即可。 

2)替换绑定:针对执行计划一直较差,没有好的执行计划作为参考,可通过添加hints让其走好的执行计划,然后通过coe工具手动修改文件或coe_load_sql_profile或者编写存储过程绑定到好的执行计划上。


注意:如果SQL没有绑定变量,则通过coe_xfr_sql_profile生成的文件需要修改force_match=>true,手动编写存储过程或者coe_load_sql_profile做替换绑定的也需要修改force_match=>true,以让所有SQL结构相同(字面量条件不同)的SQL都绑定上好的执行计划。

(对应的绑定计划的脚本在sqlt/utl目录下)


下面分别说说这两种绑定方式:

3.2.1使用coe_xfr_sql_profile脚本直接绑定

针对SQL执行计划经常突变,当计划变差时候,快速绑定到效率高的执行计划中。如下例:运行code_xfr_sql_profile然后输入sql_id:

SQL> @coe_xfr_sql_profile.sql

Parameter 1:

SQL_ID (required)

Enter value for 1: 0hzkb6xf08jhw

PLAN_HASH_VALUE AVG_ET_SECS

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

     3071332600        .006   --效率高的计划

       40103161        653

Parameter 2:         ---------------次数输入需要绑定的PLAN_HASH_VALUE,显然我们输入3071332600 

PLAN_HASH_VALUE (required)

Enter value for 2:

最后生成文件,执行。

注意:如果SQL没有使用绑定变量,需要将生成文件的force_match => FALSE中的FALSE改成TRUE

3.2.2使用coe_load_sql_profile做替换绑定

       3.1中的例子是由于CBO的缺陷导致无法判定子查询结果,从而导致走错了执行计划,这里在12c之前需要绑定执行计划,因为没有现成的执行计划,所以需要自己写hints构造一条正确执行计划的SQL,然后通过SQLT的替换绑定,将正确执行计划绑定到原SQL中去。

       先将原始SQL通过增加hints,让其执行计划正确,改造后的SQL如下:

select/*+

      BEGIN_OUTLINE_DATA

      USE_NL(@"SEL$5DA710D3" "TEST1"@"SEL$1")

      LEADING(@"SEL$5DA710D3" "TEST2"@"SEL$2" "TEST1"@"SEL$1")

      INDEX_RS_ASC(@"SEL$5DA710D3" "TEST2"@"SEL$2" ("TEST2"."OBJECT_NAME"))

      INDEX_RS_ASC(@"SEL$5DA710D3" "TEST1"@"SEL$1" ("TEST1"."STATUS"))

      OUTLINE(@"SEL$2")

      OUTLINE(@"SEL$1")

      UNNEST(@"SEL$2")

      OUTLINE_LEAF(@"SEL$5DA710D3")

      ALL_ROWS

      DB_VERSION('11.2.0.3')

      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */ *

  from test1

 where test1.status in (select test2.status from test2

                  where object_name like 'PRC_TEST%');


然后使用coe_load_sql_profile脚本做替换绑定,输入原始的sql_id和替换的sql_id:


dingjun123@ORADB> @coe_load_sql_profile

Parameter 1:

ORIGINAL_SQL_ID (required)


Enter value for 1: aak402j1r6zy3


Parameter 2:

MODIFIED_SQL_ID (required)


Enter value for 2: 6rbnw92d7djwk


     PLAN_HASH_VALUE          AVG_ET_SECS

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

           313848035                 .001


Parameter 3:

PLAN_HASH_VALUE (required)


Enter value for 3: 313848035


Values passed to coe_load_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ORIGINAL_SQL_ID: "aak402j1r6zy3"

MODIFIED_SQL_ID: "6rbnw92d7djwk"

PLAN_HASH_VALUE: "313848035"


再次执行原始语句,可以看到,绑定执行计划成功,已经走了索引和NESTED LOOPS。



SQLT的快速绑定执行计划,在处理突发SQL性能问题中使用广泛,的确是一个非常好的工具,犹如宝剑出鞘,削铁如泥。

1.3   宝剑出鞘之XPLORE快速诊断参数设置问题

某天晚上某系统一重要语句,迁移到新库后执行1小时都没有结果,原先很快(1s左右),业务人员焦急万分。对应的语句如下:

SELECT

    *

     FROM (SELECT A.ID, A.TEL_ID, A.PRE_CATE_ID, A.INSERT_TIME, A.REMARK1

             FROM TAB_BN_TEST_LOG A,

                  (SELECT TEL_ID, MIN(INSERT_TIME) AS INSERT_TIME

                     FROM TAB_BN_TEST_LOG

                    WHERE INSERT_TIME > '08-APR-19'

                      AND ID NOT IN

                          (SELECT IMEI FROM TX_MM_LOG_201907 WHERE TID = '10')

                    GROUP BY TEL_ID) B

            WHERE A.TEL_ID = B.TEL_ID

              AND A.INSERT_TIME = B.INSERT_TIME

              AND A.ID NOT IN

                  (SELECT IMEI FROM TX_MM_LOG_201907 WHERE TID = '10')

            ORDER BY INSERT_TIME)

    WHERE ROWNUM < 200


查看执行计划:

执行计划中出现FILTER,也就是子查询无法unnest,由于使用的是NOT IN,但是回头一想,这是11g,有null aware特性,应该不会出现FILTER才对,而且使用hints也无效。那么首先想到的就是检查null aware参数是否设置,经过检查:


完全没有问题,那么在收集统计信息、SQL PROFILE、可以想到的参数设置都没有问题情况下,如何解决呢?

由于查询转换受众多参数设置影响,虽然null aware已经开启,但是可能受其它参数或fix control设置影响,因此,这里可以使用SQLT的神器XPLORE分析,它会将已知参数、已知bug对应的fix control逐一重新设置一遍,然后生成对应的执行计划,最后生成一个html文件,通过查看执行计划,找到对应的参数或者BUG。

SQLT XPLORE中有XEXCUTE、XPLAIN等众多方法,对于慢的语句,建议使用XPLAIN方法。然后查看分析结果与目标计划匹配的设置,从而找出问题。


使用XPLORE,可以参考sqlt/utl/xplore中的readme.txt。这里需要将对应的SQL内容里加上

/* ^^unique_id */


最终,生成的XPLORE文件内容如下:

有8个执行计划的PLAN_HASH_VALUE,对应的点进去,找到正确的执行计划对应的参数设置:

     最终找到,原来和_optimizer_squ_bottomup参数有关,这个参数,系统设置成FALSE,导致此子查询无法进行null aware查询转换,重新设置后语句执行恢复到正常时间。

针对这样的情况,如果一个个参数去对比分析,必然耗时很长,使用SQLT的XPLORE神器,可以快速找到对应的参数设置或已知BUG问题,比如一些新特性导致的SQL性能问题、SQL产生错误的结果等,都可以通过XPLORE分析,快速找到对应的参数,然后重新设置。


       最后做个总结:SQLT里还有很多其他的功能,可以通过MOS查看对应的文章,SQLT在解决棘手的SQL性能问题时,的确是一把利器,犹如宝剑出鞘,SQL性能问题无所遁形。


阅读(253) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~