ORACLE TKPROF 使用小結

轉載自:http://icexiebin.itpub.net/post/28652/266165

1.chechk init.ora
timed_statistics=true
TOP查出最耗資源的PID

2.SQL> select s.sid,s.serial# from v$session s,v$process p
where s.paddr=p.addr and p.spid='17397';

3.如果需要在session級別上設定trace，可以在sqlplus中使用下列語句：
SQL> alter session set sql_trace=true;
或者SQL> execute dbms_session.set_sql_trace(TRUE);
會話已更改。

4.如果要在PL/SQL中對session級別上設定trace，可以使用dbms_session這個包：
SQL>exec dbms_system.set_sql_trace_in_session(sid，serial#,true);
PL/SQL 過程已成功完成。

5.在user_dump_dest下找到該trc檔案,檔案最大容量由 max_dump_file_size決定

6.使用tkprof生成相關檔案,tkprof放在$ORACLE_HOME/bin目錄下，如無法執行請檢查環境變數和PATH
tkprof erptest_ora_27576.trc session.txt explain=system/manager aggregate=yes sys=no waits=yes sort=fchela
相關說明:
sys=no：表示阻止所有以sys使用者執行的sql被顯示出來，預設為YES
aggregate=yes|no 若使用者指定AGGREGATE=NO，TKPROF將不會對相同SQL文字的多個使用者進行彙總
waits=yes|no Record summary for any wait events found in the trace file.

CALL： 每次SQL語句的處理都分成三個部分（Parse，Execute,Fetch）
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產生出來的檔案示例:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 7 0.10 0.14 0 0 0 0
Fetch 12 0.00 0.06 5 38 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.11 0.21 5 38 0 9

問題判斷：
1. query+current/rows 平均每行所需的block數，太大的話（超過20）SQL語句效率太低
2. Parse count/Execute count parse count應儘量接近1，如果太高的話，SQL會進行不必要的reparse。
要檢查Pro*C程式的MAXOPENCURSORS是不是太低了，或不適當的使用的RELEASE_CURSOR選項
3. rows Fetch/Fetch Fetch Array的大小，太小的話就沒有充分利用批次Fetch的功能，
增加了資料在客戶端和伺服器之間的往返次數。在Pro*C中可以用prefetch=NN,Java/JDBC中可呼叫SETROWPREFETCH,
在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(預設是15)　　
4. disk/query+current 磁碟IO所佔邏輯IO的比例，太大的話有可能是db_buffer_size過小(也跟SQL的具體特性有關)　　
5. elapsed/cpu 太大表示執行過程中花費了大量的時間等待某種資源　　
6. cpu　OR　elapsed 太大表示執行時間過長，或消耗了大量的CPU時間，應該考慮最佳化
7.　執行計劃中的Rows 表示在該處理階段所訪問的行數，要儘量減少