ORACLE中SQL TRACE和TKPROF的使用
SQL TRACE 和 tkprof sql語句分析工具
一 SQL TRACE 使用方法：
1.初始化sql trace
參數：
timed_statistics=true 允許sql trace 和其他的一些動態性能視圖收集與時間有關的參數、
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.為一個session 啟動sql trace
2.1命令方式
alter session set sql_trace=true
2.2 通過存儲過程啟動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.停止一個sql trace 會話
3.1 命令方式
alter session set sql_trace=false
3.2 儲存過程的方式
execute rdbms_system.set_sql_trace_in_session (168 ,1,false);
4. 為整個實例啟動SQL trace （一般消耗系統性能較高，不會用）
alter system set sql_trace=true scope=spfile
從新啟動數據庫
5. 停止一個實例的sql trace
alter system set sql_trace=flase scope=spfile
啟動sql trace 之后收集的信息包括
1.解析、執行、返回數據的次數
2.cpu和執行命令的時間
3.物理讀和邏輯讀的次數
4.系統處理的記錄數
5.庫緩沖區錯誤
二 TKPROF的使用
tkprof 的目的是將sql trace 生成的跟蹤文件轉換成用戶可以理解的格式
1. 格式
tkprof inputfile outputfile [optional | parameters ]
參數和選項：
explain=user/password 執行explain命令將結果放在SQL trace的輸出文件中
table=schema.table 指定tkprof處理sql trace文件時臨時表的模式名和表名
insert=scriptfile 創建一個文件名為scriptfile的文件，包含了tkprof存放的輸出sql語句
sys=[yes/no] 確定系統是否列出由sys用戶產生或重調的sql語句
print=number 將僅生成排序后的第一條sql語句的輸出結果
record=recordfile 這個選項創建一個名為recorderfile的文件，包含了所有重調用的sql語句
sort=sort_option 按照指定的方法對sql trace的輸出文件進行降序排序
sort_option 選項
prscnt 按解析次數排序
prscpu 按解析所花cpu時間排序
prsela 按解析所經歷的時間排序
prsdsk 按解析時物理的讀操作的次數排序
prsqry 按解析時以一致模式讀取數據塊的次數排序
prscu 按解析時以當前讀取數據塊的次數進行排序
execnt 按執行次數排序
execpu 按執行時花的cpu時間排序
exeela 按執行所經歷的時間排序
exedsk 按執行時物理讀操作的次數排序
exeqry 按執行時以一致模式讀取數據塊的次數排序
execu 按執行時以當前模式讀取數據塊的次數排序
exerow 按執行時處理的記錄的次數進行排序
exemis 按執行時庫緩沖區的錯誤排序
fchcnt 按返回數據的次數進行排序
fchcpu 按返回數據cpu所花時間排序
fchela 按返回數據所經歷的時間排序
fchdsk 按返回數據時的物理讀操作的次數排序
fchqry 按返回數據時一致模式讀取數據塊的次數排序
fchcu 按返回數據時當前模式讀取數據塊的次數排序
fchrow 按返回數據時處理的數據數量排序
三 sql trace 的輸出結果
count：提供OCI過程的執行次數
CPU： 提供執行CPU所花的時間單位是秒
Elapsed：提供了執行時所花的時間。單位是秒。這個參數值等于用戶響應時間
Disk：提供緩存區從磁盤讀取的次數
Query：以一致性模式從緩存區獲得數據的次數
Current：以當前模式從緩存區獲得數據的次數
ROWs： 返回調用或執行調用時，處理的數據行的數量。
在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 表示在該處理階段所訪問的行數，要盡量減少
四：舉例：
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數，太大的話（超過20）SQL語句效率太低
2. Parse count/Execute count parse count應盡量接近1，如果太高的話，SQL會進行不必要的reparse。
要檢查Pro*C程序的MAXOPENCURSORS是不是太低了，或不適當的使用的RELEASE_CURSOR選項
3. rows Fetch/Fetch Fetch Array的大小，太小的話就沒有充分利用批量Fetch的功能，
增加了數據在客戶端和服務器之間的往返次數。在Pro*C中可以用prefetch=NN,Java/JDBC中可調用SETROWPREFETCH,
在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(缺省是15)
4. disk/query+current 磁盤IO所占邏輯IO的比例，太大的話有可能是db_buffer_size過小(也跟SQL的具體特性有關)
5. elapsed/cpu 太大表示執行過程中花費了大量的時間等待某種資源
6. cpu OR elapsed 太大表示執行時間過長，或消耗了大量的CPU時間，應該考慮優化
7. 執行計劃中的Rows 表示在該處理階段所訪問的行數，要盡量