凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心
  • 博客访问: 1156295
  • 博文数量: 166
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3760
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

about me:oracle ace pro,optimistic,passionate and harmonious. focus on oracle,mysql and other database programming,peformance tuning,db design, j2ee,linux/aix,architecture tech,etc

文章分类

全部博文(166)

文章存档

2024年(21)

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

相关博文
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·

分类: oracle

2022-04-14 08:44:40

sqlt宝剑出鞘,sql性能问题无所遁形-凯发app官方网站

sqltxplain(简称sqlt)是oracle coe提供的一款sql性能诊断工具,sqlt主要方法是通过输入的一个sql语句,从而生成一组诊断文件,这些文件用于诊断性能较差的或产生错误结果(wrong results)的sql。

sqlt产生的诊断文件内容包括执行计划、统计信息、cbo的参数、10053文件、性能变化的历史等需要诊断sql性能的一系列文件,而且sqlt还提供一系列工具,比如快速绑定sql执行计划的工具。

sqlt主要使用场合是在需要快速绑定sql执行计划,或者一些和参数、bug等相关的疑难sql分析中。

 sqlt主要包含下列方法:
      sqlt 为一个 sql 语句提供了下面 7种主要方法来生成诊断详细信息 xtract,xecute,xtrxec,xtrsby,xplain,xprext 和 xprexc。 xtractxecutextrxecxtrsbyxprext  xprexc 处理绑定变量和会做 bind peeking(绑定变量窥视),但是 xplain 不会。这是因为 xplain 是基于 explain plan for 命令执行的,该命令不做 bind peeking。

因此,如果可能请避免使用xplain.除了 xplain 的 bind peeking 限制外,所有这 7种主要方法都可以提供足够的诊断详细信息,对性能较差或产生错误结果集的 sql 进行初步评估。如果该 sql 仍位于内存中或者 automatic workload repository (awr) 中,请使用 xtract  xtrxec,其他情况请使用 xecute对于 data guard 或备用只读数据库,请使用 xtrsby。仅当其他方法都不可行时,再考虑使用 xplain。xprext 和 xprexc 是类似于 xtract 和 xecute,但为了提高 sqlt 的性能它们禁了一些 sqlt 的特性。


几种主要方法的关系如下:

其中xtrxec包括了xtract和xecute方法,实际上它会同时执行这两个方法生成对应的文件。使用这些方法后,会生成文件,自动打包。


sqlt的详细内容请参考mos文档:sqlt 使用指南 (doc id 1677588.1),本文重点说下sqlt里比较有用的方法(本文内容的环境是11.2.0.3)。


3.1 宝剑出鞘之sqlt生成诊断文件

       生成诊断文件使用的是sqlt/run目录下的文件,此目录下还有sqlhc健康检查的脚本。这里看一个例子:

sql text:

select *

  from test1

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

                  where object_name like 'prc_test%');



这是条简单的子查询sql,其中test1status有索引,而且status有倾斜分布如下


dingjun123@oradb> select status,count(*)

  2  from test1

  3  group by status;


status    count(*)

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

invalid          6

valid        76679

--子查询结果是invalid

dingjun123@oradb> select test2.status from test2

  2                    where object_name like 'prc_test%'

  3  ;


status

-------

invalid

invalid


子查询中的语句返回的正好是invalid,那么可以预测,此语句应该是用子查询结果驱动表test1,走test.status列的索引,正常的应该是走nested loops。ok,那么我们看看执行计划:


执行计划令人费解,要知道,对于表的统计信息是最新的且采样比例100%,而且也收集了status列的直方图,为什么还走hash join,而且test1还走全表呢?先用sqlt诊断下,到sqlt/run目录下找到对应的脚本,然后输入sqlid,之后会将生成的文件打包。


dingjun123@oradb> @sqltxtrxec

pl/sql procedure successfully completed.

elapsed: 00:00:00.00


parameter 1:

sql_id or hash_value of the sql to be extracted (required)


enter value for 1: aak402j1r6zy3


paremeter 2:

sqltxplain password (required)


enter value for 2: xxxxxx

pl/sql procedure successfully completed.

elapsed: 00:00:00.00

value passed to sqltxtrxec:

sql_id_or_hash_value: "aak402j1r6zy3"


       解压文件,即可看到如下内容:

这里我们主要看main文件,这是主要内容以及10053等。

首先打开main文件,可以看到主要诊断内容:

可以看到,包括cbo的环境,执行计划以及历史执行信息,表,索引等对象统计信息都在这个main文件中,大部分时候可以通过此文件,了解sql效率不佳的原因,比如执行计划变坏的时间段内正好收集了统计信息,那么可以快速定位可能是统计信息收集不正确导致的。

       一般情况下,都是先看执行计划,通过plans目录找到execution plans,可以点那些 ,会显示对应的统计信息等内容:


在统计信息正确的情况下,cbo估算的返回结果行是76685行,而实际结果是6行,估算是实际的12781倍,这显然是有问题的。可以点开对应的 ,看看统计信息:

test1的status列收集了直方图,而且是100%采样,没有任何问题。到此,这个简单的sql很可能的情况就是:

1)  cbo的缺陷,无法准确估算对应的结果集的cardinality

2)  cbobug或参数设置原因。


针对以上两种情况,后面会介绍解决方法,这里先说下,为什么这里走了hash join,test1走了full table scan,结果集的cardinality估算的结果正好是test1的行数呢,原因在于:

1)test1的status有直方图

2)子查询结果查询出status,但是查询结果的status值在没有执行之前是未知的,也就是可能是invalid也可能是valid。

综合以上因素,cbo无法在运行期之前预知结果的具体值,从而导致优化器缺陷,走了不佳的执行计划(12c的apative plan可以解决这个问题)。


         既然知道是这个原因,那么,就采用sql profile绑定就可以了,详细内容见
下一篇:sqlt宝剑出鞘,sql性能问题无所遁形_part2

阅读(1117) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图