oracle數(shù)據(jù)庫性能優(yōu)化實(shí)務(wù)第32 33講解讀awr報(bào)告

華章培訓(xùn)網(wǎng)、[]華章培訓(xùn)網(wǎng)本章的目的掌握如何解讀AWR報(bào)告掌握在AWR報(bào)告中的一些主要小節(jié)的作用掌握AWR報(bào)告中的一些主要等待事件的含義掌握AWR報(bào)告中的一些主要統(tǒng)計(jì)數(shù)據(jù)的含義掌握如何在AWR報(bào)告中分析TOP

SQL掌握如何使用AWR報(bào)告中的建議解讀AWR報(bào)告的作用AWR報(bào)告是Oracle

數(shù)據(jù)庫性能和運(yùn)行狀態(tài)的最全面的展示AWR報(bào)告對于數(shù)據(jù)庫整體性能分析十分關(guān)鍵學(xué)會(huì)解讀AWR報(bào)告是一個(gè)高級DBA的必備功課解讀AWR報(bào)告所需的基礎(chǔ)知識(shí)Oracle

Wait

Interface時(shí)間模型Oracle

Concepts

中相關(guān)的基礎(chǔ)知識(shí)解讀AWR報(bào)告的技巧孰能生巧基于基線比對類似系統(tǒng)比對AWR報(bào)告頭WORKLOAD

REPOSITORY

report

forDB

Name DB

Id

InstanceXXOSS 1544217329

xxoss2Snap

Id Snap

TimeInst

Num

Release RAC

Host2

10.2.0.4.0 YES

rac2Sessions

Curs/Sess2972965.96.0Begin

Snap:End

Snap:Elapsed:DB

Time:13257

16-Nov-09

19:00:0813258

16-Nov-09

20:00:0059.88

(mins)698.54

(mins)關(guān)注點(diǎn)：SESSIONS:采樣時(shí)點(diǎn)的會(huì)話數(shù)CURS/SESS：采樣時(shí)點(diǎn)的每個(gè)會(huì)話打開的CURSOR的數(shù)量DB

TIME：數(shù)據(jù)庫用于用戶會(huì)話調(diào)用的時(shí)間，可反映系統(tǒng)負(fù)載情況CACHESIZECache

Sizes~~~~~~~~~BeginEndBuffer

Cache:24,432M24,432MStd

Block

Size:8KShared

Pool

Size:4,096M4,096MLog

Buffer:14,268K分析SGA

RESIZEalter

session

set

nls_date_format='yyyy-mm-dd

hh24:mi:ss';col

component

format

a40

truncate;selectcomponent,oper_type,oper_mode,start_time,end_time,trunc(target_size/1024/1024)

targetfromv$sga_resize_ops;例子COMPONENTOPER_TYPEOPER_MODESTART_TIMEEND_TIMEDEFAULT

buffercacheGROWDEFERRED2009-08-2511:52:192009-08-2511:52:19streams

poolSHRINKDEFERRED2009-08-2511:52:192009-08-2511:52:19DEFAULT

buffercacheSHRINKIMMEDIATE2009-08-2511:53:412009-08-2511:53:41streams

poolGROWIMMEDIATE2009-08-2511:53:412009-08-2511:53:41DEFAULT

buffercacheSHRINKIMMEDIATE2009-08-2511:53:422009-08-2511:53:42streams

poolGROWIMMEDIATE2009-08-2511:53:422009-08-2511:53:42streams

poolGROWIMMEDIATE2009-08-2511:53:422009-08-2511:53:42DEFAULT

buffercacheSHRINKIMMEDIATE2009-08-2511:53:422009-08-2511:53:42streams

poolGROWIMMEDIATE2009-08-2511:54:162009-08-2511:54:16DEFAULT

buffercacheSHRINKIMMEDIATE2009-08-2511:54:162009-08-2511:54:16DEFAULT

buffercacheGROWDEFERRED2009-08-2511:57:492009-08-2511:58:04streams

poolSHRINKDEFERRED2009-08-2511:57:492009-08-2511:58:04DEFAULT

buffercacheGROWDEFERRED2009-08-2512:00:172009-08-2512:00:17streams

poolSHRINKDEFERRED2009-08-2512:00:172009-08-2512:00:17streams

poolSHRINKDEFERRED2009-08-2512:03:172009-08-2512:03:17DEFAULT

buffercacheGROWDEFERRED2009-08-2512:03:172009-08-2512:03:17streams

poolSHRINKDEFERRED2009-08-2512:07:212009-08-2512:07:21DEFAULT

