tkprof和sql trace

　　准備使用SQL TRACE
　　
　　)　InitORA參數
　　
　　) 確定是以dedicated方式連接到數據庫
　　
　　 在系統中打開SQL_TRACE
　　
　　 在session中打開SQL_TRACE
　　
　　) 在SQLPLUS中 alter session set sql_trace=true
　　
　　) 在PL/SQL中由於不能執行alter session可以使用　　dbms_sessionset_sql_trace(TRUE);
　　
　　)　打開其它session的SQL_TRACE　　dbms_systemset_sql_trace_in_session(sidserial#TRUE)
　　
　　) 用event來打開
　　
　　alter session set events trace name context foreverlevel <N>;　　alter session set events trace name context off;
　　
　　找到trace文件
　　
　　用tkprof格式化trace文件
　　
　　准備使用SQL TRACE
　　
　　)　InitORA參數
　　
　　timed_statistics 設置為true(也可以在session上設置)否則不會有CPU時間信息
　　
　　user_dump_dest 指定trace文件生成的目錄
　　
　　max_dump_file_size trace文件的最大尺寸(單位為操作系統塊)UMLIMITED表示沒有限制
　　
　　Oracle以後可以在後面加上 K 或 M 來表示文件大小
　　
　　optimizer_mode 定義缺省的查詢優化器雖然可以用alter session來設置但在格式化trace文件裡optimizer_mode會回復到原來的設置(一個新的session來分析SQL的執行計劃)這樣會產生不准確的執行計劃所以建議不要通過session來修改這個參數
　　
　　注在運行tkprof時不要加explain參數就不存在這個問題執行計劃是Oracle在運行時所用的計劃
　　
　　) 確定是以dedicated方式連接到數據庫
　　
　　 在系統中打開SQL_TRACE
　　
　　在InitORA中加入
　　
　　SQL_TRACE = TRUE
　　
　　這樣會對系統性能造成明顯的影響建議不要使用
　　
　　在session中打開SQL_TRACE
　　
　　) 在SQLPLUS中
　　
　　alter session set sql_trace=true
　　
　　) 在PL/SQL中由於不能執行alter session可以使用
　　
　　dbms_sessionset_sql_trace(TRUE);
　　
　　必須安裝DBMS_SESSION包並直接賦給用戶alter session的權限
　　
　　)　打開其它session的SQL_TRACE
　　
　　dbms_systemset_sql_trace_in_session(sidserial#TRUE)
　　
　　) 用event來打開
　　
　　alter session set events trace name context foreverlevel ;
　　
　　alter session set events trace name context off;
　　
　　N為以下值之一
　　
　　N= 同alter session set sql_trace = true
　　N= 可以捕獲綁定變量
　　N= 可以捕獲查詢時的等待事件
　　N= 可以捕獲綁定變量與等待事件
　　
　　找到trace文件
　　
　　trace文件名是ora_xxxx_SIDtrc其中xxxx是與Oracle連接的shadow進程的PIDSID是Oracle實例的SID文件生成在InitORA參數user_dump_dest指定的目錄下
　　
　　用tkprof格式化trace文件
　　
　　tkprof是用來解釋trace文件內容把原始的trace文件轉化為容易理解的文件使用方法為
　　
　　tkprof trace文件名 報告文件名 [sort=option]
　　
　　例:
　　
　　tkprof ora__testtrc reporttxt
　　
　　sort參數是用來指定輸出的SQL是按什麼數據來排序(如cpu時間或elapsed時間詳見tkprof的使用參數說明)
　　
　　在reporttxt中有關於每個SQL的parse/execute/fetch/disk read/buffer get/cpu time/執行計劃(包括每一步運行時的行數)樣例如下
　　
　　select *
　　from
　　reverse_test 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:
　　
　　Rows　　 Row Source Operation
　　　
　　　 TABLE ACCESS BY INDEX ROWID REVERSE_TEST
　　　 INDEX RANGE SCAN (object id )
　　
　　在reporttxt文件頭有各個數據的解釋根據以下一些指標可以分析一下SQL的執行性能:
　　
　　query+current/rows 平均每行所需的block數太大的話（超過）SQL語句效率太低
　　
　　Parse count/Execute count parse count應盡量接近如果太高的話SQL會進行不必要的reparse要檢查Pro*C程序的MAXOPENCURSORS是不是太低了或不適當的使用的RELEASE_CURSOR選項
　　
　　rows Fetch/Fetch Fetch Array的大小太小的話就沒有充分利用批量Fetch的功能增加了數據在客戶端和服務器之間的往返次數在Pro*C中可以用prefetch=NNJava/JDBC中可調用SETROWPREFETCH在PL/SQL中可以用BULK COLLECTSQLPLUS中的arraysize(缺省是)
　　
　　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=schematablename
　　
　　useschematable 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 nonrecursive 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
　　/********************************/
　　
　　##########################################
　　##### 詳細參數　　　 ######
　　##########################################
　　
　　/*********************************/
　　
　　( ::)　 東茳荨
　　例如
　　/**************************/
　　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
　　/********************************/
　　
　　/******************************/
From:http://tw.wingwit.com/Article/program/Oracle/201311/17076.html