YonyouSoftwareCorporationSQL语句效率分析用友软件股份有限公司冯建民2013年8月10日YonyouSoftwareCorporationOracle局部性能诊断-分析sql-2缺少合适的索引？前言统计信息没有更新？查询缺少必要的过滤条件？表关联应该用hashjoin的走了nestedloop？新版本数据库Bug导致老版本高效的sql性能变差？……高水位线导致全表扫描？内存分配的问题1统计信息的问题2有无索引的问题3归档日志空间导致的问题5SQL语句本身的问题4YonyouSoftwareCorporation内存分配的问题查看SGA内存分配情况：YonyouSoftwareCorporation内存分配的问题BUFFERCACHE和SHAREDPOOL频繁收缩YonyouSoftwareCorporation内存分配的问题Highwaitsfor'Cursor:PinSWaitOnX'&'LibraryCacheLoadLocks'withautomemorymgmt内存分配的问题Highwaitsfor'Cursor:PinSWaitOnX'&'LibraryCacheLoadLocks'withautomemorymgmt[ID731233.1]ChangesYouarealsorunningtheAutoMemoryManagementfeature(ieSGA_TARGETissettoanon-zerovalue).Youmayhavealsosetminimumvaluesforsomeoftheauto-tunedareas,buthavenotsetavalueforSharedPool(ortheminimumvalueislowcomparedtothevalueseeninAWRreports...)CauseTheissuemaybecausedorexacerbatedbyfrequentresizingoperationsfortheSharedPool.SolutionSetaminimumvalueforSHARED_POOL_SIZE,perhapsthevalueshowninAWR/Statspackreportstakenfromapeakperiod.Thiswillavoidunnecessary/frequentdynamicmemoryresizingoperationsandconsequentpossiblemutex/latch/lockcontention.从两个awr开始YonyouSoftwareCorporation内存分配的问题SolutionSetaminimumvalueforSHARED_POOL_SIZE,perhapsthevalueshowninAWR/Statspackreportstakenfromapeakperiod.Thiswillavoidunnecessary/frequentdynamicmemoryresizingoperationsandconsequentpossiblemutex/latch/lockcontention.YonyouSoftwareCorporation内存分配的问题Oracle整体性能诊断-关键参数当新项目需要部署Oracle数据库初始参数或者部署NC试验环境时，通过ncperf包中tcheck和texe方法可以快速为Oracle环境设置正确的初始参数。屏蔽了查找部署文档及学习如何设置Oracle初始参数等繁琐的技术细节。select*fromtable(ncperf.tcheck(32000));beginncperf.texe(4000);end;YonyouSoftwareCorporation统计信息的问题YonyouSoftwareCorporation统计信息的问题YonyouSoftwareCorporation统计信息的问题YonyouSoftwareCorporation有无索引的问题问题描述：最近一个月打开客商档案卡片状态的速度突然变得很慢，双击以后进入卡片页面要等上大约一分钟YonyouSoftwareCorporation有无索引的问题问题定位：以下语句执行大概１分多钟selectsum(tb.bbye)fromarap_djfbtb,arap_djzbthwheretb.vouchid=th.vouchidandth.djdl='sk'andth.djztin(1,2,3)andth.dr=0andtb.dr=0andtb.wldx=0andordercusmandoc='0001AA1CU00000000001'YonyouSoftwareCorporation有无索引的问题问题分析：查看执行计划YonyouSoftwareCorporation有无索引的问题查看表的统计信息：YonyouSoftwareCorporation有无索引的问题分析：下面步这走的执行计划成本过高，并且是FULLSCAN*3|TABLEACCESSFULL|ARAP_DJFB|27|1350|65593(2)|00:13:08YonyouSoftwareCorporation有无索引的问题结论：ordercusmandoc列上无索引，可建索引CREATEindexidx_djfb_ordercusmandoc_0001onarap_djfb(ordercusmandoc);YonyouSoftwareCorporation有无索引的问题问题情况同上一样有索引，但执行计划走了不恰当的索引YonyouSoftwareCorporation有无索引的问题解决方法同上YonyouSoftwareCorporationSQL语句本身的问题YonyouSoftwareCorporationSQL语句本身的问题执行计划YonyouSoftwareCorporationSQL语句本身的问题解决方法：bd_invmandoc表增加pk_corp字段YonyouSoftwareCorporationSQL语句本身的问题YonyouSoftwareCorporation归档日志空间满导致的问题问题现象：1、软件正在操作，突然点击菜单无任何反映2、打开登入界面后，输入用户名和密码长时间没反映3、SQLPLUS访问数据库报错ORA-00257:archivererror.Connectinternalonly,untilfreedYonyouSoftwareCorporation归档日志空间满导致的问题查看db_recovery_file_dest_size:SQL>showparameterdb_recoveryNAMETYPEVALUE-----------------------------------------------------------------------------db_recovery_file_deststring/oracle/flash_recovery_areadb_recovery_file_dest_sizebiginteger20GYonyouSoftwareCorporation归档日志空间满导致的问题问题分析：SQL>select*fromV$FLASH_RECOVERY_AREA_USAGE;FILE_TYPEPERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLENUMBER_OF_FI----------------------------------------------------------------------CONTROLFILE000ONLINELOG000ARCHIVELOG99.90255BACKUPPIECE00YonyouSoftwareCorporation归档日志空间满导致的问题问题解决：ORA-19815:WARNING:db_recovery_file_dest_sizeof2147483648bytesis100.00%used,andhas0remainingbytesavailable.*************************************************************Youhavethefollowingchoicestofreeupspacefromflashrecoveryarea:1.ConsiderchangingyourRMANretentionpolicy.Ifyouareusingdataguard,thenconsiderchangingyourRMANarchivelogdeletionpolicy.2.BackupfilestotertiarydevicesuchastapeusingtheRMANcommandBACKUPRECOVERYAREA.3.Adddiskspaceandincreasethedb_recovery_file_dest_sizeparametertoreflectthenewspace.4.DeleteunncessaryfilesusingtheRMANDELETEcommand.IfanOScommandwasusedtodeletefiles,thenuseRMANCROSSCHECKandDELETEEXPIREDcommands.*************************************************************YonyouSoftwareCorporation
