(轉)oracle效能工具包Explain plan、Autotrace、Tkprof

<wbr> <wbr> <wbr> 欲先善其事，必先利其器。
<wbr> <wbr> <wbr>
<wbr> <wbr> <wbr> 最近同事總是問我如何調優SQL，有什麼工具可以使用。在此介紹一下關於Oracle內建的三個效能工具包的安裝、使用、以及一些基本技巧。
<wbr> <wbr> <wbr>
<wbr> <wbr> <wbr> 1、EXPLAIN PLAN：目的是給出一個特定SQL的查詢計劃。Oracle查詢計劃可以在邏輯上分析SQL語句的優劣。
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 安裝：

相關檔案

@$ORACLE_HOME/rdbms/admin/utlxplp.sql
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 1、sqlplus " / as sysdba"登入
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 2、@$ORACLE_HOME/rdbms/admin/utlxplan.sql
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 3、create public synonym plan_table for plan_table;
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 4、grant select ,insert,update ,delete on plan_table to public

<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 注：這裡沒有考慮安全性，plan_table對所有人都開發了增刪改查許可權，如果需要有安全性，可以針對某一個USER建立一個plan_table;

<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 使用：
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 注：還有一個utlxplp.sql用於顯示並行查詢計劃的內容；

<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 分析報告說明：
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 以oracle的scott使用者登入（這個使用者是被鎖定的，使用前使用alter user scott account unlock;解鎖）。
<wbr>SQL> explain plan for SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno <wbr> AND empno = 7782；
<wbr>SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

<wbr>PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| Id <wbr> | Operation <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> | Name <wbr> <wbr> <wbr> | Rows <wbr> | Bytes | Cost (%CPU)| Time <wbr> <wbr> <wbr> <wbr> |
----------------------------------------------------------------------------------------
| <wbr> <wbr> 0 | SELECT STATEMENT <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> | <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> | <wbr> <wbr> <wbr> <wbr> 1 | <wbr> <wbr> 117 | <wbr> <wbr> <wbr> <wbr> 3 <wbr> <wbr> <wbr> <wbr>(0)| 00:00:01 |
| <wbr> <wbr> 1 | <wbr> NESTED LOOPS <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> | <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> | <wbr> <wbr> <wbr> <wbr> 1 | <wbr> <wbr> 117 | <wbr> <wbr> <wbr> <wbr> 3 <wbr> <wbr> <wbr> <wbr>(0)| 00:00:01 |
| <wbr> <wbr> 2 | <wbr> <wbr> TABLE ACCESS BY INDEX ROWID| EMP <wbr> <wbr> <wbr> <wbr> | <wbr> <wbr> <wbr> <wbr> 1 | <wbr> <wbr> <wbr> 87 | <wbr> <wbr> <wbr> <wbr> 2 <wbr> <wbr> <wbr> <wbr>(0)| 00:00:01 |
|* <wbr> 3 | <wbr> <wbr> <wbr> INDEX UNIQUE SCAN <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> | PK_EMP <wbr> | <wbr> <wbr> <wbr> <wbr> 1 | <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> | <wbr> <wbr> <wbr> <wbr> 1 <wbr> <wbr> <wbr> <wbr>(0)| 00:00:01 |
| <wbr> <wbr> 4 | <wbr> <wbr> TABLE ACCESS BY INDEX ROWID| DEPT <wbr> <wbr> <wbr> | <wbr> <wbr> 409 | 12270 | <wbr> <wbr> <wbr> <wbr> 1 <wbr> <wbr> <wbr> <wbr>(0)| 00:00:01 |
|* <wbr> 5 | <wbr> <wbr> <wbr> INDEX UNIQUE SCAN <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> | PK_DEPT | <wbr> <wbr> <wbr> <wbr> 1 | <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> | <wbr> <wbr> <wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr>(0)| 00:00:01 |
----------------------------------------------------------------------------------------
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 注意看Operation的“縮排”。這個語句被解析成一棵“樹”。這課樹大約是
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 1
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> /
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 2 <wbr> <wbr> 4
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> / <wbr> <wbr> <wbr> <wbr>
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 3 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 5
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 上面的數字，和PLAN_TABLE_OUTPUT中的“Id”是對應的。簡單來說，就是這個語句有2，4構成；2又由3構成；4由5構成。

<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 詳細的SQL查詢計劃可以參閱其他書籍，作為初學者可以僅僅關注一下報告中的“TABLE ACCESS FULL”。如果你有where子句還有這個TABLE ACCESS FULL出現，表明你缺少索引（這僅僅針對初學者，但是現實生活中卻也能解決60－70％的問題）。

<wbr> <wbr> <wbr> 2、AUTOTRACE：目的是為了給出執行SQL時，實際進行了多少工作。
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>autotrace的特點在於簡單。

SQL> set autotrace ?
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 安裝：
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>SQL> @?/sqlplus/admin/plustrce.sql
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>SQL> grant plustrace to public；
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> SQL> grant select_catalog_role to scott; （Oracle 的sqldeveloper需要執行此語句）

