怎么進(jìn)行Oracle 執(zhí)行計(jì)劃的說(shuō)明

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)?lái)有關(guān)怎么進(jìn)行Oracle 執(zhí)行計(jì)劃的說(shuō)明，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

如果要分析某條SQL的性能問(wèn)題，通常我們要先看SQL的執(zhí)行計(jì)劃，看看SQL的每一步執(zhí)行是否存在問(wèn)題。 如果一條SQL平時(shí)執(zhí)行的好好的，卻有一天突然性能很差，如果排除了系統(tǒng)資源和阻塞的原因，那么基本可以斷定是執(zhí)行計(jì)劃出了問(wèn)題。

       看懂執(zhí)行計(jì)劃也就成了SQL優(yōu)化的先決條件。 這里的SQL優(yōu)化指的是SQL性能問(wèn)題的定位，定位后就可以解決問(wèn)題。

一．         查看執(zhí)行計(jì)劃的三種方法

1.1 設(shè)置autotrace

SQL> set autotrace on

SQL> select * from dave;

        ID NAME

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

         8 安慶

         1 dave

         2 bl

         1 bl

         2 dave

         3 dba

         4 sf-express

         5 dmm

已選擇8行。

執(zhí)行計(jì)劃

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

Plan hash value: 3458767806

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     8 |    64 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DAVE |     8 |    64 |     2   (0)| 00:00:01 |

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

統(tǒng)計(jì)信息

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

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        609  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          8  rows processed

SQL>

1.2 使用SQL

SQL>EXPLAIN PLAN FOR sql語(yǔ)句;

SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

示例：

SQL> EXPLAIN PLAN FOR SELECT * FROM DAVE;

已解釋。

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

或者：

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3458767806

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     8 |    64 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DAVE |     8 |    64 |     2   (0)| 00:00:01 |

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

已選擇8行。

執(zhí)行計(jì)劃

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

Plan hash value: 2137789089

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

| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |

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

統(tǒng)計(jì)信息

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

         25  recursive calls

         12  db block gets

        168  consistent gets

          0  physical reads

          0  redo size

        974  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          8  rows processed

SQL>

1.3 使用Toad,PL/SQL Developer工具

二．         Cardinality（基數(shù)）/ rows

Cardinality值表示CBO預(yù)期從一個(gè)行源（row source）返回的記錄數(shù)，這個(gè)行源可能是一個(gè)表，一個(gè)索引，也可能是一個(gè)子查詢。   在Oracle 9i中的執(zhí)行計(jì)劃中，Cardinality縮寫(xiě)成Card。 在10g中，Card值被rows替換。

這是9i的一個(gè)執(zhí)行計(jì)劃，我們可以看到關(guān)鍵字Card：

       執(zhí)行計(jì)劃

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=402)

   1    0   TABLE ACCESS (FULL) OF 'TBILLLOG8' (Cost=2 Card=1 Bytes=402)

Oracle 10g的執(zhí)行計(jì)劃，關(guān)鍵字換成了rows：

執(zhí)行計(jì)劃

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

Plan hash value: 2137789089

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

| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |

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

Cardinality的值對(duì)于CBO做出正確的執(zhí)行計(jì)劃來(lái)說(shuō)至關(guān)重要。 如果CBO獲得的Cardinality值不夠準(zhǔn)確（通常是沒(méi)有做分析或者分析數(shù)據(jù)過(guò)舊造成），在執(zhí)行計(jì)劃成本計(jì)算上就會(huì)出現(xiàn)偏差，從而導(dǎo)致CBO錯(cuò)誤的制定出執(zhí)行計(jì)劃。

       在多表關(guān)聯(lián)查詢或者SQL中有子查詢時(shí)，每個(gè)關(guān)聯(lián)表或子查詢的Cardinality的值對(duì)主查詢的影響都非常大，甚至可以說(shuō)，CBO就是依賴于各個(gè)關(guān)聯(lián)表或者子查詢Cardinality值計(jì)算出最后的執(zhí)行計(jì)劃。

       對(duì)于多表查詢，CBO使用每個(gè)關(guān)聯(lián)表返回的行數(shù)（Cardinality）決定用什么樣的訪問(wèn)方式來(lái)做表關(guān)聯(lián)（如Nested loops Join 或 hash Join）。

          多表連接的三種方式詳解 HASH JOIN MERGE JOIN NESTED LOOP

              http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx

