ORACLE数据库性能优化交流课件

ORACLE数据库性能优化交流

JasonWu

目录概述系统整体架构优化设计

数据库性能最佳实践如何提高性能及排除故障我们面临的挑战IT系统随着支持用户量的增长和新业务的不断扩展，数据处理量大量增加，业务处理模式日趋复杂，必然导致主机CPU和I/O占用不断成线性增加。因此充分使用现有硬件的处理能力对于保护投资至关重要。MiddlewareDatabase

Storage客户满意度

下降问题解决率低下决策支持效率低新业务系统推出周期长

系统可用性低系统设计不够优化

系统上线后不稳定系统性能下降IT投资业务回报后果…性能优化贯穿于IT系统整个生命周期ApplicationMiddlewareandotherThirdPartysoftwareDatabaseHardwareOS/NetworkApplicationSystemComponentLevel

性能优化的范围与方法AnalysisDataCollectionFindings&RecommendationsTuningsupport系统整体架构优化设计Performancetuning每个客户机或客户应用程序均和服务器或服务程序建立“硬连接”NMX两层架构拓扑图举例N+M采用三层或多层架构拓扑图举例数据通道-客户机的连接=操作系统瘫痪

10000个连接+10000个进程X30MB的RAM+10,0000个打开的文件

10000个

客户机

500个连接+500个进程X30MB的RAM+5000个打开的文件三层或多层架构500TPMonitor10000个客户机

=操作系统正常

ICAN!两层架构不采用交易中间件200个客户机

200个专用管道DBEngine20TPS采用交易中间件交易中间件

1000个客户机

24个共享管道

DBEngine80TPS数据通道-客户机的连接各二级单位核心网元业务网元外部系统DB1DB2APP1APP2DB1DB2APP1APP2磁盘阵列磁带库磁盘阵列磁带库SAN交换机核心交换机SAN交换机SAN交换机SAN交换机核心交换机核心交换机核心交换机防火墙防火墙防火墙防火墙外网交换机外网交换机外网交换机外网交换机外网路由器外网路由器外网路由器外网路由器路由器路由器生产中心A生产中心BIT系统硬件拓扑图举例字符终端TS1界面程序服务器SM1数据库服务器A应用服务器AS1应用服务器AS2数据库服务器B界面程序服务器SM2界面程序服务器SM3浏览器PC1WEB服务器WS1WEB服务器WS2字符终端TS2字符终端TS3浏览器PC2浏览器PC3接入层界面层业务逻辑层数据层IT系统业务逻辑拓扑图举例架构设计中的其他考虑事项RAID方式选择数据库block大小选择RAC环境应用的合理部署PARTITION规划主机、网络、中间件、数据库参数架构设计中的其他考虑事项使用多块HBA卡（SAN环境）使用多路径软件映射HBA卡，提供IO负载平衡和故障切换功能ASM磁盘组要包含尽量多的磁盘，从而提供更大的I/O并发能力，避免热点块数据库性能最佳实践Performancetuning为什么我们要关心时间?对企业而言人的时间是关键问题系统性能影响企业的商业目标“时间就是金钱”性能提高意味着处理事务更快数据库响应时间性能调优的核心目标：减少响应时间Responscetime=servicetime+waittime

PerformanceisalwaysandonlyabouttimeRAC环境下不建议在线维护过多索引会引起性能急剧下降主要是索引块的分裂及对索引末端项的争用建议删除不必要索引或索引在数据加载后重建合理选择LocalIndex和GlobalIndex建议使用reverseindex（如大量insert时）索引调优Sequence建议采用cache/noorder采用nocache/order时性能最差建议加大cache值（如使用sequence上的列建索引）如果要连续和次序（当不用Sequence做时序时）使用timestamp序列调优I/O布局优化DSS系统使用大block>8K全表扫描频繁有大量读的表和索引需要批量输入更改的表和索引OLTP系统使用小block<=8K一般情况db_cache_size/db_32k_cache_sizeInterconnect网卡优化用处传递数据块（大消息）CacheFusion协议（小消息）AWR报告可以监控cachefusion繁忙程度和延迟UDP协议MTU9000不要在事务中引入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)"SQL优化在应用中不要出现hardcodecursor_sharing=exact，可提高系统性能如果在应用中出现hardcodecursor_sharing=forcecursor_sharing=similar（考虑统计值）

