Oracle 使用TRACE進行SQL效能分析

Oracle 使用TRACE進行SQL效能分析

設定sql_trace參數為true會對整個執行個體進行跟蹤，包括所有進程：使用者進程和後台進程，會造成比較嚴重的效能問題，生產環境一定要慎用。

一、根據相關事務或者sql 執行trace操作：

1） 使用前需要注意的地方

1,初始化參數timed_statistics=true。允許sql trace 和其他的一些動態效能檢視收集與時間（cpu，elapsed）有關的參數。一定要開啟，不然相關資訊不會被收集。這是一個動態參數，也可以在session層級設定。

SQL>alter session set titimed_statistics=true

2,MAX_DUMP_FILE_SIZE追蹤檔案的大小的限制，如果跟蹤資訊較多可以設定成unlimited。可以是KB,MB單位，9I開始預設為unlimited這是一個動態參數，也可以在session層級設定。

SQL>alter system set max_dump_file_size=300

SQL>alter system set max_dump_file_size=unlimited

2）trace執行過程：

1.      啟動SQL_TRACE：SQL> alter session set sql_trace=true;

2.      進行相關事務或者sql操作：SQL> select * from t;

3.      關閉SQL_TRACE：SQL> alter session set sql_trace=false;

也可以通過Oracle提供的系統包 DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION來實現。例如：首先從os上利用top命令找到當前佔用cpu資源最高的一個進程的PID號；然後在資料庫中根據PID號找到相應的sid和serial#。

SQL>execute dbms_system.set_sql_trace_in_session(sid,serial#,true);

SQL>execute dbms_system.set_sql_trace_in_session(sid,serial#,false);

二、獲得當前產生trace檔案的位置：

在Oracle 10g中，SQL_TRACE產生的trace檔案預設路勁是$ORACLE_BASE/admin/SID/udump；到了11g，trace 預設路徑在：$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目錄下。

也可以通過查詢出trace檔案所在的預設路徑：SELECT VALUE  FROM V$PARAMETER WHERE NAME = 'user_dump_dest'（BACKGROUND_DUMP_DEST）

如需修改：alter system set user_diagnostic_dest = 'd:\oracle\trace';

或者： select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

此外，也可以直接用如下SQL直接查出當前的trace檔案名稱。

SELECT      d.VALUE || '\'  || LOWER (RTRIM (i.INSTANCE, CHR (0)))  || '_ora_' || p.spid || '.trc'

AS "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') d;

三、轉換產生trace檔案：

SQL_TRACE 產生最原始的trace檔案的可讀性比較差，所以通常我們使用tkprof 工具來處理trace檔案。 Tkprof 工具是Oracle 內建的一個工具，用於處理原始的trace檔案，它的作用主要是合并匯總trace檔案中的一些項，正常化檔案的格式，使檔案更具有可讀性。 Tkprof 是系統層級的，直接在系統下執行即可。

注意：tkprof 工具只能用在處理SQL_TRACE和10046事件產生的trace，其他事件如10053不能處理。

格式：  tkprof tracefile outputfile [optional | parameters ]

C:/Users/Administrator/VIDI>tkprof d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3048.trc orcl_ora_3048.txt sys=no

參數和選項：

explain=user/password執行explain命令將結果放在SQL trace的輸出檔案中

sys=[yes/no]確定系統是否列出由sys使用者產生或重調的sql語句。設定為no後，trace檔案具有更佳的可讀性

sort=sort_option按照指定的方法對sql trace的輸出檔案進行降序排序

sort_option選項：設定排序選項，可以用逗號分隔多個選項。預設是追蹤檔案中發現的SQL順序。

prscnt按解析次數排序

prscpu按解析所花cpu時間排序

prsela按解析所經曆的時間排序

prsdsk按解析時物理的讀操作的次數排序

prsqry按解析時以一致模式讀取資料區塊的次數排序

prscu按解析時以當前讀取資料區塊的次數進行排序

execnt按執行次數排序

execpu按執行時花的cpu時間排序

exeela按執行所經曆的時間排序

exedsk按執行時物理讀操作的次數排序

exeqry按執行時以一致模式讀取資料區塊的次數排序

execu按執行時以當前模式讀取資料區塊的次數排序

exerow按執行時處理的記錄的次數進行排序

exemis按執行時庫緩衝區的錯誤排序

fchcnt按返回資料的次數進行排序

fchcpu按返回資料cpu所花時間排序

fchela按返回資料所經曆的時間排序

fchdsk按返回資料時的物理讀操作的次數排序

fchqry按返回資料時一致模式讀取資料區塊的次數排序

fchcu按返回資料時當前模式讀取資料區塊的次數排序

fchrow按返回資料時處理的資料數量排序

註：這些排序中經常用到的是fchdsk,fckchela ,fchqry.因為有問題的sql一般都是大的查詢造成的，當然更新，插入，刪除時也會存在全表掃描，這就需要:exedsk,exeqry,exeela等選項。根據具體情況具體分析。

Cpu時間和Elapsed時間都是以秒為單位，而且兩個值基本上一樣，但我比較常用elapsed，他是反映的使用者相應時間，從運行sql到使用者得到結果的時間，會更實際些。

tkprof輸出檔案各列的含義：

parse:將sql語句轉換成執行計畫，包括檢查是否有正確的授權，需要到得表，列及其他引用到得對象是否存在，這些資訊分別存在v$librarycache.v$rowcache..

execute:oracle實際執行的語句，如：insert,update,delete,這些會修改資料，對於select操作，這部只是確定選擇的行數。

fetch:返回查詢獲得的行數，只有執行select會被收集。

Count:這個語句被parse，execute，fetch的次數的統計

Cpu:這個語句所有的parse，execute，fetch所用的cpu總的時間，以秒為單位。如果TIMED_STATISTICS 關閉的話，值為0。

Elapsed:這個語句所有的parse，execute，fetch所消耗的總的時間，以秒為單位。如果TIMED_STATISTICS 關閉的話，值為0。

Disk:這個語句所有的parse，execute，fetch從磁碟上的資料檔案中讀取的資料區塊的數量

Query:在一致性讀的模式下，這個語句所有的parse，execute，fetch所擷取的資料區塊數量（這部分是從記憶體讀取的也就是邏輯讀取的，相當於執行計畫裡的consistent gets）

Current:在current模式下，這個語句所有的parse，execute，fetch所擷取的資料區塊數量，一般是current模式下發生的delect,insert,update的操作都會擷取。資料區塊

Rows:語句返回的行數，不包括子查詢中返回的記錄數目。對於select語句，返回在fetch這步，對於insert，delete，update操作，返回記錄是在execute這步。

四、對trace檔案分析過程：

1，先找磁碟多的sq l(sort= fchdsk )，意味著全表掃描；

2，找已耗用時間長的(sort= fchela)，意味著sql可能寫的不好或磁碟，邏輯讀較多；

3，找出一致性讀較多的(sort= fchqry)，當表不是很大的時候（可能全部緩衝住了），沒有發生磁碟讀，但不意味著不需要建立索引，或者sql需要最佳化；

4，找出當前模式從緩衝區獲得資料的數量(sort=exedsk,exeela,exeqry)，這些主要集中在dml語句裡的操作，看是否有必要最佳化sql或建立索引之所以排序是為了在sql很多的時候快速定位sql，如果sql比較少的話就沒必要排序了，但我們要有分析問題的思路。

--------------------------------------分割線 --------------------------------------

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

--------------------------------------分割線 --------------------------------------