Oracle學習筆記之SQL_TRACE和TKPROF使用

首先我們要登入到sqlplus上

SQL>sqlplus scott/tiger

為了方便尋找追蹤檔案我們在追蹤檔案後面加上尾碼'look_for_me',也可以不加，我們下面的例子也沒有加。

SQL>alter session set tracefile_identifier='look_for_me';

第一步查看追蹤檔案的路徑和檔案名稱

SQL>show parameter user_dump_dest;-- 追蹤檔案的路徑

SQL> select c.value || '/' || d.instance_name || '_ora_' ||  2         a.spid || '.trc' ||  3         case when e.value is not null then '_'||e.value end trace  4    from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e  5   where a.addr = b.paddr  6     and b.audsid = userenv('sessionid')  7     and c.name = 'user_dump_dest'  8     and e.name = 'tracefile_identifier'  9  / TRACE--------------------------------------------------------------------------------e:\app\user\diag\rdbms\orcl\orcl\trace/orcl_ora_5240.trc

在之前版本中，我們需要訪問以上幾張V視圖才可以得到某一個sid的trace file的絕對路徑。在11g中，Oracle為我們新增了一個視圖 V$DIAG_INFO。

SQL>select * from v$diag_info;

 INST_ID NAME                                                             VALUE---------- ---------------------------------------------------------------- -----------------------------------------------------------  1 Diag Enabled                                                     TRUE  1 ADR Base                                                         e:\app\user  1 ADR Home                                                         e:\app\user\diag\rdbms\orcl\orcl  1 Diag Trace                                                       e:\app\user\diag\rdbms\orcl\orcl\trace  1 Diag Alert                                                       e:\app\user\diag\rdbms\orcl\orcl\alert  1 Diag Incident                                                    e:\app\user\diag\rdbms\orcl\orcl\incident  1 Diag Cdump                                                       e:\app\user\diag\rdbms\orcl\orcl\cdump  1 Health Monitor                                                   e:\app\user\diag\rdbms\orcl\orcl\hm  1 Default Trace File                                               e:\app\user\diag\rdbms\orcl\orcl\trace\orcl_ora_5240.trc

V$DIAG_INFO 視圖列出了所有重要的ADR 位置：

(1) ADR Base：ADR 基目錄的路徑

(2) ADR Home：當前資料庫執行個體的ADR 主目錄的路徑

(3) Diag Trace：文本預警日誌和後台/前台進程追蹤檔案的位置

(4) Diag Alert：XML 版本的預警日誌的位置

(5) …

(6) Default Trace File：會話的追蹤檔案的路徑。SQL 追蹤檔案將寫入到這裡。也是我們需要的路徑。

至於以上各個位置的檔案的具體作用這裡我們不是重點，就不在描述。

通過查詢我們可以看到通過以上兩種方法我們查詢到的追蹤檔案是一樣的

 e:\app\user\diag\rdbms\orcl\orcl\trace\orcl_ora_5240.trc

第二步開啟SQL_TRACE

SQL>alter session set sql_trace=true;

第三步執行查詢

SQL>select * from emp,dept where emp.deptno=dept.deptno;

第四步關閉sql_trace

SQL>alter session set sql_trace=false;

然後退出sqlplus

第五步 切換到我們查詢到的追蹤檔案目錄 執行

E:\app\User\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_5240.trc 5240.txt 

這時我們開啟2708.txt 可以看到輸出

select * from  emp,dept where emp.deptno=dept.deptnocall     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2      0.00       0.00          0         11          0          14------- ------  -------- ---------- ---------- ---------- ----------  ----------total        4      0.00       0.00          0         11          0          14

   CALL：每次SQL語句的處理都分成三個部分

        Parse：這步將SQL語句轉換成執行計畫，包括檢查是否有正確的授權和所需要用到的表、列以及其他引用到的對象是否存在。

        Execute：這步是真正的由Oracle來執行語句。對於insert、update、delete操作，這步會修改資料，對於select操作，這步就只是確定選擇的記錄。

        Fetch：返回查詢語句中所獲得的記錄，這步只有select語句會被執行。

   COUNT:這個語句被parse、execute、fetch的次數。

   CPU：這個語句對於所有的parse、execute、fetch所消耗的cpu的時間，以秒為單位。

   ELAPSED：這個語句所有消耗在parse、execute、fetch的總的時間。

   DISK：從磁碟上的資料檔案中物理讀取的塊的數量。一般來說更想知道的是正在從緩衝中讀取的資料而不是從磁碟上讀取的資料。

   QUERY：在一致性讀模式下，所有parse、execute、fetch所獲得的buffer的數量。一致性模式的buffer是用於給一個長時間啟動並執行事務提供一個一致性讀的快照，緩衝實際上在頭部儲存了狀態。

   CURRENT：在current模式下所獲得的buffer的數量。一般在current模式下執行insert、update、delete操作都會擷取buffer。在current模式下如果在快取區發現有新的緩衝足夠給當前的事務使用，則這些buffer都會被讀入了緩衝區中。

    ROWS: 所有SQL語句返回的記錄數目，但是不包括子查詢中返回的記錄數目。對於select語句，返回記錄是在fetch這步，對於insert、update、delete操作，返回記錄則是在execute這步。

 

使用TKPROF格式化的檔案很容易看出需要調整的SQL語句。尋找的依據是：

*耗費大量的CPU資源

*花費較長的時間進行SQL分析、執行和擷取

*從磁碟讀大量的資料區塊，而從SGA區讀的資料很少

*訪問大量的資料區塊但只返回一點資料

一旦找到這些語句就可以用執行計畫工具來進一步決定為什麼這些語句效能這麼低了。