使用SQL TRACE和TKPROF觀察SQL語句執行結果

SQL TRACE檔案存放於
show parameter user_dump下

檔名為_ora_.trc。

預設情況下，使用者SQL不會寫入TRACE檔案，可以在會話級別更改相關引數

ALTER SESSION SET SQL_TRACE=TRUE;

要查詢當前會話的程式ID：


如上例，當前會話的TRACE檔名就應為：user_dump_dest目錄下的OCP_ORCL_1711.trc。

還有一個更簡便的方法：

---

TKPROF是用來將TRACE檔案轉換成可以閱讀的格式：

SHELL$ tkprof ocp_ora_1711.trc mike.prf explain=user/password

讀取mike.prf即可。

---

觀察mike.prf的輸出：

********************************************************************************


select count(id) 
from
 bom




call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.67       1.63      72061      72093          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.68       1.64      72061      72095          0           1


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  (HK)


Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=72093 pr=72061 pw=0 time=1639929 us)
33011000   INDEX FAST FULL SCAN BOM_PK_ID (cr=72093 pr=72061 pw=0 time=84 us)(object id 52507)




Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   SORT (AGGREGATE)
33011000    INDEX   MODE: ANALYZED (FAST FULL SCAN) OF 'BOM_PK_ID' (INDEX 
               (UNIQUE))


********************************************************************************

Disk表示讀取的磁碟統計，Query表示讀取的記憶體統計。

以下是部分問題和解決措施的彙總：

The parsing numbers are high	The SHARED_POOL_SIZE may need to be increased
The disk reads are very high	Indexes are not being used or may not exist
The query and/or current (memory reads) are very high	Indexes may be on columns with low cardinality (columns where an individual value generally makes up a large percentage of the table; like a y/n field). Removing/suppressing the index or using histograms or a bitmap index may increase performance. A poor join order of tables or bad order in a concatenated index may also cause this.
The parse elapse time is high	There may be a problem with the number of open cursors
The number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows.	This could be a sign of an index with a poor distribution of distinct keys (unique values for a column). This could also be a sign of a poorly written statement.
The number of misses in the library cache during parse is greater than 1.	This indicates that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora file or do a better job of sharing SQL.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22621861/viewspace-1279733/，如需轉載，請註明出處，否則將追究法律責任。