說明
SQLTXPLAIN，也稱為SQLT，是由Oracle伺服器技術專家中心提供的工具 - ST CoE。 SQLT輸入一個SQL語句并輸出一組診斷檔案。這些檔案通常用于診斷性能不佳的SQL語句。 SQLT連接配接到資料庫并收集執行計劃，基于成本的優化程式CBO統計資訊，schema對象中繼資料，性能統計資訊，配置參數以及影響正在分析的SQL性能的類似元素。
SQLT不需要許可證，而且是免費的。
1.1. 下載下傳位址
MOS 文檔 ID 215187.1可以下載下傳
1.1.1. 安全模式
SQLT 在 安裝的過程中會建立兩個使用者和一個角色。這些使用者和角色的名字都是固定的。
SQLT repository 是由使用者 SQLTXPLAIN管理的。SQLT 的使用者每次使用 SQLT 提供的主要方法時都要提供 SQLTXPLAIN 的密碼。 SQLTXPLAIN 使用者被賦予了以下系統權限：
- CREATE SESSION
- CREATE TABLE
SQLT 包含的 PL/SQL 程式包以及視圖都是由使用者 SQLTXADMIN 管理的。SQLTXADMIN 使用者處于鎖定狀态并且由一個随機産生的密碼保護。SQLTXADMIN 使用者被賦予了以下系統權限：
- ADMINISTER SQL MANAGEMENT OBJECT
- ADMINISTER SQL TUNING SET
- ADVISOR
- ALTER SESSION
- ANALYZE ANY
- SELECT ANY DICTIONARY
- SELECT_CATALOG_ROLE
所有 SQLT 的使用者在使用 SQLT 提供的主要方法之前必須被賦予 SQLT_USER_ROLE 這個角色。SQLT_USER_ROLE 角色被賦予了以下系統權限：
- ADVISOR
- SELECT_CATALOG_ROLE
12c 在預設情況下 SYS 使用者不能作為 SQLT 的使用者，因為 PL/SQL 安全模型上改變的原因。
為了處理這個改變，SQLTADMIN 需要在 SYS 上被授予 INHERIT PRIVILEGES 權限。
GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN
1.2. 解壓SQLT壓縮包
[root@host1 software]# unzip sqlt_122180331.zip
[root@host1 software]# chown -R oracle:oinstall sqlt
[root@host1 sqlt]# ll
total 112
drwxr-xr-x 2 oracle oinstall 31 Oct 6 2017 doc
drwxr-xr-x 3 oracle oinstall 20 Oct 6 2017 input
drwxr-xr-x 2 oracle oinstall 4096 Mar 31 17:17 install
drwxr-xr-x 2 oracle oinstall 4096 Oct 6 2017 run
-rw-r--r-- 1 oracle oinstall 57883 Mar 31 16:54 sqlt_instructions.html
-rw-r--r-- 1 oracle oinstall 38899 Oct 4 2017 sqlt_instructions.txt
drwxr-xr-x 7 oracle oinstall 4096 Oct 6 2017 utl 1.3. 安裝SQLT
1.解除安裝先前版本（可選）
該可選步驟将删除所有廢棄的 SQLTXPLAIN/SQLTXADMIN schema 對象，并為全新安裝準備環境。如果要保留 SQLT Repository 的現有内容，請跳過此步驟（推薦）
[oracle@host1 ~]$ cd /software/sqlt/install/
[oracle@host1 install]$ sqlplus / as sysdba
SQL> START sqdrop.sql 2.以 SYS 身份連接配接資料庫并執行安裝腳本 sqlt/install/sqcreate.sql。
[oracle@host1 install]$ cd /software/sqlt/install/
[oracle@host1 install]$ sqlplus / as sysdba
SQL> START sqcreate.sql 在安裝期間，系統将要求你輸入以下參數值：
1）可選連接配接辨別符(當安裝在一個可插拔資料庫上時是必須的）
Optional Connect Identifier (ie: @PROD): @pdbcndba 在一些受限通路的系統中，你可能需要指定連接配接辨別符，例如 @PROD。如果不需要連接配接辨別符，則不要輸入任何資料，隻需單擊Enter鍵。什麼也不鍵入是最常使用的安裝方法。
當安裝在一個可插拔資料庫上時連接配接辨別符是必須提供的。
2）SQLTXPLAIN 密碼。
Password for user SQLTXPLAIN:
Re-enter password: 在大多數系統中區分大小寫。
3）SQLTXPLAIN 預設表空間。
Default tablespace [UNKNOWN]: CNDBA 從可用的永久表空間清單中，選擇 SQLT Repository 的 SQLTXPLAIN 應使用的表空間。它必須具有 50MB 以上的可用空間。
4）SQLTXPLAIN 臨時表空間。
Temporary tablespace [UNKNOWN]: TEMPTS1 從可用的臨時表空間清單中，選擇 SQLTXPLAIN 臨時操作和臨時對象應使用的表空間。
5）可選應用程式使用者。
Main application user of SQLT: SYS 這是發出要分析 SQL 語句的使用者。例如，在 EBS 系統上，指定為 APPS；在 Siebel 上，應指定為 SIEBEL；在 People Soft 上，應指定為 SYSADM。系統不會要求你輸入此使用者的密碼。也可以在安裝該工具後添加其他的 SQLT 使用者，方法為：授予他們角色 SQLT_USER_ROLE。
6）授權的 Oracle Pack。（T，D 或 N）
"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 可以指定 T 表示 Oracle Tuning；D 表示 Oracle Diagnostic，或 N 表示都沒有。如果選擇 T 或 D，SQLT 可以在它生成的診斷檔案中包含授權的内容。預設值為 T。如果選擇 N，SQLT 将隻安裝限定的功能。
如果需要靜默安裝，可以使用下面三個選項來傳遞所有 6 個安裝參數：
1）在檔案中。
首先使用一個腳本進行值的預先定義，類似于示例腳本 sqlt/install/sqdefparams.sql。然後使用 sqlt/install/sqcsilent.sql，而不是 sqlt/install/sqcreate.sql。
$ cd sqlt/install
$ sqlplus / as sysdba
SQL> START sqdefparams.sql
SQL> START sqcsilent.sql 2）指令行。
執行 sqlt/install/sqcsilent2.sql，而不是 sqlt/install/sqcreate.sql。前者以内嵌形式輸入 6 個安裝參數。
$ cd sqlt/install
$ sqlplus / as sysdba
SQL> START sqcsilent2.sql '' sqltxplain USERS TEMP '' T 3）在 Oracle 内部安裝。
執行 sqlt/install/sqcinternal.sql，而不是 sqlt/install/sqcreate.sql。前者首先執行 sqlt/install/sqdefparams.sql，然後是 sqlt/install/sqcsilent.sql。
$ cd sqlt/install
$ sqlplus / as sysdba
SQL> START sqcinternal.sql 1.4. 檢視sql_id
可以在AWR或ASH報告中找到語句的SQL_ID，也可以使用V$SQL視圖從資料庫資料字典中選擇它。
1）如果可以使用特定的可識别字元串或某種獨特的注釋來識别SQL，例如：/ * TARGET SQL * /那麼這将使其更容易定位。
SQL> SELECT /* TARGET SQL */ * FROM dual;
DUM
---
X
SQL> SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
FROM v$sql
WHERE sql_text like 'SELECT /* TARGET SQL */%';
SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------------------ --------------- ---------------------------------------------
0xzhrtn5gkpjs 272002086 SELECT /* TARGET SQL */ * FROM dual 2）為友善起見，此處包含hash_value。還可以使用替換變量在V$SQL視圖中找到SQL_ID：
SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%'; 在pl/sql執行語句會提示你要替換的變量，也可以直接使用以下語句模糊查詢
SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%select%'; 3）如果v$sql中沒有SQL，則可以使用DBA_HIST_SQLTEXT和DBA_HIST_SQLSTAT：
select t.sql_id,
t.sql_text,
s.executions_total,
s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.snap_id between 416 and 417; 快照id可以通過以下語句查到：
select snap_id, dbid from DBA_HIST_SNAPSHOT; 1.5. 主要方法
在使用 SQLT 提供的主要方法之前，須確定 SQLT 已經被正确安裝，并且使用 SQLT 的使用者被賦予了 SQLT_USER_ROLE 角色。
假如 SQLT 安裝繞過了 SQL*Net （意味着你并沒有在安裝期間沒有指定連接配接符），那麼在從遠端用戶端執行任何 SQLT 主要方法前，你将需要手動設定連接配接符參數。即，假如你連接配接使用 sqlplus scott/tiger@myprod 那麼你需要執行： EXEC sqltxadmin.sqlt$a.set_sess_param('connect_identifier', '@myprod');
SQLT 為一個 SQL 語句提供了下面 7種主要方法來生成診斷詳細資訊 XTRACT，XECUTE，XTRXEC，XTRSBY，XPLAIN，XPREXT 和 XPREXC。 XTRACT，XECUTE，XTRXEC，XTRSBY，XPREXT 和 XPREXC 處理綁定變量和會做 bind peeking（綁定變量窺視），但是 XPLAIN 不會。這是因為 XPLAIN 是基于 EXPLAIN PLAN FOR 指令執行的，該指令不做 bind peeking。是以，如果可能請避免使用XPLAIN.
除了 XPLAIN 的 bind peeking 限制外，所有這 7種主要方法都可以提供足夠的診斷詳細資訊，對性能較差或産生錯誤結果集的 SQL 進行初步評估。如果該 SQL 仍位于記憶體中或者 Automatic Workload Repository (AWR) 中，請使用 XTRACT 或 XTRXEC，其他情況請使用 XECUTE。對于 Data Guard 或備用隻讀資料庫，請使用 XTRSBY。僅當其他方法都不可行時，再考慮使用 XPLAIN。XPREXT 和 XPREXC 是類似于 XTRACT 和 XECUTE，但為了提高 SQLT 的性能它們禁了一些 SQLT 的特性。
1.5.1. XTRACT 方法
如果你知道待分析 SQL 的 SQL_ID 或 HASH_VALUE，請使用該方法，否則請使用 XECUTE。你可以在 AWR report 中找到 SQL_ID，在 SQL trace 中找到 HASH_VALUE（在 SQL 文本上面，通過 "hv=" 标記進行辨別）。
如果該 SQL 仍位于記憶體中，或其已被 AWR 捕獲，那麼使用 XTRACT 可發現該 SQL 并提供一組診斷檔案，否則 XTRACT 将輸出錯誤。
如果對 SQL 進行硬分析時将參數 STATISTICS_LEVEL 設定為 ALL，将可以得到重要的性能統計資訊（如每步操作的實際行數）。你也可以通過在 SQL 中包括以下 CBO 提示來生成同樣有用的性能統計資訊：/*+ GATHER_PLAN_STATISTICS */。在 11g 中，你可以在 SQL 中包含以下 CBO 提示以獲得增強的診斷資訊：/*+ GATHER_PLAN_STATISTICS MONITOR */
使用此方法時，它會要求提供 SQLTXPLAIN 密碼，這個在導出與該 XTRACT 執行所對應的 SQLT Repository 時會被使用。
該方法需要對執行 SQLT 的應用程式使用者授予 SQLT_USER_ROLE 角色。
要使用該 XTRACT 方法，首先確定已安裝了 SQLT，然後以已執行待分析 SQL 的應用程式使用者身份連接配接到 SQL*Plus，并執行 sqlt/run/sqltxtract.sql 腳本，傳遞 SQL_ID 或 HASH_VALUE。
$ cd sqlt/run
$ sqlplus apps
SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
SQL> START sqltxtract.sql 3kqrku32p6sfn sqltxplain_password
SQL> START sqltxtract.sql 2524255098 sqltxplain_password 1.5.2. XECUTE 方法
與 XTRACT方法相比，該方法提供的資訊更為詳細。正如名稱 XECUTE 所訓示的，它将執行正在分析的 SQL，然後生成一組診斷檔案。它的主要缺點是如果正在分析的 SQL 需要很長時間來執行，那麼該方法也要花費很長的時間。
根據經驗法則，僅當 SQL 執行少于 1 小時的情況下，才考慮使用此方法，否則請使用 XTRACT。
使用此 XECUTE 方法之前，必須建立一個包含 SQL 文本的文本檔案。如果 SQL 包括綁定變量，則你的檔案必須包含綁定變量聲明和指派。以 sqlt/input/sample/script1.sql 為例。你的 SQL 應該包含标記 /* ^^unique_id */（強烈建議）。
如果你的 SQL 需要與 SQL*Plus 無法使用的資料類型綁定，或者它使用了集合，你可能需要将 SQL 嵌入到匿名 PL/SQL 塊中。在這種情況下，請使用 sqlt/input/sample/plsql1.sql 作為此方法的輸入示例。
對于修改資料的語句，例如 INSERT/UPDATE/DELETE，工具會在語句執行之前建立一個儲存點，這樣在會話結束時事務處理可以回退到該儲存點。關于 SAVEPOINT（儲存點）的更多資訊，請參閱《Oracle Concepts》參考手冊。
使用此方法時，它會要求提供 SQLTXPLAIN 密碼，這個在導出與該 XECUTE 執行所對應的 SQLT Repository 時會被使用。
該方法需要對執行 SQLT 的應用程式使用者授予 SQLT_USER_ROLE 角色。
要使用該 XECUT 方法，首先確定已安裝了 SQLT，然後以已執行待分析 SQL 的應用程式使用者身份連接配接到 SQL*Plus，并執行 sqlt/run/sqltxecute.sql 腳本，傳遞包含 SQL 文本及其綁定變量的文本檔案名稱。你需要将該檔案放置到 sqlt/input 目錄下，并在位于 sqlt 主目錄時運作 XECUTE，如下所示：
# cd sqlt
$ sqlplus apps
SQL> START [path]sqltxecute.sql [path]scriptname [sqltxplain_password]
SQL> START run/sqltxecute.sql input/sample/script1.sql sqltxplain_password 1.5.3. XTRXEC 方法
該方法合并了 XTRACT 和 XECUTE 的功能。實際上，XTRXEC 連續執行了這兩種方法。針對所請求 SQL 語句找到的開銷較大的計劃，XTRACT 階段将生成一個包含提取的 SQL 以及綁定聲明和指派的腳本。然後，XTRXEC 使用第一階段建立的腳本執行XECUTE 階段。
SQLT 根據在記憶體中生成開銷最大的執行計劃時窺視到的值，建立腳本的綁定變量的以供 XTRACT使用。判斷計劃的開銷大小的标準是基于這個計劃的平均執行時間。
如果 XTRXEC 僅執行了第一個階段（XTRACT） 後就輸出錯誤，你可能需要檢查在第二階段（XECUTE）使用的腳本并相應調整綁定變量。使用不常用資料類型時尤其需要進行調整。
使用此方法時，它會要求提供 SQLTXPLAIN 密碼，這個在導出與該 XTRXEC 執行所對應的 SQLT Repository 時會被使用。
該方法需要對執行 SQLT 的應用程式使用者授予 SQLT_USER_ROLE 角色。
要使用該 XTRXEC 方法，首先確定已安裝了 SQLT，然後以執行待分析 SQL 的應用程式使用者身份連接配接到 SQL*Plus，并執行 sqlt/run/sqltxtrxec.sql 腳本，傳遞 SQL_ID 或 HASH_VALUE。
$ cd sqlt/run
$ sqlplus apps
SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
SQL> START sqltxtrxec.sql 3kqrku32p6sfn sqltxplain_password
SQL> START sqltxtrxec.sql 2524255098 sqltxplain_password 1.5.4. XTRSBY 方法
如果需要分析在 Data Guard 或備用隻讀資料庫上執行的 SQL，請使用該方法。你需要知道要分析的 SQL 的 SQL_ID 或 HASH_VALUE。
在主庫上建立一個到備庫的database link，連接配接到的使用者需要有通路資料字典的權限，通常都是使用有 DBA 權限的使用者。
CREATE PUBLIC DATABASE LINK V1123 CONNECT TO mydba IDENTIFIED by mydba_password
USING '(DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)
(HOST=coesrv14.us.oracle.com)(PORT=1521))(CONNECT_DATA=(SID = V1123)))';
如果該 SQL 仍位于隻讀資料庫中的記憶體中，那麼使用 XTRSBY 可發現該 SQL 并提供一組診斷檔案，否則 XTRSBY 将輸出錯誤。
如果對隻讀資料庫中的 SQL 進行硬分析時将參數 STATISTICS_LEVEL 設定為 ALL，将可以得到重要的性能統計資訊（如每個執行計劃操作的實際行數）。你也可以通過在 SQL 中包括以下 CBO 提示來生成同樣有用的性能統計資訊：/*+ GATHER_PLAN_STATISTICS */。在 11g 中，你可以在 SQL 中包含以下 CBO 提示以獲得改進的診斷資訊：/*+ GATHER_PLAN_STATISTICS MONITOR */
使用此方法時，它會要求提供 SQLTXPLAIN 密碼，這個在導出與該 XTRSBY 執行所對應的 SQLT Repository 時會被使用。
XTRSBY 需要 3 個參數： SQL id，DB_LINK的 ID，以及 SQLTXPLAIN 的密碼。
該方法需要對執行 SQLT 的應用程式使用者授予 SQLT_USER_ROLE 角色。
要使用該 XTRSBY 方法，首先確定在主資料庫上已安裝了 SQLT，并且已複制到該隻讀資料庫中。然後連接配接到主要資料庫中的 SQL*Plus 并執行 sqlt/run/sqltxtrsby.sql 腳本，傳遞 SQL_ID 或 HASH_VALUE，然後是 DB_LINK。
$ cd sqlt/run
$ sqlplus apps
SQL> START sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] [DB_LINK]
SQL> START sqltxtrsby.sql 3kqrku32p6sfn sqltxplain_password V1123
SQL> START sqltxtrsby.sql 2524255098 sqltxplain_password v1123 除了 XTRSBY，還可以從隻讀資料庫直接執行 sqlt/utl/sqlhc.sql 或 sqlt/utl/sqlhcxec.sql。這兩個隻讀腳本不在資料庫上安裝任何東西，也不執行 DML 指令。它們提供在 XTRSBY 中沒有的其他資訊。
1.5.5. XPLAIN 方法
該方法是基于 EXPLAIN PLAN FOR 指令執行的，是以它将無視你的 SQL 語句引用的綁定變量。僅當無法使用 XTRACT 或 XECUTE 時才使用該方法。
使用此 XPLAIN 方法之前，必須建立一個包含 SQL 文本的文本檔案。如果 SQL 包括綁定變量，你有兩個選擇：保持 SQL 文本“不變”，或謹慎使用相同資料類型的字面值替換該綁定。以 sqlt/input/sample/sql1.sql 為例。
使用此方法時，它會要求提供 SQLTXPLAIN 密碼，這個在導出與該 XPLAIN 執行所對應的 SQLT Repository 時會被使用。
該方法需要對執行 SQLT 的應用程式使用者授予 SQLT_USER_ROLE 角色。
要使用該 XPLAIN 方法，首先確定已安裝了 SQLT，然後以已執行待分析 SQL 的應用程式使用者的身份連接配接到 SQL*Plus，并執行 sqlt/run/sqltxplain.sql 腳本，傳遞包含 SQL 文本的文本檔案名稱。你需要将該檔案放置到 sqlt/input 目錄下，并在位于 sqlt 主目錄時運作 XPLAIN，如下所示：
# cd sqlt
$ sqlplus apps
SQL> START [path]sqltxplain.sql [path]filename [sqltxplain_password]
SQL> START run/sqltxplain.sql input/sample/sql1.sql sqltxplain_password 1.5.6. XPREXT 方法
假如你想使用 XTRACT 同時希望禁用一些 SQLT 的特性使之 執行更快，請使用這個方法。腳本 sqlt/run/sqltcommon11.sql 顯示了哪些特性被禁用。
假如你知道要被分析的 SQL 的 SQL_ID 或者 HASH_VALUE，使用這個方法，否則請使用 XPREXC.你可以在 AWR report 中找到 SQL_ID，在 SQL trace 中找到 HASH_VALUE（在 SQL 文本上面，通過 "hv=" 标記進行辨別）。
使用此方法時，它會要求提供 SQLTXPLAIN 密碼，這個在導出與該 XPREXT 執行所對應的 SQLT Repository 時會被使用。
該方法需要對執行 SQLT 的應用程式使用者授予 SQLT_USER_ROLE 角色。
要使用該 XPREXT 方法，首先確定已 安裝了 SQLT，然後以已執行待分析 SQL 的應用程式使用者身份連接配接到 SQL*Plus，并執行 sqlt/run/sqltxprext.sql 腳本，傳遞 SQL_ID 或 HASH_VALUE。
$ cd sqlt/run
$ sqlplus apps
SQL> START sqltxprext.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
SQL> START sqltxprext.sql 3kqrku32p6sfn sqltxplain_password
SQL> START sqltxprext.sql 2524255098 sqltxplain_password 1.5.7. XPREXC 方法
假如你想使用 XECUTE 同時希望禁用一些 SQLT 的特性使之執行更快，請使用這個方法。腳本 sqlt/run/sqltcommon11.sql 顯示哪些特性被禁用
根據經驗法則，僅當 SQL 執行少于 1 小時的情況下，才考慮使用此方法，否則請使用 XPREXT。
使用此 XPREXC 方法之前，必須建立一個包含 SQL 文本的文本檔案。如果 SQL 包括綁定變量，則你的檔案必須包含綁定變量聲明和指派。以 sqlt/input/sample/script1.sql 為例。你的 SQL 應該包含标記 /* ^^unique_id */，這個标記應該拼寫準确，換句話說請不要改變它。
如果你的 SQL 需要與 SQL*Plus 無法使用的資料類型綁定，或者它使用了集合，你可能需要将 SQL 嵌入到匿名 PL/SQL 塊中。在這種情況下，請使用 sqlt/input/sample/plsql1.sql 作為此方法的輸入示例。
對于修改資料的語句，例如 INSERT/UPDATE/DELETE，工具會在語句執行之前建立一個儲存點，這樣在會話結束時事務處理可以回退到該儲存點。關于 SAVEPOINT（儲存點）的更多資訊，請參閱《Oracle Concepts》參考手冊。
用此方法時，它會要求提供 SQLTXPLAIN 密碼，這個在導出與該 XPREXC 執行所對應的 SQLT Repository 時會被使用。
該方法需要對執行 SQLT 的應用程式使用者授予 SQLT_USER_ROLE 角色。
要使用該 XPREXC 方法，首先確定已 安裝了 SQLT，然後以已執行待分析 SQL 的應用程式使用者身份連接配接到 SQL*Plus，并執行 sqlt/run/sqltxprexc.sql 腳本，傳遞包含 SQL 文本及其綁定變量的文本檔案名稱。你需要将該檔案放置到 sqlt/input 目錄下，并在位于 sqlt 主目錄時運作 XPREXC，如下所示：
# cd sqlt
$ sqlplus apps
SQL> START [path]sqltxprexc.sql [path]scriptname [sqltxplain_password]
SQL> START run/sqltxprexc.sql input/sample/script1.sql sqltxplain_password ![latex及TeXstudio使用筆記（不定時更新）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)