sql跟蹤及tkprof使用，sql跟蹤tkprof使用

sql跟蹤及tkprof使用，sql跟蹤tkprof使用
簡述

在oracle資料庫中，awr是關於資料庫系統整體的負載情況和運行情況的報告。而當系統負載都顯示正常，而用戶端執行某些動作響應很慢，或者某些終端串連的會話執行緩慢或異常時，就需要用到會話層級的跟蹤了。

常見方法概述

Session層級跟蹤的方法有許多，比如當前會話的跟蹤，可以執行命令

Alter session set sql_trace=true;

Alter session set sql_trace=false;

或者使用10046事件

Alter session set events ‘10046 trace name context forever,level 12’;

Alter session set events ‘10046 trace name context off’

其中level 12是可選層級。

Level 1是指標準的sql跟蹤，與設定sql_trace效果相同。

Level 4是指在標準的基礎上增加綁定變數資訊。

Level 8是指在標準的基礎上增加等待事件資訊。

Level 12是指在標準的基礎上同時增加綁定變數資訊和等待事件資訊。

以上例子是在session層級進行跟蹤，若環境中需要在整個執行個體層級進行跟蹤，則直接將以上命令中的session更改為system即可實現。但系統層級的跟蹤將消耗大量的系統資源，因此，若非效能診斷需要，不要開啟在系統層級的sql_trace。

我們要用的方法

方法有很多，會用一種就行，下面我們說下我推薦的一種方法。

首先，可以設定trace檔案標誌，使我們的trace檔案更容易找出來。

Alter session set tracefile_identifier=’wjf’;

之後開啟計時

Alter system set timed_statistics=true;

然後尋找到要跟蹤會話的sid與serial#,

Select sid,serial#,osuser,machine from v$session;

找到sid與serial#後，就可以執行跟蹤了，比如sid=22，serial#=22;

執行以下命令

Exec dbms_monitor.session_trace_enable(22,22,waits=>true,binds=>true);

Exec dbms_monitor.session_trace_disable(22,22);

其中waits=>true跟binds=>true想必也不用解釋了。

追蹤檔案處理（tkprof）

追蹤後的trc檔案可以在DIAGNOSTIC_DEST目錄下找到，但是直接追蹤產生的trc檔案可讀性並不好，需要藉助一個oracle提供的工具處理以下，這個工具就是tkprof。

例如我們此處產生trc檔案名稱為orcl_ora_1234_wjf.trc.

則我們需要在系統層級，即退出sqlplus，在shell或cmd下執行以下命令

Shell>tkprof d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_1234_wjf.trcd:/wjf_2222.txt

這樣就能產生可讀性較強的追蹤檔案進行分析了。Tkprof工具必須輸入的兩個參數也就是一個輸入檔案，一個輸出檔案而已。

但tkprof還有一些其他參數，很多時候需要利用其他參數來組建檔案，從而更方便找出問題。

Tkprof工具的標準文法

tkprof filename1filename2 [waits=yes|no] [sort=option] [print=n]

    [aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]

    [explain=user/password] [record=filename4] [width=n]

Tkprof參數介紹

介紹幾個自我感覺比較常用的參數

Waits=yes|no：是否包含等待事件資訊。

Print=n:設定顯示多少行sql。比如你設定了排序資訊，可以只查看top 10的sql，就可以設定print=10了。

Sys=yes|no:設定是否包含sys使用者所發布的sql。主要是為了啟動或禁止顯示使用者為執行自己的sql所產生的遞迴sql，預設為yes。

Sort=option：設定產生sql根據指定選項進行排序。

prscnt  number oftimes parse was called

 prscpu  cpu timeparsing

  prsela  elapsedtime parsing

  prsdsk  numberof disk reads during parse

  prsqry  numberof buffers for consistent read during parse

   prscu   numberof buffers for current read during parse

   prsmis  numberof misses in library cache during parse

 

    execnt  numberof execute was called

    execpu  cputime spent executing

    exeela  elapsedtime executing

    exedsk  numberof disk reads during execute

    exeqry  numberof buffers for consistent read during execute

    execu   numberof buffers for current read during execute

    exerow  numberof rows processed during execute

    exemis  numberof library cache misses during execute

 

    fchcnt  numberof times fetch was called

    fchcpu  cputime spent fetching

    fchela  elapsedtime fetching

    fchdsk  numberof disk reads during fetch

    fchqry  numberof buffers for consistent read during fetch

    fchcu   numberof buffers for current read during fetch

    fchrow  numberof rows fetched

    userid  useridof user that parsed the cursor

