ORACLE數據庫性能優化交流

JasonWu

ORACLE數據庫性能優化交流

JasonWu

目錄概述系統整體架構優化設計

數據庫性能最佳實踐如何提高性能及排除故障目錄概述我們面臨的挑戰IT系統隨著支持用戶量的增長和新業務的不斷擴展，數據處理量大量增加，業務處理模式日趨復雜，必然導致主機CPU和I/O占用不斷成線性增加。因此充分使用現有硬件的處理能力對于保護投資至關重要。MiddlewareDatabase

Storage我們面臨的挑戰IT系統隨著支持用戶量的增長和新業務的不斷擴展客戶滿意度

下降問題解決率低下決策支持效率低新業務系統推出周期長

系統可用性低系統設計不夠優化

系統上線后不穩定系統性能下降IT投資業務回報后果…客戶滿意度系統可用性低系統上線后不穩定系統性能下降IT投性能優化貫穿于IT系統整個生命周期性能優化貫穿于IT系統整個生命周期ApplicationMiddlewareandotherThirdPartysoftwareDatabaseHardwareOS/NetworkApplicationSystemComponentLevel

性能優化的范圍與方法AnalysisDataCollectionFindings&RecommendationsTuningsupportApplicationMiddlewareandothe系統整體架構優化設計Performancetuning系統整體架構優化設計Performancetuning每個客戶機或客戶應用程序均和服務器或服務程序建立“硬連接”NMX兩層架構拓撲圖舉例每個客戶機或客戶應用程序均和服務器或服務程序建立“硬連接”NN+M采用三層或多層架構拓撲圖舉例N+M采用三層或多層架構拓撲圖舉例數據通道-客戶機的連接=操作系統癱瘓

10000個連接+10000個進程X30MB的RAM+10,0000個打開的文件

10000個

客戶機

500個連接+500個進程X30MB的RAM+5000個打開的文件三層或多層架構500TPMonitor10000個客戶機

=操作系統正常

ICAN!兩層架構數據通道-客戶機的連接=操作系統癱瘓10000個連接不采用交易中間件200個客戶機

200個專用管道DBEngine20TPS采用交易中間件交易中間件

1000個客戶機

24個共享管道

DBEngine80TPS數據通道-客戶機的連接不采用交易中間件200個200個DBEngine20T各二級單位核心網元業務網元外部系統DB1DB2APP1APP2DB1DB2APP1APP2磁盤陣列磁帶庫磁盤陣列磁帶庫SAN交換機核心交換機SAN交換機SAN交換機SAN交換機核心交換機核心交換機核心交換機防火墻防火墻防火墻防火墻外網交換機外網交換機外網交換機外網交換機外網路由器外網路由器外網路由器外網路由器路由器路由器生產中心A生產中心BIT系統硬件拓撲圖舉例各二級單位核心網元業務網元外部系統DB1DB2APP1APP字符終端TS1界面程序服務器SM1數據庫服務器A應用服務器AS1應用服務器AS2數據庫服務器B界面程序服務器SM2界面程序服務器SM3瀏覽器PC1WEB服務器WS1WEB服務器WS2字符終端TS2字符終端TS3瀏覽器PC2瀏覽器PC3接入層界面層業務邏輯層數據層IT系統業務邏輯拓撲圖舉例字符終端界面程序服務器SM1數據庫服務器應用服務器應用服務器架構設計中的其他考慮事項RAID方式選擇數據庫block大小選擇RAC環境應用的合理部署PARTITION規劃主機、網絡、中間件、數據庫參數架構設計中的其他考慮事項RAID方式選擇架構設計中的其他考慮事項使用多塊HBA卡（SAN環境）使用多路徑軟件映射HBA卡，提供IO負載平衡和故障切換功能ASM磁盤組要包含盡量多的磁盤，從而提供更大的I/O并發能力，避免熱點塊架構設計中的其他考慮事項使用多塊HBA卡（SAN環境）數據庫性能最佳實踐Performancetuning數據庫性能最佳實踐Performancetuning為什么我們要關心時間?對企業而言人的時間是關鍵問題系統性能影響企業的商業目標“時間就是金錢”性能提高意味著處理事務更快為什么我們要關心時間?對企業而言人的時間是關鍵問題數據庫響應時間性能調優的核心目標：減少響應時間Responscetime=servicetime+waittime

