ORACLE中檢視執行計劃(轉)

有三種方法：

ORACLE SQL_TRACE

“SQL TRACE”是Oracle提供的用於進行SQL跟蹤的手段，是強有力的輔助診斷工具。在日常的資料庫問題診斷和解決中，“SQL TRACE”是非常常用的方法。

一般，一次跟蹤可以分為以下幾步：

1、界定需要跟蹤的目標範圍，並使用適當的命令啟用所需跟蹤。

2、經過一段時間後，停止跟蹤。此時應該產生了一個跟蹤結果檔案。

3、找到跟蹤檔案，並對其進行格式化，然後閱讀或分析。

本文就“SQL TRACE”的這些使用作簡單探討，並通過具體案例對SQL_TRACE的使用進行說明。

一、“SQL TRACE”的啟用。

(A)SQL_TRACE說明

SQL_TRACE可以作為初始化引數在全域性啟用，也可以通過命令列方式在具體session啟用。

1． 在全域性啟用

在引數檔案(pfile/spfile)中指定: SQL_TRACE = true

    在全域性啟用SQL_TRACE會導致所有程式的活動被跟蹤，包括後臺程式及所有使用者程式，這通常會導致比較嚴重的效能問題，所以在生產環境中要謹慎使用。

提示: 通過在全域性啟用SQL_TRACE，我們可以跟蹤到所有後臺程式的活動，很多在文件中的抽象說明，通過跟蹤檔案的實時變化，我們可以清晰的看到各個程式之間的緊密協調。

2． 在當前session級設定

大多數時候我們使用SQL_TRACE跟蹤當前會話的程式。通過跟蹤當前程式可以發現當前操作的後臺資料庫遞迴活動(這在研究資料庫新特性時尤其有效)，研究SQL執行，發現後臺錯誤等。

在session級啟用和停止SQL_TRACE方式如下:

啟用當前session的跟蹤:

SQL> alter session set SQL_TRACE=true;

Session altered.

此時的SQL操作將被跟蹤:

SQL> select count(*) from dba_users;

COUNT(*)

----------

        34

結束跟蹤:

SQL> alter session set SQL_TRACE=false;

Session altered.

3． 跟蹤其它使用者程式

    在很多時候我們需要跟蹤其他使用者的程式，而不是當前使用者，這可以通過Oracle提供的系統包DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION來完成

通過v$session我們可以獲得sid、serial#等資訊:

獲得程式資訊，選擇需要跟蹤的程式:

SQL> select sid,serial#,username from v$session where username =’***’

       SID    SERIAL# USERNAME

---------- ---------- ------------------------------

         8       2041 SYS

         9        437 EYGLE

設定跟蹤:

SQL> exec dbms_system.set_SQL_TRACE_in_session(9,437,true)

PL/SQL procedure successfully completed.

….

可以等候片刻，跟蹤session執行任務,捕獲sql操作…

….

停止跟蹤:

SQL> exec dbms_system.set_SQL_TRACE_in_session(9,437,false)

PL/SQL procedure successfully completed.

(B) 10046事件說明

10046事件是Oracle提供的內部事件，是對SQL_TRACE的增強.

10046事件可以設定以下四個級別:

1 - 啟用標準的SQL_TRACE功能,等價於SQL_TRACE。

4 - Level 1 加上繫結值(bind values)

8 - Level 1 + 等待事件跟蹤

12 - Level 1 + Level 4 + Level 8

類似SQL_TRACE方法，10046事件可以在全域性設定，也可以在session級設定。

對於10046事件的設定，涉及到了oracle的“診斷事件”的概念。

可以參考以下連結瞭解詳情。http://www.itpub.net/323537,1.html

1． 在全域性設定

在引數檔案中增加:

EVENT="10046 trace name context forever,level 12"

此設定對所有使用者的所有程式生效、包括後臺程式.

2． 對當前session設定

通過alter session的方式修改，需要alter session的系統許可權:

SQL> alter session set events '10046 trace name context forever';

Session altered.

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> alter session set events '10046 trace name context off';

Session altered.

3． 對其他使用者session設定

通過DBMS_SYSTEM.SET_EV系統包來實現:

SQL> desc DBMS_SYSTEM.SET_EV;

Parameter Type           Mode Default?

--------- -------------- ---- --------

SI        BINARY_INTEGER IN           

SE        BINARY_INTEGER IN            

EV        BINARY_INTEGER IN           

LE        BINARY_INTEGER IN           

NM        VARCHAR2       IN  

其中的引數SI、SE來自v$session檢視:

查詢獲得需要跟蹤的session資訊:

SQL> select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME

---------- ---------- ------------------------------

