ControlChange5Change2015-06-NoPrevious 文檔概 Oraclewaitinterface概 概 OWI組 概 OpenandClosedIssuesforthis Open Closed 文檔概…Oraclewaitinterface概概Oracelwaitinterface(OWI)Oracle提供用來(lái)追蹤Oracle等待事件，通過(guò)OWI的組件可以從session維度和系統(tǒng)維度分析性能瓶頸，進(jìn)而進(jìn)一步對(duì)數(shù)據(jù)庫(kù)進(jìn)行調(diào)優(yōu)。OWIOracle7.0.12104Oracle10g800個(gè)等待事件經(jīng)常從awr中可以看到ForegroundWaitEvents和BackgroundWait事件是指Oracle內(nèi)核代表用戶會(huì)話或者自身的進(jìn)程執(zhí)行的特定功能或任務(wù)如IO、SGAOracle在執(zhí)行這些事件的時(shí)候會(huì)發(fā)生等待，連接到Oracle的每個(gè)會(huì)話都需要資源來(lái)執(zhí)行所需要完成IO等待事件：dbfilesequentialread和dbfilescatteredreadlibrarycachepin等。OWI通過(guò)對(duì)I/O操作、鎖定、鎖存器、進(jìn)程活動(dòng)、網(wǎng)絡(luò)延遲等造成的等待進(jìn)行記V$視圖中，通過(guò)分析和消除這些性能ResponseTime=CPUusedwhencallstarted在會(huì)話級(jí)，服務(wù)時(shí)間是指V$SESSTAT視圖中的CPUusedwhencallstarted統(tǒng)計(jì)的時(shí)TIME_WAITED之和。SELECTSELECTevent,timewaitedASFROMWHEREsid=ANDeventNOTIN('Null'client'KXFX:ExecutionMessageDequeue-Slave','PXDeq:ExecutionMsg','KXFQ:kxfqdeq-normaldeqeue','PXDeq:TableQNormal','Waitforcredit-sendblocked','PXDeqCredit:sendblkd','Waitforcredit-needbuffertosend','PXDeqCredit:needbuffer','Waitforcredit-freebuffer','PXDeqCredit:freebuffer','parallel'parallelquerydequeuewait','PXDequewait','ParallelQueryIdleWait-Slaves','PXIdleWait','slavewait','dispatchertimer','virtualcircuitstatus','pipeget','rdbmsipcmessage','rdbmsipcreply','pmontimer','smontimer','PL/SQLlocktimer','SQL*Netmessagefromclient','WMONgoestosleep')UNIONSELECT,FROMv$sesstata,v$statnameWHEREa.statistic#=AND='CPUusedwhencallANDa.sid=OWI組概Oraclewaitinterface(OWI)SQL追蹤文件的集合。OracleWaitInterfaceOracleRelease7.0.124V$視圖常用視圖介 V$EVENT_NAME視圖包含了數(shù)據(jù)庫(kù)實(shí)例定義的所有等待事件，這些等待事件的數(shù)量Oracle的版本有關(guān)，也和Oracle的安裝選項(xiàng)有關(guān)。Oracle11g的查詢例子如下：Oracle版本，會(huì)發(fā)生變化V$SESSION_WAIT視圖中的P1TEXT列，實(shí)際值再V$SESSION_WAITP1列V$SESSION_WAIT視圖中的P2TEXT列，實(shí)際值再V$SESSION_WAITP2列V$SESSION_WAIT視圖中的P3TEXT列，實(shí)際值再V$SESSION_WAITP3列等待事件對(duì)應(yīng)的分類，比如UserI/O、Concurrency等V$SYSTEM_EVENT顯示自從實(shí)例啟動(dòng)后所有Oracle會(huì)話遇到的所有等待事件的總秒所請(qǐng)求資源的次數(shù)，針對(duì)前臺(tái)session秒（百分之一秒），針對(duì)前臺(tái)session臺(tái)（百萬(wàn)分之一秒），針對(duì)前臺(tái)等待事件對(duì)應(yīng)的分類，比如UserI/O、Concurrency等f(wàn)reebufferwaits(空閑緩存區(qū)等待)100100厘秒，如果超時(shí)，會(huì)在TOTAL_TIMEOUTS100厘秒的等待。有些等待 V$SESSION_EVENT包含了當(dāng)前連接到實(shí)例的所有會(huì)話的總計(jì)等待事件統(tǒng)計(jì)。該視圖包含該SESSION記錄該SESSION在初次等待之后未能成功記錄該SESSION等待該事件所消耗的總的秒等待事件對(duì)應(yīng)的分類，比如UserI/O、Concurrency等V$SESSION_EVENT視圖只記錄會(huì)話里每個(gè)事件的總的統(tǒng)計(jì)信息，沒(méi)辦法追蹤到具體導(dǎo)致性能問(wèn)題的SQL。不過(guò)通過(guò)此視圖可以很方便的知道當(dāng)前SESSION是哪些事件 V$SESSION_WAIT給定時(shí)間，只包含SESSION的一行等待事件或者不活動(dòng)信息。的P1的2對(duì)應(yīng)的屬性值，不同事件表示不P2的P3等待事件對(duì)應(yīng)的分類，比如UserI/O、Concurrency等待時(shí)間，0表示會(huì)話正在等待，正值表示最秒），SEDONDS_IN_WAIT–WAIT_ITME/100表示上次等待結(jié)束到現(xiàn)在0-WAITING當(dāng)前的SESSION-2WAITEDUNKNOWNTIME-1WAITEDSHORTTIME小于1100>0WAITEDKNOWNTIME表示oracle間存在WAIT_ITME列SECONDS_IN_WAIT（1/1000000秒11g0表示當(dāng)前等待超時(shí)；>0表示當(dāng)前等待還需等待的時(shí)間；-1表示當(dāng)前等待將地等待；null表示該會(huì)話當(dāng)前無(wú)等11G新增，表示距離上次等待結(jié)束所消耗的V$SESSION_WAIT比如dbfilesequencialread和dbfilescatteredreadP1P2的值，能夠獲得 V$SESSION_WAIT_HISTORY10個(gè)等待事件信息，字段大部分和V$SESSION_WAIT一致。的的的待時(shí)間，0表示會(huì)話正在等待，正值表示最SECONDS_IN_WAIT（1/1000000秒11G新增，表示距離上次等待結(jié)束所消耗的 SessionSession通過(guò)次視圖可以快速查詢某個(gè)session它的哪個(gè)等待類所消耗的等待時(shí)間比較多，以便常見(jiàn)等待事常見(jiàn)等待事bufferbusybufferbusywaits。時(shí)產(chǎn)生bufferbusywaits事件等待。Oracle10g開(kāi)始，這種等待作為readbyothersession事件進(jìn)行提交，而bufferbusywaits則表示一個(gè)會(huì)話等候其他會(huì)話完成對(duì)緩沖v$waitstat中的classdatasortsaveundosegmentsaveundofreeextent1stlevel2ndlevel3rdlevelbitmapbitmapindexfileheadersystemundosystemundoundoundo1001秒在SGA中或者修改緩存區(qū)的會(huì)話首先獲取cachebufferschains鎖存器并且遍歷pinpin，會(huì)話會(huì)pinbufferbusywaits等待時(shí)間上等待。SQL查詢bufferbusywaitsSELECTSELECT'segmentheader'CLASS,a.segment_type,a.segment_name,FROMdba_segmentsa,v$session_waitWHEREa.headerfile=ANDa.headerblock=ANDb.event='bufferbusySELECT'freelistgroups'CLASS,a.segment_type,a.segment_name,FROMdbasegmentsa,v$sessionwaitWHEREb.p2BETWEENa.header_block+1AND(a.header_block+a.freelistgroups)ANDa.headerfile=ANDa.freelistgroups>ANDb.event='bufferbusy egment_type||'block'CLASS,a.segment_type,a.segment_name,a.partition_nameFROMdbaextentsa,v$sessionwaitWHEREb.p2BETWEENa.block_idANDa.block_id+a.blocks-ANDa.file_id=ANDb.event='bufferbusyANDNOTEXISTS(SELECTFROMWHEREheader_file=ANDheader_block=dbfilescattereddbfilescatteredread表示多個(gè)數(shù)據(jù)塊的I/O請(qǐng)求，初始化參數(shù)DB_FILE_MULTIBLOCK_READ_COUNT決定了數(shù)據(jù)塊的最大數(shù)目一般此事件dbfilesequential dbfilesequentialread表示按順序數(shù)據(jù)文件，比如按索引、回滾或者撤銷段、rowid表等。該事件不一定表示存在性能問(wèn)題。從V$SESSION_EVENT視圖中可以發(fā)現(xiàn)哪些會(huì)話的dbfilesequential等待事件具有較高的TIME_WAITED值，此值還需要考慮此session的登入時(shí)間。有可能某個(gè)session登入時(shí)間很長(zhǎng)，積累了大量的dbfilesequentialSQL可以進(jìn)行分析，查找可能存在問(wèn)題的SID。 a.time_waited/c.sum_time_waited*100pct_wait_time,round((sysdate-b.logon_time)*24)hours_connected v$session_eventa,v$session(selectsid,sum(time_waited)sum_time_waited eventnotin'Nullevent','clientmessage','KXFX:ExecutionMessageDequeue-Slave','PXDeq:ExecutionMsg','KXFQ:kxfqdeq-normaldeqeue','PXDeq:TableQNormal','Waitforcredit-sendblocked','PXDeqCredit:sendblkd','Waitforcredit-needbuffertosend','PXDeqCredit:needbuffer','Waitforcredit-freebuffer','PXDeqCredit:freebuffer','parallelquerydequeuewait','PXDequewait','ParallelQueryIdleWait-Slaves','PXIdleWait','slavewait','dispatchertimer','virtualcircuit'pipe'pipe'rdbmsipcmessage','rdbmsipcreply','pmontimer','smontimer','PL/SQLlocktimer','SQL*Netmessagefromclient','WMONgoestosleep')havingsum(time_waited)>0groupbysid)==a.time_waited>='dbfilesequentialorderbyhours_connecteddesc,192SID TIME_WAITEDPCT_WAIT_TIME186dbfilesequential284dbfilesequential194dbfilesequential322