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