ORACLE中SQL TRACE和TKPROF的使用
SQL TRACE 和 tkprof sql語(yǔ)句分析工具
一 SQL TRACE 使用方法：
1.初始化sql trace
參數(shù)：
timed_statistics=true 允許sql trace 和其他的一些動(dòng)態(tài)性能視圖收集與時(shí)間有關(guān)的參數(shù)、
SQL>alter session set titimed_statistics=true
max_dump_file_size=500 指定跟蹤文件的大小
SQL> alter system set max_dump_file_size=500;
user_dump_dest 指定跟蹤文件的路徑
SQL> alter system set user_dump_dest=/oracle/oracle/diag/rdbms/orcl/orcl/trace;
2.為一個(gè)session 啟動(dòng)sql trace
2.1命令方式
alter session set sql_trace=true
2.2 通過(guò)存儲(chǔ)過(guò)程啟動(dòng)sqltrace
select sid,serial#,osuser from v$session;
SID SERIAL# OSUSER
168 1 oracle
execute rdbms_system.set_sql_trace_in_session (168 ,1,true);
3.停止一個(gè)sql trace 會(huì)話
3.1 命令方式
alter session set sql_trace=false
3.2 儲(chǔ)存過(guò)程的方式
execute rdbms_system.set_sql_trace_in_session (168 ,1,false);
4. 為整個(gè)實(shí)例啟動(dòng)SQL trace （一般消耗系統(tǒng)性能較高，不會(huì)用）
alter system set sql_trace=true scope=spfile
從新啟動(dòng)數(shù)據(jù)庫(kù)
5. 停止一個(gè)實(shí)例的sql trace
alter system set sql_trace=flase scope=spfile
啟動(dòng)sql trace 之后收集的信息包括
1.解析、執(zhí)行、返回?cái)?shù)據(jù)的次數(shù)
2.cpu和執(zhí)行命令的時(shí)間
3.物理讀和邏輯讀的次數(shù)
4.系統(tǒng)處理的記錄數(shù)
5.庫(kù)緩沖區(qū)錯(cuò)誤
二 TKPROF的使用
tkprof 的目的是將sql trace 生成的跟蹤文件轉(zhuǎn)換成用戶(hù)可以理解的格式
1. 格式
tkprof inputfile outputfile [optional | parameters ]
參數(shù)和選項(xiàng)：
explain=user/password 執(zhí)行explain命令將結(jié)果放在SQL trace的輸出文件中
table=schema.table 指定tkprof處理sql trace文件時(shí)臨時(shí)表的模式名和表名
insert=scriptfile 創(chuàng)建一個(gè)文件名為scriptfile的文件，包含了tkprof存放的輸出sql語(yǔ)句
sys=[yes/no] 確定系統(tǒng)是否列出由sys用戶(hù)產(chǎn)生或重調(diào)的sql語(yǔ)句
print=number 將僅生成排序后的第一條sql語(yǔ)句的輸出結(jié)果
record=recordfile 這個(gè)選項(xiàng)創(chuàng)建一個(gè)名為recorderfile的文件，包含了所有重調(diào)用的sql語(yǔ)句
sort=sort_option 按照指定的方法對(duì)sql trace的輸出文件進(jìn)行降序排序
sort_option 選項(xiàng)
prscnt 按解析次數(shù)排序
prscpu 按解析所花cpu時(shí)間排序
prsela 按解析所經(jīng)歷的時(shí)間排序
prsdsk 按解析時(shí)物理的讀操作的次數(shù)排序
prsqry 按解析時(shí)以一致模式讀取數(shù)據(jù)塊的次數(shù)排序
prscu 按解析時(shí)以當(dāng)前讀取數(shù)據(jù)塊的次數(shù)進(jìn)行排序
execnt 按執(zhí)行次數(shù)排序
execpu 按執(zhí)行時(shí)花的cpu時(shí)間排序
exeela 按執(zhí)行所經(jīng)歷的時(shí)間排序
exedsk 按執(zhí)行時(shí)物理讀操作的次數(shù)排序
exeqry 按執(zhí)行時(shí)以一致模式讀取數(shù)據(jù)塊的次數(shù)排序
execu 按執(zhí)行時(shí)以當(dāng)前模式讀取數(shù)據(jù)塊的次數(shù)排序
exerow 按執(zhí)行時(shí)處理的記錄的次數(shù)進(jìn)行排序
exemis 按執(zhí)行時(shí)庫(kù)緩沖區(qū)的錯(cuò)誤排序
fchcnt 按返回?cái)?shù)據(jù)的次數(shù)進(jìn)行排序
fchcpu 按返回?cái)?shù)據(jù)cpu所花時(shí)間排序
fchela 按返回?cái)?shù)據(jù)所經(jīng)歷的時(shí)間排序
fchdsk 按返回?cái)?shù)據(jù)時(shí)的物理讀操作的次數(shù)排序
fchqry 按返回?cái)?shù)據(jù)時(shí)一致模式讀取數(shù)據(jù)塊的次數(shù)排序
fchcu 按返回?cái)?shù)據(jù)時(shí)當(dāng)前模式讀取數(shù)據(jù)塊的次數(shù)排序
fchrow 按返回?cái)?shù)據(jù)時(shí)處理的數(shù)據(jù)數(shù)量排序
三 sql trace 的輸出結(jié)果
count：提供OCI過(guò)程的執(zhí)行次數(shù)
CPU： 提供執(zhí)行CPU所花的時(shí)間單位是秒
Elapsed：提供了執(zhí)行時(shí)所花的時(shí)間。單位是秒。這個(gè)參數(shù)值等于用戶(hù)響應(yīng)時(shí)間
Disk：提供緩存區(qū)從磁盤(pán)讀取的次數(shù)
Query：以一致性模式從緩存區(qū)獲得數(shù)據(jù)的次數(shù)
Current：以當(dāng)前模式從緩存區(qū)獲得數(shù)據(jù)的次數(shù)
ROWs： 返回調(diào)用或執(zhí)行調(diào)用時(shí)，處理的數(shù)據(jù)行的數(shù)量。
在report.txt文件頭有各個(gè)數(shù)據(jù)的解釋?zhuān)鶕?jù)以下一些指標(biāo)可以分析一下SQL的執(zhí)行性能: query+current/rows 平均每行所需的block數(shù)，太大的話（超過(guò)20）SQL語(yǔ)句效率太低
Parse count/Execute count parse count應(yīng)盡量接近1，如果太高的話，SQL會(huì)進(jìn)行不必要的reparse。要檢查Pro*C程序的MAXOPENCURSORS是不是太低了，或不適當(dāng)?shù)氖褂玫腞ELEASE_CURSOR選項(xiàng)
rows Fetch/Fetch Fetch Array的大小，太小的話就沒(méi)有充分利用批量Fetch的功能，增加了數(shù)據(jù)在客戶(hù)端和服務(wù)器之間的往返次數(shù)。在Pro*C中可以用prefetch=NN,Java/JDBC中可調(diào)用SETROWPREFETCH,在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(缺省是15)
disk/query+current 磁盤(pán)IO所占邏輯IO的比例，太大的話有可能是db_buffer_size過(guò)小(也跟SQL的具體特性有關(guān))
elapsed/cpu 太大表示執(zhí)行過(guò)程中花費(fèi)了大量的時(shí)間等待某種資源
cpu OR elapsed 太大表示執(zhí)行時(shí)間過(guò)長(zhǎng)，或消耗了大量的CPU時(shí)間，應(yīng)該考慮優(yōu)化
執(zhí)行計(jì)劃中的Rows 表示在該處理階段所訪問(wèn)的行數(shù)，要盡量減少
四：舉例：
sql>alter session set sql_trace=true
SQL>select * from dba_users;
SQL>show parameter user_dump_dest
user_dump_dest string /oracle/oracle/diag/rdbms/orcl/orcl/trace
SQL>exit
cd /oracle/oracle/diag/rdbms/orcl/orcl/trace
tkprof orcl_ora_11066.trc /oracle/oracle/trace1.out sys=yes
vi trace.out
1. query+current/rows 平均每行所需的block數(shù)，太大的話（超過(guò)20）SQL語(yǔ)句效率太低
2. Parse count/Execute count parse count應(yīng)盡量接近1，如果太高的話，SQL會(huì)進(jìn)行不必要的reparse。
要檢查Pro*C程序的MAXOPENCURSORS是不是太低了，或不適當(dāng)?shù)氖褂玫腞ELEASE_CURSOR選項(xiàng)
3. rows Fetch/Fetch Fetch Array的大小，太小的話就沒(méi)有充分利用批量Fetch的功能，
增加了數(shù)據(jù)在客戶(hù)端和服務(wù)器之間的往返次數(shù)。在Pro*C中可以用prefetch=NN,Java/JDBC中可調(diào)用SETROWPREFETCH,
在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(缺省是15)
4. disk/query+current 磁盤(pán)IO所占邏輯IO的比例，太大的話有可能是db_buffer_size過(guò)小(也跟SQL的具體特性有關(guān))
5. elapsed/cpu 太大表示執(zhí)行過(guò)程中花費(fèi)了大量的時(shí)間等待某種資源
6. cpu OR elapsed 太大表示執(zhí)行時(shí)間過(guò)長(zhǎng)，或消耗了大量的CPU時(shí)間，應(yīng)該考慮優(yōu)化
7. 執(zhí)行計(jì)劃中的Rows 表示在該處理階段所訪問(wèn)的行數(shù)，要盡量