https://blog.csdn.net/lukeUnique/article/details/79331779

https://mauro-pagano.com/2014/10/27/when-the-going-gets-tough-the-tough-get-going-aka-sqlt-xplore/

When upgrading a database sometime you find that one or more SQLs run slower because of a new and suboptimal execution plan. Usually the number of those SQLs is pretty small compared to the overall workload but it’s not straightforward to understand what caused the plan change so even a small number can become tricky to track down.
Each optimizer fix as well as any new feature could be responsible for the plan change but every patchset introduces quite a lot of fixes/features (just check V$SYSTEM_FIX_CONTROL to get an idea) so how can we find out which specific fix is responsible for our performance regression?

The first good news is that CBO fixes are (usually) tied to the OPTIMIZER_FEATURES_ENABLE (OFE) parameter so we can quickly set this param back to the version of the database we upgraded from and check if the SQL returns to the old good performance.
Assuming the answer is yes then the second good news is SQLT provides a way to evaluate each fix_control and CBO parameter, SQLT XPLORE.

XPLORE is an independent module of SQLT that is available under sqlt/utl/xplore, it does require a very small installation (details in the readme.txt) and can be easily removed after our run is complete.
Let’s play a little with XPLORE to better understand its potential and application.

I have a SQL that regressed in performance after a 11.2.0.3 -> 11.2.0.4 upgrade, the SQL is

select count(*)

  from t1, t2, t3

 where t1.id = t2.id

   and t3.store_id = t2.store_id

   and lower(t1.name) like :b1

   and t1.country=:b2

   and t3.store_id = :b3

   and t1.flag=:b4

and the execution plan after the upgrade (right after parse so no possibility bind peeking is trickying us) is

Plan hash value: 1584518234

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

| Id  | Operation                          | Name         | Rows  | Cost (%CPU)|

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

|   0 | SELECT STATEMENT                   |              |       |   395 (100)|

|   1 |  SORT AGGREGATE                    |              |     1 |            |

|   2 |   NESTED LOOPS                     |              |     1 |   395  (11)|

|   3 |    NESTED LOOPS                    |              |  6702 |   395  (11)|

|   4 |     TABLE ACCESS BY INDEX ROWID    | T2           |     1 |     2   (0)|

|*  5 |      INDEX RANGE SCAN              | T2_STORE_IDX |     1 |     1   (0)|

|   6 |     BITMAP CONVERSION TO ROWIDS    |              |       |            |

|   7 |      BITMAP AND                    |              |       |            |

|   8 |       BITMAP CONVERSION FROM ROWIDS|              |       |            |

|*  9 |        INDEX RANGE SCAN            | T1_ID_IDX    |  6702 |    18   (6)|

|  10 |       BITMAP CONVERSION FROM ROWIDS|              |       |            |

|  11 |        SORT ORDER BY               |              |       |            |

|* 12 |         INDEX RANGE SCAN           | T1_FLAG_IDX  |  6702 |   103   (6)|

|* 13 |    TABLE ACCESS BY INDEX ROWID     | T1           |     1 |   395  (11)|

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

Predicate Information (identified by operation id):

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

   5 - access("T2"."STORE_ID"=:B3)

   9 - access("T1"."ID"="T2"."ID")

  12 - access("T1"."FLAG"=:B4)

       filter("T1"."FLAG"=:B4)

  13 - filter((LOWER("T1"."NAME") LIKE :B1 AND "T1"."COUNTRY"=:B2))

Note T3 is removed by Join Elimination transformation
The plan cost is pretty small because the estimation for step 12 is very off and the real number of rows returned on such step is over 90% of the data, making the performance drop significantly.
Setting OFE back to 11.2.0.3 then the old good plan is generated

Plan hash value: 2709605153

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

| Id  | Operation                      | Name         | Rows  | Cost (%CPU)|

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

|   0 | SELECT STATEMENT               |              |       |  2007 (100)|

|   1 |  SORT AGGREGATE                |              |     1 |            |

|   2 |   NESTED LOOPS                 |              |   354 |  2007   (2)|

|   3 |    NESTED LOOPS                |              |  6702 |  2007   (2)|

|   4 |     TABLE ACCESS BY INDEX ROWID| T2           |     1 |     2   (0)|

|*  5 |      INDEX RANGE SCAN          | T2_STORE_IDX |     1 |     1   (0)|

|*  6 |     INDEX RANGE SCAN           | T1_ID_IDX    |  6702 |    18   (6)|

|*  7 |    TABLE ACCESS BY INDEX ROWID | T1           |   332 |  2005   (2)|

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

Predicate Information (identified by operation id):

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

   5 - access("T2"."STORE_ID"=:B3)

   6 - access("T1"."ID"="T2"."ID")

   7 - filter((LOWER("T1"."NAME") LIKE :B1 AND "T1"."COUNTRY"=:B2 AND

      "T1"."FLAG"=:B4))

Here the cost is more realistic and the CBO stayed away from T1_FLAG_IDX so the final performance is much better

We have one of those cases where after an upgrade the SQL runs slow and setting OFE back to the previous version reverts the old good plan, let’s see how XPLORE is going to help us find out what changed my plan.

