SQL调优和诊断利器之SQLT介绍

Oracle数据库技术 2021-05-14

516

【免责声明】本公众号文章仅代表个人观点，与任何公司无关。

概述

本文介绍在SQL问题诊断过程中利用的工具SQLT。

SQLT

SQLTXPLAIN (SQLT) 是Oracle用于诊断SQL相关问题的工具。

SQLT 会根据用户指定的模式，连接到数据库，收集执行计划、基于成本的 Optimizer CBO 统计信息、Schema 对象元数据、性能统计信息、配置参数和会影响正在分析的 SQL 性能的其他元素。

SQLT安装

可通过官方MOS Document 215187.1 可以下载最新的SQLT安装包，下载后可参考安装包中sqlt_instructions.html的介绍进行安装。

以下是安装SQLT步骤。

例：（12c NONCDB @Linux）

1.对SQLT安装包（sqlt_latest.zip）解压

    [oracle@db12102 20160827]$ ls
    sqlt_latest.zip
    [oracle@db12102 20160827]$ unzip sqlt_latest.zip
    Archive: sqlt_latest.zip
    creating: sqlt/
    creating: sqlt/doc/
    ...
    inflating: sqlt/utl/xplore/xplore.pks
    [oracle@db12102 20160827]$ ls
    sqlt sqlt_latest.zip

2.进入sqlt的路径，准备安装

    [oracle@db12102 20160827]$ cd sqlt
    [oracle@db12102 sqlt]$ ls
    doc input install run sqlt_instructions.html sqlt_instructions.txt utl

3.执行sqcreate.sql进行安装

SQL> conn as sysdba
Connected.
SQL> @install/sqcreate.sql

在安装过程中需要指定的参数和各参数的含义如下：

3-1.连接标识符（当安装在一个可插拔数据库上时是必须的）

Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key.
You *MUST* provide a connect identifier when installing
SQLT in a Pluggable Database in 12c
This connect identifier is only used while exporting SQLT
repository everytime you execute one of the main methods.

Optional Connect Identifier (ie: @PROD):

3-2.SQLTXPLAIN 密码

Define SQLTXPLAIN password (hidden and case sensitive).
Password for user SQLTXPLAIN:

3-3.SQLTXPLAIN 默认表空间


The next step is to choose the tablespaces to be used by SQLTXPLAIN
The Tablespace name is case sensitive.
Do you want to see the free space of each tablespace [YES]
or is it ok just to show the list of tablespace [NO]?

Type YES or NO [Default NO]: NO

... please wait

TABLESPACE FREE_SPACE_MB
------------------------------ -------------
EXAMPLE
USERS

Specify PERMANENT tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Default tablespace [UNKNOWN]: USERS

PL/SQL procedure successfully completed.
... please wait

TABLESPACE
------------------------------
TEMP

Specify TEMPORARY tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Temporary tablespace [UNKNOWN]: TEMP

3-4. 应用程序用户（发出要分析 SQL 语句的用户）

The main application user of SQLT is the schema
owner that issued the SQL to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role.

Main application user of SQLT: teacherwhat

※也可以在安装该工具后添加其他的 SQLT 用户，方法为：授予他们角色 SQLT_USER_ROLE，或通过使用提供的脚本 sqlt/install/sqguser.sql

例：

SQL> conn as sysdba
SQL> @install/sqguser.sql scott

3-5. 授权的 Oracle Pack。（T，D 或 N）

SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring and Automatic Workload Repository
(AWR).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:


"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses

Oracle Pack license [T]: T

4.如果需要的话，通过下面的脚本可以卸载 SQLT。

SQL> conn as sysdba
SQL> @install/sqdrop.sql

※卸载 SQLT 会同时移除 SQLT Repository 以及所有SQLT相关的内容。

SQLT的使用

执行方法的种类

SQLT的执行方法主要包括以下:

XTRACT：通过V$SQL、AWR的过去执行过的SQL内容，收集相关信息。
XECUTE：通过执行SQL，收集相关信息。

XPLAIN：不执行SQL，基于EXPLAIN PLAN FOR命令收集相关信息。（不推荐）
XTRXEC：该方法合并了XTRACT和XECUTE 的功能。实际上，XTRXEC连续执行了这两种方法。
XTRSBY：分析在 Data Guard 或备用只读数据库上执行的 SQL。
XPREXT：使用XTRACT同时禁用一些SQLT的特性，使之执行更快。
XPREXC：使用XECUTE同时禁用一些SQLT的特性，使之执行更快。

执行方法

XTRACT 方法

通过V$SQL、AWR的过去执行过的SQL内容，收集相关信息。

1.通过V$SQL或AWR找到要收集数据的SQL的SQL_ID 或者HASH_VALUE。

SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

2.进入到sqlt的run路径下，执行sqltxtract.sql脚本收集相关信息

$ ls
doc input install run SQLT_installation_logs_archive.zip sqlt_instructions.html sqlt_instructions.txt utl
$ cd run
$sqlplus Username/Password
SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]

3.执行例：

[oracle@db12102 sqlt]$ ls
doc input install run SQLT_installation_logs_archive.zip sqlt_instructions.html sqlt_instructions.txt utl
[oracle@db12102 sqlt]$ cd run
[oracle@db12102 run]$ sqlplus nolog


SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 27 20:18:29 2016


Copyright (c) 1982, 2014, Oracle. All rights reserved.


SQL> conn teacherwhat/teacherwhat
Connected.
SQL> select/*+ GATHER_PLAN_STATISTICS MONITOR */ count(*) from SQLMON where a='A';


COUNT(*)
----------
1000


SQL> SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE 'select/*+ GATHER_PLAN_STATISTICS MONITOR */ count(*)%'; 2 3


SQL_ID HASH_VALUE
------------- ----------
TEXT
--------------------------------------------------------------------------------
acsdndc43qs74 138109156
select/*+ GATHER_PLAN_STATISTICS MONITOR

SQL> START sqltxtract.sql acsdndc43qs74 SQLTXPLAIN
...
adding: sqlt_s28359_sqldx.zip (stored 0%)


#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s28359_purge.sql out of sqlt repository ...


SQLTXTRACT completed.

相关的信息生成在了sqlt_s28359_sqldx.zip 中。

XECUTE 方法

通过执行SQL，收集相关信息;与 XTRACT方法相比，该方法提供的信息更为详细.

1.使用XECUTE 方法之前，创建一个包含 SQL 文本的文本文件。

如果 SQL 包括绑定变量，则您的文件必须包含绑定变量声明和赋值。

例：

target.sql
-----------------------
select/*+ GATHER_PLAN_STATISTICS MONITOR */ count(*) from SQLMON where a='A';
-----------------------

2.执行sqltxecute.sql脚本收集相关信息

$cd sqlt/run
$sqlplus Username/Password
SQL> START [path]sqltxecute.sql [path]scriptname [sqltxplain_password]

3.执行例

[oracle@db12102 run]$ vi target.sql
[oracle@db12102 run]$ more target.sql
select/*+ GATHER_PLAN_STATISTICS MONITOR */ count(*) from SQLMON where a='A';
[oracle@db12102 run]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 27 21:06:09 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn teacherwhat/teacherwhat
Connected.
SQL> START sqltxecute.sql target.sql SQLTXPLAIN
...
adding: sqlt_s28360_sqldx.zip (stored 0%)


#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collecte
d info can be purged from the database using the following file :

... getting sqlt_s28360_purge.sql out of sqlt repository ...


SQLTXECUTE completed.