Performanceisalwaysandonlyabouttime數據庫響應時間PerformanceisalwaysaRAC環境下不建議在線維護過多索引會引起性能急劇下降主要是索引塊的分裂及對索引末端項的爭用建議刪除不必要索引或索引在數據加載后重建合理選擇LocalIndex和GlobalIndex建議使用reverseindex（如大量insert時）索引調優RAC環境下不建議在線維護過多索引索引調優Sequence建議采用cache/noorder采用nocache/order時性能最差建議加大cache值（如使用sequence上的列建索引）如果要連續和次序（當不用Sequence做時序時）使用timestamp序列調優Sequence建議采用cache/noorder序列調優I/O布局優化DSS系統使用大block>8K全表掃描頻繁有大量讀的表和索引需要批量輸入更改的表和索引OLTP系統使用小block<=8K一般情況db_cache_size/db_32k_cache_sizeI/O布局優化DSS系統使用大block>8KInterconnect網卡優化用處傳遞數據塊（大消息）CacheFusion協議（小消息）AWR報告可以監控cachefusion繁忙程度和延遲UDP協議MTU9000Interconnect網卡優化用處不要在事務中引入Trigger建議相應的功能在事務中實現非報表統計業務不要出現全表掃描開發注意事項不要在事務中引入Trigger開發注意事項編譯選項優化針對編譯的pro*c代碼，可在編譯選項里面加上hold_cursor=yes

和prefetch=200

例

PROC_PROFLAGS=ireclen=132oreclen=132\ select_error=no\ mode=ansi\ userid=$(USERID)\ SQLCHECK=SEMANTICS\ PARSE=PARTIAL\ HOLD_CURSOR=YES\ PREFETCH=200\ RELEASE_CURSOR=NO\

MAXOPENCURSORS=60\ define=USE_PRO_C\ def_sqlcode=YES\ CODE=CPP\ CPP_SUFFIX=cpp\ CLOSE_ON_COMMIT=NO\ threads=YES\ CPOOL=YES\# unsafe_null=YES\#只支持oracle的數據庫，要求mode=oracle include="$(ORAClEHOME)\precomp\public"\ include="$(ORAClEHOME)\oci\include"\ include="$(VC_HOME)"\ include="$(PROJECT_HOME)"編譯選項優化針對編譯的pro*c代碼，可在編譯選項里面加上hSQL優化在應用中不要出現hardcodecursor_sharing=exact，可提高系統性能如果在應用中出現hardcodecursor_sharing=forcecursor_sharing=similar（考慮統計值）

在一些特定平臺的9i版本中，需要打patch到9204之后查sql語句的執行計劃,并做調整如加入分區條件,sql執行cost降低SQL優化在應用中不要出現hardcodeHP-UX下相關參數調整ndd-set/dev/udpsocket_udp_rcvbuf_default1048576ndd-set/dev/udpsocket_udp_sndbuf_default1048576streams_sqmax=50000避免gccrblocklostHP-UX下相關參數調整ndd-set/dev/udpLinux下相關參數調整net.core.rmem_max=262144net.core.wmem_max=262144net.core.rmem_default=262144net.core.wmem_default=262144net.ipv4.tcp_keepalive_probes=2net.ipv4.tcp_keepalive_intvl=30net.ipv4.tcp_keepalive_time=3000net.ipv4.tcp_retries2=5net.ipv4.tcp_syn_retries=1Linux下相關參數調整net.core.rmem_max=AIX下相關參數調整內存參數：minperm%=3

maxperm%=90

maxclient%=90

lru_file_repage=0環境變量：exportAIXTHREAD_SCOPE=S網絡參數：ipqmaxlen=512

rfc1323=1

tcp_recvspace=65536

tcp_sendspace=65536

udp_sendspace=65536

udp_recvspace=655360AIX下相關參數調整內存參數：網絡參數：版本以下建議設置以下參數_ENABLE_NUMA_OPTIMIZATION=FALSE_gc_affinity_time=0_gc_undo_affinity=FALSE_optimizer_squ_bottomup=FALSE_undo_autotune=FALSE版本以下建議設置以下參數_ENABLE_NU如何提高性能及排除故障Performancetuning如何提高性能及排除故障Performancetuning如何診斷數據庫性能問題alter<sid>.log、trace文件v$session_waitAWR報告10046tracesystemdumphanganalyze如何診斷數據庫性能問題alter<sid>.log、trac數據庫常見WAIT事件—LibraryCachePin

