SQLT也叫SQLTXPLAIN，如果某一条SQL执行效率很低，也不知道从哪里下手，可以使用SQLT来收集和性能相关的各种信息(如:Execution plans, Cost-based Optimizer CBO statistics, Schema objects metadata, Performance statistics, Configuration parameters...)

适用范围
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

1,如果以前安装过，则可以执行以下的命令进行卸载
# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdrop.sql

2，安装sqlt软件

1），首先要创建一个SQLT的管理用户和临时表空间
创建管理用户
SQL>create user admin identified by admin;
创建临时表空间
SQL> CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/u01/oracle/oradata/ora10g/temp02.dbf' SIZE 100m;

  2），安装软件
[oracle@ora10g ~]$ tar -xzvf sqlt.tar.gz
[oracle@ora10g ~]$ cd sqlt/install
[oracle@ora10g install]$ sqlplus / as sysdba
SQL> START sqcreate.sql
Optional Connect Identifier (ie: @PROD): @ora10g //数据库的sid
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Define SQLTXPLAIN password (hidden and case sensitive).
Password for user SQLTXPLAIN: *** //设置sqlt用户的密码
Re-enter password: ***
PL/SQL procedure successfully completed.
... please wait
TABLESPACE FREE_SPACE_MB
------------------------------ -------------
TEST01 73
EXAMPLE 32700
USERS 32763
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
TEMP01
Specify TEMPORARY tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Temporary tablespace [UNKNOWN]: TEMP01
PL/SQL procedure successfully completed.
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: admin   //之前创建的专用用户
PL/SQL procedure successfully completed.
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
…
SQCREATE completed. Installation completed successfully.

3，使用方法：
SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] //SQL_ID或HASH_VALUE选择其一
SQL> START sqltxtract.sql 0w6uydn50g8cx sqltxplain_password
SQL> START sqltxtract.sql 2524255098 sqltxplain_password

实例：
cd /sqlt/run
SQL> start sqltxtract.sql 6gvch1xu9ca3g //输入SQL_id

4,授予权限

grant connect,resource to admin;
grant SQLT_USER_ROLE to admin;

参考文档：SQLT Diagnostic Tool (文档 ID 215187.1)

ITPub博客

sql优化工具SQLT

最新文章