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)

sql最佳化工具SQLT

相關文章