在sharedpool中的latch爭用非常高的CPU解析時間消耗大量的SQL重載大量不同版本的cursors大量的解析調用容易觸發ORA-04031錯誤數據庫常見WAIT事件—LibraryCachePin數據庫常見WAIT事件—LibraryCachePin優化

找出硬解析SQL進行修改，使用綁定變量：SELECTsubstr(sql_text,1,40)"SQL",count(*),sum(executions)"TotExecs"FROMv$sqlareaWHEREexecutions<5GROUPBYsubstr(sql_text,1,40)HAVINGcount(*)>30ORDERBY2;業務期間避免TRUNCATE、ANALYZE、DBMS_STATS.GATHER_XXX數據庫常見WAIT事件—LibraryCachePin優數據庫常見WAIT事件—LibraryCachePin優化

設置CURSOR_SHARING=SIMILAR/FORCE參數設置SESSION_CACHED_CURSORS設置CURSOR_SPACE_FOR_TIME

設置SHARED_POOL_RESERVED_SIZE

增加編譯選項HOLD_CURSOR、RELEASE_CURSORALTERSYSTEMFLUSHSHARED_POOL數據庫常見WAIT事件—LibraryCachePin優數據庫常見WAIT事件—BufferBusywaits優化熱快爭用V$session_waitp1,p2,p3select

CHILD#

"cCHILD"

,

ADDR

"sADDR"

,

GETS

"sGETS"

,

MISSES

"sMISSES"

,

SLEEPS

"sSLEEPS"

from

v$latch_children

where

name

=

'cache

buffers

chains'

order

by

5,

1,

2,

3;數據庫常見WAIT事件—BufferBusywaits優數據庫常見WAIT事件—BufferBusywaits優化column

segment_name

format

a35

select

/*+

RULE

*/

e.owner

||'.'||

e.segment_name

segment_name,

e.extent_id

extent#,

x.dbablk

-

e.block_id

+

1

block#,

x.tch,

l.child#

from

sys.v$latch_children

l,

sys.x$bh

x,

sys.dba_extents

e

where

x.hladdr

=

'&ADDR'

and

e.file_id

=

x.file#

and

x.hladdr

=

l.addr

and

x.dbablk

between

e.block_id

and

e.block_id

+

e.blocks

-1

order

by

x.tch

desc

;數據庫常見WAIT事件—BufferBusywaits優數據庫常見WAIT事件—BufferBusywaits優化全表掃描索引低效或用不上是否索引列為序列值，考慮反向索引調整pctfree使用更小的block數據庫常見WAIT事件—BufferBusywaits優數據庫常見WAIT事件—LogFileSync

LGWR進程寫logbuffer中的信息到磁盤不夠快（表象）I/O性能較差，比如RAID5COMMIT太頻繁數據庫常見WAIT事件—LogFileSync

LGWR數據庫常見WAIT事件—LogFileSync優化

使用更快的磁盤存放redolog滿足業務的情況下改單條commit為批量處理commit滿足業務的情況下使用commit_wait=nowait滿足業務的情況下使用NOLOGGING數據庫常見WAIT事件—LogFileSync優化

使用數據庫常見WAIT事件—ROWCACHELOCK/CURSOR:PINSWAITONX

...>>>WAITEDTOOLONGFORAROWCACHEENQUEUELOCK!<<<rowcacheenqueue:session:70000001b542d48,mode:N,request:Srowcacheparentobject:address=700000036f27628cid=0(dc_tablespaces)hash=a6840aa5typ=9transaction=0flags=00008000...DC_TABLESPACESDC_SEQUENCESDC_USERSDC_OBJECT_IDSDC_SEGMENTSDC_ROLLBACK_SEGMENTSDC_TABLE_SCNS數據庫常見WAIT事件—ROWCACHELOCK/CU數據庫常見WAIT事件—ROWCACHELOCK優化

設置sys.AUDSES$序列cache值到50000設置sys.ORA_TQ_BASE序列cache值到50000