In my specific case I reproduced both plans in a test environment where I have no data (took 3 mins to reproduce thanks to a SQLT TC sqlt 之分析 DB upgrade 导致SQL 性能下降的方法 xplore so even the poor plan will not take more than a few milliseconds to run, just the parse time.

To install XPLORE all we need to do is connect as SYS, run install.sql and provide the username/pwd of the user we want to run XPLORE from. In case our system runs with a non default CBO environment and we need it to replicate the plans then we will be asked to set the proper environment too so that XPLORE can define a baseline CBO environment.
At the end of the installation a file called xplore_script_1.sql is generated, that’s our XPLORE driver script.

Next step is to run XPLORE so let’s connect as our application user and start xplore_script_1.sql
Input parameters are the name of the script for our SQL (remember the mandatory /* ^^unique_id */ comment!!!) and the password for our application user.

SQL> @xplore_script_1.sql

CONNECTED_USER

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

TC84168

Parameter 1:

Name of SCRIPT file that contains SQL to be xplored (required)

Note: SCRIPT must contain comment /* ^^unique_id */

Enter value for 1: myq.sql

Parameter 2:

Password for TC84168 (required)

Enter value for 2:

At this point XPLORE will test all the fix_controls and CBO parameters generating an execution plan for each of them (for some parameters, ie. optimizer_index_cost_adj we test several values), packing the result in a HTML report.

Let’s navigate the result

sqlt 之分析 DB upgrade 导致SQL 性能下降的方法 xplore

The section reports a list of all the PHVs identified, how many tests generated each plan as well as other useful information about those plans (some details later).
Looks like both are good (PHV 2709605153) and bad (PHV 1584518234) execution plans have been reproduced so let’s focus our attention on those two in the next section

sqlt 之分析 DB upgrade 导致SQL 性能下降的方法 xplore

We see that for our bad plan there are three lines, same PHV but different SQLT PHVs, that’s because those two additional PHVs are more restrictive and take into consideration additional factors (ie. filters) to better help differentiate between plans.
The plan in line #3 has been generated by 1148 tests including our baseline (‘B’) “execution zero” that is with no parameter/fix_control change. It’s not surprising to have so many tests generating the same plan as the baseline because most of the fixes/params usually don’t affect each and every SQL.

Our target plan is the one reported at lines #7,8,9 so let’s navigate to them

sqlt 之分析 DB upgrade 导致SQL 性能下降的方法 xplore

That’s the list of all the parameters and fix_controls that can lead to our good plan so we just need to go back to MOS and get some more information about those fixes to decide which one we want to test.
In this case the answer is 12555499 because it generates the same identical plan as OFE=11.2.0.3.

It’s usually better to use a fix_control rather than a parameter since the former is more likely to have a narrower scope than the latter.

SQL> alter session set "_fix_control"='12555499:0';

Session altered.

SQL> @myq

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

  COUNT(*)

----------

	 0

Plan hash value: 2709605153

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

| Id  | Operation                      | Name         | Rows  | Cost (%CPU)|

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

|   0 | SELECT STATEMENT               |              |       |  2007 (100)|

|   1 |  SORT AGGREGATE                |              |     1 |            |

|   2 |   NESTED LOOPS                 |              |   354 |  2007   (2)|

|   3 |    NESTED LOOPS                |              |  6702 |  2007   (2)|

|   4 |     TABLE ACCESS BY INDEX ROWID| T2           |     1 |     2   (0)|

|*  5 |      INDEX RANGE SCAN          | T2_STORE_IDX |     1 |     1   (0)|

|*  6 |     INDEX RANGE SCAN           | T1_ID_IDX    |  6702 |    18   (6)|

|*  7 |    TABLE ACCESS BY INDEX ROWID | T1           |   332 |  2005   (2)|

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

Predicate Information (identified by operation id):

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

   5 - access("T2"."STORE_ID"=:B3)

   6 - access("T1"."ID"="T2"."ID")

   7 - filter((LOWER("T1"."NAME") LIKE :B1 AND "T1"."COUNTRY"=:B2 AND

	      "T1"."FLAG"=:B4))

So using SQLT XPLORE we found in a few minutes which fix changed our execution plan, a workaround with a very narrow scope and also a way to research in MOS if any known issue related to this specific fix has been reported.

SQLT XPLORE can be used to troubleshoot other types of issues too, ie the report shows Min/Max Elapsed Time (ET) per plan as well as the ET per test, in case of no-data testcase then all the time will be parse time so we can use XPLORE to troubleshoot slow-parse issues and find which feature is accounting for most of the time and what to set to reduce such parse time

########2

http://blog.itpub.net/28539951/viewspace-2109691/

文档Doc ID 1526574.1包含了下载,安装,参考资料,最佳实践等相关信息.

以下是一个简单的安装测试:
OS:centos 6.6
DB:oracle 11.2.0.4

#安装
[oracle@ct6604 ~]$ ll sqlt*
-rw-r--r-- 1 root root 919827 Apr 5 16:06 sqlt_latest.zip
#解压
[oracle@ct6604 ~]$ unzip sqlt_latest.zip
[oracle@ct6604 ~]$ cd sqlt
[oracle@ct6604 sqlt]$ ls
doc input install run sqlt_instructions.html sqlt_instructions.txt utl
[oracle@ct6604 sqlt]$ cd install/

[oracle@ct6604 install]$ ORACLE_SID=ctdb
[oracle@ct6604 install]$ sqlplus / as sysdba
#卸载先前sqlt版本(可选)
SQL> @sqdrop.sql

#安装sqlt
#sqcsilent2.sql后面6个参数的含义:connect_identifier,tool_password,default_tablespace,temporary_tablespace,main_application_schema,pack_license
SQL> @sqcsilent2.sql '' sqlt_pwd USERS TEMP SYSTEM T
SQL> exit

#测试
[oracle@ct6604 install]$ cd ~
[oracle@ct6604 ~]$ sqlplus system/system
SQL> select /* 1 */a.* from scott.emp a,scott.dept b where a.deptno=b.deptno and b.dname='SALES';
SQL> select sql_text,sql_id from v$sql where sql_text like 'select /* 1 */a.* from scott.emp a%';
/*
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
select /* 1 */a.* from scott.emp a,scott.dept b where a.deptno=b.deptno and b.dn
ame='SALES'
864hxkdxph5th
*/

#用于设置生成测试用例时是否包含业务数据以及要收集的比例,默认不收集业务数据
SQL>EXEC sqltxadmin.sqlt$a.set_sess_param('tcb_export_data', 'TRUE');
SQL>EXEC sqltxadmin.sqlt$a.set_sess_param('tcb_sampling_percent', '100');
SQL>EXEC sqltxadmin.sqlt$a.set_sess_param('tcb_export_pkg_body', 'TRUE');

#生成报告
SQL> @/home/oracle/sqlt/run/sqltxtrxec.sql 864hxkdxph5th sqlt_pwd
/*
...
updating: sqlt_s95230_tc_script.sql (deflated 17%)

adding: sqlt_s95230_xtract_864hxkdxph5th.zip (stored 0%)
adding: sqlt_s95231_xecute.zip (stored 0%)
adding: sqltxtrxec.log (deflated 74%)

PL/SQL procedure successfully completed.

SQLTXTRXEC completed.
*/

下面是官方的sqlt使用指南 (文档 ID 1677588.1)

SQLT 概览

SQLTXPLAIN，也称为 SQLT，是 Oracle Server Technologies Center of Expertise - ST CoE 提供的一款工具。 SQLT 主要方法通过输入的一个 SQL 语句，可输出一组诊断文件。这些文件通常用于诊断性能不佳或者产生错误结果的 SQL 语句。

一旦安装，便可通过向 SQLT 传递一个包含 SQL 语句脚本（包括绑定变量）的文本文件或者其 SQL_ID，对 SQL 语句进行分析。SQL_ID 可在 AWR 和 ASH 的报告中找到，HASH_VALUE 出现在 SQL_TRACE 的输出中（SQL 文本的上面以"hv="的标志来识别）。您也能在 V$SQL 视图中发现这些字段。请参照接下来的文档：

Document 1627387.1 How to Determine the SQL_ID for a SQL Statement

SQLT 主要方法会连接到数据库，收集执行计划、基于成本的 Optimizer CBO 统计信息、Schema 对象元数据、性能统计信息、配置参数和会影响正在分析的 SQL 性能的其他元素。这些方法会对有问题的SQL_ID产生一揽子输出，包括一个html格式的"main"报表。关于如何使用"main"报表，您可以参考下面的文档：

Document 1922234.1 SQLT Main Report: Usage Suggestions

SQLT 可以使用 Oracle Diagnostic 和（或）Oracle Tuning Packs，前提是您的数据库需要具有这两个软件包的许可证。这两个软件包向 SQLT 工具提供了增强的功能。在 SQLT 安装期间，您可以指定是否其中一个软件包在您的数据库被授权使用。如果一个都没有，SQLT 仍会提供一些基本信息，用于最开始的 SQL 性能诊断。

安全模式

SQLT 在安装的过程中会创建两个用户和一个角色。这些用户和角色的名字都是固定的。

SQLT repository 是由用户 SQLTXPLAIN管理的。SQLT 的使用者每次使用 SQLT 提供的主要方法时都要提供 SQLTXPLAIN 的密码。 SQLTXPLAIN 用户被赋予了以下系统权限：

CREATE SESSION
CREATE TABLE

SQLT 包含的 PL/SQL 程序包以及视图都是由用户 SQLTXADMIN 管理的。SQLTXADMIN 用户处于锁定状态并且由一个随机产生的密码保护。SQLTXADMIN 用户被赋予了以下系统权限：

ADMINISTER SQL MANAGEMENT OBJECT
ADMINISTER SQL TUNING SET
ADVISOR
ALTER SESSION
ANALYZE ANY
SELECT ANY DICTIONARY
SELECT_CATALOG_ROLE

所有 SQLT 的使用者在使用 SQLT 提供的主要方法之前必须被赋予 SQLT_USER_ROLE 这个角色。SQLT_USER_ROLE 角色被赋予了以下系统权限：

ADVISOR
SELECT_CATALOG_ROLE

12c 在缺省情况下 SYS 用户不能作为 SQLT 的用户，因为 PL/SQL 安全模型上改变的原因。
为了处理这个改变，SQLTADMIN 需要在 SYS 上被授予 INHERIT PRIVILEGES 权限。
GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN
更多详细的内容请参见 Oracle? Database PL/SQL Language Reference 12c Release 1 (12.1) - Invoker's Rights and Definer's Rights (AUTHID Property) and in Oracle? Database Security Guide 12c Release 1 (12.1) - Managing Security for Definer's Rights and Invoker's Rights

安装 SQLT

SQLT 安装在其自己的 schema SQLTXPLAIN 和 SQLTXADMIN 下。它不会将任何对象安装到应用程序 schema 中。您可以在 UNIX、Linux 或 Windows 平台，Oracle DB 10.2、11.1、11.2 及更高版本中安装此版本的 SQLT。

安装步骤：

卸载先前版（可选）。

该可选步骤将删除所有废弃的 SQLTXPLAIN/SQLTXADMIN schema 对象，并为全新安装准备环境。如果要保留 SQLT Repository 的现有内容，请跳过此步骤（推荐）。

# cd sqlt/install

# sqlplus / as sysdba

SQL> START sqdrop.sql

以 SYS 身份连接数据库并执行安装脚本 sqlt/install/sqcreate.sql。

# cd sqlt/install

# sqlplus / as sysdba

SQL> START sqcreate.sql

在安装期间，系统将要求您输入以下参数值：

可选连接标识符(当安装在一个可插拔数据库上时是必须的）

在一些受限访问的系统中，您可能需要指定连接标识符，例如 @PROD。如果不需要连接标识符，则不要输入任何数据，只需单击回车键。什么也不键入是最常使用的安装方法。
当安装在一个可插拔数据库上时连接标识符是必须提供的。

SQLTXPLAIN 密码。

在大多数系统中区分大小写。

SQLTXPLAIN 默认表空间。

从可用的永久表空间列表中，选择 SQLT Repository 的 SQLTXPLAIN 应使用的表空间。它必须具有 50MB 以上的可用空间。

SQLTXPLAIN 临时表空间。

从可用的临时表空间列表中，选择 SQLTXPLAIN 临时操作和临时对象应使用的表空间。

可选应用程序用户。

这是发出要分析 SQL 语句的用户。例如，在 EBS 系统上，指定为 APPS；在 Siebel 上，应指定为 SIEBEL；在 People Soft 上，应指定为 SYSADM。系统不会要求您输入此用户的密码。也可以在安装该工具后添加其他的 SQLT 用户，方法为：授予他们角色 SQLT_USER_ROLE。

授权的 Oracle Pack。（T，D 或 N）

可以指定 T 表示 Oracle Tuning；D 表示 Oracle Diagnostic，或 N 表示都没有。如果选择 T 或 D，SQLT 可以在它生成的诊断文件中包含授权的内容。默认值为 T。如果选择 N，SQLT 将只安装限定的功能。

如果需要静默安装，可以使用下面三个选项来传递所有 6 个安装参数：

在文件中。

首先使用一个脚本进行值的预先定义，类似于示例脚本 sqlt/install/sqdefparams.sql。然后使用 sqlt/install/sqcsilent.sql，而不是 sqlt/install/sqcreate.sql。

# cd sqlt/install

# sqlplus / as sysdba

SQL> START sqdefparams.sql

SQL> START sqcsilent.sql

命令行。

执行 sqlt/install/sqcsilent2.sql，而不是 sqlt/install/sqcreate.sql。前者以内嵌形式输入 6 个安装参数。

# cd sqlt/install

# sqlplus / as sysdba

SQL> START sqcsilent2.sql '' sqltxplain USERS TEMP '' T

在 Oracle 内部安装。

执行 sqlt/install/sqcinternal.sql，而不是 sqlt/install/sqcreate.sql。前者首先执行 sqlt/install/sqdefparams.sql，然后是 sqlt/install/sqcsilent.sql。

# cd sqlt/install

# sqlplus / as sysdba

SQL> START sqcinternal.sql

假如您需要更多关于安装问题的帮助，您能从下面的论坛得到帮助：SQLTXPLAIN: SQLT Installation Issues

卸载 SQLT

卸载 SQLT 会同时移除 SQLT Repository 以及所有 SQLTXPLAIN/SQLTXADMIN schema 对象。另外还会删除 SQLTXPLAIN和 SQLTXADMIN 用户。要卸载 SQLT，只需以 SYS 身份连接，执行 sqlt/install/sqdrop.sql。

# cd sqlt/install

# sqlplus / as sysdba

SQL> START sqdrop.sql

升级 SQLT

如果在系统中已安装先前版本的 SQLT，则可以将 SQLT 升级到其最新版本，同时部分保留现有 SQLT Repository 的一些对象。然后可以使用新迁移的 SQLT Repository 来恢复 CBO 统计信息，或者在 SQLT 的新旧执行程序之间执行 COMPARE。

要升级 SQLT，只需执行安装，而不用执行可选卸载步骤。

如果升级失败，可能是先前的 SQLT 版本太旧而无法升级。在这种情况下，请先继续卸载 SQLT，然后执行全新安装。

常见问题

请参考 MOS 文档 ID1454160.1.

主要方法

在使用 SQLT 提供的主要方法之前，须确保 SQLT 已经被正确安装，并且使用 SQLT 的用户被赋予了 SQLT_USER_ROLE 角色。

假如 SQLT 安装绕过了 SQL*Net （意味着您并没有在安装期间没有指定连接符），那么在从远端客户端执行任何 SQLT 主要方法前，您将需要手动设置连接符参数。即，假如您连接使用 sqlplus scott/tiger@myprod 那么您需要执行： EXEC sqltxadmin.sqlt$a.set_sess_param('connect_identifier', '@myprod');

SQLT 为一个 SQL 语句提供了下面 7种主要方法来生成诊断详细信息 XTRACT，XECUTE，XTRXEC，XTRSBY，XPLAIN，XPREXT 和 XPREXC。 XTRACT，XECUTE，XTRXEC，XTRSBY，XPREXT 和 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 的特性。

XTRACT 方法

如果您知道待分析 SQL 的 SQL_ID 或 HASH_VALUE，请使用该方法，否则请使用 XECUTE。您可以在 AWR report 中找到 SQL_ID，在 SQL trace 中找到 HASH_VALUE（在 SQL 文本上面，通过 "hv=" 标记进行标识）。

如果该 SQL 仍位于内存中，或其已被 AWR 捕获，那么使用 XTRACT 可发现该 SQL 并提供一组诊断文件，否则 XTRACT 将输出错误。

如果对 SQL 进行硬分析时将参数 STATISTICS_LEVEL 设置为 ALL，将可以得到重要的性能统计信息（如每步操作的实际行数）。您也可以通过在 SQL 中包括以下 CBO 提示来生成同样有用的性能统计信息：/*+ GATHER_PLAN_STATISTICS */。在 11g 中，您可以在 SQL 中包含以下 CBO 提示以获得增强的诊断信息：/*+ GATHER_PLAN_STATISTICS MONITOR */

使用此方法时，它会要求提供 SQLTXPLAIN 密码，这个在导出与该 XTRACT 执行所对应的 SQLT Repository 时会被使用。

该方法需要对执行 SQLT 的应用程序用户授予 SQLT_USER_ROLE 角色。

要使用该 XTRACT 方法，首先确保已安装了 SQLT，然后以已执行待分析 SQL 的应用程序用户身份连接到 SQL*Plus，并执行 sqlt/run/sqltxtract.sql 脚本，传递 SQL_ID 或 HASH_VALUE。

###sample

##安装SQLT 5分钟

##运行分析：先授权，在运行分析，总时间大概5分钟

Grant SQLT_USER_ROLE to dbmonopr;

START sqltxtract.sql 7yqwwybwctqsp sqlt

注意事项：

In case of a session disconnect please verify the following:
1. There are no errors in sqltxtract.log.
2. Your SQL 7yqwwybwctqsp exists in memory or in AWR.
3. You connected as the application user that issued original SQL. 或者SYS用户
4. User SYS has been granted SQLT_USER_ROLE.
5. temp 表空间1024M 以上

6. application user 有SQLT_USER_ROLE 权限。

# cd sqlt/run

# sqlplus apps

SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]

SQL> START sqltxtract.sql 0w6uydn50g8cx sqltxplain_password

SQL> START sqltxtract.sql 2524255098 sqltxplain_password

XECUTE 方法

与 XTRACT方法相比，该方法提供的信息更为详细。正如名称 XECUTE 所指示的，它将执行正在分析的 SQL，然后生成一组诊断文件。它的主要缺点是如果正在分析的 SQL 需要很长时间来执行，那么该方法也要花费很长的时间。

根据经验法则，仅当 SQL 执行少于 1 小时的情况下，才考虑使用此方法，否则请使用 XTRACT。

使用此 XECUTE 方法之前，必须创建一个包含 SQL 文本的文本文件。如果 SQL 包括绑定变量，则您的文件必须包含绑定变量声明和赋值。以 sqlt/input/sample/script1.sql 为例。您的 SQL 应该包含标记 /* ^^unique_id */（强烈建议）。

如果您的 SQL 需要与 SQL*Plus 无法使用的数据类型绑定，或者它使用了集合，您可能需要将 SQL 嵌入到匿名 PL/SQL 块中。在这种情况下，请使用 sqlt/input/sample/plsql1.sql 作为此方法的输入示例。

对于修改数据的语句，例如 INSERT/UPDATE/DELETE，工具会在语句执行之前创建一个保存点，这样在会话结束时事务处理可以回退到该保存点。关于 SAVEPOINT（保存点）的更多信息，请参阅《Oracle Concepts》参考手册。

使用此方法时，它会要求提供 SQLTXPLAIN 密码，这个在导出与该 XECUTE 执行所对应的 SQLT Repository 时会被使用。

该方法需要对执行 SQLT 的应用程序用户授予 SQLT_USER_ROLE 角色。

要使用该 XECUT 方法，首先确保已安装了 SQLT，然后以已执行待分析 SQL 的应用程序用户身份连接到 SQL*Plus，并执行 sqlt/run/sqltxecute.sql 脚本，传递包含 SQL 文本及其绑定变量的文本文件名称。您需要将该文件放置到 sqlt/input 目录下，并在位于 sqlt 主目录时运行 XECUTE，如下所示：

# cd sqlt

# sqlplus apps

SQL> START [path]sqltxecute.sql [path]scriptname [sqltxplain_password]

SQL> START run/sqltxecute.sql input/sample/script1.sql sqltxplain_password

XTRXEC 方法

该方法合并了 XTRACT 和 XECUTE 的功能。实际上，XTRXEC 连续执行了这两种方法。针对所请求 SQL 语句找到的开销较大的计划，XTRACT 阶段将生成一个包含提取的 SQL 以及绑定声明和赋值的脚本。然后，XTRXEC 使用第一阶段创建的脚本执行XECUTE 阶段。

SQLT 根据在内存中生成开销最大的执行计划时窥视到的值，创建脚本的绑定变量的以供 XTRACT使用。判断计划的开销大小的标准是基于这个计划的平均执行时间。

如果 XTRXEC 仅执行了第一个阶段（XTRACT）后就输出错误，您可能需要检查在第二阶段（XECUTE）使用的脚本并相应调整绑定变量。使用不常用数据类型时尤其需要进行调整。

使用此方法时，它会要求提供 SQLTXPLAIN 密码，这个在导出与该 XTRXEC 执行所对应的 SQLT Repository 时会被使用。

该方法需要对执行 SQLT 的应用程序用户授予 SQLT_USER_ROLE 角色。

要使用该 XTRXEC 方法，首先确保已安装了 SQLT，然后以执行待分析 SQL 的应用程序用户身份连接到 SQL*Plus，并执行 sqlt/run/sqltxtrxec.sql 脚本，传递 SQL_ID 或 HASH_VALUE。

# cd sqlt/run

# sqlplus apps

SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]