在一些特定平台的9i版本中，需要打patch到9204之后查sql语句的执行计划,并做调整如加入分区条件,sql执行cost降低HP-UX下相关参数调整ndd-set/dev/udpsocket_udp_rcvbuf_default1048576ndd-set/dev/udpsocket_udp_sndbuf_default1048576streams_sqmax=50000避免gccrblocklostLinux下相关参数调整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=1AIX下相关参数调整内存参数：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=655360版本以下建议设置以下参数_ENABLE_NUMA_OPTIMIZATION=FALSE_gc_affinity_time=0_gc_undo_affinity=FALSE_optimizer_squ_bottomup=FALSE_undo_autotune=FALSE如何提高性能及排除故障Performancetuning如何诊断数据库性能问题alter<sid>.log、trace文件v$session_waitAWR报告10046tracesystemdumphanganalyze数据库常见WAIT事件—LibraryCachePin

在sharedpool中的latch争用非常高的

CPU解析时间消耗大量的SQL重载大量不同版本的cursors大量的解析调用容易触发

ORA-04031错误数据库常见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优化

设置CURSOR_SHARING=SIMILAR/FORCE参数设置SESSION_CACHED_CURSORS设置CURSOR_SPACE_FOR_TIME

设置SHARED_POOL_RESERVED_SIZE

增加编译选项HOLD_CURSOR、RELEASE_CURSORALTERSYSTEMFLUSHSHARED_POOL数据库常见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优化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优化全表扫描索引低效或用不上是否索引列为序列值，考虑反向索引调整pctfree使用更小的block数据库常见WAIT事件—LogFileSync

LGWR进程写logbuffer中的信息到磁盘不够快（表象）I/O性能较差，比如RAID5COMMIT太频繁数据库常见WAIT事件—LogFileSync优化

使用更快的磁盘存放redolog满足业务的情况下改单条commit为批量处理commit满足业务的情况下使用commit_wait=nowait满足业务的情况下使用NOLOGGING数据库常见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优化

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

相关segment的extent值加大业务运行期间不要grant、revoke操作控制瞬间并发数，sleep1数据库常见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)RAC集群性能优化RAC性能分析的重点是集群互连性能的分析，包括对GCS（GlobalCacheServices）和GES（GlobalEnqueueServices）的性能分析统计数据的采集GlobalCacheServiceGlobalEnqueueServiceStatisticsGCSandGESMessagingstatistics

RAC性能优化—GCS等待事件的分析执行如下语句可分析与GCS有关的等待事件：SELECTinst_id,event,p1FILE_NUMBER,p2BLOCK_NUMBER,WAIT_TIME

FROMgv$session_waitWHEREeventin('bufferbusyglobalcr','globalcachebusy','bufferbusyglobalcache');没有上述等待事件，说明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性能优化—当前块传输（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性能优化—全局缓冲转换和获取（GlobalCacheConvertandGet）效率分析执行如下命令可进行效率全局缓冲转换和获取（GlobalCacheConvertandGet）分析：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性能优化—GES性能分析AWR报告中的GES性能抽样数据Avegloballockgettime(ms)Avegloballockconverttime(ms)Ratioofgloballockgetsvsgloballockreleases上述时间指标正常值应该<15msRatioofgloballockgetsvsgloballockreleases正常值应该在1.0左右RAC性能优化—全局缓冲区延迟服务（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性能优化—业务部署原则基本策略：以数据为中心，将数据处理本地化，尽可能减少数据访问冲突。逻辑划分策略：按业务子系统划分按部门划分物理划分策略：使用LocalManagement表空间和AutomaticSegmentSpaceManagement.RAC性能优化—实施原则RAC性能调优与单实例调优的原理和方法一样BufferCache应该更大减少不必要的parse减少锁删除没有使用的索引物理分区表的使用目的：降低相同业务数据访问冲突使用Range，List,Composite分区技术，HASH分区索引的分区，尽量使用LocalIndexRAC中表空间的设计策略为只读表设计read-only表空间将所有小的标准表或参考表设计到一个表空间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性能优化—并行处理的监控操作系统级监测

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连接的配置案例需求透明应用切换（TAF）XA事务提交完整性等功能初始化参数SID参数名参数类型参数值test1Local_listenerStringLISTENER_test1test2Local_listenerStringLISTENER_test2*remote_listenerString

RAC性能优化—RAC连接的配置案例Tnsnames.ora文件内容

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

LISTENER_test2=(ADDRESS=(PROTOCOL=TCP)(HOST=10.182.1