oracle sql_trace 簡單應用介紹

標籤：oracle

SQL_TRACE是Oracle提供的用於進行SQL跟蹤的手段，是強有力的輔助診斷工具.在日常的資料庫問題診斷和解決中，SQL_TRACE是非常常用的方法。

1.通過putty 或其他主機工具進入資料庫所在主機.

2.在命令列中通過sqlplus登入oracle.

???串連資料庫命令:sqlplus username/password

3.使用SQL_TRACE分析sql語句.

SQL_TRACE可以作為初始化參數在全域啟用，也可以通過命令列方式在具體session啟用。(以下案例均在session下進行)

?1).在全域啟用?

?在參數檔案(pfile/spfile)中指定:

?sql_trace =true

在全域啟用SQL_TRACE會導致所有進程的活動被跟蹤，包括後台進程及所有使用者進程，這通常會導致比較嚴重的效能問題，所以在生產環境中要謹慎使用，這個參數在10g之後是動態參數，可以隨時調整，在某些診斷中非常有效。
提示: 通過在全域啟用sql_trace，我們可以跟蹤到所有後台進程的活動，很多在文檔中的抽象說明，通過追蹤檔案的即時變化，我們可以清晰的看到各個進程之間的緊密協調.

所以不建議使用以上方式

?2).在當前session級設定

?大多數時候我們使用sql_trace跟蹤當前進程.通過跟蹤當前進程可以發現當前操作的後台資料庫遞迴活動(這在研究資料庫新特性時尤其有效)，
?研究SQL執行，發現後台錯誤等.

啟用當前session的跟蹤:SQL>?alter?session?set?sql_trace=true;Session?altered.此時的SQL操作將被跟蹤:SQL>?select?count(*)?from?dba_users;--此sql語句可被更換成需要跟蹤分析的sql.??COUNT(*)----------????????34結束跟蹤:SQL>?alter?session?set?sql_trace=false;Session?altered.

或者使用?DBMS_SESSION程式包?來開啟或關閉sql_trace
SQL> exec DBMS_SESSION.SET_SQL_TRACE (sql_trace boolean);

? ?

一般放seesion追蹤的資訊，對應系統初始化參數檔案參數show?parameter?user_dump???--11g之前使用user_dump_dest對應的就是它的位置。SQL>?show?parameter?user_dump?--查看session中trace檔案的存放位置NAME?????????????????????????????????TYPE??????????????????????????????VALUE------------------------------------?---------------------------------?------------------------------user_dump_dest???????????????????????string????????????????????????????/oracle/diag/rdbms/templatedb/??????????????????????????????????????????????????????????????????????????????????????templatedb/trace????SQL>?show?parameter?trace?--查看trace在當前session中的相關參數的值,sql_trace的值會隨著trace的開啟與關閉發生變化,如下為僅為驗證過程.NAME?????????????????????????????????TYPE??????????????????????????????VALUE------------------------------------?---------------------------------?------------------------------log_archive_trace????????????????????integer???????????????????????????0sec_protocol_error_trace_action??????string????????????????????????????TRACEsql_trace????????????????????????????boolean???????????????????????????FALSEtrace_enabled????????????????????????boolean???????????????????????????TRUEtracefile_identifier?????????????????stringSQL>?alter?session?set?sql_trace=TRUE;Session?altered.SQL>?show?parameter?traceNAME?????????????????????????????????TYPE??????????????????????????????VALUE------------------------------------?---------------------------------?------------------------------log_archive_trace????????????????????integer???????????????????????????0sec_protocol_error_trace_action??????string????????????????????????????TRACEsql_trace????????????????????????????boolean???????????????????????????TRUEtrace_enabled????????????????????????boolean???????????????????????????TRUEtracefile_identifier?????????????????stringSQL>?select?value?from?v$diag_info?where?name=‘Default?Trace?File‘;--查看當前session預設的trace檔案urlVALUE--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------/oracle/diag/rdbms/templatedb/templatedb/trace/templatedb_ora_5581.trc--設定自訂標識符,也可以不設定,設定後的則會改變當前session檔案名稱末尾的字串?如:templatedb_ora_5581.trc?變為?templatedb_ora_5581_testsession.trc,如下所示SQL>?alter?session?set?tracefile_identifier=‘testsession‘;Session?altered.SQL>?select?value?from?v$diag_info?where?name=‘Default?Trace?File‘;VALUE--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------/oracle/diag/rdbms/templatedb/templatedb/trace/templatedb_ora_5581_testsession.trc