SQL> START sqltxtrxec.sql 0w6uydn50g8cx sqltxplain_password

SQL> START sqltxtrxec.sql 2524255098 sqltxplain_password

XTRSBY 方法

如果需要分析在 Data Guard 或备用只读数据库上执行的 SQL，请使用该方法。您需要知道要分析的 SQL 的 SQL_ID 或 HASH_VALUE。

在主库上创建一个到备库的database link，连接到的用户需要有访问数据字典的权限，通常都是使用有 DBA 权限的用户。
CREATE PUBLIC DATABASE LINK V1123 CONNECT TO mydba IDENTIFIED by mydba_password
USING '(DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)
(HOST=coesrv14.us.oracle.com)(PORT=1521))(CONNECT_DATA=(SID = V1123)))';

如果该 SQL 仍位于只读数据库中的内存中，那么使用 XTRSBY 可发现该 SQL 并提供一组诊断文件，否则 XTRSBY 将输出错误。

如果对只读数据库中的 SQL 进行硬分析时将参数 STATISTICS_LEVEL 设置为 ALL，将可以得到重要的性能统计信息（如每个执行计划操作的实际行数）。您也可以通过在 SQL 中包括以下 CBO 提示来生成同样有用的性能统计信息：/*+ GATHER_PLAN_STATISTICS */。在 11g 中，您可以在 SQL 中包含以下 CBO 提示以获得改进的诊断信息：/*+ GATHER_PLAN_STATISTICS MONITOR */

