SQL 优化一3session wait labs.docx
《SQL 优化一3session wait labs.docx》由会员分享，可在线阅读，更多相关《SQL 优化一3session wait labs.docx（10页珍藏版）》请在冰豆网上搜索。
SQL优化一3sessionwaitlabs
1各项资源争用事件的说明
UsingCPU
Thiscouldbeanindicationofinefficientprocessing.Theonlywaytotunethisareaisthroughaccesspathtuning（SQLorIndexes）.
CPUWait
AtrueCPUbottleneck.TuningsolutionsareeitheraddingmoreCPU,ortuneaccesspath.ThemotivationfortuningaccesspathistouselessCPUcyclestodothesameamountofwork.NOTE:
ThisfigureisbogusonAIX.ConfirmCPUbottlenecksatthehostbeforresearchingtheissuefurther.
I/OWait
I/Owaitismeasuredfordatarequestsfromdisk.Readsandwritestosortandrollbackarenotincludedhere.I/Owaitsneedtobedividedintosupplyanddemand.Ademandissuewillbeindicatedbyapoorhitratio.ForI/Owaitsandapoorhitratio,investigateSGAsizingandSQLprocessing.ForI/Owaitsandagoodhitratio,investigatediskperformanceandfileplacement.
MemoryWait
IndicatesapagingoperationontheSGA.InvestigateavailablememoryandSGAsize.ThisishighlyprevalentonSUNplatformswithhighI/Odemands.Refertoforinformationoffilebuffercacheandprioritypaging.
OtherHostWait
Alloftheabovecategoriesarehostrelated.OtherHostwaitsarewaitstatesonthesamedatabasehostotherthanthoselistedabove.Itemstoresearchinthisareaare:
1.OracleGateways2.SortDirectwrites（ie.SortsonDisk....inmostcasethisisthereason）
3.Transactionmonitors4.NFSfiles-whendatafilesarestoredonNFSwemaynotidentifyI/Owaitsbecauseitlookslikenetworkwaiting.5.Paging-wedonotidentifypagingonNTandDEC/Compaq6.SQLnetactivity（timeoutwaitingforaresponse）.7.Databaselinkquerieswhenthelinkeddatabaseisonthesamehost.
TableLockWait
AprocessspenttimewaitingonTablelevellocktorelease.Verytrickytocatchthelockholderinhistoricalanalysis.InvestigateforANALYZEandDMLcommandsinthesametimeframe.
RowLockWait
Thisisanapplicationissue.Sessionswaitedonarowlevellockfromanothersession.Again,difficulttocatchinhistoricalanalysis.LookforDMLstatementsthataccessthesametable.NOTE:
TheDMLdidnothavetohappeninthesametimeframe.AsessionthatissuesDMLanddoesnotendatransactionwillholdthelock,eventhoughitisinrequestwaitstate.ThereforeDMLstatementmayoccurinapriortimeperiod.
SharedPoolWait
Indicatesawaitforsharedpoolspace.Usuallyduetoasharedpooltoosmall.Thisdoesnotmeasurefragmentation–thatwillshowupinInternalLockWaits.
BufferWait
Thisiswaittimeforabufferinmemory.UsuallyablockneedstobereadintotheSGAandthereisnoplacetoputit.Bufferwaitssignalcontentionforthesameobject,becauseitgetshashedintoaparticularspotinthebuffercache.Investigatemanystatementsthataccessthesametableorindex,usuallyinafullscanmode.Possibleresolutionsincludeaccesspathchanges,increasingDB_BLOCK_BUFFERS,addingadditionaldbwriters,orincreasingthenumberoffreelistsonanobject
RollbackSegmentWait
Ashortageinaparticularrollbacksegment.Ihavenotencounteredtoomuchofthis.
RedoLogBufferWait
ThisisawaitformemoryintheSGA.ThiscanbebecausetheRedoLogBufferistoosmallortoolarge.Thegoalheristohaveenoughspaceforredotransactions,butnottoomuchsothattheflushtodiskisslow.ModifyLOG_BUFFERparameterasnecessary.Alsolookatmovingredologfilestospeedbufferflush.
LogSwitchandClearWait
Mostoftenthisisaswitchoftheredologstoofrequently.Possiblyaddmore,andincreaseredologs.Alsomakesurethatcheckpointsarenothappeningtoofrequently
InternalLockwait
ContaineitherwaitsforEnqueuesorLatches.LookinthePWforwhichtype,andtheassociatelatchestofindtheparticularlatchcontendedupon.Sleepinglatchesaremoreimportantthanmissratios.SharedPoolfragmentationwillshowuphereascontentionfortheLibraryCacheorSharedPoollatch
BackgroundProcessWait
Noexperience
ParallelQuerySynchWait
Notabottleneck.Thisindicatesadegreeofparallelismthatistoohigh,andmostoftheslaveprocesseshavenothingtodoforthedurationofthequery.Thatidletimeisrecordedhere.Aproductenhancementrequestistocategorizethistimeasrequestwait.
表五WAITEVENTDETAIL
2ASH中等待事件及解决方案
等待事件
解决方案
bufferbusywaits
该指标说明缓冲区以一种非共享方式被使用，或者正被读入缓存。
该指标不应高于1%。
该等待针对的对象有：
a、针对一个段的头信息，则增加空闲列表或者组数量，或者扩大pctused与pctfree之间的间隔。
b、针对撤销请求的头信息，则增加回滚段。
c、针对撤销的数据块，则增加DB_CACHE_SIZE或者减少驱动一致性读操作的表上的数据密度集。
d、针对数据块，则将数据迁移到另外一个数据块上，或者增加表上的空闲列表大小，或者使用本地托管的表空间。
e、针对索引数据块，则重建索引或者将索引分区化，或者使用一个反转键索引。
Controlfileparallelwrite
这个事件出现在服务器进程更新所有的控制文件时等待I/O。
可以从以下方面来调整：
在确保控制文件不会同时丢失的前提下，将控制文件的数据减少到最少；如果系统支持异步I/O，则使用异步I/O；将控制文件移到负载比较低，速度比较快的磁盘上。
Controlfilesequentialread
这个事件出现在服务器进程读取所有的控制文件时等待I/O。
可以从以下方面来调整：
如果系统支持异步I/O，则使用异步I/O；将控制文件移到负载比较低，速度比较快的磁盘上。
Controlfilesinglewrite
这个事件出现在写控制文件的共享信息到磁盘的时候。
可以从以下方面来调整：
如果系统支持异步I/O，则使用异步I/O；将控制文件移到负载比较低，速度比较快的磁盘上。
dbfilescatteredread
该指数说明等待与全表扫描有关。
该指数过大说明缺少索引或者限制使用索引。
这种情况也有可能是正常的，因为执行全表扫描有可能比索引扫描效率更高，并且需要检查确定全表扫描是否是必须的。
Db_file_multiblock_read_count参数要满足以下的条件：
db_block_size*db_file_multiblock_read_count=max_io_sizeofsystem.
dbfilesequentialread
该指数说明等待与数据块读操作（例如索引的读取）有关。
该值过大说明表的连接顺序很糟糕，或者使用了非选择性索引。
检查sql的执行计划，确定sql的执行计划正确，尤其是有多个索引可供选择的时候要保证正确的索引被使用。
directpathread
主要是由读取数据表空间和临时表空间的sql引起的，包括以下的sql类型ORDERBY,GROUPBY,UNION,DISTINCT,andROLLUP。
同时需要检查hint以及优化器的优化策略。
如果等待的文件是临时表空间的数据文件，需要查看是否存在大量不合理的磁盘排序，优化相应的存在问题的sql语句。
Oracle9i可以考虑使用自动sql执行内存管理。
确认异步I/O是否配置正确。
检查是否存在I/O消耗很严重的sql语句。
还要检查是否到达了磁盘的I/O极限，需要考虑更换性能更好的硬件设备。
directpathwrite
主要是由写入数据表空间和临时表空间的sql引起的，包括以下的sql类型SORT,HASH,INDEX,DATA,LOB_DATA,andLOB_INDEX。
CTAS（createtableasselect）,INSERT/*+APPEND*/…SELECT,andSQLloaderrunningindirectloadmode.如果等待的文件是临时表空间的数据文件，需要查看是否存在大量不合理的磁盘排序，优化相应的存在问题的sql语句。
Oracle9i可以考虑使用自动sql执行内存管理。
确认异步I/O是否配置正确。
检查是否存在I/O消耗很严重的sql语句。
还要检查是否到达了磁盘的I/O极限，需要考虑更换性能更好的硬件设备。
尽量减少I/O请求的次数，设置初始化参数db_file_direct_io_count,满足：
Db_block_size*db_file_direct_io_count=max_io_sizeofsystem.
dbfileparallelwrite
与DBWR进程相关的等待，一般都代表了I/O能力出现了问题。
通常与配置的DBWR进程或者DBWR的I/O
slaves个数有关，也有可能设备上存在着I/O竞争。
Enqueue
入列是保护共享资源的一种锁机制，分为：
a、ST入列，解决方法是使用本地托管的表空间，保证有问题的管理字典表空间有足够大的扩展空间。
b、HW入列，用于高容量的段，手工分配扩展空间可以回避这种等待。
通常和Freelist争用相关。
c、TX4入列，如果是唯一索引或者位图索引的则需要执行提交/回滚操作来释放队列；如果是同时更新同一个数据块，则可以增加initans或者maxtrans来支持更多的ITL空间，或者通过增加表上的pctfree值来实现。
d、TM入列，外键建立索引，查看应用程序中的表锁。
freebufferinspected
表示在将数据读入数据高速缓存区的时候等待进程找到足够大的内存空间，通常这类等待表示数据高速缓存区偏小。
freebufferwaits
a、InefficientSQLStatements，则调优sql。
b、InsufficientDBWRProcesses，则增加DBWR的进程。
c、SlowI/OSubsystem，则调整系统I/O。
d、SmallBufferCache，则增加缓存区大小，缓存区大小不受50%比例的限制。
latchfree
闩锁是底层的队列机制（或者是互斥机制），用于保护系统全局区（SGA）的共享内存结构。
绝大多数的闩锁问题都与使用绑定变量失败（库缓存闩锁）、生成重做问题（重执行分配闩锁）、缓存的争用问题（缓存LRU链）以及缓存的热数据块（缓存链）
有关。
如果闩锁不可用，就会记录一次闩锁丢失。
librarycachepin
是由库缓存并发操作引起的，一个对象在被锁定时发生错误，可能是由动态sql引起的，需要调整相应的sql，同时需要调整数据字典共享池。
该事件通常发生在编译或重新编译PL/SQL、VIEW、TYPES等object时，编译都是显性的，如安装应用程序、升级、安装补丁程序等，另外，alter、grant、revoke等操作也会使object变得无效。
Package或者过程中存在复杂的、交互的依赖关系就极易导致该事件的出现。
librarycachelock
该事件通常发生在编译或重新编译PL/SQL、VIEW、TYPES等object时，编译都是显性的，如安装应用程序、升级、安装补丁程序等，另外，alter、grant、revoke等操作也会使object变得无效。
通常表示不合理的共享池大小。
librarycacheloadlock
表示等待LGWR向操作系统请求I/O直到完成I/O。
在触发LGWR写的情况下如3秒，1/3，1MB，DBWR写之前可能发生。
通常表示日志文件发生了I/O竞争或者文件所在的驱动器较慢。
logbufferspace
增加日志缓冲区，为重做日志使用更快的磁盘。
logfileswitch
归档目标已满或者速度太慢，增加或者使用更大的重做日志。
logfilesync
一次提交更多的记录，为撤销日志使用更快的磁盘，使用裸设备。
可以从以下方面进行调整：
调整LGWR进程使其具有更好的磁盘I/O吞吐量，例如不要将日志文件放在RAID5的磁盘上；如果存在很多执行时间很短的事务，可以考虑将这些事务集合成一个批处理事务以减少提交的次数；查看是否一些操作可以安全的使用NOLOGGING或者UNRECOVERABLE选项。
logfileparallelwrite
表示在将对象装入到库高速缓存时出现了等待。
这种事件通常代表着发生了负荷很重的语句重载或者装载，可能由于sql语句没有共享或者共享池区域偏小造成的。
可以从以下方面进行调整：
对能使用unrecoverable/nologging的操作尽量使用这两个选项来减少重做日志的产生；在保证不会同时丢失重做日志文件的前提下尽量减少重做日志组中的成员的个数，减少每次写重做日志组文件的时间；对于使用logminer,logicstandby或者streams,在能够满足要求功能的前提下，尽量使用最低级别的追加日志以减少重做日志的产生；尽量将同一个组内的重做日志文件分散到不同的硬盘上，减少并行写重做日志文件时产生的I/O竞争；不要将重做日志文件放在RAID5的硬盘上，最好使用裸设备来存放重做日志文件；不要将归档日志文件的目的地设置为存放重做日志文件的磁盘上。
logfilesinglewrite
表示写日志文件头块时出现了等待，一般都是发生在检查点发生时。
对重做日志文件尽量使用裸设备，避免将多个日志文件放在同一个磁盘上，减少产生I/O竞争的可能。
Transaction
表示发生了一个阻塞回滚操作的事件。
undosegmentextension
表示在等待回滚段的动态扩展。
这表示可能事务量过大，同时也意味着可能回滚段的初始大小不是最优，MINEXTENTS设置的偏小。
考虑减少事务，或者使用最小区数更大的回滚段。
writecompletewaits
增加数据库写入器，增加更多的检查点，增大缓存。
3Findoutwaitevents
----查找当前系统中占用资源的进程与数据库中等待的事件和sql
selectse.SID,
se.SERIAL#,
p.osthread,
se.logon_time,
se.event,
se.sql_text
from（selecta.sid,
b.serial#,
c.SQL_TEXT,
a.state,
a.wait_time,
a.seconds_in_wait,
b.username,
b.osuser,
b.logon_time,
b.saddr,
a.EVENT
fromv$session_waita,v$sessionb,v$sqlareac
whereb.SQL_HASH_VALUE=c.HASH_VALUE
anda.sid=b.sid
andb.usernameisnotnull
andb.type<>'BACKGROUND'
orderbyb.logon_time）se,
（selects.sidsid,p.spidOSThread,b.nameusername,s.program
fromv$processp,v$sessions,v$bgprocessb
wherep.addr=s.paddr
andp.addr=b.paddr
UNIONALL
selects.SID,p.spidOSThread,s.usernameusername,s.program
fromv$processp,v$sessions
wherep.addr=s.paddr
ands.usernameisnotnull）p
wherese.SID=p.sid
4实验
----inlinux
step1（session1）:
updatedepartments
setdepartment_name='Doc'
wheredepartment_id=260;
step2（session2）:
updatedepartments
setdepartment_name='Doc'
wheredepartment_id=260;
step3（session3）:
findoutwaiteventsandprocessofOS.
step4（solutions）:
solutionone:
killoperatingsystemprocessusingkillcommand.
solutiontwo:
rollbackorcommittransactioninsession1.
升级会员 