5 退出sqlplus

使用exit命令即可退出.

6 根據剛才設定的標示符可以很容易的鎖定當前session所涉及的trace檔案

[[email protected] trace]$ ll *test*

-rw-r----- 1 oracle oinstall 2780 Dec 11 11:41 templatedb_ora_10420_testsession.trc

-rw-r----- 1 oracle oinstall? 136 Dec 11 11:41 templatedb_ora_10420_testsession.trm

7?使用Oracle內建的一個命令列工具tkprof，將trace檔案產生一個易讀的文字檔

[[email protected] trace]$ tkprof templatedb_ora_10420_testsession.trc testsession.txt

[[email protected] trace]$ ll *test*

-rw-r----- 1 oracle oinstall 2780 Dec 11 11:41 templatedb_ora_10420_testsession.trc

-rw-r----- 1 oracle oinstall? 136 Dec 11 11:41 templatedb_ora_10420_testsession.trm

-rw-r--r-- 1 oracle oinstall 5605 Dec 11 11:44 testsession.txt

cat?testsession.txtTKPROF:?Release?11.2.0.4.0?-?Development?on?Mon?Dec?11?11:44:46?2017Copyright?(c)?1982,?2011,?Oracle?and/or?its?affiliates.??All?rights?reserved.Trace?file:?templatedb_ora_10420_testsession.trcSort?options:?default********************************************************************************count????=?number?of?times?OCI?procedure?was?executedcpu??????=?cpu?time?in?seconds?executingelapsed??=?elapsed?time?in?seconds?executingdisk?????=?number?of?physical?reads?of?buffers?from?diskquery????=?number?of?buffers?gotten?for?consistent?readcurrent??=?number?of?buffers?gotten?in?current?mode?(usually?for?update)rows?????=?number?of?rows?processed?by?the?fetch?or?execute?call********************************************************************************SQL?ID:?61yfbh3s7h5x1?Plan?Hash:?2596900044select?count(1)?from?test_random_04call?????count???????cpu????elapsed???????disk??????query????current????????rows-------?------??--------?----------?----------?----------?----------??----------Parse????????1??????0.00???????0.00??????????0??????????0??????????0???????????0Execute??????1??????0.00???????0.00??????????0??????????0??????????0???????????0Fetch????????2??????0.10???????0.10??????????0???????2769??????????0???????????1-------?------??--------?----------?----------?----------?----------??----------total????????4??????0.10???????0.10??????????0???????2769??????????0???????????1Misses?in?library?cache?during?parse:?0Optimizer?mode:?ALL_ROWSParsing?user?id:?62Number?of?plan?statistics?captured:?1Rows?(1st)?Rows?(avg)?Rows?(max)??Row?Source?Operation----------?----------?----------??---------------------------------------------------?????????1??????????1??????????1??SORT?AGGREGATE?(cr=2769?pr=0?pw=0?time=102729?us)????999999?????999999?????999999???TABLE?ACCESS?FULL?TEST_RANDOM_04?(cr=2769?pr=0?pw=0?time=192830?us?cost=762?size=0?card=999999)********************************************************************************

oracle效能最佳化：如何讀懂tkprof

CALL?：每次SQL語句的處理都分成以下三個部分
??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這步。

?

A、query+current/rows 平均每行所需的block數，太大的話（超過20）SQL語句效率太低
B、Parse count/Execute count parse count應盡量接近1，如果太高的話，SQL會進行不必要的reparse
C、rows Fetch/Fetch Fetch Array的大小，太小的話就沒有充分利用批量Fetch的功能，增加了資料在用戶端和伺服器之間的往返次數。
D、disk/query+current 磁碟IO所佔邏輯IO的比例，太大的話有可能是db_buffer_size過小(也跟SQL的具體特性有關)
E、elapsed/cpu 太大表示執行過程中花費了大量的時間等待某種資源
F、cpu　Or　elapsed 太大表示執行時間過長，或消耗了了大量的CPU時間，應該考慮最佳化
G、執行計畫中的Rows 表示在該處理階段所訪問的行數，要盡量減少

oracle sql_trace 簡單應用介紹