Oracle筆記-優化策略與工具
2024-08-29 13:39:24
供稿：網友
第 10 章 優化策略與工具 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=TRUEFALSE； 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_triggerAFTER LOGON ON DATABASEBEGIN 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 varchar2AS 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很多數據的來源。
