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

Oraclesql

2013-11-13 16:07:46  來源: Oracle 

  前言

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