對(duì)于子查詢，它的Cardinality將決定子查詢是使用索引還是使用全表掃描的方式訪問(wèn)數(shù)據(jù)。

三． SQL 的執(zhí)行計(jì)劃

       生成SQL的執(zhí)行計(jì)劃是Oracle在對(duì)SQL做硬解析時(shí)的一個(gè)非常重要的步驟，它制定出一個(gè)方案告訴Oracle在執(zhí)行這條SQL時(shí)以什么樣的方式訪問(wèn)數(shù)據(jù)：索引還是全表掃描，是Hash Join還是Nested loops Join等。 比如說(shuō)某條SQL通過(guò)使用索引的方式訪問(wèn)數(shù)據(jù)是最節(jié)省資源的，結(jié)果CBO作出的執(zhí)行計(jì)劃是全表掃描，那么這條SQL的性能必然是比較差的。

       Oracle SQL的硬解析和軟解析

       http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

示例：

SQL> SET AUTOTRACE TRACEONLY;  -- 只顯示執(zhí)行計(jì)劃，不顯示結(jié)果集

SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr;

已選擇13行。

執(zhí)行計(jì)劃

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

Plan hash value: 992080948

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

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |        |    13 |   988 |     6  (17)| 00:00:01 |

|   1 |  MERGE JOIN                  |        |    13 |   988 |     6  (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                  |        |    13 |   494 |     4  (25)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL         | EMP    |    13 |   494 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   4 - access("A"."EMPNO"="B"."MGR")

       filter("A"."EMPNO"="B"."MGR")

   5 - filter("B"."MGR" IS NOT NULL)

統(tǒng)計(jì)信息

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

          0  recursive calls

          0  db block gets

         11  consistent gets

          0  physical reads

          0  redo size

       2091  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         13  rows processed

SQL>

圖片是Toad工具查看的執(zhí)行計(jì)劃。 在Toad 里面，很清楚的顯示了執(zhí)行的順序。 但是如果在SQLPLUS里面就不是那么直接。 但我們也可以判斷：一般按縮進(jìn)長(zhǎng)度來(lái)判斷，縮進(jìn)最大的最先執(zhí)行，如果有2行縮進(jìn)一樣，那么就先執(zhí)行上面的。

3.1 執(zhí)行計(jì)劃中字段解釋：

       ID: 一個(gè)序號(hào)，但不是執(zhí)行的先后順序。執(zhí)行的先后根據(jù)縮進(jìn)來(lái)判斷。

       Operation： 當(dāng)前操作的內(nèi)容。

       Rows： 當(dāng)前操作的Cardinality，Oracle估計(jì)當(dāng)前操作的返回結(jié)果集。

       Cost（CPU）：Oracle 計(jì)算出來(lái)的一個(gè)數(shù)值（代價(jià)），用于說(shuō)明SQL執(zhí)行的代價(jià)。

       Time：Oracle 估計(jì)當(dāng)前操作的時(shí)間。

3.2 謂詞說(shuō)明：

Predicate Information (identified by operation id):

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

   4 - access("A"."EMPNO"="B"."MGR")

       filter("A"."EMPNO"="B"."MGR")

   5 - filter("B"."MGR" IS NOT NULL)

       Access: 表示這個(gè)謂詞條件的值將會(huì)影響數(shù)據(jù)的訪問(wèn)路勁（表還是索引）。

       Filter：表示謂詞條件的值不會(huì)影響數(shù)據(jù)的訪問(wèn)路勁，只起過(guò)濾的作用。

在謂詞中主要注意access，要考慮謂詞的條件，使用的訪問(wèn)路徑是否正確。

3.3 統(tǒng)計(jì)信息說(shuō)明：

db block gets ： 從buffer cache中讀取的block的數(shù)量    

consistent gets： 從buffer cache中讀取的undo數(shù)據(jù)的block的數(shù)量    

physical reads： 從磁盤(pán)讀取的block的數(shù)量    

redo size： DML生成的redo的大小    

sorts (memory) ：在內(nèi)存執(zhí)行的排序量    

sorts (disk) ：在磁盤(pán)上執(zhí)行的排序量    

       Physical Reads通常是我們最關(guān)心的，如果這個(gè)值很高，說(shuō)明要從磁盤(pán)請(qǐng)求大量的數(shù)據(jù)到Buffer Cache里，通常意味著系統(tǒng)里存在大量全表掃描的SQL語(yǔ)句，這會(huì)影響到數(shù)據(jù)庫(kù)的性能，因此盡量避免語(yǔ)句做全表掃描，對(duì)于全表掃描的SQL語(yǔ)句，建議增 加相關(guān)的索引，優(yōu)化SQL語(yǔ)句來(lái)解決。

關(guān)于physical reads ，db block gets 和consistent gets這三個(gè)參數(shù)之間有一個(gè)換算公式：

       數(shù)據(jù)緩沖區(qū)的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。

用以下語(yǔ)句可以查看數(shù)據(jù)緩沖區(qū)的命中率：

       SQL>SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');

       查詢出來(lái)的結(jié)果Buffer Cache的命中率應(yīng)該在90％以上，否則需要增加數(shù)據(jù)緩沖區(qū)的大小。

Recursive Calls：  Number of recursive calls generated at both the user and system level.    

Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls。

DB Block Gets： Number of times a CURRENT block was requested.

Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them. (DB Block Gets:請(qǐng)求的數(shù)據(jù)塊在buffer能滿足的個(gè)數(shù))
       當(dāng)前模式塊意思就是在操作中正好提取的塊數(shù)目，而不是在一致性讀的情況下而產(chǎn)生的塊數(shù)。正常的情況下，一個(gè)查詢提取的塊是在查詢開(kāi)始的那個(gè)時(shí)間點(diǎn)上存在的數(shù)據(jù)塊，當(dāng)前塊是在這個(gè)時(shí)刻存在的數(shù)據(jù)塊，而不是在這個(gè)時(shí)間點(diǎn)之前或者之后的數(shù)據(jù)塊數(shù)目。

Consistent Gets： Number of times a consistent read was requested for a block.

This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example. Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification. (Consistent Gets: 數(shù)據(jù)請(qǐng)求總數(shù)在回滾段Buffer中的數(shù)據(jù)一致性讀所需要的數(shù)據(jù)塊)
       這里的概念是在處理你這個(gè)操作的時(shí)候需要在一致性讀狀態(tài)上處理多少個(gè)塊，這些塊產(chǎn)生的主要原因是因?yàn)橛捎谠谀悴樵兊倪^(guò)程中，由于其他會(huì)話對(duì)數(shù)據(jù)塊進(jìn)行操 作，而對(duì)所要查詢的塊有了修改，但是由于我們的查詢是在這些修改之前調(diào)用的，所以需要對(duì)回滾段中的數(shù)據(jù)塊的前映像進(jìn)行查詢，以保證數(shù)據(jù)的一致性。這樣就產(chǎn) 生了一致性讀。

