【最佳化】SQL_TRACE之生成跟蹤檔案

一：跟蹤檔案：

Oracle跟蹤檔案分為三種型別，一種是後臺報警日誌檔案，記錄資料庫在啟動、關閉和執行期間後臺程式的活動情況,如表空間建立、回滾段建立、某些alter命令、日誌切換、錯誤訊息等。在資料庫出現故障時，應首先檢視該檔案，但檔案中的資訊與任何錯誤狀態沒有必然的聯絡。後臺報警日誌檔案儲存BACKGROUND_DUMP_DEST引數指定的目錄中，檔案格式為 SIDALRT.LOG。另一種型別是DBWR、LGWR、SMON等後臺程式建立的後臺跟蹤檔案。後臺跟蹤檔案根據後臺程式執行情況產生，後臺跟蹤檔案也儲存在BACKGROUND_DUMP_DEST引數指定的目錄中，檔案格式為siddbwr.trc、sidsmon.trc等。還有一種型別是由連線到Oracle的使用者程式(Server Processes)生成的使用者跟蹤檔案。這些檔案僅在使用者會話期間遇到錯誤時產生。此外，使用者可以透過執行oracle跟蹤事件（見後面）來生成該類檔案，使用者跟蹤檔案儲存在USER_DUMP_DEST引數指定的目錄中，檔案格式為oraxxxxx.trc，xxxxx為建立檔案的程式號（或執行緒號）

 

二：當SQL語句出現效能問題時，我們可以用SQL_TRACE來跟蹤SQL的執行情況，透過跟蹤，我們可以瞭解一條SQL或者PL/SQL包的執行情況，SQL_TRACE命令會將SQL執行的整個過程輸出到一個trace檔案中，我們可以讀這個trace 檔案來了解在這個SQL執行過程中Oracle 都做了哪些操作。可以透過sql命令啟動SQL_TRACE，或者在初始化引數裡面。

 

三：有以下三種方法生成跟蹤檔案

1.SQL_TRACE

2.10046

3.DBMS_MONITOR包

 

四：Trace檔案命名規則

9iR1：ora_xxxx.trc

9iR2以後：$ORACLE_SID_ora_xxxx.trc

 

實驗：

sql_tarce測試：

啟用SQL_TRACE

會話級別：alter session set sql_trace=true;

例項級：alter database set sql_trace=true;（不建議開啟）

 

TRACE檔案存放位置

10G: $ORACLE_BASE/admin/SID/udump

11G: $ORACLE_BASE/diag/rdbms/sid/SID/trace

 

1）——檢視引數：

SYS@ORA11GR2>show parameter tracefile

（此引數為空，表示生成trace檔案按系統預設方式生成tracefile，當然也可以自定義）

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------

tracefile_identifier                 string

SYS@ORA11GR2>show parameter sql_trace

(value是false表示系統當前不會產生trace檔案)

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------

sql_trace                            boolean     FALSE

 

2）進入hr使用者下進行操作生成trace檔案：

自定義tracefile檔名：

HR@ORA11GR2>alter session set tracefile_identifier='my_trace';

 

Session altered.

 

開啟當前會話，以產生trace檔案：

HR@ORA11GR2>alter session set sql_trace=true;

 

Session altered.

 

進行相關操作以產生trace檔案；

HR@ORA11GR2>select count(*) from hr.employees where job_id='SA_REP';

 

  COUNT(*)

----------

        30

關閉當前會話，不生成trace檔案

HR@ORA11GR2>alter session set sql_trace=false;

 

Session altered.

 

3）查詢trace檔案位置

HR@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>show parameter dump

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/ora

                                                 11gr2/ORA11GR2/trace

core_dump_dest                       string      /u01/app/oracle/diag/rdbms/ora

                                                 11gr2/ORA11GR2/cdump

max_dump_file_size                   string      unlimited

shadow_core_dump                     string      partial

user_dump_dest                       string      /u01/app/oracle/diag/rdbms/ora

                                                 11gr2/ORA11GR2/trace

 

4）檢視生成的trace檔案：

[oracle@wang trace]$ pwd

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace

[oracle@wang trace]$ ls *my_trace*trc

ORA11GR2_ora_17931_my_trace.trc

[oracle@wang trace]$

[oracle@wang trace]$ ls | grep my_trace

ORA11GR2_ora_17931_my_trace.trc

ORA11GR2_ora_17931_my_trace.trm

[oracle@wang trace]$

或者

SYS@ORA11GR2> SELECT c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace

   FROM v$process a, v$session b, v$parameter c, v$instance d

  WHERE a.addr = b.paddr

    AND b.audsid = userenv('sessionid')

    AND c.name = 'user_dump_dest';

 

TRACE

-------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_2016.trc

 

SYS@ORA11GR2>

這個sql語句是個通用的sql，由於我們之前設定了trace的標識名，那麼，我們用sql語句生得到的trace檔案還需要加上標識名稱才可以，或者修改一下sql語句如下：

SELECT c.value || '/' || d.instance_name || '_ora_' || a.spid || 'my_trace.trc' trace FROM ……

 

5）利用tkprof工具格式化trace檔案

[oracle@wang trace]$ tkprof ORA11GR2_ora_17931_my_trace.trc my_trace.txt

 

TKPROF: Release 11.2.0.4.0 - Development on Wed Oct 12 21:32:17 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

6）驗證：

[oracle@wang trace]$ ls my_trace.txt

my_trace.txt

[oracle@wang trace]$

 

——檢視格式化後的trace檔案：

[oracle@wang trace]$ vi my_trace.txt

……省略……

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

SQL ID: fa48b614fg9d1 Plan Hash: 128828553

 

select count(*)

from

 hr.employees where job_id='SA_REP'

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          1          1          0           1

------- ------  -------- ---------- ---------- ---------- ----------

total        4      0.00       0.00          1          1          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84

Number of plan statistics captured: 1

 

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ----------------------------------

         1          1          1  SORT AGGREGATE (cr=1 pr=1 pw=0 time=2140 us)

        30         30         30   INDEX RANGE SCAN EMP_JOB_IX (cr=1 pr=1 pw=0 time=2127 us cost=1 size=270 card=30)(object id 88139)

 

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

……省略……