《ORACLE数据库性能优化交流课件.ppt》由会员分享，可在线阅读，更多相关《ORACLE数据库性能优化交流课件.ppt（63页珍藏版）》请在三一办公上搜索。
1、ORACLE数据库性能优化交流Jason Wu,目录,概述系统整体架构优化设计 数据库性能最佳实践如何提高性能及排除故障,我们面临的挑战,IT系统随着支持用户量的增长和新业务的不断扩展，数据处理量大量增加，业务处理模式日趋复杂，必然导致主机CPU和I/O占用不断成线性增加。因此充分使用现有硬件的处理能力对于保护投资至关重要。,后果,性能优化贯穿于IT系统整个生命周期,性能优化的范围与方法,系统整体架构优化设计,Performance tuning,每个客户机或客户应用程序均和服务器或服务程序建立“硬连接”,N,M,X,两层架构拓扑图举例,N,+,M,采用三层或多层架构拓扑图举例,数据通道-客户
2、机的连接,两层架构,数据通道-客户机的连接,IT系统硬件拓扑图举例,字符终端TS1,界面程序服务器SM1,数据库服务器A,应用服务器AS1,应用服务器AS2,数据库服务器B,界面程序服务器SM2,界面程序服务器SM3,浏览器PC1,WEB 服务器WS1,WEB服务器WS2,字符终端TS2,字符终端TS3,浏览器PC2,浏览器PC3,接入层,界面层,业务逻辑层,数据层,IT系统业务逻辑拓扑图举例,架构设计中的其他考虑事项,RAID方式选择数据库block大小选择RAC环境应用的合理部署PARTITION规划主机、网络、中间件、数据库参数,架构设计中的其他考虑事项,使用多块HBA卡（SAN环境）使
3、用多路径软件映射HBA卡，提供IO负载平衡和故障切换功能ASM磁盘组要包含尽量多的磁盘，从而提供更大的I/O并发能力，避免热点块,数据库性能最佳实践,Performance tuning,为什么我们要关心时间?,对企业而言人的时间是关键问题系统性能影响企业的商业目标“时间就是金钱”性能提高意味着处理事务更快,数据库响应时间,性能调优的核心目标：减少响应时间Responsce time=service time+wait time,Performance is always and only about time,RAC环境下不建议在线维护过多索引会引起性能急剧下降主要是索引块的分裂及对索引末端
4、项的争用建议删除不必要索引或索引在数据加载后重建合理选择Local Index和Global Index建议使用reverse index（如大量insert时）,索引调优,Sequence 建议采用cache/noorder采用nocache/order时性能最差建议加大cache值（如使用sequence上的列建索引）如果要连续和次序（当不用Sequence做时序时）使用timestamp,序列调优,I/O布局优化,DSS系统使用大block8K全表扫描频繁有大量读的表和索引需要批量输入更改的表和索引OLTP系统使用小block=8K一般情况db_cache_size/db_32k_cac
5、he_size,Interconnect网卡优化,用处传递数据块（大消息）Cache Fusion协议（小消息）AWR报告可以监控cache fusion繁忙程度和延迟UDP协议MTU 9000,不要在事务中引入Trigger建议相应的功能在事务中实现非报表统计业务不要出现全表扫描,开发注意事项,编译选项优化,针对编译的pro*c代码，可在编译选项里面加上hold_cursor=yes 和prefetch=200 例PROC_PROFLAGS=ireclen=132 oreclen=132 select_error=no mode=ansi userid=$(USERID)SQLCHECK=S
6、EMANTICS PARSE=PARTIAL HOLD_CURSOR=YES PREFETCH=200RELEASE_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=oracleinclude=$(ORAClEHOME)precomppublic include=$(ORAClEHOME)ociinclude inclu
7、de=$(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/udp socket_udp_rcvbuf_default 1048576ndd-set/dev/udp socke
8、t_udp_sndbuf_default 1048576streams_sqmax=50000 避免gc cr block lost,Linux下相关参数调整,net.core.rmem_max=262144 net.core.wmem_max=262144 net.core.rmem_default=262144 net.core.wmem_default=262144 net.ipv4.tcp_keepalive_probes=2net.ipv4.tcp_keepalive_intvl=30net.ipv4.tcp_keepalive_time=3000net.ipv4.tcp_retri
9、es2=5net.ipv4.tcp_syn_retries=1,AIX下相关参数调整,内存参数：minperm%=3maxperm%=90maxclient%=90lru_file_repage=0环境变量：export AIXTHREAD_SCOPE=S,网络参数：ipqmaxlen=512rfc1323=1tcp_recvspace=65536tcp_sendspace=65536udp_sendspace=65536udp_recvspace=655360,10.2.0.5版本以下建议设置以下参数,_ENABLE_NUMA_OPTIMIZATION=FALSE_gc_affinity_t
10、ime=0_gc_undo_affinity=FALSE_optimizer_squ_bottomup=FALSE_undo_autotune=FALSE,如何提高性能及排除故障,Performance tuning,如何诊断数据库性能问题,alter.log、trace文件v$session_waitAWR报告10046 tracesystem dumphanganalyze,数据库常见WAIT事件Library Cache Pin,在shared pool中的latch争用非常高的 CPU解析时间消耗大量的SQL重载大量不同版本的cursors大量的解析调用容易触发 ORA-04031 错
11、误,数据库常见WAIT事件Library Cache Pin优化,找出硬解析SQL进行修改，使用绑定变量：SELECT substr(sql_text,1,40)SQL,count(*),sum(executions)TotExecs FROM v$sqlarea WHERE executions 30 ORDER BY 2;业务期间避免TRUNCATE、ANALYZE、DBMS_STATS.GATHER_XXX,数据库常见WAIT事件Library Cache Pin优化,设置CURSOR_SHARING=SIMILAR/FORCE参数设置SESSION_CACHED_CURSORS 设置C
12、URSOR_SPACE_FOR_TIME 设置SHARED_POOL_RESERVED_SIZE 增加编译选项HOLD_CURSOR、RELEASE_CURSOR ALTER SYSTEM FLUSH SHARED_POOL,数据库常见WAIT事件Buffer Busy waits优化,热快争用V$session_wait p1,p2,p3selectCHILD#cCHILD,ADDRsADDR,GETSsGETS,MISSESsMISSES,SLEEPSsSLEEPS fromv$latch_children wherename=cachebufferschains orderby5,1,2
13、,3;,数据库常见WAIT事件Buffer Busy waits优化,columnsegment_nameformata35 select/*+RULE*/e.owner|.|e.segment_namesegment_name,e.extent_idextent#,x.dbablk-e.block_id+1block#,x.tch,l.child#from sys.v$latch_childrenl,sys.x$bhx,sys.dba_extentse where x.hladdr=,数据库常见WAIT事件Buffer Busy waits优化,全表扫描索引低效或用不上是否索引列为序列值，考
14、虑反向索引调整pctfree使用更小的block,数据库常见WAIT事件Log File Sync,LGWR进程写log buffer中的信息到磁盘不够快（表象）I/O性能较差，比如RAID 5COMMIT太频繁,数据库常见WAIT事件Log File Sync优化,使用更快的磁盘存放redo log满足业务的情况下改单条commit为批量处理commit满足业务的情况下使用commit_wait=nowait满足业务的情况下使用NOLOGGING,数据库常见WAIT事件ROW CACHE LOCK/CURSOR:PIN S WAIT ON X,.WAITED TOO LONG FOR A R
15、OW CACHE ENQUEUE LOCK!row cache enqueue:session:70000001b542d48,mode:N,request:S row cache parent object:address=700000036f27628 cid=0(dc_tablespaces)hash=a6840aa5 typ=9 transaction=0 flags=00008000.,DC_TABLESPACESDC_SEQUENCESDC_USERSDC_OBJECT_IDSDC_SEGMENTSDC_ROLLBACK_SEGMENTSDC_TABLE_SCNS,数据库常见WAI
16、T事件ROW CACHE LOCK优化,设置sys.AUDSES$序列cache值到50000设置sys.ORA_TQ_BASE序列cache值到50000 相关segment的extent值加大业务运行期间不要grant、revoke操作控制瞬间并发数，sleep 1,数据库常见WAIT事件enqueue,TX=Transaction enqueue lockUS=Undo segment DDL lockTS=New block allocation enqueue lock(ID2=1)CI=Cross-instance function invocation instance lock
17、DX=Distributed transaction entry lockTM=DML enqueue lockTA=Generic enqueue lockTD=DDL enqueue lockTE=Extend-segment enqueue lockHW=Space management operations on a specific segment lockTO=Temporary Table Object EnqueueTT=Temporary table enqueue lockTS=Temporary segment enqueue lock(ID2=0)CF=Control
18、file schema global enqueue lockDL=Direct loader parallel index createDR=Distributed recovery process lockIR=Instance recovery serialization global enqueue lockIV=Library cache invalidation instance lockPI=Parallel operation lockSN=Sequence number instance lockSQ=Sequence number enqueue lockST=Space
19、transaction enqueue lockQA=Row cache instance lock(A=cache),RAC集群性能优化,RAC性能分析的重点是集群互连性能的分析，包括对GCS（Global Cache Services）和GES（Global Enqueue Services）的性能分析 统计数据的采集Global Cache Service Global Enqueue Service Statistics GCS and GES Messaging statistics,RAC性能优化GCS等待事件的分析,执行如下语句可分析与GCS有关的等待事件：SELECT inst
20、_id,event,p1 FILE_NUMBER,p2 BLOCK_NUMBER,WAIT_TIME FROM gv$session_waitWHERE event in(buffer busy global cr,global cache busy,buffer busy global cache);没有上述等待事件，说明GCS的数据传输效率能满足需求。,RAC性能优化一致性读（CR）效率分析,执行如下命令可进行一致性读（CR）效率分析：SELECT b1.inst_id,b2.value RECEIVED,b1.value RECEIVE TIME,(b1.value/b2.value)*
21、10)AVG RECEIVE TIME(ms)FROM gv$sysstat b1,gv$sysstat b2WHERE b1.name=global cache cr block receive time AND b2.name=global cache cr blocks received AND b1.inst_id=b2.inst_id;AVG RECEIVE TIME(ms)低于10ms表示一致性读（CR）效率正常。,RAC性能优化当前块传输（Current Block Transfer）效率分析,执行如下命令可进行当前块传输（Current Block Transfer）效率分析：
22、SELECT b1.inst_id,b2.value RECEIVED,b1.value RECEIVE TIME,(b1.value/b2.value)*10)AVG RECEIVE TIME(ms)FROM gv$sysstat b1,gv$sysstat b2WHERE b1.name=global cache current block receive time AND b2.name=global cache current blocks received AND b1.inst_id=b2.inst_id;AVG RECEIVE TIME(ms)低于15ms表示当前块传输效率正常。
23、,RAC性能优化全局缓冲转换和获取（Global Cache Convert and Get）效率分析,执行如下命令可进行效率全局缓冲转换和获取（Global Cache Convert and Get）分析：SELECT A.inst_id Instance,A.value/B.value Avg Cache Conv.Time,C.value/D.value Avg Cache Get Time,E.value GC Convert TimeoutsFROM GV$SYSSTAT A,GV$SYSSTAT B,GV$SYSSTAT C,GV$SYSSTAT D,GV$SYSSTAT EWH
24、ERE A.name=global cache convert time AND B.name=global cache converts AND c.name=global cache get time AND D.name=global cache getsAND E.name=global cache convert timeouts AND B.inst_id=A.inst_idAND C.inst_id=A.inst_idAND D.inst_id=A.inst_idAND E.inst_id=A.inst_idORDER BY A.inst_id;Avg Cache Conv.Ti
25、me(ms)和Avg Cache Get Time（ms）低于10ms表示全局缓冲转换和获取（Global Cache Convert and Get）效率正常。,RAC性能优化GES性能分析,AWR报告中的GES性能抽样数据 Ave global lock get time(ms)Ave global lock convert time(ms)Ratio of global lock gets vs global lock releases 上述时间指标正常值应该 15ms Ratio of global lock gets vs global lock releases正常值应该在1.0左
26、右,RAC性能优化全局缓冲区延迟服务（Global Cache Defers）性能分析,执行如下命令可进行全局缓冲区延迟服务（Global Cache Defers）性能分析：SELECT a.inst_id Instance,a.value Defers,b.value Current Blks Served,(a.value)/b.value Current Blk Service TimeFROM GV$SYSSTAT A,GV$SYSSTAT BWHERE A.name=global cache defers AND B.name=global cache current blocks
27、 servedAND B.inst_id=A.inst_id ORDER BY a.inst_id;上述指标小于0.3为正常值。该指标值高表示事例间由于数据访问集中，导致全局缓冲区出现大量延迟服务。,RAC性能优化业务部署原则,基本策略：以数据为中心，将数据处理本地化，尽可能减少数据访问冲突。逻辑划分策略：按业务子系统划分 按部门划分 物理划分策略：使用Local Management表空间和Automatic Segment Space Management.,RAC性能优化实施原则,RAC性能调优与单实例调优的原理和方法一样Buffer Cache应该更大减少不必要的parse减少锁删除没
28、有使用的索引 物理分区表的使用目的：降低相同业务数据访问冲突使用Range，List,Composite分区技术，HASH分区索引的分区，尽量使用Local IndexRAC中表空间的设计策略 为只读表设计read-only表空间将所有小的标准表或参考表设计到一个表空间,RAC性能优化并行处理的控制,Oracle可自动进行节点间的并行处理和负载均衡。Instance_groups定义一个或多个可进行RAC实例间并行处理的实例组。该参数是静态参数sid|1-32|.INSTANCE_GROUPS=base,gameParallel_Instance_group定义指定的一个或多个可进行RAC实例
29、间并行处理的实例。该参数是动态参数ALTER SESSION SET PARALLEL_INSTANCE_GROUP=sales;Instance_groups和Parallel_Instance_group必须匹配，否则系统报错,RAC性能优化并行处理的监控,操作系统级监测 topas,nmon,glance,top,vmstat,iostat,sar数据库级监测select*from gv$px_session order by inst_id;select inst_id,count(*)from gv$px_session group by inst_id;select*from gv
30、$px_sesstat order by inst_id;select*from gv$px_process order by inst_id;select*from gv$px_process_sysstat order by inst_id;select*from gV$PX_PROCESS_SYSSTAT order by inst_id;select*from gv$pq_sesstat;,RAC性能优化 RAC连接的配置案例,需求透明应用切换（TAF）XA事务提交完整性等功能初始化参数,RAC性能优化 RAC连接的配置案例,Tnsnames.ora文件内容 LISTENER_test
31、1=(ADDRESS=(PROTOCOL=TCP)(HOST=10.182.1.10)(PORT=1521)LISTENER_test2=(ADDRESS=(PROTOCOL=TCP)(HOST=10.182.1.12)(PORT=1521)test1=(DESCRIPTION=(load_balance=off)(failover=on)(ADDRESS=(PROTOCOL=TCP)(Host=10.182.1.10)(Port=1521)(ADDRESS=(PROTOCOL=TCP)(Host=10.182.1.12)(Port=1521)(CONNECT_DATA=(SERVICE_NA
32、ME=test)(FAILOVER_MODE=(BACKUP=test2)(TYPE=SESSION)(METHOD=BASIC),test2=(DESCRIPTION=(load_balance=off)(failover=on)(ADDRESS=(PROTOCOL=TCP)(Host=10.182.1.10)(Port=1521)(ADDRESS=(PROTOCOL=TCP)(Host=10.182.1.12)(Port=1521)(CONNECT_DATA=(SERVICE_NAME=test)(FAILOVER_MODE=(BACKUP=test1)(TYPE=SESSION)(MET
33、HOD=BASIC),数据库WAIT事件在不同版本的对比,总结,架构规划设计注意事项WAIT事件RAC调优,案例一某省移动BOSS系统营帐数据库,数据库硬解析对比：8月Hard parses/Per Transaction 2.0510月Hard parses/Per Transaction 0.77优化后数据库应解析减少了62%,数据库处理的事物数对比：8月Transactions/Per Second160.3510月Transactions/Per Second266.17优化后数据库吞吐量提高了66%,案例一某省移动BOSS系统营帐数据库,7-8月平均响应时间:0.633ms8-9月平均响应时间:0.490ms9-10月平均响应时间:0.324ms优化后数据库响应时间缩短了49%，性能提升0.95倍,案例二某省移动BOSS系统营帐数据库,优化前后User Calls（业务调用次数）平均增加了11%,案例二某省移动BOSS系统营帐数据库,优化后物理读平均减少了30%,案例二某省移动BOSS系统营帐数据库,优化后主机CPU平均使用率下降了22%。,案例二某省移动BOSS系统营帐数据库,优化前后数据库平均响应时间缩短了45%，性能平均提升0.8倍,