Physical Reads： Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache. (Physical Reads:實(shí)例啟動(dòng)后，從磁盤(pán)讀到Buffer Cache數(shù)據(jù)塊數(shù)量)

就是從磁盤(pán)上讀取數(shù)據(jù)塊的數(shù)量，其產(chǎn)生的主要原因是：
       （1） 在數(shù)據(jù)庫(kù)高速緩存中不存在這些塊
       （2） 全表掃描
       （3） 磁盤(pán)排序
它們?nèi)咧g的關(guān)系大致可概括為：
       邏輯讀指的是Oracle從內(nèi)存讀到的數(shù)據(jù)塊數(shù)量。一般來(lái)說(shuō)是'consistent gets' + 'db block gets'。當(dāng)在內(nèi)存中找不到所需的數(shù)據(jù)塊的話就需要從磁盤(pán)中獲取，于是就產(chǎn)生了'physical reads'。

Sorts(disk):

    Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.

bytes sent via SQL*Net to client:
    Total number of bytes sent to the client from the foreground processes.

bytes received via SQL*Net from client:
    Total number of bytes received from the client over Oracle Net.

SQL*Net roundtrips to/from client:
    Total number of Oracle Net messages sent to and received from the client.

更多內(nèi)容參考Oracle聯(lián)機(jī)文檔：

       Statistics Descriptions

       http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/stats002.htm#i375475

