Oracle工具(Oracle Tools) – SQLT(SQLTXPLAIN)

Oracle又提供一款非常強大的調優SQL語句的工具，叫SQLTXPLAIN，我們一般簡稱SQLT，目前最新版本為11.4.4.8。

中國Oracle部落格上也有對SQLT的簡介
背景
SQLTXPLAIN (簡稱SQLT) 是Oracle提供的一種用來診斷SQL語句調優問題的工具。通常，當使用者遇到一個SQL語句調優問題時，Oracle Support會要求提供很多的資訊，比如SQL語句，10046/10053 trace，物件統計資訊，optimizer資訊等等。這些資訊的收集是非常繁瑣的事情，而且需要使用者具有深入的產品知識。由於資訊收集不全，或者客戶不知道如何收集，導致一些問題最終無法解決。Oracle提供了SQLT這個工具來解決這個問題。

功能
 ? 收集單條SQL語句的資訊，包括：
  ○ SQL文字
  ○ 執行計劃(explain)
  ○ 真實執行計劃(row source operation)
  ○ 執行時的資訊(時間，記錄數等等)
  ○ 10046/10053 trace
 ? 收集影響optimizer的統計資訊(包括SYSTEM statistics和Object statistics)。
 ? 收集所有可能會影響optimizer計算的引數：
  ○ 平臺
  ○ 版本
  ○ NLS資訊
  ○ 初始化引數
  ○ fix_control
  ○ gather_statistics_job
 ? 如果Oracle Tuning Packs是可以使用的話，自動呼叫SQL Tuning Advisor來生成最佳化報告。
 ? 自動匯出相關資料以方便客戶/Oracle Support/Oracle Development建立test case來重現客戶的問題，加速問題的處理。
 ? 自動生成SQL Profile的指令碼，來固定SQL的執行計劃。
 
特點
 ? SQLT是用SQL和PL/SQL編寫的，可以執行在各種不同版本作業系統的資料庫。
 ? 程式碼都是非加密的，可以清楚的看到操作的內容。
 ? 不會洩露使用者敏感資料。比如Column的最大/最小值還有histogram可以選擇隱藏起來。
 ? 安裝在自己的schema下，不會影響使用者資料。
 ? 免費下載和使用，只需有一個可用的My Oracle Support license。

下載及使用
 ? 下載：下載文件 ID:215187.1中的附件（需登陸My Oracle Support）。目前SQLT有兩個不同的版本，一個是基於9.2/10.1的，另一個是基於10.2以後的。
 ? 使用說明：解壓縮資料夾中的sqlt_instructions.html或者SQLTXPLAIN.pdf。在解壓縮資料夾下的doc目錄下也可以找到相同的資訊(PPT)。
在絕大多數的情況下，SQLT包含了診斷SQL語句調優問題的所有必要資訊。因此推薦客戶儘可能安裝並用它來收集資訊，以便於問題更有效率的解決。

官方原文介紹
SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise – ST CoE. SQLT main methods input one SQL statement and output a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly.

Once installed, you can use SQLT to analyze a SQL statement by passing its text within a script (including bind variables), or by providing its SQL_ID.

SQLT main methods connect to the database and collect execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and other elements that influence the performance of the one SQL being analyzed.

SQLT may use the Oracle Diagnostic and/or the Oracle Tuning Packs if your site has a license for them. These two provide enhanced functionality to the SQLT tool. During SQLT installation you can specify if one of these two packages is licensed by your site. If none, SQLT still provides some basic information that can be used for initial SQL performance diagnostics.

參考文件

• SQLT (SQLTXPLAIN) – Tool that helps to diagnose a SQL statement performing poorly [ID 215187.1]
• FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions [ID 1454160.1]
• How to Use SQLT (SQLTXPLAIN) to Create a Testcase Containing Application Data [ID 1465741.1]
• How to Use SQLT (SQLTXPLAIN) to Create a Testcase Without Row Data [ID 1470811.1]

