oracle性能优化之awr分析
oracle性能優化之awr分析
作者：bingjava
?
最近某證券公司系統在業務期間系統運行緩慢，初步排查懷疑是數據庫存在性能問題，因此導出了oracle的awr報告進行分析，在此進行記錄。
導致系統的性能問題有很多，比如內存、cpu占用率過高，網絡延遲、系統存儲io瓶頸、還有程序方面的代碼邏輯、性能低下的sql語句等等，這里主要從awr的角度說明如何通過awr的報告來定位問題。
一、awr報告分析及問題定位
| DB Name? | DB Id? | Instance? | Inst num? | Release? | RAC? | Host? |
| ＊＊DB | 1527139216? | ＊＊DB | 1? | 10.2.0.5.0 | NO? | p3-＊＊DB |
| ? | Snap Id? | Snap Time? | Sessions? | Cursors/Session? |
| Begin Snap:? | 16021? | 01-Mar-16 10:00:34? | 213? | 2.4? |
| End Snap:? | 16022? | 01-Mar-16 11:00:36? | 213? | 2.3? |
| Elapsed:? | ?? | 60.04 (mins)? | ?? | ?? |
| DB Time:? | ?? | 176.32 (mins)? | ?? | ?? |
關鍵項說明：
DB TIME：代表了此統計期間的數據庫負載，是所有前臺session花費在database調用上的總和時間（包括CPU時間、IO Time、和其他一系列非空閑等待時間）。如果 DB Time 接近于 Elapsed Time*cpu 數,表明數據庫比較忙,cpu 負載也許比較大。這時很有可能是因為資源爭用導致等待事件的結果,可以去 top 5 等待事件分析原因。
Operating System Statistics
| Statistic? | Total? | |
| BUSY_TIME? | 1,037,128? | |
| IDLE_TIME? | 10,487,927? | |
| IOWAIT_TIME? | 19,061 | |
| NICE_TIME? | 316? | |
| SYS_TIME? | 132,552? | |
| USER_TIME? | 882,792? | |
| LOAD? | 3? | |
| RSRC_MGR_CPU_WAIT_TIME? | 0? | |
| VM_IN_BYTES? | 1,274,466,304? | |
| VM_OUT_BYTES? | 2,174,697,472? | |
| PHYSICAL_MEMORY_BYTES? | 33,712,308,224? | |
| NUM_CPUS? | 32? | |
| NUM_CPU_SOCKETS? | 2? | |
?
?
從以上信息可知：
單數據庫實例，非集群部署模式；2個物理cpu（NUM_CPU_SOCKETS＝2），32個邏輯cpu（NUM_CPUS＝32）。
cpu利用率為：DB Time /(Elapsed* NUM_CPUS)=176/(60*32) *100%=9.2%
cpu的負載處于正常水平。
Load Profile
| ? | Per Second? | Per Transaction? |
| Redo size:? | 89,367.47? | 21,227.40? |
| Logical reads:? | 105,600.68? | 25,083.26? |
| Block changes:? | 458.93? | 109.01? |
| Physical reads: | 27,716.84? | 6,583.56? |
| Physical writes:? | 30.80? | 7.32? |
| User calls:? | 3,675.70? | 873.09? |
| Parses:? | 324.60? | 77.10? |
| Hard parses:? | 14.13? | 3.36? |
| Sorts:? | 44.47? | 10.56? |
| Logons:? | 1.69? | 0.40? |
| Executes:? | 340.07? | 80.78? |
| Transactions:? | 4.21? | ?? |
?
| % Blocks changed per Read:? | 0.43? | Recursive Call %: | 16.91? |
| Rollback per transaction %:? | 0.09? | Rows per Sort:? | 397.30? |
?
Redosize:每秒產生的日志大小(單位字節)，可標志數據變更頻率,大的redosize往往對lgwr寫日志，和arch歸檔造成I/O壓力，也有可能造成logbuffer堵塞從而產生相關的等待事件。很繁忙的系統中日志生成量可能達到幾百k，甚至幾M。在Top 5 Timed Events中未發現log方面的等待事件，說明redo生成的頻率屬于正常范圍。
?
Logical reads: 從內存中讀取數據的次數（次數*塊數），每秒鐘邏輯讀數據量：105,600.68＊8k＝825m
Physical reads:當從內存中未都到數據時則從硬盤上讀取數據，每秒物理讀數據量：27,716.84 ＊8k＝216m
Physical reads / Logical reads=27,716.84/105,600.68=26%,有26%的邏輯讀導致了物理io。因此此處的物理io可能是系統的性能瓶頸（具體需在后面的 top 5中進行分析）。
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %:? | 98.73? | Redo NoWait %:? | 100.00? |
| Buffer Hit %: | 73.77? | In-memory Sort %:? | 100.00? |
| Library Hit %:? | 89.85? | Soft Parse %:? | 95.65? |
| Execute to Parse %:? | 4.55? | Latch Hit %:? | 96.92? |
| Parse CPU to Parse Elapsd %:? | 95.60? | % Non-Parse CPU:? | 96.41? |
?
buffer hit：表示進程從內存中找到數據塊的比率,監視這個值是否發生重大變化比這個 值本身更重要。對于一般的 OLTP 系統,通常應在 95%以上。否則應考慮加大 db_cache_size, 但是大量的非選擇的索引也會造成該值很高(大量的 db file sequential read)。
Latch Hit：Latch是一種保護內存結構的鎖，可以認為是SERVER進程獲取訪問內存數據結構的許可。要確保Latch Hit>99%，否則意味著Shared Pool latch爭用，可能由于未共享的SQL，或者Library Cache太小，可使用綁定變更或調大Shared Pool解決。
Execute to Parse：是語句執行與分析的比例，如果要SQL重用率高，則這個比例會很高。該值越高表示一次解析后被重復執行的次數越多。
?
Parse CPU to Parse Elapsd：該指標反映了快照內解析CPU時間和總的解析時間的比值(Parse CPU Time/ Parse Elapsed Time); 若該指標水平很低,那么說明在整個解析過程中 實際在CPU上運算的時間很短,而主要的解析時間都耗費在各種其他非空閑的等待事件上了，此值越高越好。
?
Shared Pool Statistics
| ? | Begin? | End? |
| Memory Usage %: | 56.42? | 55.58? |
| % SQL with executions>1:? | 54.12? | 49.23? |
| % Memory for SQL w/exec>1:? | 49.88? | 48.29? |
SQL with executions
：代表了sql重復執行的比例，本報告中是54%，是比較低的，說明存在sql硬編碼的情況，同時上面的Execute to Parse也只有4.55%，也說明了sql解析的重用率低。
內存利用率為55%左右，屬于正常情況。
?
Top 5 Timed Events
業務11:00－12:00期間：
| Event? | Waits? | Time(s)? | Avg Wait(ms)? | % Total Call Time? | Wait Class? |
| CPU time? | ?? | 10,028? | ?? | 94.8? | ?? |
| db file scattered read? | 6,943,920? | 644? | 0? | 6.1? | User I/O? |
| read by other session? | 4,837,558? | 578? | 0? | 5.5? | User I/O? |
| CSS initialization? | 13? | 65? | 4,967? | .6? | Other? |
| db file sequential read | 512,027? | 58? | 0? | .6? | User I/O? |
業務15:00－16:00期間
| Event? | Waits? | Time(s)? | Avg Wait(ms)? | % Total Call Time? | Wait Class? | ||
| CPU time? | ?? | 2,569? | ?? | 95.8? | ?? | ||
| SQL*Net more data to client? | 1,150,806? | 233? | 0? | 8.7? | Network? | ||
| db file scattered read? | 1,381,500? | 136? | 0? | 5.1? | User I/O? | ||
| CSS initialization | 13? | 63? | 4,878? | 2.4? | Other? | ||
| db file sequential read? | 42,488? | 30? | 1? | 1.1? | User I/O? | ||
?
db file scattered read:
表明Oracle內核請求從磁盤讀取多個數據塊到buffer cache中，
這種情況通常顯示與全表掃描相關的等待。當數據庫進行全表掃時,基于性能的考慮, 數據會分散讀入Buffer Cache。如果這個等待事件比較顯著,可能說明對于某些全表掃描的表,沒有創建索引或者沒有創建合適的索引。
read by other session:
Oracle 操作的最小單位是塊(Block),當對數據塊做修改時,其他的會話將被阻止對這個數據塊上的數據做修改，但是可以以一致性的方式讀取這個數據塊(from undo)。當前的用戶修改完這個數據塊后,將會立即釋放掉加在這個數據塊上的排他鎖,這樣另一個會話就可以繼續修改它,這種加鎖的機制叫Latch。當一個會話將數據塊都到內存中時，其它的會話同時也請求了這個數據塊，就導致被等待的會話出現read by other session。而當前會話一般是db file scattered read或db file sequential read。
從本次awr報告中都發現，db file scattered read、db file sequential read、read by other session這幾個事件的等待次數很高，因此可以判斷當前業務場景存在熱點塊競爭問題。
?
SQL*Net more data to client：
????當服務器端有太多的數據需要發給客戶端時,可能會產生此等待事件,也可能由于網絡問題導致服務器無法及時地將信息或者處理結果發送給客戶端, 同樣會產生這個等待。在15:00-－16:00業務期間此等待事件相對較高，從SQL*Net看并不像應用程序（應用程序是JDBC Thin Client），可能是第三方的oracle監控程序導致的。
?
?
?
File IO Stats
| Tablespace? | Filename? | Reads? | Av Reads/s? | Av Rd(ms)? | Av Blks/Rd? | Writes? | Av Writes/s? | Buffer Waits? | Av Buf Wt(ms)? | ||||
| JSZ35_TBS? | ＊tbs01.dbf | 2,635,786? | 732? | 0.10? | 14.88? | 4,032? | 1? | 2,016,907? | 0.12? | ||||
| JSZ35_TBS? | ＊tbs02.dbf | 2,730,384? | 758? | 0.09? | 12.89? | 10,420? | 3? | 1,679,836? | 0.12? | ||||
| JSZ35_TBS? | ＊tbs03.dbf | 2,084,937? | 579? | 0.08? | 12.19? | 9,183? | 3? | 1,141,265? | 0.13? | ||||
以上數據文件，平均每秒被讀700多次，平均每秒讀取的數據塊為14塊左右。
Tablespace IO Stats
| Tablespace? | Reads? | Av Reads/s | Av Rd(ms)? | Av Blks/Rd? | Writes? | Av Writes/s? | Buffer Waits? | Av Buf Wt(ms)? | ||||
| JSZ35_TBS? | 1,420,317? | 394? | 0.11? | 14.73? | 9,502? | 3? | 113? | 2.30? | ||||
?
Segments by Buffer Busy Waits
| Owner? | Tablespace Name? | Object Name? | Subobject Name? | Obj. Type? | Buffer Busy Waits? | % of Capture? | |||
| JSZ35? | JSZ35_TBS | TF_SUBJECTPRICE_TMP? | ?? | TABLE? | 30? | 32.26? | |||
| JSZ35? | JSZ35_TBS? | IND_T_*LOG | ?? | INDEX? | 21? | 22.58? | |||
| JSZ35? | JSZ35_TBS? | PK_T_**_TMP | ?? | INDEX? | 15? | 16.13? | |||
| JSZ35? | JSZ35_TBS? | T_***HER | CHER_P2016? | TABLE PARTITION? | 9? | 9.68? | |||
| JSZ35? | JSZ35_TBS? | IND_T_***HER | ?? | INDEX? | 7? | ? | |||
其它業務時間段：
| Owner? | Tablespace Name? | Object Name? | Subobject Name? | Obj. Type? | Buffer Busy Waits? | % of Capture? | |||
| JSZ35? | JSZ35_TBS? | IND_T_*LOG | ?? | INDEX? | 60? | 68.18? | |||
| JSZ35? | JSZ35_TBS? | IND_T_***SED | ?? | INDEX? | 20? | 22.73? | |||
?
| JSZ35? | JSZ35_TBS? | TF_SUBJECTPRICE_TMP? | ? | TABLE? | 18? | 17.65? | ||
| JSZ35? | JSZ35_TBS | IND_T_***HER | ? | INDEX? | 7? | 6.86? | ||
?
Segments by Physical Reads
| Owner? | Tablespace Name? | Object Name? | Subobject Name? | Obj. Type? | Physical Reads? | %Total? | |||
| JSZ35? | JSZ35_TBS? | T_***NCE | ANCE_P2015? | TABLE PARTITION? | 81,573,441? | 81.70? | |||
| JSZ35? | JSZ35_TBS? | T_***NCE | ANCE_P2016? | TABLE PARTITION | 12,884,029? | 12.90? | |||
| JSZ35? | JSZ35_TBS? | T_***CE | RICE_P2016? | TABLE PARTITION? | 3,471,341? | 3.48? | |||
熱點數據塊主要是T_***NCE、T_***CE引起。
數據塊熱點問題io等待的主要對象為：
T_***LOG、TF_SUBJECTPRICE_TMP、TS_PROCESSED、TF_SUBJECTPRICE_TMP、T_***NCE、T_***CE
可結合SQL ordered by CPU Time（最耗時的sql）、SQL ordered by Gets（邏輯讀最多的sql）、SQL ordered by Reads（物理讀最多的sql）來定位具體的sql語句。
?
二、問題總結及解決方式
????本報告期，系統的cpu、內存表現正常，造成系統性能問題的主要原因為物理讀過多，產生io等待；同時由于相關業務表存在頻繁的并發訪問現象（邏輯讀較多）且性能較差而導致了數據塊競爭問題。邏輯讀是消耗cpu的，而物理讀是消耗io的，這也說明了系統的大部分時間都消耗在io等待上，所以cpu相對空閑。
優化方案主要包括應用層的優化和oracle數據庫的優化：
????一、應用層的優化目標主要在于降低對數據庫的訪問頻率、合理有效使用索引（合理有效使用索引，需通過對sql語句的執行計劃進行分析和調優）：
將此表的單條insert的操作改為批量入庫提交的方式（比例100條記錄入庫一次）。
| 關鍵sql語句：SELECT /*+ LEADING ("A3" "A2" "A1") PQ_DISTRIBUTE ("A1", BROADCAST, NONE)USE_NL ("A1") FULL ("A1") PQ_DISTRIBUTE ("A2", BROADCAST, NONE)USE_NL ("A2") FULL ("A2") FULL ("A3") */ "A3"."FSETCODE", "A2"."FDATE", "A1"."FSETNAME", SUM(CASE WHEN "A3"."FACCTATTR" LIKE '??±????%' THEN "A2"."FENDBAL" ELSE 0 END ), SUM(CASE WHEN "A3"."FACCTATTR" LIKE '???±￡??%' THEN "A2"."FENDBAL" ELSE 0 END ) FROM "T_A***T" "A3", "T_***NCE" "A2", "T_AS**T" "A1" WHERE "A3"."FACCTDETAIL"=1 AND "A2"."FDATE"=TO_DATE(TO_CHAR(:1), 'yyyy-mm-dd') AND ("A3"."FACCTATTR" LIKE '??±????%' OR "A3"."FACCTATTR" LIKE '???±￡??') AND "A3"."FSETCODE"="A1"."FSETCODE" AND "A3"."FSETCODE"="A2"."FSETCODE" AND "A3"."FACCTCODE"="A2"."FACCTCODE" GROUP BY "A3"."FSETCODE", "A2"."FDATE", "A1"."FSETNAME" |
| select sum(NVL(fbacccredit, 0)) as fje from(select fsetcode, facctcode, fbacccredit from T_***NCE where fsetcode=:1 and fdate=:2 ) a left join T_A***T b on a.fsetcode = b.fsetcode and a.facctcode = b.facctcode where b.facctattr like :3 and b.facctdetail=1 |
| select a.fdate, a.fsetcode, a.fzqdm, a.fhqssj, a.fhqpjj, a.fbjsj, a.fsjsj, a.fzdcj, a.fjyzt, a.fjysc, a.fzqlb, a.fsyqx, a.fdatasource, a.fyqfyfx, a.fgzjgly, a.ftpdate from T_***CE a where fsh = 1 and fdate = to_date('2016-02-29', 'yyyy-MM-dd') and a.fsetcode = 0 union select a.fdate, a.fsetcode, a.fzqdm, a.fhqssj, a.fhqpjj, a.fbjsj, a.fsjsj, a.fzdcj, a.fjyzt, a.fjysc, a.fzqlb, a.fsyqx, a.fdatasource, a.fyqfyfx, a.fgzjgly, a.ftpdate from (select fdate, fsetcode, fzqdm, fhqssj, fhqpjj, fbjsj, fsjsj, fzdcj, fjyzt, fjysc, fzqlb, fsyqx, fdatasource, fyqfyfx, fgzjgly, ftpdate, fsh from T_***CE where fzqlb = 'JJ') a right join (select FDate, FZqdm, fjysc From T_***CE where fsh = 1 and fdate = to_date('2016-02-26', 'yyyy-MM-dd') and fsetcode = 0 and fzqlb = 'JJ') b on b.FDate = a.FDate and a.FZqdm = b.FZqdm and a.fjysc = b.fjysc and a.fsh = 1 where fsetcode = 0 and a.fjysc = 'Y' |
關鍵的sql語句：其中上面的第一條語句執行情況，SQL ordered by Elapsed Time：
| Elapsed Time (s)? | CPU Time (s)? | Executions? | Elap per Exec (s)? | % Total DB Time | SQL Id? | SQL Module? | SQL Text? | ||
| 3,519? | 3,601? | 0? | ?? | 33.26? | f089ggtmuxsnu | oracle@p3tgbmsdb1 (TNS V1-V3)? | SELECT /*+ LEADING…? | ||
| 1,305? | 1,086? | 158? | 8.26? | 12.34? | 7m0bfdfskwgcc | JDBC Thin Client? | select sum(… | ||
該語句執行了3600秒（即整個快照期）都還未執行完成，該語句是三張表的關聯統計查詢，oracle自動對其進行并行查詢，可能由于此三張表（T_A***T、T_***NCE、T_AS**T）的數據量較大，尤其是T_A***T的數據量較大時更是影響性能，采用并行查詢后反而導致了對io的爭用，降低了性能。
4、全表掃描問題
大表在一小時內發生了822次全表掃描，如果表的數據比較大則對性能有很大影響。小表每秒中有28次全表掃描，需重點優化以上3條sql語句。
| table scans (direct read) | 0? | 0.00? | 0.00? | ||
| table scans (long tables)? | 822? | 0.23? | 0.07? | ||
| table scans (rowid ranges)? | 0? | 0.00? | 0.00? | ||
| table scans (short tables)? | 102,749? | 28.52? | 8.27? | ||
| total number of times SMON posted? | 22? | 0.01? | ? | ||
?
?
二、oracle優化
? ? ? 1、合理設置DB_FILE_MULTIBLOCK_READ_COUNT，此參數控制在多數據塊讀時一次讀入數據塊的次數。適當增加這個參數大小，能夠提高多數據塊操作（如全表掃描）的IO效率。
2、可以考慮對以上熱點表重建索引、分區表等方式來降低該數據段上的IO負荷，將歷史數據進行分離（比如根據業務情況將2015年之前的數據轉移到另外的備份庫中）。
3、因Buffer Hit只有73%，可根據Buffer Pool Advisory調整buffer pool大小為：16g。
4、將頻繁并發訪問的表或數據移到另一數據塊或者進行更大范圍的分布(可以增大pctfree值 ，擴大數據分布，減少競爭)。
5、屬于index block的表（如T_***SED、T_***_TMP），應該考慮重建索引、分割索引或使用反向鍵索引。關于反向鍵索引需根據sql語句查詢特點進行有選擇使用（如果在where中對索引列進行了范圍搜索，則會導致該索引無效會進行全表掃描，反向鍵索引只對<>\＝有效）。 ? ?
轉載于:https://www.cnblogs.com/bingjava/p/5255760.html
總結
以上是生活随笔為你收集整理的oracle性能优化之awr分析的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Exchange2010外部传输域的使用
- 下一篇： VBA之文件筛选