3.4 動(dòng)態(tài)分析

       如果在執(zhí)行計(jì)劃中有如下提示：

              Note

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

                     -dynamic sampling used for the statement

       這提示用戶CBO當(dāng)前使用的技術(shù)，需要用戶在分析計(jì)劃時(shí)考慮到這些因素。 當(dāng)出現(xiàn)這個(gè)提示，說(shuō)明當(dāng)前表使用了動(dòng)態(tài)采樣。 我們從而推斷這個(gè)表可能沒(méi)有做過(guò)分析。

這里會(huì)出現(xiàn)兩種情況：

（1）       如果表沒(méi)有做過(guò)分析，那么CBO可以通過(guò)動(dòng)態(tài)采樣的方式來(lái)獲取分析數(shù)據(jù)，也可以或者正確的執(zhí)行計(jì)劃。

（2）       如果表分析過(guò)，但是分析信息過(guò)舊，這時(shí)CBO就不會(huì)在使用動(dòng)態(tài)采樣，而是使用這些舊的分析數(shù)據(jù)，從而可能導(dǎo)致錯(cuò)誤的執(zhí)行計(jì)劃。

總結(jié)：

       在看執(zhí)行計(jì)劃的時(shí)候，除了看執(zhí)行計(jì)劃本身，還需要看謂詞和提示信息。 通過(guò)整體信息來(lái)判斷SQL 效率。

第二章相關(guān)文章：

 一、什么是執(zhí)行計(jì)劃（explain plan） 

執(zhí)行計(jì)劃：一條查詢語(yǔ)句在ORACLE中的執(zhí)行過(guò)程或訪問(wèn)路徑的描述。 

二、如何查看執(zhí)行計(jì)劃 

1: 在PL/SQL下按F5查看執(zhí)行計(jì)劃。第三方工具toad等。 

很多人以為PL/SQL的執(zhí)行計(jì)劃只能看到基數(shù)、優(yōu)化器、耗費(fèi)等基本信息，其實(shí)這個(gè)可以在PL/SQL工具里面設(shè)置的?？梢钥吹胶芏嗥渌畔ⅲ缦滤?/p>

2: 在SQL*PLUS(PL/SQL的命令窗口和SQL窗口均可)下執(zhí)行下面步驟 

復(fù)制代碼 代碼如下:

SQL>EXPLAIN PLAN FOR 
SELECT * FROM SCOTT.EMP; --要解析的SQL腳本 
SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

3: 在SQL*PLUS下(有些命令在PL/SQL下無(wú)效)執(zhí)行如下命令： 

復(fù)制代碼 代碼如下:

SQL>SET TIMING ON 　　　　　　　　　　　　--控制顯示執(zhí)行時(shí)間統(tǒng)計(jì)數(shù)據(jù) 
SQL>SET AUTOTRACE ON EXPLAIN 　　　 　　--這樣設(shè)置包含執(zhí)行計(jì)劃、腳本數(shù)據(jù)輸出，沒(méi)有統(tǒng)計(jì)信息 
SQL>執(zhí)行需要查看執(zhí)行計(jì)劃的SQL語(yǔ)句 
SQL>SET AUTOTRACE OFF 　　　　　　　　　 --不生成AUTOTRACE報(bào)告，這是缺省模式 
SQL> SET AUTOTRACE ON 　　　　　　　　　 --這樣設(shè)置包含執(zhí)行計(jì)劃、統(tǒng)計(jì)信息、以及腳本數(shù)據(jù)輸出 
SQL>執(zhí)行需要查看執(zhí)行計(jì)劃的SQL語(yǔ)句 
SQL>SET AUTOTRACE OFF 
SQL> SET AUTOTRACE TRACEONLY 　　　　　--這樣設(shè)置會(huì)有執(zhí)行計(jì)劃、統(tǒng)計(jì)信息，不會(huì)有腳本數(shù)據(jù)輸出 
SQL>執(zhí)行需要查看執(zhí)行計(jì)劃的SQL語(yǔ)句 
SQL>SET AUTOTRACE TRACEONLY STAT --這樣設(shè)置只包含有統(tǒng)計(jì)信息 
SQL>執(zhí)行需要查看執(zhí)行計(jì)劃的SQL語(yǔ)句

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í)行此命令會(huì)報(bào)錯(cuò)

