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

Oracle筆記-優化策略與工具

2013-11-13 15:27:43  來源: Oracle 

  第 章 優化策略與工具

   標識問題

   我的方法

   綁定變量與分析(再次)

  不使用綁定變量將增加語句分析除了消耗CPU時間外還會增加字典高速緩存上的闩鎖

  顯示會話等待的事件V$SESSION_EVENT具體事件名和含義可以參考Oracle Reference Manual的附錄Oracle Wait Events

  CURSOR_SHARING

  CURSOR_SHARING參數缺省為EXACT若指定為FORCE則優化器可能將語句中所有的常數轉換為綁定變量雖然減少了語句分析但是也會帶來如下副作用

  優化器可供利用的信息可能減少從而改變執行路徑例如條件中對於某個特定值索引有較好的選擇性改為綁定變量時優化器並不會發現這一點

  查詢輸出格式發生變化雖然返回的數據長度不變但列的長度可能改變例如對於SELECT id tom name from emp name應該為VARCHAR但是由於tom被改為綁定變量則可能name的顯示長度變為

  查詢計劃更難評估由於語句的改變EXPLAIN PLAN看到的查詢與數據庫看到的可能不一致從而使AUTOTRACE等的輸出與實際執行路徑不一致

  因此完善的應用系統不應當依靠CURSOR_SHARING來提高效率僅能作為權宜之計

   SQL_TRACE TIMED_STATISTICS與TKPROF

  TIMED_STATISTICS並不會對系統產生過大負擔因此建議設置為TRUE

  啟動跟蹤

  SQL_TRACE可在系統或會話級激活激活後跟蹤文件將產生至initora參數USER_DUMP_DEST(專用服務器)或 BACKGROUND_DUMP_DEST(MTS)指定的目錄而文件大小通過MAX_DUMP_FILE_SIZE控制其設置有如下三種方法

  僅數值以OS塊為單位

  數值+K/M指定文件絕對大小

  UNLIMITED無上限

  一般只需要設置M就足夠了

  激活SQL_TRACE的幾種常用方式如下

  ALTER SESSION SET SQL_TRACE=TRUE|FALSE

  SYSDBMS_SYSTEMSET_SQL_TRACE_IN_SESSION 這裡我們需要指定SID和SERIAL#(參考V$SESSION)

  ALTER SESSION SET EVENTS 可獲得更詳細的信息

  此外也可通過DBMS_SUPPORT包相當於EVENTS跟蹤的一個界面但此包需要Oracle人員支持非標配

  隨著WEB服務方式的普及往往一個數據庫會話很短難以單獨跟蹤對此我們可以根據用戶在數據庫級建立觸發器

  CREATE OR REPLACE TRIGGER logon_trigger

  AFTER LOGON ON DATABASE

  BEGIN

  IF ( USER= TKYTE ) THEN

  EXECUTE IMMEDIATE ALTER SESSION SET EVENTS TRACE NAME CONTEXT FOREVER LEVEL ;

  END IF;

  END;/
  使用並解析TKPROF輸出

          激活SQL_TRACE後通過如下查詢檢查SPID

  SELECT aspid  FROM v$process a v$session b

  WHERE aaddr = bpaddr

  AND baudsid = userenv(sessionid

  此SPID就包含在跟蹤文件的文件名中

  UNIX系統中若你不在Oracle的管理組中則生成的跟蹤文件所在目錄可能無法訪問此時需要設定initora參數_trace_files_public = true

          TKPROF語法  TKPROF *trc *txt

  其他用法可以直接運行TKPROF查看一般常用選項就是sort可以根據某些參數值排序

          對跟蹤文件輸出的一些解釋

  i

  PARSE階段包括了軟分析(在SHARED_POOL中找到語句)和硬分析

  EXECUTE階段對SELECT幾乎為空對UPDATE則幾乎是全部工作的體現

  FETCH階段對SELECT是幾乎所有的工作對UPDATE則為空

  ii

  COUNT事件發生的次數

  CPU消耗的CPU時間(CPU秒)

  ELAPSED總體運行時間

  DISK磁盤物理I/O

  QUERY一致讀模式訪問的塊數也包括了從回滾段讀取的塊數

  CURRENT訪問的當前信息數據塊(而不是一致讀模式)例如SELECT時讀取數據字典內容修改時也需要訪問數據字典內容以寫

  ROWS所涉及的行數

          需要注意的現象

  i 高的PARSE COUNT/EXECUTE COUNT(接近%)且EXECUTE COUNT大於

  即執行語句時分析的次數如果過高可能是軟分析也過多了對一個會話應該是分析一次反復執行

  ii 對幾乎所有SQLEXECUTE COUNT都是

  可能沒有使用綁定變量在一個真實應用中應該很少看到不同的SQL同一個SQL應執行多次

  iii CPU和ELAPSED時間相差較大

  說明花了很長時間等待一個事件例如磁盤I/O鎖等

  iv (FETCH COUNT)/(ROWS FETCHED)比例高

  沒有很好的使用批量提取批量提取數據的方法是和語言/API相關的例如Pro* C中需要使用prefetch=NN預編譯Java/JDBC下可以調用SETROWPREFETCH方法PL/SQL可以在SELECT INTO中直接使用BULK COLLECT而SQL* PLUS缺省為每次取

  v 極大的DISK COUNT

  較難推斷但若DISK COUNT = QUERY + CURRENT MODE BLOCK COUNT則說明幾乎所有數據都來自磁盤此時需要考慮SGA大小和此查詢效率

  vi 極大的QUERY COUNT或CURRENT COUNT

  SQL工作量很大需要注意

          EXPLAIN PLAN問題

  跟蹤文件中顯示的是真正執行的路徑TKPROF也支持EXPLAIN=XXX/XXX選項不建議使用其輸出是轉換跟蹤文件當時優化器選擇的執行路徑並是利用數據庫的EXPLAIN工具與真實路徑時不完全一致的

  使用與解析原始跟蹤文件

          EVENTS跟蹤

  ALTER SESSION SET EVENTS trace name context forever level N

  N= 同標准SQL_TRACE

  N= 增加獲得綁定變量值

  N= 增加獲得查詢級的等待事件

  N= 增加獲得綁定變量值和查詢級的等待事件

          原始跟蹤文件分段解析

  文件頭含有時間數據庫版本OS版本實例名等

  APPNAME mod=%s mh=%lu act=%s ah=%lu

  mod

  傳入DBMS_APPLICATION_INFO的模塊名

  mh

  模塊哈希值

  act

  傳入DBMS_APPLICATION_INFO的動作

  ah

  動作哈希值

  Parsing in Cursor #%d dep=%d uid=%ld oct=%d lid=%ld tim=%ld hv=%ld ad=%s

  Cursor #

  游標號也可以用此值獲知應用最大打開的游標數

  len

  下面SQL語句的長度

  dep

  SQL語句的遞歸(recursive)深度

  uid

  當前方案的用戶ID注意這並不一定和後面的lid一致因為可以用

  alter session set current_schema來修改分析時的方案

  oct

  Oracle命令類型(Oracle Command Type)

  lid

  用於安全性檢查訪問權限的用戶ID

  tim

  定時器/

  ha

  SQL語句的哈希ID

  ad

  V$SQLAREA中此SQL語句的ADDR列

  EXEC Cursor#c=%de=%dp=%dcr=%dmis=%dr=%ddep=%dog=%dtim=%d

  Cursor #

  游標號

  c

  CPU時間/

  e

  流逝(Elapsed)時間/

  p

  物理讀

  cr

  一致(QUERY模式)讀(邏輯I/O)

  cu

  當前(Current)模式讀(邏輯I/O)

  mis

  字典緩存中的游標不命中數說明由於過期已從共享池中清除或從未進入共享池等而不得不分析此語句

  r

  處理的行數

  dep

  SQL語句的遞歸深度

  og

  優化器目標=ALL ROWS =FIRST ROWS =RULE =CHOOSE

  tim

  定時器

  與EXEC段類似的還有(即取代EXEC

  PARSE

  分析一個語句

  FETCH

  從一個游標取出數據行

  UNMAP

  用於顯示在不需要時從中間結果釋放臨時段

  SORT UMAP

  同UNMAP指排序段

  WAIT Cursor# nam=%s ela=%d p=%ul p=%ul p=%ul

  Cursor#

  游標號

  nam

  等待事件名

  ela

  流逝時間/

  ppp

  等待事件特定的參數

  以上為文件頭與ALTER SESSION出現的跟蹤信息此後開始出現運行的SQL語句

  BIND段

  cursor#

  游標號

  bind N

  綁定位置開始

  dty

  數據類型

  mxl

  綁定變量最大長度

  mal

  最大數組長度(當使用數組綁定或BULK操作時)

  scl

  數值范圍(scale)

  pre

  精度(precision)

  oacflg

  內部標記若此值為奇數則綁定變量可能為NULL(允許為NULL)

  oacfl

  內部標記續

  size

  緩沖區大小

  offset

  用於逐片(piecewise)綁定

  bfp

  綁定地址

  bln

  綁定緩沖區大小

  avl

  真實值長度

  flag

  內部標記

  value

  綁定值的字符串表示(如果可能會是一個十六進制dump)

  其中dtySELECT text FROM ALL_VIEWS WHERE view_name = USER_VIEWS 可看到一個將dty數值轉換為字符串表示的函數

  此後我們可以看到WAIT段即真正的等待事件

  對於ENQUEUE事件實際就是鎖可用以下函數(傳入參數為p)判斷類型

  CREATE OR REPLACE FUNCTION enqueue_decode(l_p in number) return varchar

  AS

  l_str varchar();

  BEGIN

  SELECT CHR(BITAND(l_p ) / ) ||

  CHR(BITAND(l_p ) / ) ||   ||

  DECODE(BITAND(l_p )

   No lock

   No lock

   RowShare

   RowExclusive

   Share

   Share RowExcl

   Exclusive )

  INTO l_str

  FROM DUAL;

  RETURN l_str;

  END;

  XCTEND(事務邊界)段記錄了提交等

  rlbk

  回滾標記 提交 回滾

  rd_only

  只讀標記 變化提交或回滾 事務只讀

  STAT段記錄了運行時SQL真正的執行計劃

  cursor #

  游標號

  id

  執行計劃行號

  cnt

  查詢計劃中流經此步驟的行數

  pid

  此步驟的父ID

  pos

  執行計劃中的位置

  obj

  訪問的對象的對象ID

  op

  操作的文本描述

  PARSE ERROR段

  len

  SQL語句長度

  dep

  SQL語句遞歸深度

  uid

  分析的方案

  oct

  Oracle命令類型

  lid

  權限方案ID

  tim

  定時器

  err

  ORA錯誤代碼

  ERROR段

  cursor #

  游標數

  err

  ORA錯誤代碼

  tim

  定時器

   DBMS_PROFILER

   StatsPack

   V$表

  V$EVENT_NAME

  說明事件名和ppp三個參數

  V$FILESTAT和V$TEMPSTAT

  說明系統I/O概況

  V$LOCK

  說明系統鎖的情況但注意Oracle並不在外部保存行鎖此視圖可以找到TM(DML Enqueue)鎖即說明產生了行鎖

  V$MYSTAT

  說明當前會話的統計信息需要V_$STATNAME(不用V$STATNAME只是V_$STATNAME的一個同義詞)和V_$MYSTAT上的SELECT權限

  CREATE VIEW MY_STATS AS

  SELECT aname bvalue

  FROM V$STATNAME a V$MYSTAT b

  WHERE astatistic# = bstatistic#

  V$OPEN_CURSOR

  記錄所有會話打開的游標由於Oracle也會緩存已關閉的游標因此此視圖中也會包含已關閉的游標信息

  V$PARAMETER

  說明了所有的initora參數

  V$SESSION

  記錄數據庫的每個會話需要對V_$SESSION的SELECT權限

  V$SESSION_EVENT

  說明會話的事件情況

  V$SESSION_LONGOPS

  記錄CBO認為執行時間超過秒的命令及進展

  V$SESSION_WAIT

  記錄所有正在等待某事件的會話及已等待時間

  V$SESSTAT

  類似V$MYSTAT但顯示所有會話

  V$SESS_IO

  說明會話的I/O信息

  V$SQL和V$SQLAREA

  記錄SQL信息建議使用V$SQLV$SQLAREA是從V$SQL合並而來的視圖代價較高對已經繁忙的系統是一個負擔

  V$STATNAME

  說明了統計號到統計名的映射

  V$SYSSTAT

  記錄實例層面的統計信息當數據庫關閉時才清空也是StatsPack很多數據的來源

  V$SYSTEM_EVENT

  記錄實例層面的等待事件信息也是StatsPack很多數據的來源


From:http://tw.wingwit.com/Article/program/Oracle/201311/16750.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.