Oracle筆記-最佳化策略與工具

第 10 章 最佳化策略與工具[@more@]

　　10.1 標識問題

　　10.2 我的方法

　　10.3 繫結變數與分析（再次）

　　不使用繫結變數將增加語句分析，除了消耗CPU時間外，還會增加字典快取記憶體上的閂鎖。

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

　　CURSOR_SHARING

　　CURSOR_SHARING引數預設為EXACT，若指定為FORCE，則最佳化器可能將語句中所有的常數轉換為繫結變數，雖然減少了語句分析，但是也會帶來如下副作用：

　　最佳化器可供利用的資訊可能減少，從而改變執行路徑，例如條件中對於某個特定值索引有較好的選擇性，改為繫結變數時最佳化器並不會發現這一點。

　　查詢輸出格式發生變化。雖然返回的資料長度不變，但列的長度可能改變。例如對於SELECT id， ‘tom’ name from emp； name應該為VARCHAR2（3），但是由於‘tom’被改為繫結變數，則可能name的顯示長度變為32.

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

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

　　10.4 SQL_TRACE， TIMED_STATISTICS與TKPROF

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

　　啟動跟蹤

　　SQL_TRACE可在系統或會話級啟用。啟用後跟蹤檔案將產生至init.ora引數USER_DUMP_DEST（專用伺服器）或 BACKGROUND_DUMP_DEST（MTS）指定的目錄。而檔案大小透過MAX_DUMP_FILE_SIZE控制，其設定有如下三種方法：

　　僅數值：以OS塊為單位；

　　數值+K/M：指定檔案絕對大小；

　　UNLIMITED：無上限。

　　一般只需要設定50-100M就足夠了。

　　啟用SQL_TRACE的幾種常用方式如下：

　　ALTER SESSION SET SQL_TRACE=TRUE|FALSE；

　　SYS.DBMS_SYSTEM.SET_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 ‘ ’10046 TRACE NAME CONTEXT FOREVER, LEVEL 4’ ‘ ’;

END IF;

END;/
　　使用並解析TKPROF輸出

　　1. 啟用SQL_TRACE後，透過如下查詢檢查SPID：

　　SELECT a.spid FROM v$process a， v$session b

　　WHERE a.addr = b.paddr

　　AND b.audsid = userenv（‘sessionid’）；

　　此SPID就包含在跟蹤檔案的檔名中。

　　UNIX系統中，若你不在Oracle的管理組中，則生成的跟蹤檔案所在目錄可能無法訪問，此時需要設定init.ora引數_trace_files_public = true .

　　2. TKPROF語法： TKPROF *.trc *.txt

　　其他用法可以直接執行TKPROF檢視。一般常用選項就是-sort，可以根據某些引數值排序。

　　3. 對跟蹤檔案輸出的一些解釋：

　　i. 行：

　　PARSE階段：包括了軟分析（在SHARED_POOL中找到語句）和硬分析；

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

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

　　ii. 列：

　　COUNT：事件發生的次數；

　　CPU：消耗的CPU時間（CPU秒）；

　　ELAPSED：總體執行時間；

　　DISK：磁碟物理I/O；

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

　　CURRENT：訪問的當前資訊資料塊（而不是一致讀模式），例如SELECT時讀取資料字典內容，修改時也需要訪問資料字典內容以寫。

　　ROWS：所涉及的行數。

　　4. 需要注意的現象：

　　i. 高的PARSE COUNT/EXECUTE COUNT（接近100%），且EXECUTE COUNT大於1

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

　　ii. 對幾乎所有SQL，EXECUTE COUNT都是1

　　可能沒有使用繫結變數。在一個真實應用中，應該很少看到不同的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預設為每次取15行。

　　v. 極大的DISK COUNT

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

　　vi. 極大的QUERY COUNT或CURRENT COUNT

　　SQL工作量很大，需要注意。

　　5. EXPLAIN PLAN問題

　　跟蹤檔案中顯示的是真正執行的路徑。TKPROF也支援EXPLAIN=XXX/XXX選項，不建議使用，其輸出是轉換跟蹤檔案當時最佳化器選擇的執行路徑，並是利用資料庫的EXPLAIN工具，與真實路徑時不完全一致的。

　　使用與解析原始跟蹤檔案

　　1. EVENTS跟蹤

　　ALTER SESSION SET EVENTS ‘10046 trace name context forever， level N’；

　　N=1 同標準SQL_TRACE；

　　N=4 增加獲得繫結變數值；

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

　　N=12 增加獲得繫結變數值和查詢級的等待事件。

　　2. 原始跟蹤檔案分段解析

　　檔案頭含有時間、資料庫版本、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

　　定時器，1/100秒

　　ha

　　SQL語句的雜湊ID

　　ad

　　V$SQLAREA中此SQL語句的ADDR列