其他還有一些參數，具體可以查看聯機文檔中的performance tunning guide。

Tkprof舉例

所以，一個比較正常的tkprof語句可能是這個樣子的

tkprof d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_1234_wjf.trcd:/wjf_2222.txt waits=no  sys=no  sort=(prscpu,execpu,fchcpu) print=10;

就這樣了，就像產生awr報告一樣，組建檔案永遠是最簡單的，能把產生的報告讀的多透徹才是見功底的東西。在此，我僅僅把產生報告的過程做個記錄，至於怎麼讀，慢慢再說。

 

oracle sql的執行計畫怎查看

一、通過PL/SQL Dev工具
1、直接File->New->Explain Plan Window，在視窗中執行sql可以查看計劃結果。其中，Cost表示cpu的消耗，單位為n%，Cardinality表示執行的行數，等價Rows。
2、先執行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的執行計畫了，看到的結果和1中的一樣，所以使用工具的時候推薦使用1方法。
注意：PL/SQL Dev工具的Command window中不支援set autotrance on的命令。還有使用工具方法查看計劃看到的資訊不全，有些時候我們需要sqlplus的支援。

二、通過sqlplus
1．最簡單的辦法
Sql> set autotrace on
Sql> select * from dual;
　　執行完語句後，會顯示explain plan 與 統計資訊。
　　這個語句的優點就是它的缺點，這樣在用該方法查看執行時間較長的sql語句時，需要等待該語句執行成功後，才返回執行計畫，使最佳化的周期大大增長。如果不想執行語句而只是想得到執行計畫可以採用：
Sql> set autotrace traceonly
這樣，就只會列出執行計畫，而不會真正的執行語句，大大減少了最佳化時間。雖然也列出了統計資訊，但是因為沒有執行語句，所以該統計資訊沒有用處，如果執行該語句時遇到錯誤，解決方案為：
(1）在要分析的使用者下：
Sqlplus > @ ?
dbmsadminutlxplan.sql
(2) 用sys使用者登陸
Sqlplus > @ ?sqlplusadminplustrce.sql
Sqlplus > grant plustrace to user_name;
- - user_name是上面所說的分析使用者

　2．用explain plan命令
(1) sqlplus > explain plan for select * from testdb.myuser
(2) sqlplus > select * from table(dbms_xplan.display);
　　上面這2種方法只能為在本會話中正在啟動並執行語句產生執行計畫，即我們需要已經知道了哪條語句啟動並執行效率很差，我們是有目的只對這條SQL語句去最佳化。其實，在很多情況下，我們只會聽一個客戶抱怨說現在系統運行很慢，而我們不知道是哪個SQL引起的。此時有許多現成的語句可以找出耗費資源比較多的語句，如：
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG FROM v$sqlarea
WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- -......餘下全文>>
 
資料庫TRC檔案怎查看相關的資訊以便於分析

查看歸檔模式
conn /as sysdba
archive log list

如果資料庫為歸檔模式的話，可以通過logmnr來進行挖掘記錄檔查看這些資訊的。如果是非歸檔模式。對不起無法查看了

開啟後台進程跟蹤，
設定參數(initsid.ora)
.backgroudn_dump_dest=目錄名 -- 指定根蹤檔案存放的路徑
.user_dmup_test=目錄名 --指定使用者資訊追蹤檔案的存放路徑
.使用者的追蹤檔案(.trc), 用TKPROF 來格式化使用者追蹤檔案
SQL 語句跟蹤即可。
.imed_statistics=true; --設定啟用 sql_trace =true;
.user_dump_dest=目錄 --指定追蹤檔案的存放路徑
.max_dump_file_size=5M --指定追蹤檔案最大尺寸
.SQL_TRACE=TRUE;
.動態改變 ：alter session set sql_trace=true;

或者開啟產生的追蹤檔案:
預設在..\oralce\admin\user\udump\*.trc，由於oralce 產生的*.trc 直接開啟格式不規格，看得很累，可以用tkprof gk 來格式化 ：c:\tkprof ora00001.trc a.txt