SQL> SET AUTOTRACE ON;

Cannot SET AUTOTRACE

4：SQL_TRACE可以作為參數(shù)在全局啟用，也可以通過(guò)命令形式在具體SESSION啟用

4.1 在全局啟用，在參數(shù)文件（pfile/spfile)中指定SQL_TRACE =true，在全局啟用SQL_TRACE時(shí)會(huì)導(dǎo)致所有進(jìn)程活動(dòng)被跟蹤，包括后臺(tái)進(jìn)程以及用戶進(jìn)程，通常會(huì)導(dǎo)致比較嚴(yán)重的性能問(wèn)題，所以在生產(chǎn)環(huán)境要謹(jǐn)慎使用。

提示：通過(guò)在全局啟用SQL_TRACE， 我們可以跟蹤到所有后臺(tái)進(jìn)程的活動(dòng)，很多在文檔中的抽象說(shuō)明，通過(guò)跟蹤文件的實(shí)時(shí)變化，我們可以清晰的看到各個(gè)進(jìn)程間的緊密協(xié)調(diào)。

4.2在當(dāng)前SESSION級(jí)別設(shè)置，通過(guò)跟蹤當(dāng)前進(jìn)程可以發(fā)現(xiàn)當(dāng)前操作的后臺(tái)數(shù)據(jù)庫(kù)遞歸活動(dòng)（這在研究數(shù)據(jù)庫(kù)新特性時(shí)尤其有效），研究SQL執(zhí)行時(shí)，發(fā)現(xiàn)后臺(tái)

錯(cuò)誤等。

復(fù)制代碼 代碼如下:

SQL> ALTER SESSION SET SQL_TRACE=TRUE; 
SQL> SELECT * FROM SCOTT.EMP; 
SQL> ALTER SESSION SET SQL_TRACE =FALSE;

那么此時(shí)如何查看相關(guān)信息？不管你在SQL*PLUS抑或PL/SQL DEVELOPER工具里面執(zhí)行上面腳本過(guò)后都看不到什么信息，你可以通過(guò)下面腳本查詢到trace日志信息

復(fù)制代碼 代碼如下:

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的幫助信息如下

復(fù)制代碼 代碼如下:

TKPROF 選項(xiàng) 
選項(xiàng) 說(shuō)明 
TRACEFILE 跟蹤輸出文件的名稱 
OUTPUTFILE 已設(shè)置格式的文件的名稱 
SORT=option 語(yǔ)句的排序順序 
PRINT=n 打印前 n 個(gè)語(yǔ)句 
EXPLAIN=user/password 以指定的用戶名運(yùn)行 EXPLAIN PLAN 
INSERT=filename 生成 INSERT 語(yǔ)句 
SYS=NO 忽略作為用戶 sys 運(yùn)行的遞歸 SQL 語(yǔ)句 
AGGREGATE=[Y|N] 如果指定 AGGREGATE = NO TKPROF 不聚集相同 
SQL 文本的多個(gè)用戶 
RECORD=filename 記錄在跟蹤文件中發(fā)現(xiàn)的語(yǔ)句 
TABLE=schema.tablename 將執(zhí)行計(jì)劃放入指定的表而不是缺省的PLAN_TABLE 

可以在操作系統(tǒng)中鍵入 tkprof 以獲得所有可用選項(xiàng)和輸出的列表 
注 排序選項(xiàng)有 