使用此方法时，它会要求提供 SQLTXPLAIN 密码，这个在导出与该 XTRSBY 执行所对应的 SQLT Repository 时会被使用。

XTRSBY 需要 3 个参数： SQL id，DB_LINK的 ID，以及 SQLTXPLAIN 的密码。

该方法需要对执行 SQLT 的应用程序用户授予 SQLT_USER_ROLE 角色。

要使用该 XTRSBY 方法，首先确保在主数据库上已安装了 SQLT，并且已复制到该只读数据库中。然后连接到主要数据库中的 SQL*Plus 并执行 sqlt/run/sqltxtrsby.sql 脚本，传递 SQL_ID 或 HASH_VALUE，然后是 DB_LINK。

# cd sqlt/run

# sqlplus apps

SQL> START sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] [DB_LINK]

SQL> START sqltxtrsby.sql 0w6uydn50g8cx sqltxplain_password V1123

SQL> START sqltxtrsby.sql 2524255098 sqltxplain_password v1123

除了 XTRSBY，还可以从只读数据库直接执行 sqlt/utl/sqlhc.sql 或 sqlt/utl/sqlhcxec.sql。这两个只读脚本不在数据库上安装任何东西，也不执行 DML 命令。它们提供在 XTRSBY 中没有的其他信息。

XPLAIN 方法

该方法是基于 EXPLAIN PLAN FOR 命令执行的，因此它将无视您的 SQL 语句引用的绑定变量。仅当无法使用 XTRACT 或 XECUTE 时才使用该方法。

使用此 XPLAIN 方法之前，必须创建一个包含 SQL 文本的文本文件。如果 SQL 包括绑定变量，您有两个选择：保持 SQL 文本“不变”，或谨慎使用相同数据类型的字面值替换该绑定。以 sqlt/input/sample/sql1.sql 为例。

使用此方法时，它会要求提供 SQLTXPLAIN 密码，这个在导出与该 XPLAIN 执行所对应的 SQLT Repository 时会被使用。

该方法需要对执行 SQLT 的应用程序用户授予 SQLT_USER_ROLE 角色。

要使用该 XPLAIN 方法，首先确保已安装了 SQLT，然后以已执行待分析 SQL 的应用程序用户的身份连接到 SQL*Plus，并执行 sqlt/run/sqltxplain.sql 脚本，传递包含 SQL 文本的文本文件名称。您需要将该文件放置到 sqlt/input 目录下，并在位于 sqlt 主目录时运行 XPLAIN，如下所示：

# cd sqlt

# sqlplus apps

SQL> START [path]sqltxplain.sql [path]filename [sqltxplain_password]

SQL> START run/sqltxplain.sql input/sample/sql1.sql sqltxplain_password

XPREXT 方法

假如您想使用 XTRACT 同时希望禁用一些 SQLT 的特性使之执行更快，请使用这个方法。脚本 sqlt/run/sqltcommon11.sql 显示了哪些特性被禁用。

假如您知道要被分析的 SQL 的 SQL_ID 或者 HASH_VALUE，使用这个方法，否则请使用 XPREXC.您可以在 AWR report 中找到 SQL_ID，在 SQL trace 中找到 HASH_VALUE（在 SQL 文本上面，通过 "hv=" 标记进行标识）。

使用此方法时，它会要求提供 SQLTXPLAIN 密码，这个在导出与该 XPREXT 执行所对应的 SQLT Repository 时会被使用。

该方法需要对执行 SQLT 的应用程序用户授予 SQLT_USER_ROLE 角色。

要使用该 XPREXT 方法，首先确保已安装了 SQLT，然后以已执行待分析 SQL 的应用程序用户身份连接到 SQL*Plus，并执行 sqlt/run/sqltxprext.sql 脚本，传递 SQL_ID 或 HASH_VALUE。

# cd sqlt/run

# sqlplus apps

SQL> START sqltxprext.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]

SQL> START sqltxprext.sql 0w6uydn50g8cx sqltxplain_password

SQL> START sqltxprext.sql 2524255098 sqltxplain_password

XPREXC 方法

假如您想使用 XECUTE 同时希望禁用一些 SQLT 的特性使之执行更快，请使用这个方法。脚本 sqlt/run/sqltcommon11.sql 显示哪些特性被禁用

根据经验法则，仅当 SQL 执行少于 1 小时的情况下，才考虑使用此方法，否则请使用 XPREXT。

使用此 XPREXC 方法之前，必须创建一个包含 SQL 文本的文本文件。如果 SQL 包括绑定变量，则您的文件必须包含绑定变量声明和赋值。以 sqlt/input/sample/script1.sql 为例。您的 SQL 应该包含标记 /* ^^unique_id */，这个标记应该拼写准确，换句话说请不要改变它。

用此方法时，它会要求提供 SQLTXPLAIN 密码，这个在导出与该 XPREXC 执行所对应的 SQLT Repository 时会被使用。

该方法需要对执行 SQLT 的应用程序用户授予 SQLT_USER_ROLE 角色。

