ORACLE數(shù)據(jù)庫查看執(zhí)行計劃的方法
更新時間：2012年05月31日 15:12:41 作者：
基于ORACLE的應用系統(tǒng)很多性能問題，是由應用系統(tǒng)SQL性能低劣引起的，所以，SQL的性能優(yōu)化很重要，分析與優(yōu)化SQL的性能我們一般通過查看該SQL的執(zhí)行計劃，本文就如何看懂執(zhí)行計劃，以及如何通過分析執(zhí)行計劃對SQL進行優(yōu)化做相應說明
一、什么是執(zhí)行計劃（explain plan）
執(zhí)行計劃：一條查詢語句在ORACLE中的執(zhí)行過程或訪問路徑的描述。
二、如何查看執(zhí)行計劃
1: 在PL/SQL下按F5查看執(zhí)行計劃。第三方工具toad等。
很多人以為PL/SQL的執(zhí)行計劃只能看到基數(shù)、優(yōu)化器、耗費等基本信息，其實這個可以在PL/SQL工具里面設置的?？梢钥吹胶芏嗥渌畔?，如下所示
SQL>EXPLAIN PLAN FOR
SELECT * FROM SCOTT.EMP; --要解析的SQL腳本
SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
SQL>SET TIMING ON --控制顯示執(zhí)行時間統(tǒng)計數(shù)據(jù)
SQL>SET AUTOTRACE ON EXPLAIN --這樣設置包含執(zhí)行計劃、腳本數(shù)據(jù)輸出，沒有統(tǒng)計信息
SQL>執(zhí)行需要查看執(zhí)行計劃的SQL語句
SQL>SET AUTOTRACE OFF --不生成AUTOTRACE報告，這是缺省模式
SQL> SET AUTOTRACE ON --這樣設置包含執(zhí)行計劃、統(tǒng)計信息、以及腳本數(shù)據(jù)輸出
SQL>執(zhí)行需要查看執(zhí)行計劃的SQL語句
SQL>SET AUTOTRACE OFF
SQL> SET AUTOTRACE TRACEONLY --這樣設置會有執(zhí)行計劃、統(tǒng)計信息，不會有腳本數(shù)據(jù)輸出
SQL>執(zhí)行需要查看執(zhí)行計劃的SQL語句
SQL>SET AUTOTRACE TRACEONLY STAT --這樣設置只包含有統(tǒng)計信息
SQL>執(zhí)行需要查看執(zhí)行計劃的SQL語句
SQL> ALTER SESSION SET SQL_TRACE=TRUE;
SQL> SELECT * FROM SCOTT.EMP;
SQL> ALTER SESSION SET SQL_TRACE =FALSE;
那么此時如何查看相關信息？不管你在SQL*PLUS抑或PL/SQL DEVELOPER工具里面執(zhí)行上面腳本過后都看不到什么信息，你可以通過下面腳本查詢到trace日志信息
SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM
( SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# =1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P,
( SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME ='thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T
TKPROF 選項
選項 說明
TRACEFILE 跟蹤輸出文件的名稱
OUTPUTFILE 已設置格式的文件的名稱
SORT=option 語句的排序順序
PRINT=n 打印前 n 個語句
EXPLAIN=user/password 以指定的用戶名運行 EXPLAIN PLAN
INSERT=filename 生成 INSERT 語句
SYS=NO 忽略作為用戶 sys 運行的遞歸 SQL 語句
AGGREGATE=[Y|N] 如果指定 AGGREGATE = NO TKPROF 不聚集相同
SQL 文本的多個用戶
RECORD=filename 記錄在跟蹤文件中發(fā)現(xiàn)的語句
TABLE=schema.tablename 將執(zhí)行計劃放入指定的表而不是缺省的PLAN_TABLE
可以在操作系統(tǒng)中鍵入 tkprof 以獲得所有可用選項和輸出的列表
注 排序選項有
排序 選項說明
prscnt execnt fchcnt 調用分析執(zhí)行提取的次數(shù)
prscpu execpu fchcpu 分析執(zhí)行提取所占用的 CPU 時間
prsela exela fchela 分析執(zhí)行提取所占用的時間
prsdsk exedsk fchdsk 分析執(zhí)行提取期間的磁盤讀取次數(shù)
prsqry exeqry fchqry 分析執(zhí)行提取期間用于持續(xù)讀取的緩沖區(qū)數(shù)
prscu execu fchcu 分析執(zhí)行提取期間用于當前讀取的緩沖區(qū)數(shù)
prsmis exemis 分析執(zhí)行期間庫高速緩存未命中的次數(shù)
exerow fchrow 分析執(zhí)行期間處理的行數(shù)
userid 分析游標的用戶的用戶 ID
TKPROF 統(tǒng)計數(shù)據(jù)
Count: 執(zhí)行調用數(shù)
CPU: CPU 的使用秒數(shù)
Elapsed: 總共用去的時間
Disk: 物理讀取次數(shù)
Query: 持續(xù)讀取的邏輯讀取數(shù)
Current: 當前模式下的邏輯讀取數(shù)
Rows: 已處理行數(shù)
TKPROF 統(tǒng)計信息
統(tǒng)計 含義
Count 分析或執(zhí)行語句的次數(shù)以及為語句發(fā)出的提取調用數(shù)
CPU 每個階段的處理時間以秒為單位如果在共享池中找到該語句對于分析階段為 0
Elapsed 占用時間以秒為單位通常不是非常有用因為其它進程影響占用時間
Disk 從數(shù)據(jù)庫文件讀取的物理數(shù)據(jù)塊如果該數(shù)據(jù)被緩沖則該統(tǒng)計可能很低
Query 為持續(xù)讀取檢索的邏輯緩沖區(qū)通常用于 SELECT 語句
Current 在當前模式下檢索的邏輯緩沖區(qū)通常用于 DML 語句
Rows 外部語句所處理的行對于 SELECT 語句在提取階段顯示它對于 DML 語句在執(zhí)行階段顯示它
Query 和Current 的總和為所訪問的邏輯緩沖區(qū)的總數(shù)
執(zhí)行下面命令：tkprof D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc h:\out.txtoutputfile explain=etl/etl
TKPROF: Release 10.2.0.1.0 - Production on 星期三 5月 23 16:56:41 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
begin :id := sys.dbms_transaction.local_transaction_id; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 2
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
SELECT *
FROM
SCOTT.EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 14
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
Rows Execution Plan
------- ---------------------------------------------------
SELECT STATEMENT MODE: CHOOSE
TABLE ACCESS MODE: ANALYZED (FULL) OF 'EMP' (TABLE)
********************************************************************************
ALTER SESSION SET SQL_TRACE = FALSE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 2
Fetch 1 0.00 0.00 0 7 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 7 0 16
Misses in library cache during parse: 2
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
user SQL statements in session.
internal SQL statements in session.
SQL statements in session.
statement EXPLAINed in this session.
********************************************************************************
Trace file: D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc
Trace file compatibility: 10.01.00
Sort options: default
session in tracefile.
user SQL statements in trace file.
internal SQL statements in trace file.
SQL statements in trace file.
unique SQL statements in trace file.
SQL statements EXPLAINed using schema:
ETL.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
lines in trace file.
elapsed seconds in trace file.
4.3跟蹤其它用戶的進程，在很多時候我們需要跟蹤其它用戶的進程，而不是當前用戶，可以通過ORACLE提供的系統(tǒng)包
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION來完成。
例如：
SELECT SID, SERIAL#, USERNAME FROM V$SESSION WHERE USERNAME = 'ETL'
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(61,76,TRUE);
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(61,76,FALSE);
5 利用10046事件
ALTER SESSION SET TRACEFILE_IDENTIFIER = 10046;
ALTER SESSION SET EVENTS='10046 trace name context forever, level 8';
SELECT * FROM SCOTT.EMP;
ALTER SESSION SET EVENTS ='10046 trace name context off';
然后你可以用腳本查看追蹤文件的位置
SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM
( SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# =1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P,
( SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME ='thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T
查詢結果為wgods_ora_28279.trc文件， 但是去相應目錄卻沒有找到對應的追蹤文件，而是如下trace文件：wgods_ora_28279_10046.trc
6 利用10053事件
有點類似10046，在此略過、
7 系統(tǒng)視圖
通過下面一些系統(tǒng)視圖，你可以看到一些零散的執(zhí)行計劃的相關信息，有興趣的話可以多去研究一下。
SELECT * FROM V$SQL_PLAN
SELECT * FROM V$RSRC_PLAN_CPU_MTH
SELECT * FROM V$SQL_PLAN_STATISTICS
SELECT * FROM V$SQL_PLAN_STATISTICS_ALL
SELECT * FROM V$SQLAREA_PLAN_HASH
SELECT * FROM V$RSRC_PLAN_HISTORY
三、看懂執(zhí)行計劃
1.執(zhí)行順序
執(zhí)行順序的原則是：由上至下，從右向左
由上至下：在執(zhí)行計劃中一般含有多個節(jié)點，相同級別(或并列)的節(jié)點，靠上的優(yōu)先執(zhí)行，靠下的后執(zhí)行
從右向左：在某個節(jié)點下還存在多個子節(jié)點，先從最靠右的子節(jié)點開始執(zhí)行。
當然，你在PL/SQL工具中也可以通過它提供的功能來查看執(zhí)行順序。如下圖所示：
名詞解釋：
recursive calls 遞歸調用
db block gets 從buffer cache中讀取的block的數(shù)量當前請求的塊數(shù)目，當前模式塊意思就是在操作中正好提取的塊數(shù)目，而不是在一致性讀的情況下而產生的正常情況下，一個查詢提取的塊是在查詢查詢開始的那個時間點上存在的數(shù)據(jù)庫，當前塊是在這個時候存在數(shù)據(jù)塊，而不是這個時間點之前或者之后的的數(shù)據(jù)塊數(shù)目。
consistent gets 從buffer cache中讀取的undo數(shù)據(jù)的block的數(shù)量數(shù)據(jù)請求總數(shù)在回滾段Buffer中的數(shù)據(jù)一致性讀所需要的數(shù)據(jù)塊，，這里的概念是在你處理你這個操作的時侯需要在一致性讀狀態(tài)上處理多個塊，這些塊產生的主要原因是因為你在查詢過程中，由于其它會話對數(shù)據(jù) 塊進行操作，而對所要查詢的塊有了修改，但是由于我們的查詢是在這些修改之前調用的，所要需要對回滾 段中的數(shù)據(jù)塊的前映像進行查詢，以保證數(shù)據(jù)的一致性。這樣就產生了一致性讀。
physical reads 物理讀 就是從磁盤上讀取數(shù)據(jù)塊的數(shù)量。其產生的主要原因是：
1：在數(shù)據(jù)庫高速緩存中不存在這些塊。
2：全表掃描
3：磁盤排序
redo size DML生成的redo的大小
sorts (memory) 在內存執(zhí)行的排序量
sorts (disk) 在磁盤執(zhí)行的排序量
2091 bytes sent via SQL*Net to client 從SQL*Net向客戶端發(fā)送了2091字節(jié)的數(shù)據(jù)
416 bytes received via SQL*Net from client 客戶端向SQL*Net發(fā)送了416字節(jié)的數(shù)據(jù)。
參考文檔：SQLPlus User's Guide and Reference Release 11.1
consistent gets + db block gets. 當在內存中找不到所需要的數(shù)據(jù)塊的話，就需要從磁盤中獲取，于是就產生了物理讀。
3.具體內容查看
1> Plan hash Value
這一行是這一條語句的的hash值，我們知道ORACLE對每一條ORACLE語句產生的執(zhí)行計劃放在SHARE POOL里面，第一次要經過硬解析，產生hash值。下次再執(zhí)行時比較hash值，如果相同就不會執(zhí)行硬解析。
2> COST
COST沒有單位，是一個相對值，是SQL以CBO方式解析執(zhí)行計劃時，供ORACLE來評估CBO成本，選擇執(zhí)行計劃用的。沒有明確的含義，但是在對比是就非常有用。
公式：COST=（Single Block I/O COST + MultiBlock I/O Cost + CPU Cost）/ Sreadtim
3> 對上面執(zhí)行計劃列字段的解釋：
Id: 執(zhí)行序列，但不是執(zhí)行的先后順序。執(zhí)行的先后根據(jù)Operation縮進來判斷（采用最右最上最先執(zhí)行的原則看層次關系，在同一級如果某個動作沒有子ID就最先執(zhí)行。一般按縮進長度來判斷，縮進最大的最先執(zhí)行，如果有2行縮進一樣，那么就先執(zhí)行上面的。）
Operation：當前操作的內容。
Name：操作對象
Rows：也就是10g版本以前的Cardinality(基數(shù))，Oracle估計當前操作的返回結果集行數(shù)。
Bytes：表示執(zhí)行該步驟后返回的字節(jié)數(shù)。
Cost（CPU）：表示執(zhí)行到該步驟的一個執(zhí)行成本，用于說明SQL執(zhí)行的代價。
Time：Oracle 估計當前操作的時間。
4.謂詞說明：
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"."MGR" IS NOT NULL)
4 - access("A"."EMPNO" = "B"."MGR")
Access: 表示這個謂詞條件的值將會影響數(shù)據(jù)的訪問路勁（全表掃描還是索引）。
Filter：表示謂詞條件的值不會影響數(shù)據(jù)的訪問路勁，只起過濾的作用。
在謂詞中主要注意access，要考慮謂詞的條件，使用的訪問路徑是否正確。
5、 動態(tài)分析
如果在執(zhí)行計劃中有如下提示：
Note
------------
-dynamic sampling used for the statement
這提示用戶CBO當前使用的技術，需要用戶在分析計劃時考慮到這些因素。 當出現(xiàn)這個提示，說明當前表使用了動態(tài)采樣。我們從而推斷這個表可能沒有做過分析。
這里會出現(xiàn)兩種情況：
（1） 如果表沒有做過分析，那么CBO可以通過動態(tài)采樣的方式來獲取分析數(shù)據(jù)，也可以或者正確的執(zhí)行計劃。
（2） 如果表分析過，但是分析信息過舊，這時CBO就不會在使用動態(tài)采樣，而是使用這些舊的分析數(shù)據(jù)，從而可能導致錯誤的執(zhí)行計劃。
四、表訪問方式
1.Full Table Scan (FTS) 全表掃描
2.Index Lookup 索引掃描
There are 5 methods of index lookup:
index unique scan --索引唯一掃描
通過唯一索引查找一個數(shù)值經常返回單個ROWID，如果存在UNIQUE或PRIMARY KEY約束（它保證了語句只存取單行的話），ORACLE
經常實現(xiàn)唯一性掃描
Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.
index range scan --索引局部掃描
Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .
使用一個索引存取多行數(shù)據(jù)，在唯一索引上使用索引范圍掃描的典型情況是在謂詞(WHERE 限制條件)中使用了范圍操作符號（如>, < <>, >=, <=,BWTEEN)
index full scan --索引全局掃描
Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.
index fast full scan --索引快速全局掃描，不帶order by情況下常發(fā)生
Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.
index skip scan --索引跳躍掃描，where條件列是非索引的前提情況下常發(fā)生
Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.
3.Rowid 物理ID掃描
This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid掃描是最快的訪問數(shù)據(jù)方式
執(zhí)行計劃：一條查詢語句在ORACLE中的執(zhí)行過程或訪問路徑的描述。
二、如何查看執(zhí)行計劃
1: 在PL/SQL下按F5查看執(zhí)行計劃。第三方工具toad等。
很多人以為PL/SQL的執(zhí)行計劃只能看到基數(shù)、優(yōu)化器、耗費等基本信息，其實這個可以在PL/SQL工具里面設置的?？梢钥吹胶芏嗥渌畔?，如下所示
復制代碼 代碼如下:
SQL>EXPLAIN PLAN FOR
SELECT * FROM SCOTT.EMP; --要解析的SQL腳本
SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
復制代碼 代碼如下:
SQL>SET TIMING ON --控制顯示執(zhí)行時間統(tǒng)計數(shù)據(jù)
SQL>SET AUTOTRACE ON EXPLAIN --這樣設置包含執(zhí)行計劃、腳本數(shù)據(jù)輸出，沒有統(tǒng)計信息
SQL>執(zhí)行需要查看執(zhí)行計劃的SQL語句
SQL>SET AUTOTRACE OFF --不生成AUTOTRACE報告，這是缺省模式
SQL> SET AUTOTRACE ON --這樣設置包含執(zhí)行計劃、統(tǒng)計信息、以及腳本數(shù)據(jù)輸出
SQL>執(zhí)行需要查看執(zhí)行計劃的SQL語句
SQL>SET AUTOTRACE OFF
SQL> SET AUTOTRACE TRACEONLY --這樣設置會有執(zhí)行計劃、統(tǒng)計信息，不會有腳本數(shù)據(jù)輸出
SQL>執(zhí)行需要查看執(zhí)行計劃的SQL語句
SQL>SET AUTOTRACE TRACEONLY STAT --這樣設置只包含有統(tǒng)計信息
SQL>執(zhí)行需要查看執(zhí)行計劃的SQL語句
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
參考文檔：SQLPlus User's Guide and Reference Release 11.1
注意：PL/SQL Developer 工具并不完全支持所有的SQL*Plus命令，像SET AUTOTRACE ON 就如此，在PL/SQL Developer工具下執(zhí)行此命令會報錯
SQL> SET AUTOTRACE ON;
Cannot SET AUTOTRACE
4：SQL_TRACE可以作為參數(shù)在全局啟用，也可以通過命令形式在具體SESSION啟用
4.1 在全局啟用，在參數(shù)文件（pfile/spfile)中指定SQL_TRACE =true，在全局啟用SQL_TRACE時會導致所有進程活動被跟蹤，包括后臺進程以及用戶進程，通常會導致比較嚴重的性能問題，所以在生產環(huán)境要謹慎使用。
提示：通過在全局啟用SQL_TRACE， 我們可以跟蹤到所有后臺進程的活動，很多在文檔中的抽象說明，通過跟蹤文件的實時變化，我們可以清晰的看到各個進程間的緊密協(xié)調。
4.2在當前SESSION級別設置，通過跟蹤當前進程可以發(fā)現(xiàn)當前操作的后臺數(shù)據(jù)庫遞歸活動（這在研究數(shù)據(jù)庫新特性時尤其有效），研究SQL執(zhí)行時，發(fā)現(xiàn)后臺
錯誤等。
復制代碼 代碼如下:
SQL> ALTER SESSION SET SQL_TRACE=TRUE;
SQL> SELECT * FROM SCOTT.EMP;
SQL> ALTER SESSION SET SQL_TRACE =FALSE;
那么此時如何查看相關信息？不管你在SQL*PLUS抑或PL/SQL DEVELOPER工具里面執(zhí)行上面腳本過后都看不到什么信息，你可以通過下面腳本查詢到trace日志信息
復制代碼 代碼如下:
SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM
( SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# =1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P,
( SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME ='thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T
TKPROF的幫助信息如下
復制代碼 代碼如下:
TKPROF 選項
選項 說明
TRACEFILE 跟蹤輸出文件的名稱
OUTPUTFILE 已設置格式的文件的名稱
SORT=option 語句的排序順序
PRINT=n 打印前 n 個語句
EXPLAIN=user/password 以指定的用戶名運行 EXPLAIN PLAN
INSERT=filename 生成 INSERT 語句
SYS=NO 忽略作為用戶 sys 運行的遞歸 SQL 語句
AGGREGATE=[Y|N] 如果指定 AGGREGATE = NO TKPROF 不聚集相同
SQL 文本的多個用戶
RECORD=filename 記錄在跟蹤文件中發(fā)現(xiàn)的語句
TABLE=schema.tablename 將執(zhí)行計劃放入指定的表而不是缺省的PLAN_TABLE
可以在操作系統(tǒng)中鍵入 tkprof 以獲得所有可用選項和輸出的列表
注 排序選項有
排序 選項說明
prscnt execnt fchcnt 調用分析執(zhí)行提取的次數(shù)
prscpu execpu fchcpu 分析執(zhí)行提取所占用的 CPU 時間
prsela exela fchela 分析執(zhí)行提取所占用的時間
prsdsk exedsk fchdsk 分析執(zhí)行提取期間的磁盤讀取次數(shù)
prsqry exeqry fchqry 分析執(zhí)行提取期間用于持續(xù)讀取的緩沖區(qū)數(shù)
prscu execu fchcu 分析執(zhí)行提取期間用于當前讀取的緩沖區(qū)數(shù)
prsmis exemis 分析執(zhí)行期間庫高速緩存未命中的次數(shù)
exerow fchrow 分析執(zhí)行期間處理的行數(shù)
userid 分析游標的用戶的用戶 ID
TKPROF 統(tǒng)計數(shù)據(jù)
Count: 執(zhí)行調用數(shù)
CPU: CPU 的使用秒數(shù)
Elapsed: 總共用去的時間
Disk: 物理讀取次數(shù)
Query: 持續(xù)讀取的邏輯讀取數(shù)
Current: 當前模式下的邏輯讀取數(shù)
Rows: 已處理行數(shù)
TKPROF 統(tǒng)計信息
統(tǒng)計 含義
Count 分析或執(zhí)行語句的次數(shù)以及為語句發(fā)出的提取調用數(shù)
CPU 每個階段的處理時間以秒為單位如果在共享池中找到該語句對于分析階段為 0
Elapsed 占用時間以秒為單位通常不是非常有用因為其它進程影響占用時間
Disk 從數(shù)據(jù)庫文件讀取的物理數(shù)據(jù)塊如果該數(shù)據(jù)被緩沖則該統(tǒng)計可能很低
Query 為持續(xù)讀取檢索的邏輯緩沖區(qū)通常用于 SELECT 語句
Current 在當前模式下檢索的邏輯緩沖區(qū)通常用于 DML 語句
Rows 外部語句所處理的行對于 SELECT 語句在提取階段顯示它對于 DML 語句在執(zhí)行階段顯示它
Query 和Current 的總和為所訪問的邏輯緩沖區(qū)的總數(shù)
執(zhí)行下面命令：tkprof D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc h:\out.txtoutputfile explain=etl/etl
復制代碼 代碼如下:
TKPROF: Release 10.2.0.1.0 - Production on 星期三 5月 23 16:56:41 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
begin :id := sys.dbms_transaction.local_transaction_id; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 2
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
SELECT *
FROM
SCOTT.EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 14
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
Rows Execution Plan
------- ---------------------------------------------------
SELECT STATEMENT MODE: CHOOSE
TABLE ACCESS MODE: ANALYZED (FULL) OF 'EMP' (TABLE)
********************************************************************************
ALTER SESSION SET SQL_TRACE = FALSE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 2
Fetch 1 0.00 0.00 0 7 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 7 0 16
Misses in library cache during parse: 2
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
user SQL statements in session.
internal SQL statements in session.
SQL statements in session.
statement EXPLAINed in this session.
********************************************************************************
Trace file: D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc
Trace file compatibility: 10.01.00
Sort options: default
session in tracefile.
user SQL statements in trace file.
internal SQL statements in trace file.
SQL statements in trace file.
unique SQL statements in trace file.
SQL statements EXPLAINed using schema:
ETL.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
lines in trace file.
elapsed seconds in trace file.
4.3跟蹤其它用戶的進程，在很多時候我們需要跟蹤其它用戶的進程，而不是當前用戶，可以通過ORACLE提供的系統(tǒng)包
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION來完成。
例如：
復制代碼 代碼如下:
SELECT SID, SERIAL#, USERNAME FROM V$SESSION WHERE USERNAME = 'ETL'
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(61,76,TRUE);
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(61,76,FALSE);
5 利用10046事件
復制代碼 代碼如下:
ALTER SESSION SET TRACEFILE_IDENTIFIER = 10046;
ALTER SESSION SET EVENTS='10046 trace name context forever, level 8';
SELECT * FROM SCOTT.EMP;
ALTER SESSION SET EVENTS ='10046 trace name context off';
然后你可以用腳本查看追蹤文件的位置
SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM
( SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# =1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P,
( SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME ='thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T
查詢結果為wgods_ora_28279.trc文件， 但是去相應目錄卻沒有找到對應的追蹤文件，而是如下trace文件：wgods_ora_28279_10046.trc
6 利用10053事件
有點類似10046，在此略過、
7 系統(tǒng)視圖
通過下面一些系統(tǒng)視圖，你可以看到一些零散的執(zhí)行計劃的相關信息，有興趣的話可以多去研究一下。
復制代碼 代碼如下:
SELECT * FROM V$SQL_PLAN
SELECT * FROM V$RSRC_PLAN_CPU_MTH
SELECT * FROM V$SQL_PLAN_STATISTICS
SELECT * FROM V$SQL_PLAN_STATISTICS_ALL
SELECT * FROM V$SQLAREA_PLAN_HASH
SELECT * FROM V$RSRC_PLAN_HISTORY
三、看懂執(zhí)行計劃
1.執(zhí)行順序
執(zhí)行順序的原則是：由上至下，從右向左
由上至下：在執(zhí)行計劃中一般含有多個節(jié)點，相同級別(或并列)的節(jié)點，靠上的優(yōu)先執(zhí)行，靠下的后執(zhí)行
從右向左：在某個節(jié)點下還存在多個子節(jié)點，先從最靠右的子節(jié)點開始執(zhí)行。
當然，你在PL/SQL工具中也可以通過它提供的功能來查看執(zhí)行順序。如下圖所示：
2.執(zhí)行計劃中字段解釋
名詞解釋：
recursive calls 遞歸調用
db block gets 從buffer cache中讀取的block的數(shù)量當前請求的塊數(shù)目，當前模式塊意思就是在操作中正好提取的塊數(shù)目，而不是在一致性讀的情況下而產生的正常情況下，一個查詢提取的塊是在查詢查詢開始的那個時間點上存在的數(shù)據(jù)庫，當前塊是在這個時候存在數(shù)據(jù)塊，而不是這個時間點之前或者之后的的數(shù)據(jù)塊數(shù)目。
consistent gets 從buffer cache中讀取的undo數(shù)據(jù)的block的數(shù)量數(shù)據(jù)請求總數(shù)在回滾段Buffer中的數(shù)據(jù)一致性讀所需要的數(shù)據(jù)塊，，這里的概念是在你處理你這個操作的時侯需要在一致性讀狀態(tài)上處理多個塊，這些塊產生的主要原因是因為你在查詢過程中，由于其它會話對數(shù)據(jù) 塊進行操作，而對所要查詢的塊有了修改，但是由于我們的查詢是在這些修改之前調用的，所要需要對回滾 段中的數(shù)據(jù)塊的前映像進行查詢，以保證數(shù)據(jù)的一致性。這樣就產生了一致性讀。
physical reads 物理讀 就是從磁盤上讀取數(shù)據(jù)塊的數(shù)量。其產生的主要原因是：
1：在數(shù)據(jù)庫高速緩存中不存在這些塊。
2：全表掃描
3：磁盤排序
redo size DML生成的redo的大小
sorts (memory) 在內存執(zhí)行的排序量
sorts (disk) 在磁盤執(zhí)行的排序量
2091 bytes sent via SQL*Net to client 從SQL*Net向客戶端發(fā)送了2091字節(jié)的數(shù)據(jù)
416 bytes received via SQL*Net from client 客戶端向SQL*Net發(fā)送了416字節(jié)的數(shù)據(jù)。
參考文檔：SQLPlus User's Guide and Reference Release 11.1
consistent gets + db block gets. 當在內存中找不到所需要的數(shù)據(jù)塊的話，就需要從磁盤中獲取，于是就產生了物理讀。
3.具體內容查看
1> Plan hash Value
這一行是這一條語句的的hash值，我們知道ORACLE對每一條ORACLE語句產生的執(zhí)行計劃放在SHARE POOL里面，第一次要經過硬解析，產生hash值。下次再執(zhí)行時比較hash值，如果相同就不會執(zhí)行硬解析。
2> COST
COST沒有單位，是一個相對值，是SQL以CBO方式解析執(zhí)行計劃時，供ORACLE來評估CBO成本，選擇執(zhí)行計劃用的。沒有明確的含義，但是在對比是就非常有用。
公式：COST=（Single Block I/O COST + MultiBlock I/O Cost + CPU Cost）/ Sreadtim
3> 對上面執(zhí)行計劃列字段的解釋：
Id: 執(zhí)行序列，但不是執(zhí)行的先后順序。執(zhí)行的先后根據(jù)Operation縮進來判斷（采用最右最上最先執(zhí)行的原則看層次關系，在同一級如果某個動作沒有子ID就最先執(zhí)行。一般按縮進長度來判斷，縮進最大的最先執(zhí)行，如果有2行縮進一樣，那么就先執(zhí)行上面的。）
Operation：當前操作的內容。
Name：操作對象
Rows：也就是10g版本以前的Cardinality(基數(shù))，Oracle估計當前操作的返回結果集行數(shù)。
Bytes：表示執(zhí)行該步驟后返回的字節(jié)數(shù)。
Cost（CPU）：表示執(zhí)行到該步驟的一個執(zhí)行成本，用于說明SQL執(zhí)行的代價。
Time：Oracle 估計當前操作的時間。
4.謂詞說明：
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"."MGR" IS NOT NULL)
4 - access("A"."EMPNO" = "B"."MGR")
Access: 表示這個謂詞條件的值將會影響數(shù)據(jù)的訪問路勁（全表掃描還是索引）。
Filter：表示謂詞條件的值不會影響數(shù)據(jù)的訪問路勁，只起過濾的作用。
在謂詞中主要注意access，要考慮謂詞的條件，使用的訪問路徑是否正確。
5、 動態(tài)分析
如果在執(zhí)行計劃中有如下提示：
Note
------------
-dynamic sampling used for the statement
這提示用戶CBO當前使用的技術，需要用戶在分析計劃時考慮到這些因素。 當出現(xiàn)這個提示，說明當前表使用了動態(tài)采樣。我們從而推斷這個表可能沒有做過分析。
這里會出現(xiàn)兩種情況：
（1） 如果表沒有做過分析，那么CBO可以通過動態(tài)采樣的方式來獲取分析數(shù)據(jù)，也可以或者正確的執(zhí)行計劃。
（2） 如果表分析過，但是分析信息過舊，這時CBO就不會在使用動態(tài)采樣，而是使用這些舊的分析數(shù)據(jù)，從而可能導致錯誤的執(zhí)行計劃。
四、表訪問方式
1.Full Table Scan (FTS) 全表掃描
2.Index Lookup 索引掃描
There are 5 methods of index lookup:
index unique scan --索引唯一掃描
通過唯一索引查找一個數(shù)值經常返回單個ROWID，如果存在UNIQUE或PRIMARY KEY約束（它保證了語句只存取單行的話），ORACLE
經常實現(xiàn)唯一性掃描
Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.
index range scan --索引局部掃描
Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .
使用一個索引存取多行數(shù)據(jù)，在唯一索引上使用索引范圍掃描的典型情況是在謂詞(WHERE 限制條件)中使用了范圍操作符號（如>, < <>, >=, <=,BWTEEN)
index full scan --索引全局掃描
Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.
index fast full scan --索引快速全局掃描，不帶order by情況下常發(fā)生
Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.
index skip scan --索引跳躍掃描，where條件列是非索引的前提情況下常發(fā)生
Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.
3.Rowid 物理ID掃描
This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid掃描是最快的訪問數(shù)據(jù)方式
相關文章
這篇文章主要介紹了oracle獲取當前用戶表、字段等詳細信息SQL,需要的朋友可以參考下2014-07-07
故障現(xiàn)象：臨時表空間不足的問題已經報錯過3次，客戶也煩了，前兩次都是同事添加5G的數(shù)據(jù)文件，目前已經達到40G，占用臨時表空間主要是distinct 和group by 以及Union all 表數(shù)據(jù)量在200W左右，也不至于把40G的臨時表空間撐爆。2013-04-04
利用Oracle數(shù)據(jù)庫發(fā)送郵件的實例代碼
本文給大家利用oracle數(shù)據(jù)庫發(fā)送郵件的實例，代碼簡單易懂，試用性非常高，對此文感興趣的朋友一起學習吧2016-09-09
這篇文章主要介紹了Oracle如何查詢表索引和索引字段問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-07-07
今天在手工配置Oracle11g的EM時總是報如下錯誤,也沒有找到解決辦法，以下是我的解決過程,希望可以幫助你們2012-11-11
mybatis?調用?Oracle?存儲過程并接受返回值的示例代碼
這篇文章主要介紹了mybatis?調用?Oracle?存儲過程?并接受返回值,本文通過示例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-08-08
Oracle 手動創(chuàng)建數(shù)據(jù)庫步驟詳解
本文給大家介紹oracle手動創(chuàng)建數(shù)據(jù)庫步驟,需要的朋友可以參考下2015-10-10
Oracle數(shù)據(jù)庫中TRUNC()函數(shù)示例詳解
在Oracle數(shù)據(jù)庫中TRUNC函數(shù)主要用于截斷日期、時間或數(shù)值,通過指定不同的格式參數(shù),可以截取日期或時間的特定部分,如年份、月份、小時等,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-10-10
在SQL Server中有表變量,可以在function中方便地返回,習慣SQL Server或者需要把腳本從SQL Server轉到Oracle中的朋友可以都會碰到這個問題2012-10-10
ORACLE PL/SQL 觸發(fā)器能夠完成由數(shù)據(jù)庫的完整性約束難以完成的復雜業(yè)務規(guī)則的約束；監(jiān)視數(shù)據(jù)庫的各種操作以及實現(xiàn)審計功能2012-11-11