tkprof和sql trace
2024-07-21 02:39:51
供稿：網友
1.預備使用SQL TRACE
1) Init.ORA參數
2) 確定是以"dedicated"方式連接到數據庫
2. 在系統中打開SQL_TRACE
3. 在session中打開SQL_TRACE
1) 在SQLPLUS中 alter session set sql_trace=true
2) 在PL/SQL中,由于不能執行alter session,可以使用 dbms_session.set_sql_trace(TRUE);
3) 打開其它session的SQL_TRACE dbms_system.set_sql_trace_in_session(sid,serial#,TRUE)
4) 用event來打開
alter session set events '10046 trace name context forever,level <N>'; alter session set events '10046 trace name context off';
4.找到trace文件
5.用tkPRof格式化trace文件
1.預備使用SQL TRACE
1) Init.ORA參數
timed_statistics 設置為true(也可以在session上設置),否則不會有CPU時間信息
user_dump_dest 指定trace文件生成的目錄
max_dump_file_size trace文件的最大尺寸(單位為操作系統塊),UMLIMITED表示沒有限制
Oracle8以后可以在后面加上 K 或 M 來表示文件大小
optimizer_mode 定義缺省的查詢優化器。雖然可以用alter session來設置，但在格式化trace文件里optimizer_mode會回復到原來的設置(一個新的session來分析SQL的執行計劃)，這樣會產生不準確的執行計劃，所以建議不要通過session來修改這個參數
注：在運行tkprof時不要加eXPlain參數，就不存在這個問題，執行計劃是Oracle在運行時所用的計劃
2) 確定是以"dedicated"方式連接到數據庫
2. 在系統中打開SQL_TRACE
在Init.ORA中加入
SQL_TRACE = TRUE
這樣會對系統性能造成明顯的影響，建議不要使用。
3.在session中打開SQL_TRACE
1) 在SQLPLUS中
alter session set sql_trace=true
2) 在PL/SQL中,由于不能執行alter session,可以使用
dbms_session.set_sql_trace(TRUE);
必須安裝DBMS_SESSION包，并"直接"賦給用戶alter session的權限。
3) 打開其它session的SQL_TRACE
dbms_system.set_sql_trace_in_session(sid,serial#,TRUE)
4) 用event來打開
alter session set events '10046 trace name context forever,level ';
alter session set events '10046 trace name context off';
N為以下值之一：
N=1 同alter session set sql_trace = true
N=4 可以捕捉綁定變量
N=8 可以捕捉查詢時的等待事件
N=12 可以捕捉綁定變量與等待事件
4.找到trace文件
trace文件名是ora_xxxx_SID.trc，其中xxxx是與Oracle連接的shadow進程的PID，SID是Oracle實例的SID。文件生成在Init.ORA參數user_dump_dest指定的目錄下。
5.用tkprof格式化trace文件
tkprof是用來解釋trace文件內容，把原始的trace文件轉化為輕易理解的文件。使用方法為
tkprof trace文件名 報告文件名 [sort=option]
例:
tkprof ora_12345_test.trc report.txt
sort參數是用來指定輸出的SQL是按什么數據來排序(如cpu時間或elapsed時間,詳見tkprof的使用參數說明)
在report.txt中有關于每個SQL的parse/execute/fetch/disk read/buffer get/cpu time/執行計劃(包括每一步運行時的行數),樣例如下：
select *
from
reverse_test where id=23456
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.06 2 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.06 2 4 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE access BY INDEX ROWID REVERSE_TEST
2 INDEX RANGE SCAN (object id 5833)
在report.txt文件頭有各個數據的解釋，根據以下一些指標可以分析一下SQL的執行性能:
query+current/rows 平均每行所需的block數，太大的話（超過20）SQL語句效率太低
Parse count/Execute count parse count應盡量接近1，假如太高的話，SQL會進行不必要的reparse。
要檢查Pro*C程序的MAXOPENCURSORS是不是太低了，或不適當的使用的RELEASE_CURSOR選項
rows Fetch/Fetch Fetch Array的大小，太小的話就沒有充分利用批量Fetch的功能，增加了數據在客戶端和服務器之間的往返次數。在Pro*C中可以用prefetch=NN,java/JDBC中可調用SETROWPREFETCH,在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(缺省是15)
disk/query+current 磁盤IO所占邏輯IO的比例，太大的話有可能是db_buffer_size過小(也跟SQL的具體特性有關)
elapsed/cpu 太大表示執行過程中花費了大量的時間等待某種資源
cpu OR elapsed 太大表示執行時間過長，或消耗了大量的CPU時間，應該考慮優化
執行計劃中的Rows 表示在該處理階段所訪問的行數，要盡量減少
tkprof的參數有下面幾個
/*********************************/
explain=username/passWord
connect to oracle and issue explain plain
/*********************************/
/*********************************/
talbe=schema.tablename
use'schema.table' with explain option
/*********************************/
/*********************************/
aggregate=yes/no
/*********************************/
/*********************************/
insert=filename
list sql statements and data inside insert statements
/*********************************/
/*********************************/
sys=no
tkprof does not list sql statements run as user sys.
/*********************************/
/*********************************/
record=filename
record non-recursive statements found in the trace file
/*********************************/
/*********************************/
print=integer
list only the first 'integer' sql statements
/*********************************/
/*********************************/
sort=option
set zero or more of the following sort options
##########################################
##### 具體參數 #####
##########################################
/*********************************/
sort=userid
userid of user that parsed the cursor
/*********************************/
/*********************************/
sort=fchrow
number of rows fetched
/********************************/
/********************************/
sort=fchcu
number of buffers for current read during fetch
/********************************/
/********************************/
sort=fchela
elapsed time fetching
/********************************/
/********************************/
sort=fchcnt
number of times fetch was called
/********************************/
##########################################
##### 具體參數 ######
##########################################
/*********************************/
(2005-01-25 19:52:46) 東茳蕁客.net
例如
/**************************/
sort=userid
userid of user that parsed the cursor
/********************************/
/*********************************/
sort=fchrow
number of rows fetched
/*****************************/
/*********************************/
sort=fchcu
number of buffers for current read during fetch
/**************************/
/**************************/
sort=fchela
elapsed time fetching
/****************************************/
/*************************************/
sort=fchcnt
number of times fetch was called
/********************************/
/******************************/ right">（出處：清風軟件下載學院）