要使用该 XPREXC 方法，首先确保已安装了 SQLT，然后以已执行待分析 SQL 的应用程序用户身份连接到 SQL*Plus，并执行 sqlt/run/sqltxprexc.sql 脚本，传递包含 SQL 文本及其绑定变量的文本文件名称。您需要将该文件放置到 sqlt/input 目录下，并在位于 sqlt 主目录时运行 XPREXC，如下所示：

# cd sqlt

# sqlplus apps

SQL> START [path]sqltxprexc.sql [path]scriptname [sqltxplain_password]

SQL> START run/sqltxprexc.sql input/sample/script1.sql sqltxplain_password

特殊方法

除了主要方法，SQLT 还提供了一些特殊方法。

最常用的特殊方法是 COMPARE。该方法将 SQLT 先前的两次执行（主要方法中的任何一个）作为输入并生成差异分析的报告。

其他特殊方法包括：TRCANLZR，TRCAXTR，TRCASPLIT 和 XTRSET。前三种方法基于一个单独的 SQL trace 执行操作，最后一种对一组 SQL trace 执行操作。

COMPARE 方法

当您具有两个相似的系统 (SOURCES)，但是相同 SQL 语句在其中一个系统中执行正常而在另一个系统中不正常时，请使用该 COMPARE 方法。该方法可以帮助确定两个 SOURCES 之间在计划、元数据、CBO 统计信息、初始化参数以及问题修复控制方面的区别。前提是必须在这两个系统中安装 SQLT，并且必须在两个系统中的相同 SQL 上使用了主要方法中的任何一个。

这种比较可以在任何两个 SOURCES 数据库的其中一个或第三个 COMPARE 数据库执行。后者应包含两个 SOURCES 的 SQLT Repoitory。要导入 SQLT Repository，请使用由任何主要方法生成的 sqlt_99999_readme.html 文件中提供的语法。

一旦 COMPARE 系统包含来自两个 SOURCES 的资源库，执行 sqlt/run/sqltcompare.sql，以 SYS 或应用程序用户身份连接。系统将显示 STATEMENT_ID 的列表，您可以从中选择要比较的两个 SQLT 存储的执行程序。输入两个 STATEMENT_ID 后，会要求您提供来自两个 SOURCES 的特定 PLAN_HASH_VALUE。

# cd sqlt

# sqlplus sqltxplain

SQL> START [path]sqltcompare.sql [STATEMENT_ID 1] [STATEMENT_ID 2]

SQL> START run/sqltcompare.sql 92263 72597

SQL> START run/sqltcompare.sql

TRCANLZR 方法

该方法将 SQL 跟踪文件名作为输入并分析该文件。实际 trace 必须位于 TRCA$INPUT1 目录中，其在安装期间默认为 USER_DUMP_DEST 目录。

TRCANLZR 方法还可以将多个相关 trace 作为一组同时进行分析。当分析 PX trace 时将需要用到该功能。在这种情况下，需要创建一个包含 trace 列表的 control.txt 文件（每行一个文件名，不包括路径指定），并将此 control.txt 放置到 TRCA$INPUT1 或 TRCA$INPUT2 目录中。这两个目录在安装。期间分别默认为 USER_DUMP_DEST 和 BACKGROUND_DUMP_DEST。然后，TRCANLZR 将从两个输入目录之一读取 control.txt 文件，并在这两个目录的任何一个中查找 trace 集。

TRCANLZR 类似于 TKPROF，但它具有扩展功能。当它分析一个 trace （或 trace 集）时，它还包括类似 CBO 统计信息的 Schema 对象特征以及一些其他重要的性能度量。

要使用此 TRCANLZR 方法，请确保首先已经安装了 SQLT。然后，以生成 trace 的应用程序用户身份启动 SQL*Plus，并执行 sqlt/run/sqltrcanlzr.sql 脚本，传递待分析 trace 的名称或者填充了文件名的 control.txt 文件名称。不用包括任何路径指定。

# cd sqlt

# sqlplus [application_user]

SQL> START [path]sqltrcanlzr.sql [SQL Trace filename|control.txt]

SQL> START run/sqltrcanlzr.sql V1122_ora_24292.trc

SQL> START run/sqltrcanlzr.sql control.txt

TRCAXTR 方法

该方法执行的操作与 TRCANLZR相同，但是当 trace 分析完成时，它会针对在 trace 中发现的* SQL 继续执行XTRACT 。该方法基本上自动调用并合并 TRCANLZR 和 XTRACT 生成的所有报告。

要使用此 TRCAXTR 方法，请确保首先已经安装了 SQLT。然后，进入到 sqlt/run 目录并启动 SQL*Plus，以生成 trace 的应用程序用户身份连接。接着，执行 sqlt/run/sqltrcaxtr.sql 脚本，传递待分析 trace 的名称或者填充了文件名的 control.txt 文件名称。不用包括任何路径指定。

# cd sqlt/run

# sqlplus [application_user]

SQL> START sqltrcaxtr.sql [SQL Trace filename|control.txt]

SQL> START sqltrcaxtr.sql V1122_ora_24292.trc

SQL> START sqltrcaxtr.sql control.txt

TRCASPLIT 方法

该方法将 EVENT 10046 和其他 EVENT（通常是 10053）创建的 SQL trace 文件名作为输入。然后，它继续将此输入 trace 文件分为两个输出文件。一个包含与 EVENT 10046 对应的 trace 行，另一个包含其补充信息。换句话说，第二个文件包含不属于 EVENT 10046 语法部分的那些 trace 行。所以，如果输入 trace 是同时使用 EVENT 10046 和 EVENT 10053 创建的，得到的输出文件将是 10046 trace 和 10053 trace 。实际输入 trace 必须位于 TRCA$INPUT1 目录中，其在安装期间默认为 USER_DUMP_DEST 目录。

要使用此 TRCASPLIT 方法，请确保首先已经安装了 SQLT。然后，启动 SQL*Plus（以任何 SQLT 用户身份连接）并执行 sqlt/run/sqltrcasplit.sql 脚本，传递待分割 trace 的名称。不用包括任何路径指定。

# cd sqlt

# sqlplus [sqlt_user]

SQL> START [path]sqltrcasplit.sql [SQL Trace filename]

SQL> START run/sqltrcasplit.sql V1122_ora_24292.trc

XTRSET 方法

XTRSET 从内存或 AWR 提取由 SQL_ID 或 HASH_VALUE 标识的 SQL 语句列表，然后对其中每个 SQL 语句执行XTRACT。最后它将所有 SQLT 文件合并到一个压缩文件中。通过一系列测试确定相同 SQL 语句集的基准时需要使用此 XTRSET 方法。

使用此方法时，它只要求提供一次 SQLTXPLAIN 密码，将在对 SQL 语句列表的每个 XTRACT 执行导出 SQLT Repository 时需要。

要使用此 XTRSET 方法，必须首先安装了 SQLT。导航到 sqlt/run 目录并启动 SQL*Plus，以发出所有或大部分 SQL 语句的应用程序用户身份连接。然后，执行 sqlt/run/sqltxtrset.sql 脚本。出现提示时，传递由 SQL_ID 或 HASH_VALUE 标识的 SQL 语句列表（以逗号分隔），以及 SQLTXPLAIN 的密码。

# cd sqlt/run

# sqlplus [application_user]

SQL> START sqltxtrset.sql

List of SQL_IDs or HASH_VALUEs: 2yas208zgt5cv, 6rczmqdtg99mu, 8w8tjgac6tv12

高级方法和模块

除了主要方法和特殊方法，SQLT 还提供了一些其他功能。仅当 Oracle Support 要求时才使用这些高级方法和模块：PROFILE, XGRAM, XPLORE 和 XHUME。最后一个仅供 Oracle Support 使用，仅用于内部测试环境中。

PROFILE 方法

当性能较差的 SQL 语句正好有已知的更好的计划时，在 10g 使用该 PROFILE 方法可以提供一个快速修复。这个更好的计划可以位于相同或不同系统中的内存中，或者位于相同或不同系统的 AWR 中。换句话说，如果有更好的计划，该方法允许使用自定义 SQL Profile “固定”该计划。使用该方法之前，必须对您要为其提取和固定此计划的 SQL 使用主要方法中的任何一个。在 11g 或更高的版本你可以使用 SQL Plan Management (SPM) 来代替这个方法。

请注意，PROFILE 使用了 DBMS_SQLTUNE API，其是“SQL Tuning Advisor”的一部分，因此需要 Oracle Tuning Pack 的使用许可。仅当您的数据库具有 Oracle Tuning Pack 的许可时才使用该 PROFILE 方法。