8 2041 SYS

9 437 EYGLE

執行跟蹤:

SQL> exec dbms_system.set_ev(9,437,10046,8,'eygle');

PL/SQL procedure successfully completed.

結束跟蹤:

SQL> exec dbms_system.set_ev(9,437,10046,0,'eygle');

PL/SQL procedure successfully completed.

(C)對啟用方法的一些總結。

因為trace的目標範圍不同，導致必須使用不同的方法。

?nbsp;        作用於資料庫全域性的，就改初始化引數。

?nbsp;        只作用於本session的，就用alter session 命令。

?nbsp;        作用於其它session的，就用DBMS_SYSTEM包。

再加上10046診斷事件，是SQL_TRACE的增強，又多了一套方法。

二、獲取跟蹤檔案

以上生成的跟蹤檔案位於“user_dump_dest”引數所指定的目錄中，位置及檔名可以通過以下SQL查詢獲得:

1.如果是查詢當前session的跟蹤檔案，使用如下查詢：

SELECT d.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') d

TRACE_FILE_NAME

-------------------------------------------------------------------------------

D:/ORACLE/PRODUCT/10.2.0/ADMIN/MYORACLE/UDUMP/hsjf_ora_1026.trc

2.如果是查詢其他使用者session的跟蹤檔案，則根據使用者的sid和#serial使用如下查詢：

SELECT d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name

from

    ( select p.spid from v$session s, v$process p

      where s.sid=’’ and s. SERIAL#='' 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') d

TRACE_FILE_NAME

-------------------------------------------------------------------------------

D:/ORACLE/PRODUCT/10.2.0/ADMIN/MYORACLE/UDUMP/hsjf_ora_1026.trc

三、格式化跟蹤檔案。

原始的跟蹤檔案是很難讀懂的。需要使用oracle自帶的tkprof命令列工具格式化一下。

SQL>$tkprof D:/ORACLE/PRODUCT/10.2.0/ADMIN/MYORACLE/UDUMP/hsjf_ora_1026.trc D:/ORACLE/PRODUCT/10.2.0/ADMIN/MYORACLE/UDUMP/hsjf_ora_1026.txt

這個就可以方便的閱讀了。可以在hsjf_ora_1026.txt檔案中看到所有的sql語句執行次數,CPU使用時間等資料。

備註：可以通過以下方法讀取當前已經設定的引數

對於全域性的SQL_TRACE引數的設定，可以通過show parameter命令獲得。

當我們通過alter session的方式設定了SQL_TRACE,這個設定是不能通過show parameter的方式得到的,我們需要通過dbms_system.read_ev來獲取：

SQL> set feedback off

SQL> set serveroutput on

SQL> declare

2 event_level number;

3 begin

4 for event_number in 10000..10999 loop

5 sys.dbms_system.read_ev(event_number, event_level);

6 if (event_level > 0) then

7 sys.dbms_output.put_line(

8 'Event ' ||

9 to_char(event_number) ||

10 ' is set at level ' ||

11 to_char(event_level)

12 );

13 end if;

14 end loop;

15 end;

16 /

Event 10046 is set at level 1

PS：如何看懂ORACLE的執行計劃

一、什麼是執行計劃

An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

二、如何訪問資料

At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:
Full Table Scan (FTS)    --全表掃描
Index Lookup (unique & non-unique)    --索引掃描（唯一和非唯一）
Rowid    --物理行id

三、執行計劃層次關係

When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing that is executed. --採用最右最上最先執行的原則看層次關係，在同一級如果某個動作沒有子ID就最先執行

1.看一個簡單的例子：

Query Plan
-----------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1234
**TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED] --[:Q65001]表示是並行方式，[ANALYZED]表示該物件已經分析過了

優化模式是CHOOSE的情況下，看Cost引數是否有值來決定採用CBO還是RBO：
SELECT STATEMENT [CHOOSE] Cost=1234 --Cost有值，採用CBO
SELECT STATEMENT [CHOOSE] Cost= --Cost為空，採用RBO

2.層次的父子關係，看比較複雜的例子：

PARENT1
**FIRST CHILD
****FIRST GRANDCHILD
**SECOND CHILD

Here the same principles apply, the FIRST GRANDCHILD is the initial operation then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT collates the output. 

四、例子解說

Execution Plan
----------------------------------------------------------
0 **SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=8 Bytes=248)
1 0 **HASH JOIN (Cost=3 Card=8 Bytes=248)
2 1 ****TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)
3 1 ****TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304) 

左側的兩排資料，前面的是序列號ID，後面的是對應的PID（父ID）。

