调优脚本 SQLT 安装&收集
调优脚本 SQLT 安装&收集SQLT 安装SQLT安装在自己的schema SQLTXPLAIN下，它不会在其他业务用户下安装任何对象.你可以在10.2以上版本的UNIX、LINUX或WINDOWS上安装.安装步骤:
[*]卸载之前版本 (可选).
这个步骤可选，用来删除之前的SQLTXPLAIN schema 对象，准备为全新安装做准备. $ cd sqlt/install$ sqlplus / as sysdbaSQL> START sqdrop.sql
[*]使用SYS用户登录并执行安装脚本sqlt/install/sqcreate.sql.
$ cd sqlt/install$ sqlplus / as sysdbaSQL> 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用户使用的临时表空间.
[*]
可选应用程序用户.
选择你要对哪个用户下的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 sqltdrwxr-xr-x 7 oracle oinstall 4096 Apr 2 12:44 sqlt-bash-3.2$ cd sqlt/install-bash-3.2$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 28 11:20:45 2012Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> START sqcreate.sqlPL/SQL procedure successfully completed. Specify optional Connect Identifier (as per Oracle Net)Include "@" symbol, ie. @PRODIf not applicable, enter nothing and hit the "Enter" keyOptional 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 waitno rows selectedSpecify PERMANENT tablespace to be used by SQLTXPLAIN.Tablespace name is case sensitive.Default tablespace : USERS è选择SQLTXPLAIN用户表空间，不能是SYSTEM或SYSAUXPL/SQL procedure successfully completed. ... please waitTABLESPACE------------------------------TEMPSpecify TEMPORARY tablespace to be used by SQLTXPLAIN.Tablespace name is case sensitive.Temporary tablespace : TEMP è选择临时表空间PL/SQL procedure successfully completed. The main application user of SQLT is the schemaowner that issued the SQL to be analyzed.For example, on an EBS application you wouldenter APPS.You will not be asked to enter its password.To add more SQLT users after this installationis completed simply grant them the SQLT_USER_ROLErole, or execute sqlt/install/sqguser.sql. Main application user of SQLT: FRED è选择要分析哪个用户下的SQLPL/SQL procedure successfully completed. SQLT can make extensive use of licensed featuresprovided by the Oracle Diagnostic and the OracleTuning Packs, including SQL Tuning Advisor (STA),SQL Monitoring and Automatic Workload Repository(AWR).To enable or disable access to these featuresfrom the SQLT tool enter one of the followingvalues 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 licensesOracle Pack license : 默认是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 appsSQL> START sqltxtract.sql |SQL> START sqltxtract.sql 0w6uydn50g8cxSQL> START sqltxtract.sql 2524255098
附件：分析实例 -bash-3.2$ ls -ld sqltdrwxr-xr-x 7 oracle oinstall 4096 Apr 2 12:44 sqlt-bash-3.2$ cd sqlt/run-bash-3.2$ sqlplus fred/oracleSQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 28 11:42:13 2012Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> 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
楼主把重点忽略了！伤不起
页:
[1]