要使用该 PROFILE 方法，请确保 SQLT 已经安装并在 SOURCE 系统中使用，然后以 SYS 或 SQLTXPLAIN 身份连接到 SQL*Plus，执行 sqlt/utl/sqltprofile.sql 脚本。它将要求提供先前 SQLT 执行的列表中的 STATEMENT_ID。选择 STATEMENT_ID 后，它将要求提供可用计划列表中的 PLAN_HASH_VALUE。这些执行计划是在对 SQL 使用 XTRACT 或 XECUTE 时捕获和存储的。

在需要实施自定义 SQL Profile 的 TARGET 系统中，不需要安装 SQLT。

该 PROFILE 方法基本上包含 4 个步骤。

在 SOURCE 系统上使用 XTRACT 或 XECUTE。
在 SOURCE 中执行 sqlt/utl/sqltprofile.sql，以生成自定义 SQL Profile 的脚本。
查看生成的脚本并根据需要调整 SQL 文本。例如，在使用 XECUTE 时要移除由 /* ^^unique_id */ 生成的注释。
在要固定计划的 TARGET 系统中执行生成的脚本。

# cd sqlt/utl

# sqlplus sqltxplain

SQL> START sqltprofile.sql [statement id] [plan hash value];

SQL> START sqltprofile.sql 32263 923669362;

SQL> START sqltprofile.sql 32263;

SQL> START sqltprofile.sql;

由该方法创建的自定义 SQL Profile 是基于计划大纲数据完成的，因此它更稳定。如果您稍后要删除此自定义 SQLProfile，您可以在 PROFILE 生成的脚本内找到删除命令。

如果尚未在 SOURCE 系统中安装 SQLT 或者无法为有关 SQL 执行 XTRACT 或 XECUTE您可以使用 sqlt/utl/coe_xfr_sql_profile.sql 实现 PROFILE 方法提供的相同功能。该脚本也使用了 DBMS_SQLTUNE；因此，需要 Oracle Tuning Pack 的许可。

如果您的系统为 11g 并且在考虑使用该 PROFILE 方法，请查看由任何主要方法生成的动态 Readme 文件，并查找 "Create SQL Plan Baseline from SQL Set" 部分内容。如动态 Readme 文件中所述，您可以考虑通过 SQL Set 使用“SQL Plan Management SPM”。

XGRAM 模块

XGRAM 模块提供了修改 CBO 直方图的功能，用以增强某些列的 CBO 统计信息或作为测试用例的一部分。通过该模块，可以插入、更新或删除直方图或单个存储桶。

实施 XGRAM 模块的脚本的按字母排序列表：

sqlt/utl/xgram/sqlt_delete_column_hgrm.sql
sqlt/utl/xgram/sqlt_delete_hgrm_bucket.sql
sqlt/utl/xgram/sqlt_delete_schema_hgrm.sql
sqlt/utl/xgram/sqlt_delete_table_hgrm.sql
sqlt/utl/xgram/sqlt_display_column_stats.sql
sqlt/utl/xgram/sqlt_insert_hgrm_bucket.sql
sqlt/utl/xgram/sqlt_set_bucket_size.sql
sqlt/utl/xgram/sqlt_set_column_hgrm.sql
sqlt/utl/xgram/sqlt_set_min_max_values.sql

XGRAM 是在 SQLT 安装过程中自动安装的。如果您想在 SQLT 以外使用这个模块，您只需要安装一个单独的 package 并使用以上脚本（不依赖于 SQLTXADMIN）。

XPLORE 模块

如果在数据库升级后 SQL 开始性能变差或者它可能产生明显的错误结果，那么使用 XPLORE 模块将有所帮助。如果将 optimizer_features_enable OFE 切换到升级之前的数据库版本，SQL 重新执行正常或者产生不同的结果，您可以使用此 XPLORE 模块尝试标识哪个特定 Optimizer 功能或修复引入了未预期的行为。确定特定故障有助于进一步故障排除或者对此特定功能和（或）修复执行更详细的研究。

此模块通过切换初始化参数和 fix control 参数来搜索计划。

仅当满足以下所有条件时才使用 XPLORE：

当使用“差”计划时，SQL 执行性能差或者返回错误结果。
可以在测试系统上重新生成差计划（最好没有真实数据）。
可以通过切换 OFE 在测试系统上重新生成“好”计划。
您需要将原因范围缩小到特定参数或 bug fix control。
您对测试系统具有完全访问权限，包括 SYS 访问权限。

当符合以下任一条件时不要使用 XPLORE：

SQL 语句可能导致数据损坏或被更新。
在 SQL 引用的表中存在大量数据。
执行 SQL 需要的时间可能超过几秒钟。

要安装和使用该 XPLORE 模块，请阅读相应的 sqlt/utl/xplore/readme.txt。

XHUME 模块

该模块仅供 Oracle Support 使用，且只能在 Oracle 内部系统中使用。因为它会更新数据字典，而且 Oracle 不支持此操作。

XHUME 可用于搜索仅旧版本的 Schema 对象统计信息（与一个 SQL 相关）可以产生的计划。使用 SQLT 创建测试用例 (TC) 后，该 XHUME 模块将系统地还原先前版本的统计信息，并通过执行正在研究的 SQL 来生成计划。它将捕获统计信息的每个版本可以生成的计划。然后生成报告，可用于了解执行计划不稳定性的原因，或者查找可用于创建 SQL Profile 或 SQL Plan Baseline 的执行计划。

该模块永远不能应用于生产系统，因为它会修改数据字典。仅能用于 Oracle 内部测试环境。

作为修改测试用例 (TC) Schema 对象的创建日期的替代方案，可以在 TC 实施之前更改服务器上的日期并在创建 TC 后重置为当前日期。这个临时的先前日期至少要早一个月，这样 Schema 对象统计信息的所有历史记录的保存时间均将新于 TC 对象创建时间。

仅当满足以下所有下列条件时才使用 XHUME：

已知 SQL 有多个计划并且其中一个或多个性能较差。
Bind peeking 已经被排除不是导致计划不稳定的故障。
对 CBO 参数的更改已经被排除不是导致计划不稳定的故障。
您具有能产生已知计划的 SQLT TC（“好”或“差”计划）。
您需要了解计划的不稳定性或者正在查找专用的已知“好”计划。
您对 Oracle 内部测试系统具有完全访问权限，包括 SYS 访问权限。

当符合以下任一条件时不要使用 XHUME：

SQL 语句可能导致数据损坏或被更新。
您只有一个生产环境中可以运行 TC。
您没有使用 SQLT 创建 TC。
您对包含您 TC 的 Oracle 内部测试系统没有 SYS 访问权限。
Bind peeking 或 CBO 参数尚未被排除不是导致计划不稳定的故障。

要安装和使用此 XHUME 模块，请阅读相应的 sqlt/utl/xhume/readme.txt。

上传SQLT文件给Oracle技术支持

在SQLT运行后，它会产生一个zip格式的输出文件（这个文件包含了SQLT相关的各种输出）。这个文件的文件名一般为:

sqlt_s<sqltrun#>_<method>_<sql_id>.zip

如果您已经使用一个特定的方法执行完一个SQLT，那么您可以根据方法的名称来找到输出文件。比如，下面的文件就是使用XTRACT方法执行SQLT后产生的：:

sqlt_s45774_xtract_fp48hh5dkm529.zip

如果输出文件的文件名是类似下面的格式:

sqlt_s50605_log.zip

那么SQLT很可能并未成功执行，请检查log文件中的错误日志.
一个正常的SQLT zip文件一般包含下面的文件（仅作参考) :

$ unzip -v sqlt_s45774_xtract_fp48hh5dkm529.zip

Archive: sqlt_s45774_xtract_fp48hh5dkm529.zip

Length Method Size Cmpr Date Time CRC-32 Name

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

0 Stored 0 0% 04-09-2015 18:34 00000000 sqlt_s45774_10053_explain.trc

0 Stored 0 0% 04-09-2015 18:34 00000000 sqlt_s45774_10053_i1_c0_extract.trc

610 Stored 610 0% 04-09-2015 18:35 51c54175 sqlt_s45774_cell_state.zip

4187 Stored 4187 0% 04-09-2015 18:35 ded1bbdd sqlt_s45774_driver.zip

12814 Defl:N 2983 77% 04-09-2015 18:34 d359b6e7 sqlt_s45774_lite.html

