Oracle服务器监控指标和分析

Oracle关键计数器含义

l CPU used by this session：所有session的cpu占用量，不包括后台进程。这项统计的单位是百分之x秒.完全调用一次不超过10ms51Testing软件测试网'v3qt-~'f/];?3Y7r

^AT o6J0l db block changes：那部分造成SGA中数据块变化的insert,update或delete操作数这项统计可以大概看出整体数据库状态。在各项事务级别，这项统计指出脏缓存比率。

l execute count：执行的sql语句数量(包括递归sql)51Testing软件测试网.g9of ?E2\B

|4a[)Ozk?y0l logons current：当前连接到实例的Sessions。如果当前有两个快照则取平均值。51Testing软件测试网$Kg;z!Q3q|

?hvg]t4Ew0l logons cumulative：自实例启动后的总登陆次数。

l parse count (hard)：在shared pool中解析调用的未命中次数。当sql语句执行并且该语句不在shared pool或虽然在shared pool但因为两者存在部分差异而不能被使用时产生硬解析。如果一条sql语句原文与当前存在的相同，但查询表不同则认为它们是两条不同语句，则硬解析即会发生。硬解析会带来cpu和资源使用的高昂开销，因为它需要oracle在shared pool中重新分配内存，然后再确定执行计划，最终语句才会被执行。

7AV.E-MO2U0l parse count (total)：解析调用总数，包括软解析和硬解析。当session执行了一条sql语句，该语句已经存在于shared pool并且可以被使用则产生软解析。当语句被使用(即共享)所有数据相关的现有sql语句(如最优化的执行计划)必须同样适用于当前的声明。这两项统计可被用于计算软解析命中率。51Testing软件测试网Vi7v0Ft*\/K-R @

m gTLX3{&YZ
{8^0l parse time cpu：总cpu解析时间(单位：10ms)。包括硬解析和软解析。51Testing软件测试网ib1I#GI4{1\

l parse time elapsed：完成解析调用的总时间花费。

注：SQL语句的解析有软解析soft parse与硬解析hard parse之说，以下是5个步骤：51Testing软件测试网KHM*|G8H*f:T B lk

-~/jisfaE01：语法是否合法(sql写法)51Testing软件测试网*[N&xwtK\

9G}lF;A02：语义是否合法(权限，对象是否存在)51Testing软件测试网LE#E;I3i,E

-p-k] W Sf2v C c03：检查该sql是否在公享池中存在51Testing软件测试网T~/C}"l

KP]'hP0--如果存在,直接跳过4和5,运行sql.此时算soft parse

8yn Lj0H4VH\6P#C04：选择执行计划

Oq;i+E,J2e'k9I _05：产生执行计划51Testing软件测试网f,dBF_nq ^6Z+X4f

j `d)o@~l0如果5个步骤全做,这就叫hard parse.

l physical reads：OS blocks read数。包括插入到SGA缓存区的物理读以及PGA中的直读这项统计并非i/o请求数。51Testing软件测试网,agy{ h4of-{_g

l physical writes：从SGA缓存区被DBWR写到磁盘的数据块以及PGA进程直写的数据块数量。51Testing软件测试网_%Rk7Yi-wAX&Q

_y&ZPWa9L'UGX0l redo log space requests：在redo logs中服务进程的等待空间，表示需要更长时间的log switch。51Testing软件测试网*S!v `uMJ

l redo size：redo发生的总次数(以及因此写入log buffer)，以byte为单位。这项统计显示出update活跃性。51Testing软件测试网#PZ9o3gb5Z0R

)\kT.V!|S1t0l session logical reads：逻辑读请求数。51Testing软件测试网~k z7f%P+p?

M }fJ@^o*PX1n0l sorts (memory) and sorts (disk)：sorts(memory)是适于在SORT_AREA_SIZE(因此不需要在磁盘进行排序)的排序操作的数量。sorts(disk)则是由于排序所需空间太大，SORT_AREA_SIZE不能满足而不得不在磁盘进行排序操作的数量。这两项统计通常用于计算in-memory sort ratio。

l sorts (rows):列排序总数。这项统计可被'sorts (total)'统计项除尽以确定每次排序的列。该项可指出数据卷和应用特征。

l table fetch by rowid：使用ROWID返回的总列数(由于索引访问或sql语句中使用了'where rowid=&rowid'而产生)

l table scans (rows gotten)：全表扫描中读取的总列数

l table scans (blocks gotten)：全表扫描中读取的总块数，不包括那些split的列。51Testing软件测试网^U$F_%K

X)G u:N so0l user commits + user rollbacks：系统事务起用次数。当需要计算其它统计中每项事务比率时该项可以被做为除数。例如，计算事务中逻辑读，可以使用下列公式：session logical reads / (user commits + user rollbacks)。

