Chinaunix首页 | 论坛 | 博客
  • 博客访问: 730527
  • 博文数量: 103
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3079
  • 用 户 组: 普通用户
  • 注册时间: 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

文章分类

全部博文(103)

文章存档

2020年(68)

2014年(3)

2013年(32)

分类: Oracle

2020-05-25 15:36:07

1. SQLT背景介绍

SQLTXPLAIN(简称SQLT)是ORACLE COE提供的一款SQL性能诊断工具,SQLT主要方法是通过输入的一个SQL语句,从而生成一组诊断文件,这些文件用于诊断性能较差的或产生错误结果(WRONG RESULTS)的SQL。

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

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

2. SQLT家族简介

 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)。


1. SQLT宝剑出鞘

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绑定就可以了,详细内容见PART2:http://blog.chinaunix.net/uid-7655508-id-5833184.html


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