相關segment的extent值加大業務運行期間不要grant、revoke操作控制瞬間并發數，sleep1數據庫常見WAIT事件—ROWCACHELOCK優化

數據庫常見WAIT事件—enqueueTX==>TransactionenqueuelockUS==>UndosegmentDDLlockTS==>Newblockallocationenqueuelock(ID2=1))CI==>Cross-instancefunctioninvocationinstancelockDX==>DistributedtransactionentrylockTM==>DMLenqueuelockTA==>GenericenqueuelockTD==>DDLenqueuelockTE==>Extend-segmentenqueuelockHW==>SpacemanagementoperationsonaspecificsegmentlockTO==>TemporaryTableObjectEnqueueTT==>TemporarytableenqueuelockTS==>Temporarysegmentenqueuelock(ID2=0)CF==>ControlfileschemaglobalenqueuelockDL==>DirectloaderparallelindexcreateDR==>DistributedrecoveryprocesslockIR==>InstancerecoveryserializationglobalenqueuelockIV==>LibrarycacheinvalidationinstancelockPI==>ParalleloperationlockSN==>SequencenumberinstancelockSQ==>SequencenumberenqueuelockST==>SpacetransactionenqueuelockQA==>Rowcacheinstancelock(A=cache)數據庫常見WAIT事件—enqueueTX==>TransaRAC集群性能優化RAC性能分析的重點是集群互連性能的分析，包括對GCS（GlobalCacheServices）和GES（GlobalEnqueueServices）的性能分析統計數據的采集GlobalCacheServiceGlobalEnqueueServiceStatisticsGCSandGESMessagingstatistics

RAC集群性能優化RAC性能分析的重點是集群互連性能的分析，RAC性能優化—GCS等待事件的分析執行如下語句可分析與GCS有關的等待事件：SELECTinst_id,event,p1FILE_NUMBER,p2BLOCK_NUMBER,WAIT_TIME

FROMgv$session_waitWHEREeventin('bufferbusyglobalcr','globalcachebusy','bufferbusyglobalcache');沒有上述等待事件，說明GCS的數據傳輸效率能滿足需求。RAC性能優化—GCS等待事件的分析執行如下語句可分析與RAC性能優化—一致性讀（CR）效率分析執行如下命令可進行一致性讀（CR）效率分析：SELECTb1.inst_id,b2.value"RECEIVED",b1.value"RECEIVETIME",((b1.value/b2.value)*10)"AVGRECEIVETIME(ms)"FROMgv$sysstatb1,gv$sysstatb2WHERE=‘globalcachecrblockreceivetime’AND=‘globalcachecrblocksreceived’ANDb1.inst_id=b2.inst_id;

AVGRECEIVETIME(ms)低于10ms表示一致性讀（CR）效率正常。RAC性能優化—一致性讀（CR）效率分析執行如下命令可RAC性能優化—當前塊傳輸（CurrentBlockTransfer）效率分析執行如下命令可進行當前塊傳輸（CurrentBlockTransfer）效率分析：SELECTb1.inst_id,b2.value"RECEIVED",b1.value"RECEIVETIME",((b1.value/b2.value)*10)"AVGRECEIVETIME(ms)"FROMgv$sysstatb1,gv$sysstatb2WHERE='globalcachecurrentblockreceivetime‘

AND='globalcachecurrentblocksreceived‘

ANDb1.inst_id=b2.inst_id;AVGRECEIVETIME(ms)低于15ms表示當前塊傳輸效率正常。RAC性能優化—當前塊傳輸（CurrentBlockTrRAC性能優化—全局緩沖轉換和獲?。℅lobalCacheConvertandGet）效率分析執行如下命令可進行效率全局緩沖轉換和獲?。℅lobalCacheConvertandGet）分析：SELECTA.inst_id"Instance",A.value/B.value"AvgCacheConv.Time",C.value/D.value"AvgCacheGetTime",E.value"GCConvertTimeouts"FROMGV$SYSSTATA,GV$SYSSTATB,GV$SYSSTATC,GV$SYSSTATD,GV$SYSSTATEWHEREA.name='globalcacheconverttime'ANDB.name='globalcacheconverts'AND='globalcachegettime'ANDD.name='globalcachegets'ANDE.name='globalcacheconverttimeouts'ANDB.inst_id=A.inst_idANDC.inst_id=A.inst_idANDD.inst_id=A.inst_idANDE.inst_id=A.inst_idORDERBYA.inst_id;

