ORACLE數(shù)據(jù)庫性能優(yōu)化交流課件

ORACLE數(shù)據(jù)庫性能優(yōu)化交流

JasonWu

ORACLE數(shù)據(jù)庫性能優(yōu)化交流

JasonWu

目錄概述系統(tǒng)整體架構優(yōu)化設計

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

Storage我們面臨的挑戰(zhàn)IT系統(tǒng)隨著支持用戶量的增長和新業(yè)務的不斷擴展客戶滿意度

下降問題解決率低下決策支持效率低新業(yè)務系統(tǒng)推出周期長

系統(tǒng)可用性低系統(tǒng)設計不夠優(yōu)化

系統(tǒng)上線后不穩(wěn)定系統(tǒng)性能下降IT投資業(yè)務回報后果…客戶滿意度系統(tǒng)可用性低系統(tǒng)上線后不穩(wěn)定系統(tǒng)性能下降IT投性能優(yōu)化貫穿于IT系統(tǒng)整個生命周期性能優(yōu)化貫穿于IT系統(tǒng)整個生命周期ApplicationMiddlewareandotherThirdPartysoftwareDatabaseHardwareOS/NetworkApplicationSystemComponentLevel

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

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

10000個

客戶機

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

=操作系統(tǒng)正常

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

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

1000個客戶機

24個共享管道

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

Performanceisalwaysandonlyabouttime數(shù)據(jù)庫響應時間PerformanceisalwaysaRAC環(huán)境下不建議在線維護過多索引會引起性能急劇下降主要是索引塊的分裂及對索引末端項的爭用建議刪除不必要索引或索引在數(shù)據(jù)加載后重建合理選擇LocalIndex和GlobalIndex建議使用reverseindex（如大量insert時）索引調優(yōu)RAC環(huán)境下不建議在線維護過多索引索引調優(yōu)Sequence建議采用cache/noorder采用nocache/order時性能最差建議加大cache值（如使用sequence上的列建索引）如果要連續(xù)和次序（當不用Sequence做時序時）使用timestamp序列調優(yōu)Sequence建議采用cache/noorder序列調優(yōu)I/O布局優(yōu)化DSS系統(tǒng)使用大block>8K全表掃描頻繁有大量讀的表和索引需要批量輸入更改的表和索引OLTP系統(tǒng)使用小block<=8K一般情況db_cache_size/db_32k_cache_sizeI/O布局優(yōu)化DSS系統(tǒng)使用大block>8KInterconnect網卡優(yōu)化用處傳遞數(shù)據(jù)塊（大消息）CacheFusion協(xié)議（小消息）AWR報告可以監(jiān)控cachefusion繁忙程度和延遲UDP協(xié)議MTU9000Interconnect網卡優(yōu)化用處不要在事務中引入Trigger建議相應的功能在事務中實現(xiàn)非報表統(tǒng)計業(yè)務不要出現(xiàn)全表掃描開發(fā)注意事項不要在事務中引入Trigger開發(fā)注意事項編譯選項優(yōu)化針對編譯的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的數(shù)據(jù)庫，要求mode=oracle include="$(ORAClEHOME)\precomp\public"\ include="$(ORAClEHOME)\oci\include"\ include="$(VC_HOME)"\ include="$(PROJECT_HOME)"編譯選項優(yōu)化針對編譯的pro*c代碼，可在編譯選項里面加上hSQL優(yōu)化在應用中不要出現(xiàn)hardcodecursor_sharing=exact，可提高系統(tǒng)性能如果在應用中出現(xiàn)hardcodecursor_sharing=forcecursor_sharing=similar（考慮統(tǒng)計值）

在一些特定平臺的9i版本中，需要打patch到9204之后查sql語句的執(zhí)行計劃,并做調整如加入分區(qū)條件,sql執(zhí)行cost降低SQL優(yōu)化在應用中不要出現(xiàn)hardcodeHP-UX下相關參數(shù)調整ndd-set/dev/udpsocket_udp_rcvbuf_default1048576ndd-set/dev/udpsocket_udp_sndbuf_default1048576streams_sqmax=50000避免gccrblocklostHP-UX下相關參數(shù)調整ndd-set/dev/udpLinux下相關參數(shù)調整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下相關參數(shù)調整net.core.rmem_max=AIX下相關參數(shù)調整內存參數(shù)：minperm%=3

maxperm%=90

maxclient%=90

lru_file_repage=0環(huán)境變量：exportAIXTHREAD_SCOPE=S網絡參數(shù)：ipqmaxlen=512

rfc1323=1

tcp_recvspace=65536

tcp_sendspace=65536

udp_sendspace=65536

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

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

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

設置CURSOR_SHARING=SIMILAR/FORCE參數(shù)設置SESSION_CACHED_CURSORS設置CURSOR_SPACE_FOR_TIME

設置SHARED_POOL_RESERVED_SIZE

增加編譯選項HOLD_CURSOR、RELEASE_CURSORALTERSYSTEMFLUSHSHARED_POOL數(shù)據(jù)庫常見WAIT事件—LibraryCachePin優(yōu)數(shù)據(jù)庫常見WAIT事件—BufferBusywaits優(yōu)化熱快爭用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;數(shù)據(jù)庫常見WAIT事件—BufferBusywaits優(yōu)數(shù)據(jù)庫常見WAIT事件—BufferBusywaits優(yōu)化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

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

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