18879 Stored 18879 0% 04-09-2015 18:35 8270f836 sqlt_s45774_log.zip

862169 Defl:N 121759 86% 04-09-2015 18:34 9c99af83 sqlt_s45774_main.html

3991 Stored 3991 0% 04-09-2015 18:35 d42245f2 sqlt_s45774_opatch.zip

12572 Defl:N 3645 71% 04-09-2015 18:34 2c6b2c0a sqlt_s45774_readme.html

199784 Stored 199784 0% 04-09-2015 18:35 93a85ee1 sqlt_s45774_tc.zip

394 Defl:N 293 26% 04-09-2015 18:35 a405bd8b sqlt_s45774_tc_script.sql

35 Stored 35 0% 04-09-2015 18:35 4e1901f1 sqlt_s45774_tc_sql.sql

52511 Stored 52511 0% 04-09-2015 18:35 2aee61c6 sqlt_s45774_tcx.zip

406 Stored 406 0% 04-09-2015 18:35 6df4be4d sqlt_s45774_trc.zip

216 Defl:N 113 48% 04-09-2015 18:35 213361e5 sqlt_s45774_xpand.sql

2319 Stored 2319 0% 04-09-2015 18:35 6f79e5b8 sqlt_s45774_sqldx.zip

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

1170887 411515 65% 16 files

#############sample 3:

【学习笔记】Oracle sql profile和outline exchange固定非绑定变量sql

时间:2016-12-11 19:01 来源:Oracle研究中心作者:网络点击: 次

Oracle研究中心学习笔记：分享一篇关于Oracle数据库固定非绑定变量sql的方法，详细对比Oracle sql profile和outline exchange区别。

本站文章除注明转载外，均为本站原创：转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 使用sql profle进行偷梁换柱的小例子–outline exchange(续)

一篇sql profile来固定非绑定变量sql的文章，微博上有人提到了outline exchange也可以实现类似的功能，那我们就再来看看这和sql profile有什么差异，如何去实现：

下面我们再来创建一个测试表，用于测试：

SQL> conn roger/roger
Connected.
SQL> CREATE TABLE t2 AS SELECT * FROM dba_objects;

TABLE created.

SQL> SELECT COUNT(1) FROM t2;

COUNT(1)
----------
51072

SQL> UPDATE t2 SET object_id=2000 WHERE object_id >30000;
21619 ROWS updated.

SQL> commit;
Commit complete.

SQL> CREATE INDEX idx_id_t2 ON t2(object_id);
INDEX created.

SQL> analyze TABLE t2 compute statistics FOR TABLE FOR ALL indexes FOR ALL indexed COLUMNS;
TABLE analyzed.

SQL> ALTER SESSION SET create_stored_outlines = TRUE;
SESSION altered.

SQL> SELECT * FROM v$version WHERE rownum <2;

BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - Prod
下面来创建outline：

SQL> CREATE outline test_outline_exchange FOR CATEGORY test_outlines ON
2 SELECT owner,object_name FROM t2 WHERE object_id=1000;

Outline created.

SQL> SELECT name,category,sql_text FROM user_outlines WHERE category=UPPER('test_outlines');

NAME CATEGORY
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
TEST_OUTLINE_EXCHANGE TEST_OUTLINES
SELECT owner,object_name FROM t2 WHERE object_id=1000

SQL> l
1* SELECT * FROM user_outline_hints WHERE name=UPPER('test_outline_exchange')
SQL> /

NAME                            NODE      STAGE   JOIN_POS HINT
------------------------- ---------- ---------- ---------- -------------------------------------------------------
TEST_OUTLINE_EXCHANGE              1          1          1 INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
TEST_OUTLINE_EXCHANGE              1          1          0 OUTLINE_LEAF(@"SEL$1")
TEST_OUTLINE_EXCHANGE              1          1          0 ALL_ROWS
TEST_OUTLINE_EXCHANGE              1          1          0 OPT_PARAM('_optim_peek_user_binds' 'false')
TEST_OUTLINE_EXCHANGE              1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
TEST_OUTLINE_EXCHANGE              1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

6 ROWS selected.

SQL> SET autot traceonly
SQL> SELECT owner,object_name FROM t2 WHERE object_id=1000;

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 4034027770

-----------------------------------------------------------------------------------------
| Id | Operation                   | Name      | ROWS | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    86 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T2        |     1 |    86 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_ID_T2 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

2 Oracleoracleplus.net- access("OBJECT_ID"=1000)

Statistics
----------------------------------------------------------
         55 recursive calls
         28 db block gets
         10 consistent gets
          0 physical reads
       8756 redo SIZE
        485 bytes sent via SQL*Net TO client
        400 bytes received via SQL*Net FROM client
          2 SQL*Net roundtrips TO/FROM client
          0 sorts (memory)
          0 sorts (disk)
          1 ROWS processed

