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 commit and rollback

　　使用

　　使用前需要注意的地方

　　初始化參數timed_statistics=true   允許sql trace 和其他的一些動態性能視圖收集與時間（cpuelapsed）有關的參數一定要打開不然相關信息不會被收集這是一個動態的參數也可以在session級別設置

　　SQL>alter session set titimed_statistics=true

　　MAX_DUMP_FILE_SIZE跟蹤文件的大小的限制如果跟蹤信息較多可以設置成unlimited可以是KBMB單位I開始默認為unlimited這是一個動態的參數也可以在session級別設置

　　SQL>alter system set max_dump_file_size=

　　SQL>alter system set max_dump_file_size=unlimited

　　USER_DUMP_DEST指定跟蹤文件的路徑默認路徑實在$ORACLE_BASE/admin/ORA_SID/udump這是一個動態的參數也可以在session級別設置

　　SQL>alter system set user_dump_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_SYSTEMSET_SQL_TRACE_IN_SESSION來實現

　　SQL>execute dbms_systemset_sql_trace_in_session(sidserial#true);

　　SQL>execute dbms_systemset_sql_trace_in_session(sidserial#false);

　　注

　　sidserial#從v$session視圖中獲得

　　DBMS_SYSTEM包裡還可以對其他用戶的參數（如timed_statisticsmax_dump_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按返回數據時處理的數據數量排序

　　注

　　這些排序中我經常用到的是fchdskfckchela fchqry因為有問題的sql一般都是大的查詢造成的當然更新插入刪除時也會存在全表掃描這就需要:exedskexeqryexeela等選項根據具體情況具體分析

　　Cpu時間和Elapsed時間都是以秒為單位而且兩個值基本上一樣但我比較常用elapsed他是反映的用戶相應時間從運行sql到用戶得到結果的時間會更實際些

　　tkprof輸出文件各列的含義（理解下面的含義對我們快速定位問題很有幫助）

　　parse:

　　將sql語句轉換成執行計劃包括檢查是否有正確的授權需要到得表列及其他引用到得對象是否存在這些信息分別存在v$librarycachev$rowcache

　　execute

　　oracle實際執行的語句如insertupdatedelete這些會修改數據對於select操作這部只是確定選擇的行數

　　fetch

　　返回查詢獲得的行數只有執行select會被收集

　　Count

　　這個語句被parseexecutefetch的次數的統計

　　Cpu

　　這個語句所有的parseexecutefetch所用的cpu總的時間以秒為單位如果TIMED_STATISTICS 關閉的話值為

　　Elapsed

　　這個語句所有的parseexecutefetch所消耗的總的時間以秒為單位如果TIMED_STATISTICS 關閉的話值為

　　Disk

　　這個語句所有的parseexecutefetch從磁盤上的數據文件中讀取的數據塊的數量

　　Query

　　在一致性讀的模式下這個語句所有的parseexecutefetch所獲取的buffer數量（這部分是從內存讀取的也就是邏輯讀取的相當於執行計劃裡的consistent gets）

　　Current

　　在current模式下這個語句所有的parseexecutefetch所獲取的buffer數量一般是current模式下發生的delectinsertupdate的操作都會獲取buffer

　　Rows

　　語句返回的行數不包括子查詢中返回的記錄數目對於select語句返回在fetch這步對於insertdeleteupdate操作返回記錄是在execute這步

　　分析

　　我一般的思路步驟是

　　先找磁盤多的sq l(sort= fchdsk )意味著全表掃描找運行時間長的(sort= fchela)意味著sql可能寫的不好或磁盤邏輯讀較多找出一致性讀較多的(sort= fchqry)當表不是很大的時候（可能全部緩存住了）沒有發生磁盤讀但不意味著不需要建立索引或者sql需要優化找出當前模式從緩沖區獲得數據的數量(sort=exedskexeelaexeqry)這些主要集中在dml語句裡的操作看是否有必要優化sql或建立索引之所以排序是為了在sql很多的時候快速定位sql如果sql比較少的話就沒必要排序了但我們要有分析問題的思路

　　舉例

　　我自己建立了一個表

　　create table t (id int);

　　begin

　　for v in loop

　　insert into t values(v);

　　end loop

　　commit;

　　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 during 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 during 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 (NONUNIQUE)

　　*******************************************************************************

　　同樣的語句

　　它走了索引物理讀 這個其實是開始讀索引時需要第一次讀入的以後運行就沒有了

　　邏輯讀（平均這個sql一次個邏輯讀）

　　同樣運行了次（Execute）

　　分析了次（Parse） 運行次數越多分析次數越少越好一共只用了秒（elapsed）

　　可以看出前後很大的差距

From:http://tw.wingwit.com/Article/program/Oracle/201311/17866.html