排序 選項(xiàng)說(shuō)明 
prscnt execnt fchcnt 調(diào)用分析執(zhí)行提取的次數(shù) 
prscpu execpu fchcpu 分析執(zhí)行提取所占用的 CPU 時(shí)間 
prsela exela fchela 分析執(zhí)行提取所占用的時(shí)間 
prsdsk exedsk fchdsk 分析執(zhí)行提取期間的磁盤(pán)讀取次數(shù) 
prsqry exeqry fchqry 分析執(zhí)行提取期間用于持續(xù)讀取的緩沖區(qū)數(shù) 
prscu execu fchcu 分析執(zhí)行提取期間用于當(dāng)前讀取的緩沖區(qū)數(shù) 
prsmis exemis 分析執(zhí)行期間庫(kù)高速緩存未命中的次數(shù) 
exerow fchrow 分析執(zhí)行期間處理的行數(shù) 
userid 分析游標(biāo)的用戶的用戶 ID 

TKPROF 統(tǒng)計(jì)數(shù)據(jù) 
Count: 執(zhí)行調(diào)用數(shù) 
CPU: CPU 的使用秒數(shù) 
Elapsed: 總共用去的時(shí)間 
Disk: 物理讀取次數(shù) 
Query: 持續(xù)讀取的邏輯讀取數(shù) 
Current: 當(dāng)前模式下的邏輯讀取數(shù) 
Rows: 已處理行數(shù) 
TKPROF 統(tǒng)計(jì)信息 
統(tǒng)計(jì) 含義 
Count 分析或執(zhí)行語(yǔ)句的次數(shù)以及為語(yǔ)句發(fā)出的提取調(diào)用數(shù) 
CPU 每個(gè)階段的處理時(shí)間以秒為單位如果在共享池中找到該語(yǔ)句對(duì)于分析階段為 0 
Elapsed 占用時(shí)間以秒為單位通常不是非常有用因?yàn)槠渌M(jìn)程影響占用時(shí)間 
Disk 從數(shù)據(jù)庫(kù)文件讀取的物理數(shù)據(jù)塊如果該數(shù)據(jù)被緩沖則該統(tǒng)計(jì)可能很低 
Query 為持續(xù)讀取檢索的邏輯緩沖區(qū)通常用于 SELECT 語(yǔ)句 
Current 在當(dāng)前模式下檢索的邏輯緩沖區(qū)通常用于 DML 語(yǔ)句 
Rows 外部語(yǔ)句所處理的行對(duì)于 SELECT 語(yǔ)句在提取階段顯示它對(duì)于 DML 語(yǔ)句在執(zhí)行階段顯示它 

Query 和Current 的總和為所訪問(wèn)的邏輯緩沖區(qū)的總數(shù)

執(zhí)行下面命令：tkprof D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc h:\out.txtoutputfile explain=etl/etl

執(zhí)行上面命令后，可以查看生成的文本文件

復(fù)制代碼 代碼如下:

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跟蹤其它用戶的進(jìn)程，在很多時(shí)候我們需要跟蹤其它用戶的進(jìn)程，而不是當(dāng)前用戶，可以通過(guò)ORACLE提供的系統(tǒng)包 
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION來(lái)完成。 
例如：

復(fù)制代碼 代碼如下:

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事件

復(fù)制代碼 代碼如下:

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 
查詢結(jié)果為wgods_ora_28279.trc文件， 但是去相應(yīng)目錄卻沒(méi)有找到對(duì)應(yīng)的追蹤文件，而是如下trace文件：wgods_ora_28279_10046.trc

6 利用10053事件 

有點(diǎn)類(lèi)似10046，在此略過(guò)、 

7 系統(tǒng)視圖 

通過(guò)下面一些系統(tǒng)視圖，你可以看到一些零散的執(zhí)行計(jì)劃的相關(guān)信息，有興趣的話可以多去研究一下。

復(fù)制代碼 代碼如下:

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í)行計(jì)劃 

1.執(zhí)行順序 

執(zhí)行順序的原則是：由上至下，從右向左 

由上至下：在執(zhí)行計(jì)劃中一般含有多個(gè)節(jié)點(diǎn)，相同級(jí)別(或并列)的節(jié)點(diǎn)，靠上的優(yōu)先執(zhí)行，靠下的后執(zhí)行 