/hu0duNJ^)r0 

[译]其它计数器含义

1Sj/Fyx!b$mT;f%^9X0l CPU used when call started- The CPU time used when the call is started

l CR blocks created- A buffer in the buffer cache was cloned. The most common reasonfor cloning is that the buffer is held in an incompatible mode.

}&V{L
s\z6g0l Cached Commit SCN referenced- The number of times cached Commit SCN is referenced.51Testing软件测试网I5Q'I3A6R F8b)M'q

l Commit SCN cached- The number of times Commit SCN is cached.

l Current blocks converted for CR- A CURRENT buffer (shared or exclusive) is made CR before it can be used.51Testing软件测试网2Nzr*M9Ku;nM

l DBWR Flush object call found no dirty buffers- The DBWR did not find any dirty buffers for an object that was flushed from the cache.

l DBWR Flush object cross instance calls- The number of times DBWR received a flush by object number cross instance call (from a remote instance). This includes both checkpoint and invalidate object.51Testing软件测试网adsm4y^9B`

l DBWR buffers scanned- The total number of buffers looked at when scanning each LRU set for dirty buffers to clean. This count includes both dirty and clean buffers. Divide by DBWR LRU scans to find the average number of buffers scanned.51Testing软件测试网c5~:X"]`:Z4y*c7r

4_-s q%~@%a?~e6R0l DBWR checkpoint buffers written- The number of buffers that were written for checkpoints.51Testing软件测试网e y(KI8l

l DBWR checkpoints- Number of times the DBWR was asked to scan the cache and write all blocksmarked for a checkpoint.51Testing软件测试网 Tx}FqvU

l DBWR cross instance writes- The total number of blocks written for other instances so that they can access the buffers.51Testing软件测试网Ue
K$H5_ky
O.y(_

l DBWR free buffers found- The number of buffers that DBWR found clean when it was requested to make free buffers. Divide by DBWR make free requests to find the average number of reusable buffers at the end of each LRU.

l DBWR lru scans- The number of times that DBWR does a scan of the LRU queue lookingfor buffers to write. This includes times when the scan is to fill a batch being written for another purpose, such as a checkpoint. This statistic is always greater than or equal to DBWR make free requests.51Testing软件测试网I&H-Cv/jlk.T.E

X-r'a pE+sb0l DBWR make free requests- Number of messages received requesting DBWR to make some more free buffers for the LRU.

l DBWR revisited being-written buffer- The number of times that DBWR tried to save a buffer for writing and found that it was already in the write batch. This statistic is a measure of the amount of "useless" work that DBWR had to do in trying to fill the batch. This can occur because many sources contribute to a write batch. If thesame buffer from different sources is considered for adding to the write batch, then all but the first attempt will be "useless" since the buffer is alreadymarked as being written.

T.ldj4Tj
fV0l DBWR skip hot writes- The number of times DBWR skipped writing "hot" buffers.

h4qo"V{ c/lgK[I0l DBWR summed scan depth- The current scan depth (number of buffers examined by DBWR) is added to this statistic every time DBWR scans the LRU for dirty buffers. Divide by DBWR lru scans to find the average scan depth.51Testing软件测试网,mAd5REWP#a

l DBWR timeouts- The number of times that the DBWR has been idle since the last timeout. These are the times that the DBWR looked for buffers to idle write.51Testing软件测试网*b'Jnnj8[ g

l DBWR undo block writes- The number of transaction table blocks written by DBWR. It is an indication of how many "hot" buffers were written, leading to write complete waits.

l DDL statements parallelized- The number of DDL statements that were parallelized.51Testing软件测试网%}yE\h)b

l DML statements parallelized- The number of DML statements that were parallelized.

l PX local messages received- The number of local messages received for Parallel Executions.

l PX local messages sent- The number of local messages sent for Parallel Executions.

L(uI#[Z"{ {0l PX remote messages received- The number of remote messages received for Parallel Executions.51Testing软件测试网.n*g'kjABA

3s-kF:mVV~0l PX remote messages sent- The number of remote messages sent for Parallel Executions.51Testing软件测试网*g,z/zX$s6N
q

g4]ZX{9O4Z? m;Z$t0l SQL*Net roundtrips to/from client- Total number of Net8 messages sent to and received from the client.

Y9mT%C%R F0l SQL*Net roundtrips to/from dblink- Total number of Net8 messages sent over and received from a databaselink.51Testing软件测试网NC_WJV'~

l Unnecessary process cleanup for SCN batching- The total number of times that the process cleanup was performed unnecessarily because the session/process did not get the next batched SCN. The next batched SCN went to another session instead.51Testing软件测试网5nmCH4aJ

l Background checkpoints completed- The number of checkpoints completed by the background. This statistic is incremented when the background successfully advances the thread checkpoint.

!c*WWHo0qA1}T2L0l Background checkpoints started- The number of checkpoints started by the background. It can be larger than the number completed if a new checkpoint overrides an incomplete checkpoint. This only includes checkpoints of the thread, not individual file checkpoints for operations such as offline or begin backup. This statistic does not include the checkpoints performed in the foreground, such as ALTER SYSTEM CHECKPOINT LOCAL.