<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 使用：
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> SQL> set autotrace on
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> SQL> 執行你的SQL.
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 能夠看到如下內容。
Statistics
----------------------------------------------------------
<wbr> <wbr> <wbr> <wbr> 0 <wbr> recursive calls
<wbr> <wbr> <wbr> <wbr> 0 <wbr> db block gets
<wbr> <wbr> <wbr> <wbr>32 <wbr> consistent gets
<wbr> <wbr> <wbr> <wbr> 0 <wbr> physical reads
<wbr> <wbr> <wbr> <wbr> 0 <wbr> redo size
<wbr> <wbr> <wbr> 801 <wbr> bytes sent via SQL*Net to client
<wbr> <wbr> <wbr> 416 <wbr> bytes received via SQL*Net from client
<wbr> <wbr> <wbr> <wbr> 2 <wbr> SQL*Net roundtrips to/from client
<wbr> <wbr> <wbr> <wbr> 2 <wbr> sorts (memory)
<wbr> <wbr> <wbr> <wbr> 0 <wbr> sorts (disk)
<wbr> <wbr> <wbr> <wbr>14 <wbr> rows processed

<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 統計（Statistics）內容說明：
<wbr> <wbr> <wbr> recursive calls：本SQL語句所巢狀呼叫的SQL的數目。
<wbr> <wbr> <wbr> <wbr>db block gets：邏輯讀；從快取記憶體中讀取的總塊數。
<wbr> <wbr> <wbr> <wbr>consistent gets：邏輯讀；一致性讀。
<wbr> <wbr> <wbr> <wbr>physical reads：物理讀；從資料檔案中的讀資料、或者從temp中的讀資料。
<wbr> <wbr> <wbr> <wbr>redo size：重做資料的大小。
<wbr> <wbr> <wbr> <wbr>bytes sent via SQL*Net to client：
<wbr> <wbr> <wbr> <wbr>bytes received via SQL*Net from client：
<wbr> <wbr> <wbr> <wbr>SQL*Net roundtrips to/from client：
<wbr> <wbr> <wbr> <wbr>sorts (memory)：記憶體中的排序
<wbr> <wbr> <wbr> <wbr>sorts (disk)：交換到臨時表空間中的排序
<wbr> <wbr> <wbr> <wbr>rows processed：受影響的行。
<wbr> <wbr> <wbr> 這些內容十分豐富，不是三言兩語能夠說明清楚的，有興趣可以參見後面列出的參考書目。

<wbr> <wbr> <wbr> 3、TKPROF：將底層的跟蹤檔案轉換成易懂的格式。
<wbr> <wbr> <wbr> 這東西無須安裝，關鍵是如何啟用它。
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>啟用：
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> alter session set timed_statistics=true;
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> alter session set events '10046 trace name context forever, level 12';

關閉：

<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 然後再執行你需要執行的SQL
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 獲取跟蹤檔名
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> select rtrim(c.value,'/')||'/'||d.instance_name||'_ora_'||ltrim(to_char(a.spid))||'.trc'
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> from v$process a, v$session b, v$parameter c, v$instance d
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> where a.addr=b.paddr
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> and <wbr> <wbr> <wbr> b.audsid=sys_context('userenv','sessionid')
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> and <wbr> <wbr> c.name='user_dump_dest'
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 生成tkprof報告 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> tkprof /usr/...........................xxxx.trc tkprof.prf
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 檢視分析報告
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 首先，“查詢”找到你執行那個SQL。後面列出大致如下內容。
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> call <wbr> <wbr> <wbr> <wbr> count <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> cpu <wbr> <wbr> <wbr> elapsed <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> disk <wbr> <wbr> <wbr> <wbr> <wbr> query <wbr> <wbr> <wbr> current <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> rows
------- ------ <wbr> -------- ---------- ---------- ---------- ---------- <wbr> ----------
Parse <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 1 <wbr> <wbr> <wbr> <wbr> <wbr> 0.07 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0.08 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0
Execute <wbr> <wbr> <wbr> <wbr> <wbr> 1 <wbr> <wbr> <wbr> <wbr> <wbr> 0.00 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0.00 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0
Fetch <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 2 <wbr> <wbr> <wbr> <wbr> <wbr> 2.32 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 3.05 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 187 <wbr> <wbr> <wbr> <wbr> <wbr> 44994 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 1
------- ------ <wbr> -------- ---------- ---------- ---------- ---------- <wbr> ----------
total <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 4 <wbr> <wbr> <wbr> <wbr> <wbr> 2.40 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 3.14 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 187 <wbr> <wbr> <wbr> <wbr> <wbr> 44994 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 1

<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 以及下面的內容，這是等待事件，非常有用。
Elapsed times include waiting on following events:
<wbr> Event waited on <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> Times <wbr> <wbr> Max. Wait <wbr> Total Waited
<wbr> ---------------------------------------- <wbr> <wbr> Waited <wbr> ---------- <wbr> ------------
<wbr> SQL*Net message to client <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 2 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0.00 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0.00
<wbr> db file sequential read <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 187 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0.06 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0.56
<wbr> SQL*Net message from client <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 2 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0.00 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0.00
********************************************************************************
<wbr> <wbr> <wbr>
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>

參見：《Oracle高效設計》 Thomas Kyte
<wbr> <wbr> <wbr> <wbr>－－我們不缺少書籍，缺少的是看書的人