下載
    、     

安裝
Installing SQLT
SQLT installs under its own schema SQLTXPLAIN. It does not install any objects into the application schema(s). You can install this version of SQLT in Oracle databases 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms.

Installation steps:
 1. Uninstall a prior version (optional).
     This optional step removes all obsolete SQLTXPLAIN schema objects and prepares the environment for a fresh install. Skip this step if you want to preserve the existing content of the SQLT repository (recommended).
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqdrop.sql

 2. Execute installation script sqlt/install/sqcreate.sql connected as SYS.
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqcreate.sql

During the installation you will be asked to enter values for these parameters:
 1. Optional Connect Identifier.
     In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key.
 2. SQLTXPLAIN password.
     Case sensitive in most systems.
 3. SQLTXPLAIN Default Tablespace.
     Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.
 4. SQLTXPLAIN Temporary Tablespace.
     Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.
 5. Optional Application User.
     This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won't be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE or by using provided script sqlt/install/sqguser.sql
 6. Licensed Oracle Pack. (T, D or N)
     You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.

If a silent installation is desired, there are three options to pass all 6 installation parameters:
 1. In a file.
     Executing first a script with pre-defined values, similar to sample script sqlt/install/sqdefparams.sql. Then use sqlt/install/sqcsilent.sql instead of sqlt/install/sqcreate.sql.
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqdefparams.sql
     SQL> START sqcsilent.sql

 2. In-line.
     Executing sqlt/install/sqcsilent2.sql instead of sqlt/install/sqcreate.sql. The former inputs the same 6 installation parameters but in-line.
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqcsilent2.sql '' sqltxplain USERS TEMP '' T

 3. Internal installation at Oracle.
     Executing sqlt/install/sqcinternal.sql instead of sqlt/install/sqcreate.sql. The former executes sqlt/install/sqdefparams.sql followed by sqlt/install/sqcsilent.sql.
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqcinternal.sql

Uninstalling SQLT
Uninstalling SQLT removes the SQLT repository and all SQLTXPLAIN schema objects. The SQLTXPLAIN user also gets dropped. To uninstall SQLT simply execute sqlt/install/sqdrop.sql connected as SYS.
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqdrop.sql

Upgrading SQLT
If you have a prior version of SQLT already installed in your system, you can upgrade SQLT to its latest version while partially preserving some objects of your existing SQLT repository. The new migrated SQLT repository can then be used to restore CBO statistics or to perform a COMPARE between old and new executions of SQLT.
To upgrade SQLT, simply do an installation without performing the optional uninstall step.
If the upgrade fails, then it is possible the prior SQLT version was too old to be upgraded. In such case please proceed to uninstall SQLT first, followed by a clean installation.

使用
介紹下使用方法，SQLT提供5個主要的方法：XTRACT、 XECUTE、 XTRXEC、 XTRSBY和XPLAIN，另外還有COMPARE、 TRCANLZR、 TRCAXTR、  TRCASET、 TRCASPLIT、 XTRSET等其他方法。

使用，都要使用安裝的時候建立的使用者：

XTRACT
        SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] SQL> START sqltxtract.sql 0w6uydn50g8cx SQL> START sqltxtract.sql 2524255098 XECUTE
        SQL> START [path]sqltxecute.sql [path]scriptname
        SQL> START run/sqltxecute.sql input/sample/script1.sql

XTRXEC
        SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] SQL> START sqltxtrxec.sql 0w6uydn50g8cx SQL> START sqltxtrxec.sql 2524255098 XTRSBY
        SQL> START sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [DB_LINK] SQL> START sqltxtrsby.sql 0w6uydn50g8cx V1123
        SQL> START sqltxtrsby.sql 2524255098 v1123

XPLAIN
        SQL> START [path]sqltxplain.sql [path]filename
        SQL> START run/sqltxplain.sql input/sample/sql1.sql