A shortened summary of this is:
Execution starts with ID=0: SELECT STATEMENT but this is dependand on it's child objects
So it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on it's child objects
So it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF 'DEPT'
Then the second child step: ID=3 PID=2 TABLE ACCESS (FULL) OF 'EMP'
Rows are returned to the parent step(s) until finished 

五、表訪問方式

1.Full Table Scan (FTS) 全表掃描

In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk.   --全表掃描模式下會讀資料到表的高水位線（HWM即表示表曾經擴充套件的最後一個資料塊），讀取速度依賴於Oracle初始化引數db_block_multiblock_read_count

Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
**INDEX UNIQUE SCAN EMP_I1   --如果索引裡就找到了所要的資料，就不會再去訪問表了

2.Index Lookup 索引掃描

There are 5 methods of index lookup:

index unique scan   --索引唯一掃描
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. 
eg:
SQL> explain plan for select empno,ename from emp where empno=10;

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) .
eg:
SQL> explain plan for select mgr from emp where mgr = 5;

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. 
eg: 
SQL> explain plan for select empno,ename from big_emp order by empno,ename;

index fast full scan   --索引快速全域性掃描，不帶order by情況下常發生
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. 
eg: 
SQL> explain plan for select empno,ename from big_emp;

index skip scan   --索引跳躍掃描，where條件列是非索引的前導列情況下常發生
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.
eg:
SQL> create index i_emp on emp(empno, ename);
SQL> select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH';

3.Rowid 物理ID掃描

This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid掃描是最快的訪問資料方式

六、表連線方式

有三種連線方式：

1.Sort Merge Join (SMJ)    --由於sort是非常耗資源的，所以這種連線方式要避免

Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently. 

SQL> explain plan for
select /*+ ordered */ e.deptno,d.deptno
from emp e,dept d
where e.deptno = d.deptno
order by e.deptno,d.deptno;

Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost=17
**MERGE JOIN
****SORT JOIN
******TABLE ACCESS FULL EMP [ANALYZED]
****SORT JOIN
******TABLE ACCESS FULL DEPT [ANALYZED]

Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.

2.Nested Loops (NL)    --比較高效的一種連線方式

Fetches the first batch of rows from row source 1, Then we probe row source 2 once for each row returned from row source 1.
For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.

SQL> explain plan for
select a.dname,b.sql
from dept a,emp b
where a.deptno = b.deptno;

Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost=5
**NESTED LOOPS
****TABLE ACCESS FULL DEPT [ANALYZED]
****TABLE ACCESS FULL EMP [ANALYZED]

3.Hash Join    --最為高效的一種連線方式

New join type introduced in 7.3, More efficient in theory than NL & SMJ, Only accessible via the CBO. Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory.

SQL> explain plan for
select /*+ use_hash(emp) */ empno
from emp,dept
where emp.deptno = dept.deptno;

Query Plan
----------------------------
SELECT STATEMENT [CHOOSE] Cost=3
**HASH JOIN
****TABLE ACCESS FULL DEPT
****TABLE ACCESS FULL EMP

Hash joins are enabled by the parameter HASH_JOIN_ENABLED=TRUE in the init.ora or session. TRUE is the default in 7.3.

3.Cartesian Product    --卡迪爾積，不算真正的連線方式，sql肯定寫的有問題

A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data. Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out.
It can be useful in some circumstances - Star joins uses cartesian products.Notice that there is no join between the 2 tables:

SQL> explain plan for
select emp.deptno,dept,deptno
from emp,dept

Query Plan
------------------------------
SLECT STATEMENT [CHOOSE] Cost=5
**MERGE JOIN CARTESIAN
****TABLE ACCESS FULL DEPT
****SORT JOIN
******TABLE ACCESS FULL EMP

The CARTESIAN keyword indicate that we are doing a cartesian product. 

七、運算子

1.sort    --排序，很消耗資源

There are a number of different operations that promote sorts:
order by clauses
group by
sort merge join

2.filter    --過濾，如not in、min函式等容易產生

Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another, functions such as min may introduce filter steps into query plans.

3.view    --檢視，大都由內聯檢視產生

When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.
eg: 
SQL> explain plan for
select ename,tot
from emp,(select empno,sum(empno) tot from big_emp group by empno) tmp
where emp.empno = tmp.empno;

Query Plan
------------------------
SELECT STATEMENT [CHOOSE]
**HASH JOIN
**TABLE ACCESS FULL EMP [ANALYZED]
**VIEW
****SORT GROUP BY
******INDEX FULL SCAN BE_IX 

4.partition view     --分割槽檢視

Partition views are a legacy technology that were superceded by the partitioning option. This section of the article is provided as reference for such legacy systems.