buffercacheGROWDEFERRED2009-08-2512:07:212009-08-2512:07:21Load

Profile~~~~~~~~~~~~Per

SecondPer

TransactionRedo

size:4,055,059.4233,250.38Logical

reads:143,003.191,172.59Block

changes:23,643.28193.87Physical

reads:441.323.62Physical

writes:749.276.14User

calls:1,957.5816.05Parses:602.774.94Hard

parses:0.030.00Sorts:259.332.13Logons:0.220.00Executes:2,517.3820.64Transactions:121.96%

Blocks

changed

per

Read:16.53Recursive

Call%:54.68Rollback

per

transaction

%:0.85Rows

per

Sort:409.87Instance

Efficiency

Percentages

(Target

100%)Instance

Efficiency

Percentages

(Target

100%)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Buffer

Nowait%:99.87Redo

NoWait%:99.98Buffer

Hit%:99.71In-memory

Sort%:99.99Library

Hit%:100.02Soft

Parse%:100.00Execute

to

Parse%:76.06Latch

Hit%:98.98Parse

CPU

to

Parse

Elapsd

%:2.92%

Non-Parse

CPU:99.97Shared

Pool

StatisticsShared

PoolStatisticsBeginEndMemory

Usage

%:51.0751.16%

SQL

withexecutions>1:97.0696.56%

Memory

for

SQL

w/exec>1:95.4795.04感謝您對華章培訓(xùn)網(wǎng)的支持!.Top

5

TimedEventsAvg%TotalTop

5

Timed

Events~~~~~~~~~~~~~~~~~~EventWaitsTime

(s)wait(ms)CallTime

WaitClass1,507,667Commitdb

file

sequential

readCPU

timelog

file

syncStreams

capture:

waiting

for

slog

file

parallel

write508,5801,308523,16715,69712,2036,2301,9961,02910

37.5 User

I/O29.112

14.9152624.8

Configurat2.5

System

I/OGlobal

Cache

Load

ProfileGlobal

Cache

Load

Profile~~~~~~~~~~~~~~~~~~~~~~~~~Per

SecondPer

TransactionGlobal

Cache

blocks

received:274.272.25Global

Cache

blocks

served:144.481.18GCS/GES

messages

received:1,175.379.64GCS/GES

messages

sent:1,128.879.26DBWR

Fusion

writes:12.700.10Estd

Interconnect

traffic

(KB)3,800.04Global

Cache

Efficiency

PercentagesGlobal

Cache

Efficiency

Percentages

(Target

local+remote

100%)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Bufferaccess-

localcache%:99.51Bufferaccess-

remotecache%:0.19Bufferaccess-disk%:0.29Global

Cache

and

Enqueue

Services

-

WorkloadCharacteristicsGlobal

Cache

and

Enqueue

Services

-

Workload

Characteristics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avg

global

enqueue

get

time

(ms):

0.7Avg

global

cache

cr

block

receive

time

(ms):Avg

global

cache

current

block

receive

time

(ms):0.70.7Avg

global

cache

cr

block

build

time

(ms):Avg

global

cache

cr

block

send

time

(ms):Global

cache

log

flushes

for

cr

blocks

served

%:Avg

global

cache

cr

block

flush

time

(ms):0.00.039.54.1Avg

global

cache

current

block

pin

time

(ms):Avg

global

cache

current

block

send

time

(ms):Global

cache

log

flushes

for

current

blocks

served

%:Avg

global

cache

current

block

flush

time

(ms):0.80.01.522.5重要指標(biāo)解釋Aveglobal

cache

get

time

(ms)：一個(gè)GLOBALCACHE的獲取時(shí)間，一般來說應(yīng)該小于5毫秒，如果這個(gè)指標(biāo)超過20毫秒，對系統(tǒng)的性能影響將十分大。Ave

global

cache

convert

time

(ms)：這個(gè)指標(biāo)是對一個(gè)DATA

BUFFER的訪問權(quán)限轉(zhuǎn)換所需要的平均時(shí)間，這個(gè)指標(biāo)一般來說應(yīng)該在4毫秒左右，超過20毫秒說明RAC的性能可能存在問題。Ave

time

toprocess

CRblock

request

(ms):這個(gè)指標(biāo)統(tǒng)計(jì)一個(gè)CRBLOCK請求的時(shí)間，包含生成CRBLOCK、刷新CRBLOCK和發(fā)送CRBLOCK的時(shí)間，在Oracle10G的AWR報(bào)告里，這個(gè)指標(biāo)被分解為三個(gè)部分（Avg