COMPARE
SQL> START [path]sqltcompare.sql [STATEMENT_ID 1] [STATEMENT_ID 2] SQL> START run/sqltcompare.sql 92263 72597 SQL> START run/sqltcompare.sql

TRCANLZR # sqlplus [application_user] SQL> START [path]sqltrcanlzr.sql [SQL Trace filename|control.txt] SQL> START run/sqltrcanlzr.sql V1122_ora_24292.trc
SQL> START run/sqltrcanlzr.sql control.txt

TRCAXTR # sqlplus [application_user] SQL> START sqltrcaxtr.sql [SQL Trace filename|control.txt] SQL> START sqltrcaxtr.sql V1122_ora_24292.trc
SQL> START sqltrcaxtr.sql control.txt

TRCASET # sqlplus [application_user] SQL> START sqltrcaset.sql

TRCASPLIT # sqlplus [sqlt_user] SQL> START [path]sqltrcasplit.sql [SQL Trace filename] SQL> START run/sqltrcasplit.sql V1122_ora_24292.trc

XTRSET # sqlplus [application_user] SQL> START sqltxtrset.sql List of SQL_IDs or HASH_VALUEs: 2yas208zgt5cv, 6rczmqdtg99mu, 8w8tjgac6tv12

這些方法的具體介紹和使用方法請閱讀解壓下載之後的包根目錄下sqlt_instructions.html。

下面是我在測試環境(Win7 + Oracle RDBMS 11.2.0.1)進行的操作：

安裝時六個地方互動，包括建立密碼、指定表空間等，推薦建立獨立表空間提供於SQLT使用，最小50M。 C:\>sqlplus / as sysdba

sys@LUOCS> start G:\app\Luocs\scripts\sqlt\install\sqcreate.sql 輸出內容非常多，這裡略。 安裝完後，會自動切換到sqltxplain使用者。 我以XTRACT方法為例實驗下： 首先匯出某個時間段的AWR報告，從中找到TOP 1 SQL，記錄SQL_ID - 8szmwam7fysa3 然後我們執行SQLT，XTRACT方法用到的是sqltxtract.sql指令碼 C:\>sqlplus sqltxplain/oracle

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 4 16:40:35 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

sqltxplain@LUOCS> start G:\app\Luocs\scripts\sqlt\run\sqltxtract.sql 8szmwam7fysa3 -- 省略N多行 test of sqlt_s37895_xtract_8szmwam7fysa3.zip OK Archive: sqlt_s37895_xtract_8szmwam7fysa3.zip Length Date Time Name --------- ---------- ----- ---- 135747 2012/11/04 16:43 sqlt_s37895_10053_i1_c0_extract.trc 3395 2012/11/04 16:43 sqlt_s37895_driver.zip 1159 2012/11/04 16:43 sqlt_s37895_export_driver.sql 10211 2012/11/04 16:43 sqlt_s37895_lite.html 13155 2012/11/04 16:43 sqlt_s37895_log.zip 747020 2012/11/04 16:43 sqlt_s37895_main.html 11925 2012/11/04 16:43 sqlt_s37895_readme.html 222 2012/11/04 16:42 sqlt_s37895_remote_driver.sql 192126 2012/11/04 16:43 sqlt_s37895_tc.zip 1255 2012/11/04 16:43 sqlt_s37895_tcb.zip 418 2012/11/04 16:43 sqlt_s37895_tcb_driver.sql 1089 2012/11/04 16:43 sqlt_s37895_tc_script.sql 197 2012/11/04 16:43 sqlt_s37895_tc_sql.sql 1750594 2012/11/04 16:43 sqlt_s37895_trc.zip --------- ------- 2868513 14 files File sqlt_s37895_xtract_8szmwam7fysa3.zip for 8szmwam7fysa3 has been created. SQLTXTRACT completed. OK，已經將分析結果壓縮到sqlt_s37895_xtract_8szmwam7fysa3.zip檔案，裡面包括14個檔案。這個打包檔案有必要的話就提交給Oracle Support即可。 其他方法不做演示。