從右向左：在某個(gè)節(jié)點(diǎn)下還存在多個(gè)子節(jié)點(diǎn)，先從最靠右的子節(jié)點(diǎn)開(kāi)始執(zhí)行。 

當(dāng)然，你在PL/SQL工具中也可以通過(guò)它提供的功能來(lái)查看執(zhí)行順序。如下圖所示：

2.執(zhí)行計(jì)劃中字段解釋

SQL> 
名詞解釋： 
recursive calls 　　　　　　　　　　遞歸調(diào)用 
db block gets 　　　　　　　　　　從buffer cache中讀取的block的數(shù)量當(dāng)前請(qǐng)求的塊數(shù)目，當(dāng)前模式塊意思就是在操作中正好提取的塊數(shù)目，而不是在一致性讀的情況下而產(chǎn)生的正常情況下，一個(gè)查詢提取的塊是在查詢查詢開(kāi)始的那個(gè)時(shí)間點(diǎn)上存在的數(shù)據(jù)庫(kù)，當(dāng)前塊是在這個(gè)時(shí)候存在數(shù)據(jù)塊，而不是這個(gè)時(shí)間點(diǎn)之前或者之后的的數(shù)據(jù)塊數(shù)目。 
consistent gets 　　　　　　　　 從buffer cache中讀取的undo數(shù)據(jù)的block的數(shù)量數(shù)據(jù)請(qǐng)求總數(shù)在回滾段Buffer中的數(shù)據(jù)一致性讀所需要的數(shù)據(jù)塊，，這里的概念是在你處理你這個(gè)操作的時(shí)侯需要在一致性讀狀態(tài)上處理多個(gè)塊，這些塊產(chǎn)生的主要原因是因?yàn)槟阍诓樵冞^(guò)程中，由于其它會(huì)話對(duì)數(shù)據(jù) 塊進(jìn)行操作，而對(duì)所要查詢的塊有了修改，但是由于我們的查詢是在這些修改之前調(diào)用的，所要需要對(duì)回滾 段中的數(shù)據(jù)塊的前映像進(jìn)行查詢，以保證數(shù)據(jù)的一致性。這樣就產(chǎn)生了一致性讀。 