global

cache

cr

block

build

time、Avg

global

cache

cr

block

flush

time、Avg

global

cache

cr

block

send

time），正常范圍是在0.1毫秒到1毫秒，超過10毫秒，對系統(tǒng)的性能有較大的影響。Ave

receive

time

for

CRblock

(ms)：一個(gè)CRBLOCK從發(fā)起請求到收到的時(shí)間，一般來說這個(gè)指標(biāo)在0.3毫秒-4毫秒之間，這個(gè)指標(biāo)超過12毫秒，我們就需要關(guān)注Ave

time

to

process

current

block

request

(ms):處理一個(gè)CURRENT狀態(tài)的

BLOCK請求的處理時(shí)間。一般情況下指標(biāo)在3毫秒左右，大于20毫秒的時(shí)候就需要關(guān)注了。Ave

receive

time

for

current

block

(ms)：一個(gè)CURRENT的BLOCK從發(fā)起請求到收到的時(shí)間。這個(gè)指標(biāo)一般情況下在8毫秒左右，負(fù)載較輕的系統(tǒng)可能會(huì)小于1毫秒，負(fù)載較重的系統(tǒng)，這個(gè)指標(biāo)可能偏高，不過一般情況下，這個(gè)指標(biāo)應(yīng)該小于30毫秒。Global

Cache

and

Enqueue

Services

-

Messaging

StatisticsGlobal

Cache

and

Enqueue

Services

-

Messaging

Statistics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avg

message

sent

queuetime(ms):0.0Avg

message

sent

queue

time

onksxp(ms):0.5Avg

message

received

queuetime(ms):0.0Avg

GCS

message

processtime(ms):0.0Avg

GES

message

processtime(ms):0.0%

of

direct

sentmessages:49.59%of

indirect

sentmessages:31.96%

of

flow

controlled

messages:18.45重要指標(biāo)解釋Avgmessagesentqueuetime(ms)：一個(gè)非直接傳輸?shù)倪壿嬒⒃陉?duì)列中的平均等待時(shí)間，正常情況下，這個(gè)值應(yīng)該在0.1毫秒左右，并且不會(huì)超過1毫秒Avgmessagesentqueuetimeonksxp(ms)：在IPC層面上發(fā)送一個(gè)消息到收到ACK回應(yīng)的時(shí)間差，這個(gè)指標(biāo)可以看出IPC的性能。正常情況下這個(gè)指標(biāo)也應(yīng)該小于1毫秒Avgmessagereceivedqueuetime(ms)：消息收到進(jìn)入隊(duì)列到開始處理的時(shí)間，一般來說這個(gè)時(shí)間越長說明等待處理的消息越多，一般來說這個(gè)值應(yīng)該在0.1毫秒左右，甚至更低AvgGCSmessageprocesstime(ms)：平均處理一個(gè)BUFFER相關(guān)的GCS請求的時(shí)間，一般這個(gè)指標(biāo)小于0.5毫秒。AveGESmessageprocesstime(ms)：平均處理一個(gè)鎖操作消息的時(shí)間，一般來說這個(gè)指標(biāo)小于0.5毫秒。Time

Model

StatisticsStatistic

NameTime

(s)

%

of

DB

Timesql

execute

elapsed

timeDB

CPU31,563.412,203.175.329.1PL/SQL

execution

elapsed

time2,816.96.7parse

time

elapsed68.5.2connection

management

call

elapsed

time12.5.0hard

parse

elapsed

time2.6.0repeated

bind

elapsed

time1.1.0sequence

load

elapsed

time0.4.0hard

parse

(sharing

criteria)

elapsed

time0.2.0failed

parse

elapsed

time0.0.0PL/SQL

compilation

elapsed

time0.0.0DB

time41,912.2N/Abackground

elapsed

time4,886.0N/Abackground

cpu

time1,428.3N/AWait

ClassWait

ClassWaits%Time-outsTotal

WaitTime

(s)Avgwait(ms)Waits/txnUser

I/O1,586,311.016,229103.6Commit508,580.06,230121.2System

I/O1,305,402.02,22823.0Configuration4,59420.42,0954560.0Cluster1,891,580.01,81514.3Network6,344,952.01,495014.5Other1,183,92150.91,42212.7Concurrency200,260.126910.5Application13,8583.1224160.0Wait

EventAvg%TimeTotal

WaitwaitWaitsEventWaits-outsTime

(s)(ms)/txndb

file

sequential

read1,507,667.015,697103.4log

file

sync508,580.06,230121.2Streams

capture:

waiting

