oracle sql_trace 簡單應用介紹

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                           0
sec_protocol_error_trace_action      string                            TRACE
sql_trace                            boolean                           FALSE
trace_enabled                        boolean                           TRUE
tracefile_identifier                 string
SQL> alter session set sql_trace=TRUE;

Session altered.

SQL> show parameter trace

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_trace                    integer                           0
sec_protocol_error_trace_action      string                            TRACE
sql_trace                            boolean                           TRUE
trace_enabled                        boolean                           TRUE
tracefile_identifier                 string



SQL> select value from v$diag_info where name='Default Trace File';--查看當前session默認的trace文件url

VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/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文件

[oracle@uatcrvcrtdb 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文件生成一個易讀的文本文件

[oracle@uatcrvcrtdb trace]$ tkprof templatedb_ora_10420_testsession.trc testsession.txt

[oracle@uatcrvcrtdb 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.txt

TKPROF: Release 11.2.0.4.0 - Development on Mon Dec 11 11:44:46 2017

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

Trace file: templatedb_ora_10420_testsession.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 61yfbh4s7h6x1 Plan Hash: 2596900044
select count(1) from test_random_04
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.10       0.10          0       2769          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.10       0.10          0       2769          0           1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 62
Number of plan statistics captured: 1
Rows (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 表示在該處理階段所訪問的行數，要盡量減少