設置sql_trace參數為true會對整個實例進行跟蹤，包括所有進程：用戶進程和后臺進程，會造成比較嚴重的性能問題，生產環境一定要慎

設置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需要優化；

本文原創發布php中文網，轉載請注明出處，感謝您的尊重！

總結

以上是生活随笔為你收集整理的oracle trace发起用户,Oracle 使用TRACE进行SQL性能分析的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。