for1,30870.61,99615260.0log

file

parallel

write523,167.01,02921.2gc

buffer

busy508,039.085821.2db

file

parallel

write589,632.078711.3enq:

SR

-

contention17,4407.5666380.0ARCH

wait

on

SENDREQ29,696.0525180.1read

by

other

session56,607.051590.1SQL*Net

more

data

to

dblink947,828.050312.2gcs

log

flush

sync231,54730.647320.5SQL*Net

more

data

from

clien224,985.045920.5Log

archive

I/O33,555.024770.1Background

Wait

EventsAvg%TimeTotal

WaitwaitWaitsEventWaits-outsTime

(s)(ms)/txnlog

file

parallel

write523,215.01,02921.2db

file

parallel

write589,632.078711.3events

in

waitclass

Other1,090,68754.355112.5ARCH

wait

on

SENDREQ29,696.0525180.1Log

archive

I/O33,555.024770.1db

file

sequential

read55,862.011120.1log

file

sequential

read16,081.09160.0control

file

sequential

read52,528.01800.1control

file

parallel

write6,487.01020.0log

file

switch

completion184.07390.0gc

cr

multi

block

request929.0660.0Operating

System

StatisticsStatisticTotalNUM_LCPUSNUM_VCPUSAVG_BUSY_TIMEAVG_IDLE_TIMEAVG_IOWAIT_TIMEAVG_SYS_TIMEAVG_USER_TIMEBUSY_TIMEIDLE_TIMEIOWAIT_TIMESYS_TIMEUSER_TIMELOADOS_CPU_WAIT_TIMERSRC_MGR_CPU_WAIT_TIMEPHYSICAL_MEMORY_BYTESNUM_CPUSNUM_CPU_CORES0058,026301,21747,9808,48049,4211,395,1957,232,0551,154,217206,2411,188,95401,383,500075,161,927,6802412Service

StatisticsPhysicalLogicalService

NameDBTime

(s)DBCPU

(s)ReadsReadsxxoss24,556.74,034.81,192,835##########SYS$USERS17,375.38,156.9311,706##########STRMADMIN.HD_APPLY_QU0.00.000STRMADMIN.HQ_CAPTURE_QU0.00.000STRMADMIN.HZ_APPLY_QU0.00.000SYS$BACKGROUND0.00.080,379##########sfossXDB0.00.000Service

Wait

Class

StatsService

NameUser

I/OTotal

WtsUser

I/OWt

TimeConcurcyTotal

WtsConcurcyWt

TimeAdminTotal

WtsWtAdminTimeNetworkTotal

WtsNetworkWt

Timexxoss119912513248200534087146510SYS$USERS30506128343512506170790097331150491SYS$BACKGROUND82124

145286133775002970652540SQL

ordered

by

Elapsed

TimeElapsedTime

(s)CPUTime

(s)ExecutionsElap

perExec

(s)%

TotalDB

Time SQL

Id5,582

882

210,618

0.0 13.3

g82tjz7g9cjcjModule:

JDBC

Thin

Clientinsert

into

AO1_RECORD