AvgCacheConv.Time(ms)和AvgCacheGetTime（ms）低于10ms表示全局緩沖轉換和獲取（GlobalCacheConvertandGet）效率正常。RAC性能優化—全局緩沖轉換和獲?。℅lobalCacheRAC性能優化—GES性能分析AWR報告中的GES性能抽樣數據Avegloballockgettime(ms)Avegloballockconverttime(ms)Ratioofgloballockgetsvsgloballockreleases上述時間指標正常值應該<15msRatioofgloballockgetsvsgloballockreleases正常值應該在1.0左右RAC性能優化—GES性能分析AWR報告中的GERAC性能優化—全局緩沖區延遲服務（GlobalCacheDefers）性能分析執行如下命令可進行全局緩沖區延遲服務（GlobalCacheDefers）性能分析：SELECTa.inst_id"Instance",a.value"Defers",b.value"CurrentBlksServed",(a.value)/b.value"CurrentBlkServiceTime"FROMGV$SYSSTATA,GV$SYSSTATBWHEREA.name='globalcachedefers'ANDB.name='globalcachecurrentblocksserved'ANDB.inst_id=A.inst_idORDERBYa.inst_id;上述指標小于0.3為正常值。該指標值高表示事例間由于數據訪問集中，導致全局緩沖區出現大量延遲服務。RAC性能優化—全局緩沖區延遲服務（GlobalCacheRAC性能優化—業務部署原則基本策略：以數據為中心，將數據處理本地化，盡可能減少數據訪問沖突。邏輯劃分策略：按業務子系統劃分按部門劃分物理劃分策略：使用LocalManagement表空間和AutomaticSegmentSpaceManagement.RAC性能優化—業務部署原則基本策略：以數據為中心，將數據RAC性能優化—實施原則RAC性能調優與單實例調優的原理和方法一樣BufferCache應該更大減少不必要的parse減少鎖刪除沒有使用的索引物理分區表的使用目的：降低相同業務數據訪問沖突使用Range，List,Composite分區技術，HASH分區索引的分區，盡量使用LocalIndexRAC中表空間的設計策略為只讀表設計read-only表空間將所有小的標準表或參考表設計到一個表空間RAC性能優化—實施原則RAC性能調優與單實例調優的原理和方RAC性能優化—并行處理的控制

Oracle可自動進行節點間的并行處理和負載均衡。Instance_groups定義一個或多個可進行RAC實例間并行處理的實例組。該參數是靜態參數sid|1-32|.INSTANCE_GROUPS=‘base,game’Parallel_Instance_group定義指定的一個或多個可進行RAC實例間并行處理的實例。該參數是動態參數ALTERSESSIONSETPARALLEL_INSTANCE_GROUP='sales';Instance_groups和Parallel_Instance_group必須匹配，否則系統報錯

RAC性能優化—并行處理的控制Oracle可自動進行節點間RAC性能優化—并行處理的監控操作系統級監測

topas,nmon,glance,top,vmstat,iostat,sar數據庫級監測select*fromgv$px_sessionorderbyinst_id;selectinst_id,count(*)fromgv$px_sessiongroupbyinst_id;select*fromgv$px_sesstatorderbyinst_id;select*fromgv$px_processorderbyinst_id;select*fromgv$px_process_sysstatorderbyinst_id;select*fromgV$PX_PROCESS_SYSSTATorderbyinst_id;select*fromgv$pq_sesstat;RAC性能優化—并行處理的監控操作系統級監測RAC性能優化—RAC連接的配置案例需求透明應用切換（TAF）XA事務提交完整性等功能初始化參數SID參數名參數類型參數值test1Local_listenerStringLISTENER_test1test2Local_listenerStringLISTENER_test2*remote_listenerString

RAC性能優化—RAC連接的配置案例需求SID參數名參數RAC性能優化—RAC連接的配置案例Tnsnames.ora文件內容

LISTENER_test1=(ADDRESS=(PROTOCOL=TCP)(HOST=0)(PORT=1521))

LISTENER_test2=(ADDRESS=(PROTOCOL=TCP)(HOST=2)(PORT=1521))

test1=(DESCRIPTI