　　EXEC Cursor#：c=%d，e=%d，p=%d，cr=%d，mis=%d，r=%d，dep=%d，og=%d，tim=%d

　　Cursor #

　　遊標號

　　c

　　CPU時間，1/100秒

　　e

　　流逝（Elapsed）時間，1/100秒

　　p

　　物理讀

　　cr

　　一致（QUERY模式）讀（邏輯I/O）

　　cu

　　當前（Current）模式讀（邏輯I/O）

　　mis

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

　　r

　　處理的行數

　　dep

　　SQL語句的遞迴深度

　　og

　　最佳化器目標：1=ALL ROWS 2=FIRST ROWS 3=RULE 4=CHOOSE

　　tim

　　定時器

　　與EXEC段類似的還有（即取代“EXEC”）：

　　PARSE

　　分析一個語句

　　FETCH

　　從一個遊標取出資料行

　　UNMAP

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

　　SORT UMAP

　　同UNMAP，指排序段

　　WAIT Cursor#： nam=‘%s’ ela=%d p1=%ul p2=%ul p3=%ul

　　Cursor#

　　遊標號

　　nam

　　等待事件名

　　ela

　　流逝時間，1/100秒

　　p1，p2，p3

　　等待事件特定的引數

　　以上為檔案頭與ALTER SESSION出現的跟蹤資訊。此後開始出現執行的SQL語句。

　　BIND段

　　cursor#

　　遊標號

　　bind N

　　繫結位置，從0開始

　　dty

　　資料型別

　　mxl

　　繫結變數最大長度

　　mal

　　最大陣列長度（當使用陣列繫結或BULK操作時）

　　scl

　　數值範圍（scale）

　　pre

　　精度（precision）

　　oacflg

　　內部標記。若此值為奇數，則繫結變數可能為NULL（允許為NULL）

　　oacfl2

　　內部標記續

　　size

　　緩衝區大小

　　offset

　　用於逐片（piecewise）繫結

　　bfp

　　繫結地址

　　bln

　　繫結緩衝區大小

　　avl

　　真實值長度

　　flag

　　內部標記

　　value

　　繫結值的字串表示（如果可能，會是一個十六進位制dump）

　　其中dty：SELECT text FROM ALL_VIEWS WHERE view_name = ‘USER_VIEWS’ 可看到一個將dty數值轉換為字串表示的函式。

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

　　對於ENQUEUE事件，實際就是鎖。可用以下函式（傳入引數為p1）判斷型別：

CREATE OR REPLACE FUNCTION enqueue_decode(l_p1 in number) return varchar2

AS

l_str varchar2(25);

BEGIN

SELECT CHR(BITAND(l_p1, -16777216) / 16777215) ||

CHR(BITAND(l_p1, 16711680) / 65535) || ‘ ‘ ||

DECODE(BITAND(l_p1, 65535),

0, ‘No lock’,

1, ‘No lock’,

2, ‘Row-Share’,

3, ‘Row-Exclusive’,

4, ‘Share’,

5, ‘Share Row-Excl’,

6, ‘Exclusive’ )

INTO l_str

FROM DUAL;

RETURN l_str;

END;

　　XCTEND（事務邊界）段記錄了提交等：

　　rlbk

　　回滾標記：0 提交 1 回滾

　　rd_only

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

　　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

　　定時器

　　10.5 DBMS_PROFILER

　　10.6 StatsPack

　　10.7 V$表

　　V$EVENT_NAME

　　說明事件名和p1、p2、p3三個引數。

　　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 a.name， b.value

　　FROM V$STATNAME a， V$MYSTAT b

　　WHERE a.statistic# = b.statistic#；

　　V$OPEN_CURSOR

　　記錄所有會話開啟的遊標。由於Oracle也會快取已關閉的遊標，因此此檢視中也會包含已關閉的遊標資訊。

　　V$PARAMETER

　　說明了所有的init.ora引數。

　　V$SESSION

　　記錄資料庫的每個會話。需要對V_$SESSION的SELECT許可權。

　　V$SESSION_EVENT

　　說明會話的事件情況。

　　V$SESSION_LONGOPS

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

　　V$SESSION_WAIT

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

　　V$SESSTAT

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

　　V$SESS_IO

　　說明會話的I/O資訊

　　V$SQL和V$SQLAREA

　　記錄SQL資訊。建議使用V$SQL，V$SQLAREA是從V$SQL合併而來的檢視，代價較高，對已經繁忙的系統是一個負擔。

　　V$STATNAME

　　說明了統計號到統計名的對映。

　　V$SYSSTAT

　　記錄例項層面的統計資訊。當資料庫關閉時才清空，也是StatsPack很多資料的來源。

　　V$SYSTEM_EVENT

　　記錄例項層面的等待事件資訊。也是StatsPack很多資料的來源。