SQL> SELECT owner,object_name FROM t2 WHERE object_id=2000;
21620 ROWS selected.

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | ROWS | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 21790 | 1830K|   198   (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T2   | 21790 | 1830K|   198   (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

1 - FILTER("OBJECT_ID"=2000)

Statistics
----------------------------------------------------------
         55 recursive calls
         28 db block gets
       2137 consistent gets
         56 physical reads
       8656 redo SIZE
     816430 bytes sent via SQL*Net TO client
      16251 bytes received via SQL*Net FROM client
       1443 SQL*Net roundtrips TO/FROM client
          0 sorts (memory)
          0 sorts (disk)
      21620 ROWS processed

我们这里的目的是要想让object_id=2000的sql也走index range scan。
下面也为object_id=2000的sql创建一个outline，然后进行对比:

SQL> CREATE outline test_outline_exchange2 FOR CATEGORY test_outlines ON
2 SELECT owner,object_name FROM t2 WHERE object_id=2000;

Outline created.

SQL> SELECT * FROM user_outline_hints WHERE name=UPPER('test_outline_exchange2');

NAME                     NODE      STAGE   JOIN_POS HINT
----------------------- ----- ---------- ---------- ----------------------------------------------
TEST_OUTLINE_EXCHANGE2      1          1          1 FULL(@"SEL$1" "T2"@"SEL$1")
TEST_OUTLINE_EXCHANGE2      1          1          0 OUTLINE_LEAF(@"SEL$1")
TEST_OUTLINE_EXCHANGE2      1          1          0 ALL_ROWS
TEST_OUTLINE_EXCHANGE2      1          1          0 OPT_PARAM('_optim_peek_user_binds' 'false')
TEST_OUTLINE_EXCHANGE2      1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
TEST_OUTLINE_EXCHANGE2      1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

6 ROWS selected.

SQL> SELECT * FROM user_outline_hints WHERE name=UPPER('test_outline_exchange');

NAME                     NODE      STAGE   JOIN_POS HINT
----------------------   ---- ---------- ---------- -------------------------------------------------------
TEST_OUTLINE_EXCHANGE       1          1          1 INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
TEST_OUTLINE_EXCHANGE       1          1          0 OUTLINE_LEAF(@"SEL$1")
TEST_OUTLINE_EXCHANGE       1          1          0 ALL_ROWS
TEST_OUTLINE_EXCHANGE       1          1          0 OPT_PARAM('_optim_peek_user_binds' 'false')
TEST_OUTLINE_EXCHANGE       1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
TEST_OUTLINE_EXCHANGE       1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

6 ROWS selected.
我们对比上面的hint部分，可以发现，一个是full 一个是index_rs_asc，其他完全一致，也就是说，
如果我们想让object_id=2000的sql走index range scan，那么我们只需要把hint修改即可。

那现在的问题的是:我们去哪儿修改呢？

SQL> SHOW USER
USER IS "SYS"
SQL>
SQL> SELECT dbms_metadata.get_ddl('VIEW','USER_OUTLINE_HINTS') FROM dual;

DBMS_METADATA.GET_DDL('VIEW','USER_OUTLINE_HINTS')
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYS"."USER_OUTLINE_HINTS" ("NAME", "NODE", "STAG
E", "JOIN_POS", "HINT") AS
SELECT o.ol_name, h.node#, h.stage#, table_pos,
NVL(h.hint_string, h.hint_text)
FROM outln.ol$ o, outln.ol$hints h, sys.USER$ u
WHERE o.ol_name = h.ol_name
AND o.creator = u.name
AND u.USER# = USERENV('SCHEMAID')

SQL> col HINT_TEXT FOR a60
SQL> SELECT HINT#,HINT_TEXT FROM outln.ol$hints WHERE ol_name='TEST_OUTLINE_EXCHANGE2';

HINT# HINT_TEXT
---------- ------------------------------------------------------------
         1 FULL(@"SEL$1" "T2"@"SEL$1")
         2 OUTLINE_LEAF(@"SEL$1")
         3 ALL_ROWS
         4 OPT_PARAM('_optim_peek_user_binds' 'false')
         5 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
         6 IGNORE_OPTIM_EMBEDDED_HINTS

6 ROWS selected.
我们可以看到，outline信息是存在outln用户下面的ol$hints表中，我们这里来更改hint#为1的 hint_text部分：

SQL> UPDATE outln.ol$hints SET HINT_TEXT='INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))'
2 WHERE ol_name='TEST_OUTLINE_EXCHANGE2' AND hint#=1;

1 ROW updated.

SQL> commit;

Commit complete.

SQL>
那下面我们来看看执行计划是否会变成index rang scan？

SQL> ALTER system FLUSH shared_pool;

System altered.

SQL> ALTER system FLUSH buffer_cache;

System altered.

SQL> SET autot traceonly EXP
SQL> ALTER SESSION SET use_stored_outlines=test_outlines;

SESSION altered.

SQL> SET autot traceonly EXP
SQL> SELECT owner,object_name FROM t2 WHERE object_id=2000;

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 4034027770

-----------------------------------------------------------------------------------------
| Id | Operation                   | Name      | ROWS | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 21715 |   742K|   513   (0)| 00:00:07 |
|   1 | TABLE ACCESS BY INDEX ROWID| T2        | 21715 |   742K|   513   (0)| 00:00:07 |
|* 2 |   INDEX RANGE SCAN          | IDX_ID_T2 | 21715 |       |    46   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=2000)

Note
-----
- outline "TEST_OUTLINE_EXCHANGE2" used FOR this statement
我们可以看到使用了index range scan，成功实现了egale_fan讲的outline exchange。

但是，这仍然有一个很大的问题，既然我应用没有使用绑定变量，那么你要固定其执行计划，也就是说你必须为每个一个sql创建一个outline，那样太费劲了，而且不现实。

我想这或许是sql profile引入的原因，其中有一点大家应该都看到了，sql profile有一个force_match的功能，而outline则不具备。

sqlt 之分析 DB upgrade 导致SQL 性能下降的方法 xplore的更多相关文章

12&period;1&period;0&period;2自适应特性导致SQL性能下降
背景介绍在升级到12.1.0.2.0数据库版本后,在使用12c中引入的自适应特性默认配置的情况下,可能引起SQL性能的下降. 问题现象升级到12.1.0.2.0后,SQL语句性能可能出现下降. 影响 ...
统计信息不准导致sql性能下降
某局的预生产系统一条sql语句20分钟执行完,上线以后2个小时没执行出来,在预生产执行计划是hash join在生产是nested loop,通过awr基表wri$_optstat_tab_histo ...
Oracle DB SQL 性能分析器
• 确定使用SQL 性能分析器的优点 • 描述SQL 性能分析器工作流阶段 • 使用SQL 性能分析器确定数据库更改所带来的性能改进 SQL 性能分析器:概览 • 11g 的新增功能 • 目标用户:D ...
SQL性能优化案例分析
这段时间做一个SQL性能优化的案例分析, 整理了一下过往的案例,发现一个比较有意思的,拿出来给大家分享. 这个项目是我在项目开展2期的时候才加入的, 之前一期是个金融内部信息门户, 里面有个功能是收集 ...
MySQL高级篇 | 分析sql性能
在应用的的开发过程中,由于初期数据量小,开发人员写 SQL 语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多 SQL 语句开始逐渐显露出性能问题,对生产的影响也越来越 ...
使用show profiles分析SQL性能
如何查看执行SQL的耗时使用show profiles分析sql性能. Show profiles是5.0.37之后添加的,要想使用此功能,要确保版本在5.0.37之后. 查看数据库版本 mysql ...
【性能诊断】二、单功能场景的性能分析（fiddler、SQL Profiler）
Fiddler fiddler是最强大最好用的Web调试工具之一,它能记录所有客户端和服务器的http和https请求,允许你监视,设置断点,甚至修改输入输出数据. 使用Fiddler无论对开发还是测 ...
【MS SQL】通过执行计划来分析SQL性能
原文:[MS SQL]通过执行计划来分析SQL性能如何知道一句SQL语句的执行效率呢,只知道下面3种: 1.通过SQL语句执行时磁盘的活动量(IO)信息来分析:SET STATISTICS IO O ...
SQL Server -&gt&semi;&gt&semi; 性能调优案例之 -- 包含递归查询的视图导致整个查询语句性能下降
有个语句最近性能下降很厉害,原本1秒就可以查询完毕的事情现在居然需要3-4分钟. 首先我的做法是先快速找出导致整个语句下降的元凶.在这个例子里面查询语句有3个JOIN字句,我通过删除某一个JOIN节点 ...

随机推荐

MVC Nhibernate 示例
首先,非常感谢提出问题的朋友们,使得本人又去深入研究了NHibernate的<Session-Per-Request 模式>. 前言: 谈到NHibernate大伙并不陌生,搞Java ...
大熊君大话NodeJS之 ------ Connect中间件第二季（源码分析）
一,开篇分析大家好,大熊君又回来了,今天这篇文章主要是对"Connect"中间件以及相关辅助中间件,做一个源码分析系列,我想上一篇文章大家也看了, 介绍了使用方式及用途,而这篇也 ...
SVN服务器的配置（简单易懂，带配置文件，有注释）
这两天在服务器搭建了一个SVN服务器,一些经验,也留作后用把,有不详细的欢迎批评指正另外关于子目录的访问配置,这块我还是不懂,希望有前辈能教我一下 1.安装SVN Serveryum install ...
触发UIButton长按事件
UIButton *aBtn=[UIButton buttonWithType:UIButtonTypeCustom]; [aBtn setFrame:CGRectMake(40, 100, 60, ...
CH Round &num;55 - Streaming &num;6 (NOIP模拟赛day2)
A.九九归一题目:http://ch.ezoj.tk/contest/CH%20Round%20%2355%20-%20Streaming%20%236%20(NOIP模拟赛day2)/九九归一题 ...
js私有化属性
我们先来看一个例子: var Demo1 = function(val){ this.value = val; this.getValue = function(){ return this.valu ...
整理的Python零基础入门！转载他人的！
安装Python 前往官网下载对应平台对应工具.另外Python2.7版本和3.3版本并不兼容,所以开发时请注意使用Python的版本. 作为Mac OS X使用者,其实更推荐 PyCharm I ...
springmvc的Controller里实现转发的同时弹出提示对话框
以前写servlet时就用到这个,但是现在学了springMVC+hibernate后就不知道怎么实现了,后来在网上找了好多,看了好多,最后经过自己实践成功的如下: 1.首先是Controller控制 ...
Redis常用命令【列表】
一.简介基于Linked List实现,元素是字符串类型,列表头尾增删快,中间增删慢,增删元素是常态. 元素可以重复出现,最多包含2^32-1个元素. 二.命令 1.说明 1.1 B block 块 ...
【已解决】gradle project refresh failed&colon;connection refused
git上clone一个Gradle项目,使用AS的gradle sync报错如下: Error:Connection refused (Connection refused) 原因:本地gradle版 ...

sqlt 之 分析 DB upgrade 导致SQL 性能下降 的方法 xplore

SQLT 概览

安全模式

安装 SQLT

卸载 SQLT

升级 SQLT

常见问题

主要方法

XTRACT 方法

XECUTE 方法

XTRXEC 方法

XTRSBY 方法

XPLAIN 方法

XPREXT 方法

XPREXC 方法

特殊方法

COMPARE 方法

TRCANLZR 方法

TRCAXTR 方法

TRCASPLIT 方法

XTRSET 方法

高级方法和模块

PROFILE 方法

XGRAM 模块

XPLORE 模块

XHUME 模块

上传SQLT文件给Oracle技术支持

【学习笔记】Oracle sql profile和outline exchange固定非绑定变量sql

sqlt 之 分析 DB upgrade 导致SQL 性能下降 的方法 xplore的更多相关文章

随机推荐

相关文章

sqlt 之分析 DB upgrade 导致SQL 性能下降的方法 xplore

sqlt 之分析 DB upgrade 导致SQL 性能下降的方法 xplore的更多相关文章