调优脚本 SQLT 安装&收集

ALLSTARS_ORACLE

调优脚本 SQLT 安装&收集

SQLT 安装

SQLT安装在自己的schema SQLTXPLAIN下，它不会在其他业务用户下安装任何对象.你可以在10.2以上版本的UNIX、LINUX或WINDOWS上安装.

安装步骤:

• 卸载之前版本 (可选).
  

这个步骤可选，用来删除之前的SQLTXPLAIN schema 对象，准备为全新安装做准备.

$ 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

安装过程中，你需要按照提示确认一些参数:

• 可选的连接标示符.
  

In some restricted-access systems you may need to specifya connect identifier like @PROD. If a connect identifier is notneeded, enter nothing and just hit the "Enter" key.

• SQLTXPLAIN 用户的密码.
  

按照提示设置密码，注意区分大小写.

• SQLTXPLAIN 用户默认表空间.
  

选择一个SQLTXPLAIN用户使用的默认表空间，用于存放SQLT信息，要求至少存在50M空余空间.

• SQLTXPLAIN 用户临时表空间.
  

选择一个SQLTXPLAIN用户使用的临时表空间.

ALLSTARS_ORACLE

• 
  可选应用程序用户.
  

选择你要对哪个用户下的SQL语句进行分析.例如，如果是EBS系统就选择APPS用户，如果是Siebel系统则选择SIEBEL用户，PeopleSoft系统则是SYSADM.这个步骤不会要求输入对应应用用户的密码. 你也可以在SQLT安装完成后手工添加其他SQLT分析用户，赋予需要分析的用户SQLT_USER_ROLE权限或者使用脚本 sqlt/install/sqguser.sql

• Oracle Pack. 使用许可 (T, D or N)
  

选择你拥有那些ORACLE PACK的使用许可。T代表Oracle Tuning, D代表 Oracle Diagnostic， N代表没有. 默认是T.

附件：安装实例

-bash-3.2$ ls -ld sqlt

drwxr-xr-x 7 oracle oinstall 4096 Apr  2 12:44 sqlt

-bash-3.2$ cd sqlt/install

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 28 11:20:45 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> START sqcreate.sql

PL/SQL procedure successfully completed.

Specify optional Connect Identifier (as per Oracle Net)

Include "@" symbol, ie. @PROD

If not applicable, enter nothing and hit the "Enter" key

Optional Connect Identifier (ie: @PROD):  保持默认，回车

PL/SQL procedure successfully completed.

Define SQLTXPLAIN password (hidden and case sensitive).

Password for user SQLTXPLAIN:  è设置SQLTXPLAIN用户密码

Re-enter password:

PL/SQL procedure successfully completed.

... please wait

no rows selected

Specify PERMANENT tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Default tablespace [UNKNOWN]: USERS è选择SQLTXPLAIN用户表空间，不能是SYSTEM或SYSAUX

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 è选择临时表空间

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, or execute sqlt/install/sqguser.sql.

Main application user of SQLT: FRED è选择要分析哪个用户下的SQL

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，保持

PL/SQL procedure successfully completed.

创建步骤省略 ……

SQCREATE completed. Installation completed successfully.

SQL>

SQLT 数据收集

使用SQLT的XTRACT方法对特定的SQL语句进行分析.
首先使用应用用户登录SQL*Plus（安装时选择的应用用户），执行脚本sqlt/run/sqltxtract.sql，输入需要分析语句的SQL_ID或者HASH_VALUE.

$ cd sqlt/run

$ sqlplus apps

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

SQL> START sqltxtract.sql 0w6uydn50g8cx

SQL> START sqltxtract.sql 2524255098

附件：分析实例

-bash-3.2$ ls -ld sqlt

drwxr-xr-x 7 oracle oinstall 4096 Apr  2 12:44 sqlt

-bash-3.2$ cd sqlt/run

-bash-3.2$ sqlplus fred/oracle

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 28 11:42:13 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> START sqltxtract.sql 00wmnkqhsb7ap

PL/SQL procedure successfully completed.

Parameter 1:

SQL_ID or HASH_VALUE of the SQL to be extracted (required)

Paremeter 2:

SQLTXPLAIN password (required)

Password for user SQLTXPLAIN:  输入SQLTXPLAIN用户密码

Re-enter password:

PL/SQL procedure successfully completed.

Value passed to sqltxtract:

~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID_OR_HASH_VALUE: "00wmnkqhsb7ap"

省略……

... collecting diagnostics details ...  开始收集需要数据

收集数据过程省略……

File sqlt_s77207_xtract_00wmnkqhsb7ap.zip for 00wmnkqhsb7ap has been created.

SQLTXTRACT completed.

收集完成，报告已经生成在当前目录下的sqlt_s77207_xtract_00wmnkqhsb7ap.zip

姨票难求

楼主把重点忽略了！伤不起