LGWR數(shù)據(jù)庫常見WAIT事件—LogFileSync優(yōu)化

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

使用數(shù)據(jù)庫常見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數(shù)據(jù)庫常見WAIT事件—ROWCACHELOCK/CU數(shù)據(jù)庫常見WAIT事件—ROWCACHELOCK優(yōu)化

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

相關segment的extent值加大業(yè)務運行期間不要grant、revoke操作控制瞬間并發(fā)數(shù)，sleep1數(shù)據(jù)庫常見WAIT事件—ROWCACHELOCK優(yōu)化

數(shù)據(jù)庫常見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)數(shù)據(jù)庫常見WAIT事件—enqueueTX==>TransaRAC集群性能優(yōu)化RAC性能分析的重點是集群互連性能的分析，包括對GCS（GlobalCacheServices）和GES（GlobalEnqueueServices）的性能分析統(tǒng)計數(shù)據(jù)的采集GlobalCacheServiceGlobalEnqueueServiceStatisticsGCSandGESMessagingstatistics

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

FROMgv$session_waitWHEREeventin('bufferbusyglobalcr','globalcachebusy','bufferbusyglobalcache');沒有上述等待事件，說明GCS的數(shù)據(jù)傳輸效率能滿足需求。RAC性能優(yōu)化—GCS等待事件的分析執(zhí)行如下語句可分析與RAC性能優(yōu)化—一致性讀（CR）效率分析執(zhí)行如下命令可進行一致性讀（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性能優(yōu)化—一致性讀（CR）效率分析執(zhí)行如下命令可RAC性能優(yōu)化—當前塊傳輸（CurrentBlockTransfer）效率分析執(zhí)行如下命令可進行當前塊傳輸（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性能優(yōu)化—當前塊傳輸（CurrentBlockTrRAC性能優(yōu)化—全局緩沖轉換和獲?。℅lobalCacheConvertandGet）效率分析執(zhí)行如下命令可進行效率全局緩沖轉換和獲?。℅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表示全局緩沖轉換和獲?。℅lobalCacheConvertandGet）效率正常。RAC性能優(yōu)化—全局緩沖轉換和獲?。℅lobalCacheRAC性能優(yōu)化—GES性能分析AWR報告中的GES性能抽樣數(shù)據(jù)Avegloballockgettime(ms)Avegloballockconverttime(ms)Ratioofgloballockgetsvsgloballockreleases上述時間指標正常值應該<15msRatioofgloballockgetsvsgloballockreleases正常值應該在1.0左右RAC性能優(yōu)化—GES性能分析AWR報告中的GERAC性能優(yōu)化—全局緩沖區(qū)延遲服務（GlobalCacheDefers）性能分析執(zhí)行如下命令可進行全局緩沖區(qū)延遲服務（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為正常值。該指標值高表示事例間由于數(shù)據(jù)訪問集中，導致全局緩沖區(qū)出現(xiàn)大量延遲服務。RAC性能優(yōu)化—全局緩沖區(qū)延遲服務（GlobalCacheRAC性能優(yōu)化—業(yè)務部署原則基本策略：以數(shù)據(jù)為中心，將數(shù)據(jù)處理本地化，盡可能減少數(shù)據(jù)訪問沖突。邏輯劃分策略：按業(yè)務子系統(tǒng)劃分按部門劃分物理劃分策略：使用LocalManagement表空間和AutomaticSegmentSpaceManagement.RAC性能優(yōu)化—業(yè)務部署原則基本策略：以數(shù)據(jù)為中心，將數(shù)據(jù)RAC性能優(yōu)化—實施原則RAC性能調優(yōu)與單實例調優(yōu)的原理和方法一樣BufferCache應該更大減少不必要的parse減少鎖刪除沒有使用的索引物理分區(qū)表的使用目的：降低相同業(yè)務數(shù)據(jù)訪問沖突使用Range，List,Composite分區(qū)技術，HASH分區(qū)索引的分區(qū)，盡量使用LocalIndexRAC中表空間的設計策略為只讀表設計read-only表空間將所有小的標準表或參考表設計到一個表空間RAC性能優(yōu)化—實施原則RAC性能調優(yōu)與單實例調優(yōu)的原理和方RAC性能優(yōu)化—并行處理的控制

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

RAC性能優(yōu)化—并行處理的控制Oracle可自動進行節(jié)點間RAC性能優(yōu)化—并行處理的監(jiān)控操作系統(tǒng)級監(jiān)測

topas,nmon,glance,top,vmstat,iostat,sar數(shù)據(jù)庫級監(jiān)測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性能優(yōu)化—并行處理的監(jiān)控操作系統(tǒng)級監(jiān)測RAC性能優(yōu)化—RAC連接的配置案例需求透明應用切換（TAF）XA事務提交完整性等功能初始化參數(shù)SID參數(shù)名參數(shù)類型參數(shù)值test1Local_listenerStringLISTENER_test1test2Local_listenerStringLISTENER_test2*remote_listenerString

RAC性能優(yōu)化—RAC連接的配置案例需求SID參數(shù)名參數(shù)RAC性能優(yōu)化—RAC連接的配置案例Tnsnames.ora文件內容

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

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

test1=(DESCRIPTI