physical reads 　　　　　　　　　　物理讀 就是從磁盤(pán)上讀取數(shù)據(jù)塊的數(shù)量。其產(chǎn)生的主要原因是： 
　　　　　　　　　　　　　　　　　　1：在數(shù)據(jù)庫(kù)高速緩存中不存在這些塊。 
　　　　　　　　　　　　　　　　　　2：全表掃描 
　　　　　　　　　　　　　　　　　　3：磁盤(pán)排序 
redo size 　　　　　　　　 　　　　DML生成的redo的大小 
sorts (memory) 　　　　　　　　　　在內(nèi)存執(zhí)行的排序量 
sorts (disk) 　　　　　　　　　　 在磁盤(pán)執(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

db block gets 、 consistent gets 、 physical reads這三者的關(guān)系可以概括為：邏輯讀指的是ORACLE從內(nèi)存讀到的數(shù)據(jù)塊塊數(shù)量，一般來(lái)說(shuō)是： 
consistent gets + db block gets. 當(dāng)在內(nèi)存中找不到所需要的數(shù)據(jù)塊的話，就需要從磁盤(pán)中獲取，于是就產(chǎn)生了物理讀。 
3.具體內(nèi)容查看 
1> Plan hash Value 
這一行是這一條語(yǔ)句的的hash值，我們知道ORACLE對(duì)每一條ORACLE語(yǔ)句產(chǎn)生的執(zhí)行計(jì)劃放在SHARE POOL里面，第一次要經(jīng)過(guò)硬解析，產(chǎn)生hash值。下次再執(zhí)行時(shí)比較hash值，如果相同就不會(huì)執(zhí)行硬解析。 
2> COST 

COST沒(méi)有單位，是一個(gè)相對(duì)值，是SQL以CBO方式解析執(zhí)行計(jì)劃時(shí)，供ORACLE來(lái)評(píng)估CBO成本，選擇執(zhí)行計(jì)劃用的。沒(méi)有明確的含義，但是在對(duì)比是就非常有用。 
公式：COST=（Single Block I/O COST + MultiBlock I/O Cost + CPU Cost）/ Sreadtim 

3> 對(duì)上面執(zhí)行計(jì)劃列字段的解釋： 
Id: 執(zhí)行序列，但不是執(zhí)行的先后順序。執(zhí)行的先后根據(jù)Operation縮進(jìn)來(lái)判斷（采用最右最上最先執(zhí)行的原則看層次關(guān)系，在同一級(jí)如果某個(gè)動(dòng)作沒(méi)有子ID就最先執(zhí)行。一般按縮進(jìn)長(zhǎng)度來(lái)判斷，縮進(jìn)最大的最先執(zhí)行，如果有2行縮進(jìn)一樣，那么就先執(zhí)行上面的。） 
　　　　Operation：當(dāng)前操作的內(nèi)容。 
　　　　Name：操作對(duì)象 
　　　　Rows：也就是10g版本以前的Cardinality(基數(shù))，Oracle估計(jì)當(dāng)前操作的返回結(jié)果集行數(shù)。 
　　　　Bytes：表示執(zhí)行該步驟后返回的字節(jié)數(shù)。 
　　　　Cost（CPU）：表示執(zhí)行到該步驟的一個(gè)執(zhí)行成本，用于說(shuō)明SQL執(zhí)行的代價(jià)。 
　　　　Time：Oracle 估計(jì)當(dāng)前操作的時(shí)間。 
4.謂詞說(shuō)明： 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
2 - filter("B"."MGR" IS NOT NULL) 
4 - access("A"."EMPNO" = "B"."MGR") 
　　　　Access: 表示這個(gè)謂詞條件的值將會(huì)影響數(shù)據(jù)的訪問(wèn)路勁（全表掃描還是索引）。 
　　　　Filter：表示謂詞條件的值不會(huì)影響數(shù)據(jù)的訪問(wèn)路勁，只起過(guò)濾的作用。 
　　　　在謂詞中主要注意access，要考慮謂詞的條件，使用的訪問(wèn)路徑是否正確。 
5、 動(dòng)態(tài)分析 
如果在執(zhí)行計(jì)劃中有如下提示： 
Note 
------------ 
-dynamic sampling used for the statement 
這提示用戶CBO當(dāng)前使用的技術(shù)，需要用戶在分析計(jì)劃時(shí)考慮到這些因素。 當(dāng)出現(xiàn)這個(gè)提示，說(shuō)明當(dāng)前表使用了動(dòng)態(tài)采樣。我們從而推斷這個(gè)表可能沒(méi)有做過(guò)分析。 
這里會(huì)出現(xiàn)兩種情況： 
（1） 如果表沒(méi)有做過(guò)分析，那么CBO可以通過(guò)動(dòng)態(tài)采樣的方式來(lái)獲取分析數(shù)據(jù)，也可以或者正確的執(zhí)行計(jì)劃。 
（2） 如果表分析過(guò)，但是分析信息過(guò)舊，這時(shí)CBO就不會(huì)在使用動(dòng)態(tài)采樣，而是使用這些舊的分析數(shù)據(jù)，從而可能導(dǎo)致錯(cuò)誤的執(zhí)行計(jì)劃。 

四、表訪問(wèn)方式 

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

2.Index Lookup 索引掃描 
There are 5 methods of index lookup: 
index unique scan --索引唯一掃描 
通過(guò)唯一索引查找一個(gè)數(shù)值經(jīng)常返回單個(gè)ROWID，如果存在UNIQUE或PRIMARY KEY約束（它保證了語(yǔ)句只存取單行的話），ORACLE 
經(jīng)常實(shí)現(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) . 
使用一個(gè)索引存取多行數(shù)據(jù)，在唯一索引上使用索引范圍掃描的典型情況是在謂詞(WHERE 限制條件)中使用了范圍操作符號(hào)（如>, < <>, >=, <=,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掃描是最快的訪問(wèn)數(shù)據(jù)方式

上述就是小編為大家分享的怎么進(jìn)行Oracle 執(zhí)行計(jì)劃的說(shuō)明了，如果剛好有類(lèi)似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道。