sql調優工具
Ⅰ Oraclesql
前言
sql_trace 是我在工作中經常要用到的調優工具 相比較statspack 我更願意用這個工具
因為數據逗搜庫慢原因的 %以上是由於sql問題造成的 statspack沒有sql的執行計劃 顯示沒有它直觀 方便 對想要針對性不強
介紹資料庫調優需要經常會用到的工具 可以很精確地跟抓取相關session正在運行的sql 再通過tkprof分析出來sql的執行計劃等相關信息 從而判斷那些sql語句存在問題
統計如下信孝鄭息（摘字官方文檔）
Parse execute and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Misses on the library cache
Username under which each parse occurred
Each mit and rollback
使用
使用前需要注意的地方
初始化參數timed_statistics=true 允許sql trace 和其他的一些動態性能視圖收集與時間（cpu elapsed）山慎歷有關的參數 一定要打開 不然相關信息不會被收集 這是一個動態的參數 也可以在session級別設置
SQL>alter session set titimed_statistics=true
MAX_DUMP_FILE_SIZE跟蹤文件的大小的限制 如果跟蹤信息較多可以設置成unlimited 可以是KB MB單位 I開始默認為unlimited這是一個動態的參數 也可以在session級別設置
SQL>alter system set max_mp_file_size=
SQL>alter system set max_mp_file_size=unlimited
USER_DUMP_DEST指定跟蹤文件的路徑 默認路徑實在$ORACLE_BASE/admin/ORA_SID/ump這是一個動態的參數 也可以在session級別設置
SQL>alter system set user_mp_dest=/oracle/trace
資料庫級別
設置slq_trace參數為true會對整個實例進行跟蹤 包括所有進程 用戶進程和後台進程 會造成比較嚴重的性能問題 生產環境一定要慎用
SQL>alter system set sql_trace=true;
Session級別
當前會話
SQL>alter session set sql_trace=true;
SQL>alter session set sql_trace=false;
其他會話
通過oracle提供的系統包 DBMS_SYSTEM SET_SQL_TRACE_IN_SESSION來實現
SQL>execute dbms_system set_sql_trace_in_session(sid serial# true);
SQL>execute dbms_system set_sql_trace_in_session(sid serial# false);
注
sid serial#從v$session視圖中獲得
DBMS_SYSTEM包里還可以對其他用戶的參數（如 timed_statistics max_mp_file）進行設置 在這不做介紹了 很少用到 想了解dbms_system里的程序包可以desc dbms_system看一下
得到trace文件後我們要用tkprof他進行格式化 通過sql語句快速定位到相應的trace文件
Tkprof
tkprof的目的是將sql trace生成的跟蹤文件轉換成用戶可以理解的格式
格式
tkprof tracefile outputfile [optional | parameters ]
參數和選項（這里只介紹最常用的 也是最實用的）
explain=user/password執行explain命令將結果放在SQL trace的輸出文件中
sys=[yes/no]確定系統是否列出由sys用戶產生或重調的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按返回數據時處理的數據數量排序
注
這些排序中我經常用到的是fchdsk fckchela fchqry 因為有問題的sql一般都是大的查詢造成的 當然更新 插入 刪除時也會存在全表掃描 這就需要:exedsk exeqry exeela等選項 根據具體情況具體分析
Cpu時間和Elapsed時間都是以秒為單位 而且兩個值基本上一樣 但我比較常用elapsed 他是反映的用戶相應時間 從運行sql到用戶得到結果的時間 會更實際些
tkprof輸出文件各列的含義 （理解下面的含義對我們快速定位問題很有幫助）
parse:
將sql語句轉換成執行計劃 包括檢查是否有正確的授權 需要到得表 列及其他引用到得對象是否存在 這些信息分別存在v$librarycache v$rowcache
execute
oracle實際執行的語句 如 insert update delete 這些會修改數據 對於select操作 這部只是確定選擇的行數
fetch
返回查詢獲得的行數 只有執行select會被收集
Count
這個語句被parse execute fetch的次數的統計
Cpu
這個語句所有的parse execute fetch所用的cpu總的時間 以秒為單位 如果TIMED_STATISTICS 關閉的話 值為
Elapsed
這個語句所有的parse execute fetch所消耗的總的時間 以秒為單位 如果TIMED_STATISTICS 關閉的話 值為
Disk
這個語句所有的parse execute fetch從磁碟上的數據文件中讀取的數據塊的數量
Query
在一致性讀的模式下 這個語句所有的parse execute fetch所獲取的buffer數量（這部分是從內存讀取的也就是邏輯讀取的 相當於執行計劃里的consistent gets）
Current
在current模式下 這個語句所有的parse execute fetch所獲取的buffer數量 一般是current模式下發生的delect insert update的操作都會獲取buffer
Rows
語句返回的行數 不包括子查詢中返回的記錄數目 對於select語句 返回在fetch這步 對於insert delete update操作 返回記錄是在execute這步
分析
我一般的思路步驟是
先找磁碟多的sq l(sort= fchdsk ) 意味著全表掃描 找運行時間長的(sort= fchela) 意味著sql可能寫的不好或磁碟 邏輯讀較多 找出一致性讀較多的(sort= fchqry) 當表不是很大的時候（可能全部緩存住了） 沒有發生磁碟讀 但不意味著不需要建立索引 或者sql需要優化 找出當前模式從緩沖區獲得數據的數量(sort=exedsk exeela exeqry) 這些主要集中在dml語句里的操作 看是否有必要優化sql或建立索引之所以排序是為了在sql很多的時候快速定位sql 如果sql比較少的話就沒必要排序了 但我們要有分析問題的思路
舉例
我自己建立了一個表
create table t (id int);
begin
for v in loop
insert into t values(v );
end loop
mit;
end;
下面是sql_trace所抓到得sql
不正常狀態
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache ring parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
TABLE ACCESS FULL T
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (FULL) OF T
首先這是一個select語句 它走了全部掃描
磁碟讀（ ）和邏輯讀（ ）都很多
運行了 次（Execute） 分析了 次（Parse） 一共用了將近 秒（elapsed）
我只是選擇表的一行的數據的結果 就發生這么大的成本 很顯然是全表掃描的結果造成的
正常狀態
在做跟蹤前我為這個表建立了一個索引
Create index t on t (id);
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache ring parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
INDEX RANGE SCAN T (object id )
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
INDEX (RANGE SCAN) OF T (NON UNIQUE)
*******************************************************************************
同樣的語句
它走了索引 物理讀 這個 其實是開始讀索引時需要第一次讀入的 以後運行就沒有了
邏輯讀 （平均這個sql一次 個邏輯讀）
同樣運行了 次（Execute）
分析了 次（Parse） 運行次數越多 分析次數越少越好一共只用了 秒（elapsed）
lishixin/Article/program/Oracle/201311/17866