Oracle RAC性能優(yōu)化實踐

1、Oracle RAC性能優(yōu)化實踐技術(shù)創(chuàng)新 變革未來RAC硬件架構(gòu)系統(tǒng)與參數(shù)配置應(yīng)用設(shè)計工作負(fù)載管理Node1Node2StoragePrivate NetworkPublic NetworkFC SwitchFC SwitchThe Shared I/O subsystem and Private Network can be thought of as the heart and soul of a RAC system.Testing I/O subsystemTesting Private networkOrionmbpslatencyiopsNetperfUDPlatencybandw

2、idthTCPZdata測試數(shù)據(jù)IOPS：200萬MBPS:21GB/sLatency: vmo -p -o minperm%=5 -o maxperm%=10 -o maxclient%=10 -o strict_maxclient=1 -o strict_maxperm=1 -o lru_file_repage=0 -o v_pinshm=12 RHEL6.6:IPC Send timeout/node eviction etc with high packet reassbemles failure(DOC ID 2008933.1)參照：Oracle Database (RDBMS)

3、on Unix AIX,HP-UX, Linux, Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2) (文檔 ID 169706.1)GI&DB Software升級數(shù)據(jù)庫到穩(wěn)定版本Grid和Oracle軟件保持相同的PSU版本保持PSU更新到較新的版本，并打one off patch案例：11gR2/Aix - Dedicated Server Processes Have Large USLA He

4、ap Segment Compared To Older Versions (文檔 ID 1260095.1)參照：Document 1082394.1 11.2.0.X Grid Infrastructure PSU Known IssuesDocument 756671.1 Oracle Recommended Patches - Oracle Database警示：jar命令解壓、online patchDB Parameterparallel_force_localgcs_server_processesDRM（dynamic remastering）特性單實例數(shù)據(jù)庫的參數(shù)優(yōu)化在RAC

5、環(huán)境同樣適用parallel_max_server 、 adaptive_cursor_sharing、cardinality feedback 、 serial direct path read、deferred_segment_creation、delayed failed logins 、 undo_autotune 、audit_trail 、 adaptive_log_file_sync案例：High library cache lock Wait Time Due to Invalid Login Attempts (Doc ID 1309738.1)1.22.243.254.18

6、5.176.177.1787654321010.18 0.19 0.1623456780.15 0.14 0.13 0.15 0.14 0.14 0.15 0.15 0.15 0.1591011121314151617181920異常連接耗時趨勢圖耗時(秒)6.46 10.3715.221.2536.3828.2345.4455.360.4860.4860.4560.560.4360.5560.43706050403020100120.6 0.63 0.65 1.43 3.4134567891011121314151617181920異常連接耗時趨勢圖耗時(秒)局部插入操作Root block

7、Branch blocks1000100110021003問題：右邊索引增長競爭、 gc buffer busy acquire、gc buffer busy優(yōu)化策略：哈希分區(qū)索引、哈希分區(qū)表、反向鍵索引大量的truncate和drop問題：enq: IV - contention 、 enq: RO - fast object reuse 、reliable message優(yōu)化策略：Global temp tableBuffer cacheLibrary cacheGCSGESDDLSequence緩存如果序列設(shè)置了ORDER和NOCACHE，每一次訪問序列都要更新seq$表問題：row c

8、ache lock 、 Disk file operation I/O、db file sequential read 、gc current block busy優(yōu)化策略：序列設(shè)置合適的緩存值如果序列被頻繁訪問，則使用更大的緩存值（1000+）序列拆分其他問題過多的提交長時間沒有提交的事務(wù)索引設(shè)計低效的執(zhí)行計劃過多的并行操作全表掃描存款貸款業(yè)務(wù)模塊單筆業(yè)務(wù)批量業(yè)務(wù)業(yè)務(wù)類型數(shù)據(jù)操作類查詢類操作類型應(yīng)用 關(guān)聯(lián) 性在RAC環(huán)境中通過服務(wù)實現(xiàn)工作負(fù)載管理Service_s1Service_s2Service_s1運(yùn)行在節(jié)點(diǎn)1上，節(jié)點(diǎn)1宕機(jī)時切換到節(jié)點(diǎn)2Service_s2運(yùn)行在節(jié)點(diǎn)2上，節(jié)點(diǎn)2宕機(jī)時切換到節(jié)點(diǎn)1面臨的問題硬件配置越高，性能越好，價格越貴？參數(shù)配置太多，補(bǔ)丁更新太快，無暇兼顧？應(yīng)用設(shè)計的問題總是在生產(chǎn)時才被發(fā)現(xiàn)Sq