(A01_TM,

NOOBILL_NO,

A_CODE,

OP_CODE,

OP_INFO,

B_CODE,

A_OPR_CODE,

OBJ_TYPE_CODE,

C_CODE,

Y_FLG,STAY_CODE,

PIECE_QTY,

A_UPLOAD_TYPE_CODE,

QTY,AMT,

ACC_CODE,

O_INFO,

O_NAME,

ZONE_CODE,

E_STRING,

B_SN,

SCHInstance

Activity

StatsStatisticTotalperSecondperTransCPU

used

by

this

session1,030,040286.72.4CPU

used

when

call

started707,330196.91.6CR

blocks

created213,78659.50.5Cached

Commit

SCN

referenced6,5621.80.0Commit

SCN

cached10.00.0DB

time12,024,0423,346.927.4DBWR

checkpoint

buffers

written2,569,456715.25.9DBWR

checkpoints1480.00.0DBWR

fusion

writes45,61412.70.1DBWR

object

drop

buffers

written00.00.0DBWR

revisited

being-written

buf80.00.0DBWR

thread

checkpoint

buffers

w1,802,168501.64.1感謝您對華章培訓(xùn)網(wǎng)的支持!.Instance

Activity

Stats

-

Absolute

ValuesStatisticBegin

ValueEnd

Valuesession

cursor

cache

countopened

cursors

currentworkarea

memory

allocatedlogons

current2,671,2571,74887,511,5562972,677,9271,77487,513,485296Instance

Activity

Stats

-

Thread

ActivityStatisticTotalper

Hourlog

switches

(derived)148148.31Tablespace

IOStatsTablespaceAvAvAvAvBufferAv

BufReads

Reads/sRd(ms)Blks/RdWritesWrites/sWaitsWt(ms)A01_RECORD_I01247,141A01_NOOBILL_I01235,772A01_RECORD_D01332,709STREAMS_TBS71,7906913.51.0469,81813134,2071.96610.61.0480,99413415,9791.89310.31.06,142212,1378.2202.01.035,517102590.1File

IO

StatsTablespaceFilenameReadsAvReads/sAvRd(ms)AvBlks/RdWritesAvWrites/sBufferWaitsAv

BufWt(ms)A01_RECORD_D0120,9806/dev/rv_XXXd1_1_10g11.8

1.043407118.8A01_RECORD_D0120,9116/dev/rv_XXXd1_2_10g11.5

1.069707368.6A01_RECORD_D0120,9776/dev/rv_XXXd1_3_10g11.8

1.069307589.4A01_RECORD_D0120,9956/dev/rv_XXXd1_4_10g11.5

1.0474076111.5A01_RECORD_D0120,9436/dev/rv_XXXd1_5_10g11.7

1.0414068310.3A01_RECORD_D0120,5376/dev/rv_XXXd1_6_10g12.0

1.0357067912.8A01_RECORD_D01/dev/rv_XXXd1_7_10gBuffer

Pool

StatisticsPBufferGetsPhysicalReadsBufferBusyWaitsDNumber

of

PoolBuffers

Hit%2,930,313

100513,126,6101,509,704Free

WritPhysical

Buff

CompWrites

Wait

Wait2,618,337

0

0679,515Instance

Recovery

StatsTargt

EstdMTTR

MTTR(s)

(s)Recovery

Actual

TargetEstd

IOs

Redo

Blks

Redo

BlksLog

File

Log

CkptSize

TimeoutRedo

Blks

Redo

BlksLog

CkptIntervalRedo

Blks-BE007396154681

1365065

2027520170933

1845384

20275202027520

27573862027520

2797272N/AN/ABuffer

Pool

AdvisoryPSize

forEst

(M)SizeFactorBuffers

forEstimateEstPhysReadFactorEstimatedPhysical

ReadsD2,432.1291,6884.41,884,522,143D4,864.2583,3762.91,258,101,527D7,296.3875,0642.2968,644,064D9,728.41,166,7521.9808,378,549D12,160.51,458,4401.6703,726,860D14,592.61,750,1281.5625,478,384D17,024.72,041,8161.3563,387,061D19,456.82,333,5041.2511,992,776D21,888.92,625,1921.1468,874,071D24,3201.02,916,8801.0432,162,668D24,4321.02,930,3131.0430,595,785D26,7521.13,208,5680.9400,871,822D29,1841.23,500,2560.9373,393,743D31,6161.33,791,9440.8349,076,484D34,0481.44,083,6320.8327,693,238D36,4801.54,375,3200.7309,308,005圖形化展示PGA

Aggr

SummaryPGA

Cache

Hit

%W/A

MB

ProcessedExtra

W/A

MB

Read/Written98.7123,6331,566重要的警告Warning: pga_aggregate_target

was

set

too

low

for

current

workload,asthisvalue

was

exceeded

during

this

interval. Use

the

PGA

Advisory

viewto

help

identify

a

different

value

for

pga_aggregate_target.PGAAggr

Target

StatsPGA

Aggr Auto

PGA PGA

MemTarget(M)

Target(M)

Alloc(M)W/A

PGAUsed(M)-BE10,24010,240640

17,078.9640

17,371.41.63.0.0

100.0.0

100.0%PGA

%Auto

%ManW/A

W/A

W/A

Global

MemMem

Mem

Mem

Bound(K).0

1,048,570.0

1,048,570?AUTOPGATARGET:自動(dòng)估算的PGA大小，一般小于PGA參數(shù)?PGAMem Alloc：實(shí)際分配的PGA空間的最大值?W/APGAUsed：當(dāng)前手工分配和自動(dòng)分配PGA的大小PGA

Aggr

Target

HistogramLowHighOptimalOptimalTotal

ExecsOptimal

Execs1-PassExecsM-PassExecs2K4K1,093,4881,093,4880064K128K5,9705,97000128K256K1,9041,90400256K512K4,2804,27640512K1024K144,239144,12711201M2M1,2531,251202M4M5574857204M8M5204774308M16M45044010016M32M1301300032M64M382018064M128M6600PGA

Memory

AdvisoryW/A

MBEstd

ExtraW/A

MB

Read/PGA

TargetEst

(MB)SizeFactrProcessed

Written

to

DiskEstd

PGACacheHit

%Estd

PGAOverallocCount1,2802,5605,1207,68010,24012,28814,33616,3840.10.30.50.81.01.21.41.643,285,852.443,285,852.443,285,852.443,285,852.443,285,852.443,285,852.443,285,852.443,285,852.41,443,733.31,258,915.11,014,195.2796,843.8138,262.06,783.84,854.6928.597.097.098.098.0100.0100.0100.0100.098,66290,17973,20857,49444,62831,89118,2691,28618,4321.843,285,852.40.0100.0020,48030,72040,96061,44081,9202.03.04.06.08.043,285,852.443,285,852.443,285,852.443,285,852.443,285,852.40.00.00.00.00.0100.0100.0100.0100.0100.000000感謝您對華章培訓(xùn)網(wǎng)的支持!.Shared

Pool

AdvisoryEst

LCEst

LCEst

LCEst

LCSharedSPEst

LCTimeTimeLoadLoadEst

LCPoolSizeSizeEst

LCSavedSavedTimeTimeMemSize(M)Factr(M)Mem

Obj(s)Factr(s)FactrObj

Hits1,616.445423,673#######1.028,6111.1###########2,032.562132,217#######1.026,1291.0###########2,448.669035,989#######1.026,1261.0###########2,864.771937,590#######1.026,1251.0###########3,280.871937,590#######1.026,1251.0###########3,696.971937,590#######1.026,1251.0###########4,1121.071937,590#######1.026,1251.0###########4,5281.171937,590#######1.026,1251.0###########4,9441.271937,590#######1.026,1251.0###########5,3601.371937,590#######1.026,1251.0###########5,7761.471937,590#######1.026,1251.0###########6,1921.571937,590#######1.026,1251.0###########6,6081.671937,590#######1.026,1251.0###########7,0241.771937,590#######1.026,1251.0###########7,4401.871937,590#######1.026,1251.0###########7,8561.971937,590#######1.026,1251.0###########8,2722.071937,590#######1.026,1251.0###########SGA

Target

AdvisorySGA

TargetSize

(M)SGA

SizeFactorEst

DBTime

(s)Est

PhysicalReads10,2400.320,531,7351,751,654,42220,4800.513,249,694784,349,86730,7200.811,542,685558,078,53740,9601.010,582,822430,582,93151,2001.39,909,755341,236,97361,4401.59,909,758341,236,97371,6801.89,909,760341,236,97381,9202.09,909,762341,236,973Streams

Pool

AdvisorySize

forEst

(MB)SizeFactorEst

SpillCountEst

SpillTime

(s)Est

UnspillCountEst

UnspillTime

(s)8,5920.7379,13423452,560,91819,6769,8240.8377,91723452,560,91319,67611,0560.9377,91723452,560,91319,67612,2881.0377,91523452,560,91219,67613,5201.1377,91523452,560,91219,67614,7521.2375,40223352,560,91219,67615,9841.3375,40323352,560,91219,67617,2161.4375,40123352,560,91219,67618,4481.5375,39923352,560,91119,67619,6801.6375,40023352,560,91119,67620,9121.7375,40223352,560,91119,67622,1441.8375,40223352,560,91119,67623,3761.9375,40423352,560,91119,67624,6082.0375,40623352,560,91119,67625,8402.1375,40723352,560,91119,676Buffer

WaitStatisticsClassWaitsTotalWaitTime

(s)AvgTime(ms)data

block674,8641,27921st

level

bmb2,18742undo

block74434undo

header1,093332nd

level

bmb12101segment

header8502Enqueue

ActivityEnqueue

Type

(Request

Reason)Requests Succ

Gets

Failed

GetsWaitsWt

Time

(s)

Av

Wt

Time(ms)SR-SynchronizedReplication15,96115,963013,70168349.86TX-Transaction(row

lock

contention)12712701271581,247.67TX-Transaction(index

contention)21,43121,356011,0151089.84TM-DML1,362,6081,362,566050671,342.06FB-Format

Block7,4697,46903,711133.46TT-Tablespace34,24434,244020,81913.61CF-ControlfileTransaction35,79235,719731,651106.19常見鎖類型CF–CI-DR-HW–PR-PS-SQ-ST-TM-TS–TT-TX–UL–US–控制文件（Control

File）Cross

Instance分布式事務(wù)恢復(fù)（Distributedrecovery）高水位（High

Watermark）進(jìn)程啟動(dòng)（Process

Startup）并行SLAVER進(jìn)程同步（ParallelSlaveSynchronization）序列號(hào)（Sequence）空間管理事務(wù)（Space

Transaction）DML臨時(shí)段管理（TemporarySegment）臨時(shí)表（Temporary

table）事務(wù)用戶定義鎖（DBMS_LOCK）回滾段（Undo

Segment）Undo

Segment

SummaryNumber

ofTransactionsuS/uR/uU/eS/eR/eUUndo Num

UndoTS#

Blocks

(K)5

1,132.5652,633Max

Qry Max

Tx

Min/Max

STO/Len

(s)

Concurcy

TR

(mins)

OOS383 49

120/120

0/00/0/0/0/0/0?UNDO表空間號(hào)為5?UNDO表空間約大小為9000M?采樣周期內(nèi)總共有652663個(gè)事務(wù)?最大查詢長度為383秒?最大并行事務(wù)數(shù)49個(gè)?沒有ORA-15555，沒有出現(xiàn)表空間不足相關(guān)指標(biāo)含義uS-

unexpiredStolenuR-

unexpiredReleaseduU-

unexpiredreUsedeS-

expiredStoleneR-

expiredReleasedeU-

expiredreUsedUndo

Segment

StatsEnd

TimeNum

Undo Number

of

Max

QryBlocks

Transactions

Len

(s)Max

Tx

Tun

Ret

STO/Concy (mins)

OOSuS/uR/uU/eS/eR/eU16-Nov

20:0616-Nov

19:5616-Nov

19:4616-Nov

19:3616-Nov

19:2616-Nov

19:1616-Nov

19:06134,621123,591223,815210,711188,301158,26993,21567,63967,881101,757117,319110,568110,26677,2039077111383678714141354947444440120

0/0120

0/0120

0/0120

0/0120

0/0120

0/0120

0/00/0/0/0/0/00/0/0/0/0/00/0/0/0/0/00/0/0/0/0/00/0/0/0/0/00/0/0/0/0/00/0/0/0/0/0Latch

ActivityLatch

NameGetRequestsPct

Avg

WaitGet

Slps

TimeMiss

/Miss

(s)PctNoWait

NoWaitRequests

Misscache

buffer

handlescache

buffers

chainscache

buffers

lru

chaincache

table

scan

latchchannel

handle

pool

latcchannel

operations

parencheckpoint

queue

latchclient/application

infocommit

callback

allocati1,984,7581,146,663,9819,956,53108,0711,324,50243,921,0314,83121,2300.01.20.5N/A0.00.60.00.00.00.00.00.0N/AN/A3.40.0N/AN/A020004100007,892,8078,927,384298002,694,81400N/A0.00.20.0N/AN/A0.0N/AN/Adb

cache相關(guān)的閂鎖cachebuffer

handles3,304,4260.00.000N/Acachebuffers

chains1,947,850,7673.10.01137,821,3160.0cachebuffers

lru

chain6,997,1850.40.0038,587,3350.4cachetable

scan

latch0N/AN/A0813,8980.0checkpoint

queue

latch4,562,4320.00.002,364,9200.0共享池相關(guān)的閂鎖librarycache42,338,3490.30.0010,1180.2librarycacheload

lock2820.0N/A00N/Alibrarycachelock6,764,1300.10.000N/Alibrarycachelock

alloc40,0280.0N/A00N/Alibrarycachepin24,725,5990.10.000N/Alibrarycachepin

alloca5,0110.0N/A00N/Arow

cache

objects17,337,3050.00.002330.0shared

pool9,363,1980.20.000N/Ashared

pool

simulator21,898,3570.00.000N/AREDO相關(guān)的閂鎖redoallocation3,906,6450.50.0018,728,2001.0redocopy0N/AN/A018,789,3690.3redowriting3,932,7220.00.000N/ALatch

Sleep

BreakdownLatch

NameGet

RequestsMissesSleepsSpin

GetsSleep1Sleep2Sleep3cache

buffers

chains60,121

##########0001,146,663,981

14,194,293logminer

work

area93,443,7901,887,1881791,887,036000messages8,834,029360,55687360,470000active

checkpointqueue

latch2,361,392346,53593346,442000shared

pool38,038,776256,5391,228255,340000redo

writing4,174,021185,33867185,271000Latch

Miss

SourcesNoWaitWaiterLatchNameWhereMissesSleepsSleepscachebufferschainskcbgtcr:

kslbegin

excl0267,267249,097cachebufferschainskcbrls:

kslbegin019,55340,589cachebufferschainskclcls_3011,5625,435cachebufferschainskcbzwb06,74310,608cachebufferschainskcbgtcr:

fast

path01,569302cachebufferschainskcbget:

pin

buffer01,222367cachebufferschainskcbbxsv0526548cachebufferschainskcbgcur:

kslbegin0287310cachebufferschainskcbchg:

kslbegin:

bufs

not02641,048cachebufferschainskcbnlc0230242cachebufferschainskcbget:

exchange

rls06720cachebufferschainskcbget:

exchange05445cachebufferschainskcbzgb:

scan

from

tail.

no0470cachebufferschainskcbgtcr:

kslbegin

shared04324cachebufferschainskcbgkcbcr02722感謝您對華章培訓(xùn)網(wǎng)的支持!.Segments

by

Logical

ReadsSegments

by

Logical

Reads->

Total

Logical

Reads:DB/Inst:

xxxxxx2/xxxxxx2

Snaps:

13257-13258513,746,838->

Captured

Segments

account

forTablespaceOwner

Name Object

Name89.9%

of

TotalSubobjectNameObj.TypeLogicalReads%Totalrds6rds6rds6rds6rds6rds_LOG_D0

st_THREE_PART_CHECKBAR_RECORD

st_BAR_RECORDBAR_RECORD

IDX_st_BAR_RECORD_3BAR_RECORD

IDX_st_BAR_RECORD_3BAR_RECORD

IDX_st_BAR_RECORD_3TABLEORD_200911

TABLEORD_200911

INDEXORD_200908

INDEXORD_200909

INDEX116,607,536110,294,54421,493,44017,538,89616,489,58422.7021.474.183.413.21Segments

by

Physical

ReadsOwnerTablespaceNameObject

NameSubobjectNameObj.TypePhysicalReads%Totalrds6XXX_RECORDst_XXX_RECORDORD_200911TABLE332,38620.96rds6XXX_NOOBILIDX_st_XXX_NOOBILL_TW_T_200911INDEX235,49014.85rds6XXX_RECORDIDX_st_XXX_RECORD_1ORD_200911INDEX220,28313.89rds6XXX_NOOBILst_XXX_NOOBILL_TRACEW_T_200911TABLE90,9585.74rds6XXX_NOOBILIDX_st_XXX_NOOBILL_TW_T_200910INDEX66,7084.21Segments

by

Row

Lock

WaitsOwnerTablespaceNameObject

NameSubobjectNameObj.TypeRowLockWaits%

ofCapturerds6BAR_RECORDIPK_st_BAR_RECORDORD_200911INDEX6,22129.63rds6BAR_NOOBILIPK_st_BAR_NOOBILL_TW_T_200911INDEX6,18129.44rds6BAR_RECORDIDX_st_BAR_RECORD_3ORD_200911INDEX1,3296.33rds6BAR_RECORDIDX_st_BAR_RECORD_4ORD_200911INDEX1,2796.09rds6BAR_NOOBILIDX_st_BAR_NOOBILL_TW_T_200911INDEX7363.51Segments

by

ITL

WaitsOwnerTablespaceNameObject

NameSubobjectNameObj.TypeITLWaits%

ofCapturerds6BAR_RECORDIDX_st_BAR_RECORD_3ORD_200911INDEX2228.95rds6BILLING_INIDX_TL_VISIT_PARAM_TLOG_200911INDEX1013.16rds6BILLING_INIDX_TL_VISIT_LOG_TM_LOG_200911INDEX911.84rds6BAR_RECORDIPK_st_BAR_RECORDORD_200911INDEX67.89rds6BAR_RECORDIDX_st_BAR_RECORD_4ORD_200911INDEX45.26Segments

by

Buffer

Busy

WaitsOwnerTablespaceNameObject

NameSubobjectNameObj.TypeBufferBusyWaits%

ofCapturerds6rds_LOG_D0st_BAR_EXPORT_INTTABLE60,72653.34rds6BAR_RECORDIPK_st_BAR_RECORDORD_200911INDEX29,01025.48rds6BAR_NOOBILIPK_st_BAR_NOOBILL_TW_T_200911INDEX13,69812.03rds6rds_LOG_I0IPK_st_BAR_EXPORT_ININDEX1,4391.26rds6rds6_BASE_IDX_st_BAR_EXPORT_ININDEX1,113.98Segments

by

Global

Cache

Buffer

BusyOwnerTablespaceNameObject

NameSubobject