,@h9}S X;h$xg/R0l Bytes received via SQL*Net from client- The total number of bytes received from the client over Net8.

O C Jx`l0l Bytes received via SQL*Net from dblink- The total number of bytes received from a databaselink over Net8.51Testing软件测试网Y7]!X'n.y(o

tO \e9|*n'i#WTw0l Bytes sent via SQL*Net to client- The total number of bytes sent to the client from the foreground process(es).51Testing软件测试网ZKC:iK'X

l Bytes sent via SQL*Net to dblink- The total number of bytes sent over a databaselink.51Testing软件测试网.r6N_+LOSj(F1tT

l Calls to get snapshot SCN: kcmgss- The number of times a snap System Change Number (SCN) was allocated. The SCN is allocated at the start of a transaction.

l Change write time- The elapsed time for redo write for changes made to CURRENT blocks in 10s of milliseconds.51Testing软件测试网 y7]6| Hv1@

l Cleanouts and rollbacks- consistent read gets - The number of times CR gets require both block rollbacks, and block cleanouts.51Testing软件测试网`]M6o&X}(T&w

:d'qcR+FT7m0l Cleanouts only- consistent read gets - The number of times CR gets require only block cleanouts, no rollbacks.51Testing软件测试网A+}O z%FU

V'wX
B&e o'fT0l Cluster key scan block gets- The number of blocks obtained in a cluster scan.

l Cluster key scans- The number of cluster scans that were started.

l Commit cleanout failures: block lost- The number of times a cleanout at commit was attempted and could not find the correct block due to forced write, replacement, or switch CURRENT.51Testing软件测试网9FdRt"x,o t9u

l Commit cleanout failures: buffer being written- The number of times a cleanout at commit was attempted but the buffer was currently being written.

l Commit cleanout failures: callback failure- The number of times the cleanout callback function returns FALSE.

!w*?"|6h,ps7j0l Commit cleanout failures: cannot pin- The total number of times a commit cleanout was performed but failed because the block could not be pinned.

l Commit cleanout failures: hot backup in progress- The number of times cleanout at commit was attempted during hot backup. The image of the block needs to be logged before the buffer can be made dirty.51Testing软件测试网`&}M^%Q$|} s

l Commit cleanout failures: write disabled- The number of times that a cleanout at commit time was performed but the writes to the database had been temporarily disabled.51Testing软件测试网vq6Uy~$Gy&f&Q!F,C{

l Commit cleanouts- The total number of times the cleanout block at commit time function was performed.

m${$^9C1Ir0l Commit cleanouts successfully completed- The number of times the cleanout block at commit time function successfully completed.51Testing软件测试网.xZ5z*~GWjv

XV:u(K.j"e0l Consistent changes- The number of times a database block has applied rollback entries to perform. a consistent read on the block. Workloads that produce a great deal of consistent changes can consume a great deal of resources.51Testing软件测试网_ci2];sT2z$F{.R3C-e

l Consistent gets- The number of times a consistent read was requested for a block. See also consistent changes above.51Testing软件测试网@7H9L7IO-I/r8BP k,]

%j\U b,kU0l Cross instance CR read- The number of times this instance made a cross-instance call to write a particular block due to timeout on an instance lock get. The call allowed the block to be read CR rather than CURRENT.

P7C*`_3o$m'd Ed0l Data blocks consistent reads- undo records applied - The number of undo records applied to CR rollback data blocks.

l DB block gets- This statistic tracks the number of blocks obtained in CURRENT mode.51Testing软件测试网6O2i-@dtq`kk

l Deferred (CURRENT) block cleanout applications- The number of times cleanout records are deferred, piggyback with changes, always current get.51Testing软件测试网)f
~%i;ZuKt}

l Dirty buffers inspected- The number of dirty buffers found by the foreground while the foreground is lookingfor a buffer to reuse.51Testing软件测试网/V'UvMv@

l Enqueue conversions- The total number of enqueue converts.

l Enqueue deadlocks- The total number of enqueue deadlocks between different sessions.

l Enqueue releases- The total number of enqueue releases.51Testing软件测试网I,m \|1m s N

l Enqueue requests- The total number of enqueue gets.51Testing软件测试网:\ e|1]hi(Pm_

l Enqueue timeouts- The total number of enqueue operations (get and convert) that timed out before they could complete.

l Enqueue waits- The total number of waits that happened during an enqueue convert or get because the enqueue could not be granted right away.

l Exchange deadlocks- The number of times that a process detected a potential deadlock when exchanging two buffers and raised an internal, restartable error. Index scans are currently the only operations, which perform. exchanges.51Testing软件测试网J5j,gVA6^t

l Free buffer inspected- The number of buffers skipped over from the end of an LRU queue in order to find a reusable buffer. The difference between this statistic and dirty buffers inspected is the number of buffers that could not be used because they were busy, needed to be written after rapid aging out, or they have a user, a waiter, or are being read/written. For more information, see "dirty buffers inspected."

l Free buffer requested- The count of the number of times a reusable buffer or a free buffer was requested to create or load a block.51Testing软件测试网2O1eR$r9{

l Global cache defers- The number of times a ping request was deferred until later.51Testing软件测试网*I%Op*w5]4|x;Vi B

l Global cache freelist waits- The number of pings for free lock elements (when all releasable locks are in use).

l Global lock async converts- The total number of asynchronous global lock converts.51Testing软件测试网U6R+X'B[TvpO dqa

l Global lock async gets- The total number of asynchronous global lock gets.

l Global lock convert time- The total elapsed time of all synchronous (non-asynchronous) global lock converts in 10s of milliseconds.51Testing软件测试网Kc*ud6a|:b|Hj

l Global lock get time- The total elapsed time of all synchronous (non-asynchronous) global lock gets in 10s of milliseconds.51Testing软件测试网M m(e7}~L

l Global lock releases- The total number of synchronous global lock releases.

l Global lock sync converts- The total number of synchronous global lock converts.51Testing软件测试网:m k[w+O%C0u8K(_f

l Global lock sync gets- The total number of synchronous global lock gets.51Testing软件测试网^A
h}(X&l'V ?

l Immediate (CR) block cleanout applications- The number of times cleanout records are applied immediately during CR gets.

l Immediate (CURRENT) block cleanout applications- The number of times cleanout records are applied immediately during current gets.

l Kcmccs called get current SCN- The number of times the kernel got the CURRENT SCN when there was a need to casually confirm the SCN.

l Kcmgss read SCN without going to DLM- The number of times the kernel casually confirmed the SCN without going to the LM.51Testing软件测试网8`D#PMotuQ| W9A

l Kcmgss waited for batching- The number of times the kernel waited on a snapshot SCN.51Testing软件测试网DN)TD(X&D
C

l Logons cumulative- The total number of logons since the instance started. This statistic is useful only in V$SYSSTAT. It gives an instance overview of all processes that logged on.

l Next SCNs gotten without going to DLM- The number of SCNs (System Change Numbers) obtained without going to the DLM.51Testing软件测试网E#On2Au&OFh BR r

l No work - consistent read gets- The number of times CR gets require no block cleanouts nor rollbacks.

l Opened cursors cumulative- The total number of opened cursors since the instance has started (in V$SYSSTAT). In V$SESSTAT, this statistic shows the total number of cursors opened since the start of the session.51Testing软件测试网lq.HBp\(NN

l Opened cursors current- The total number of current open cursors.51Testing软件测试网9?%no,W;x'\\7v0[

l Opens of replaced files- The total number of files that needed to be reopened because they were no longer in the process file cache.

l Opens requiring cache replacement- The total number of file opens that caused a current file to be closed in the process file cache.

l Parse time CPU- The total CPU time used for parsing (hard and soft) in 10s of milliseconds.

l Parse time elapsed- The total elapsed time for parsing in 10s of milliseconds. By subtracting parse time CPU from this statistic, the total waiting time for parse resources is determined. For more information, see parse time CPU above.51Testing软件测试网_Q4LK}!h`3lN6B

l Queries parallelized- The number of SELECT statements that got parallelized.

l Recovery array read time- The elapsed time of I/O while doing recovery.

l Recovery array reads- The number of reads performed during recovery.51Testing软件测试网z,z\2P2u(K,iW^v

l Recovery blocks read- The number of blocks read during recovery.51Testing软件测试网5ke5["j'vIB%xM/G

l Recursive calls- Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates a SQL statement. These internal SQL statements generate recursive calls.51Testing软件测试网/O XS-m6i+K2Y*g

l Recursive CPU usage- The total CPU time used by non-user calls (recursive calls). Subtract this value from CPU used by this session to determine how much CPU time was used by the user calls.

l Redo entries- This statistic increments each time redo entries are copied into the redo log buffer.

l  Redo entrieslinearized-The total number of entries of size <= REDO_ENTRY_PREBUILD_THRESHOLD. Building these entries increases CPU time, but may also increase concurren