熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

tkprof和sql trace

2013-11-13 15:39:08  來源: Oracle 

  准備使用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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.