Introduction
These values are read and evaluated from the stats$sysstat
table. In the original StatsPack package, the report just reads all data
belonging to the start and end snapshots and sets them in relation (i.e.
the values belonging to the same event: the start value will be substracted
from the end value to obtain the difference, which is then used for the
calculations).
It is quite obvious, that not all values in this table make sense. Just
to pick some examples: what does change write time per second
mean (or any other time per second)? Moreover, all the timed values have a
different base: some are in seconds, some in 1/100 seconds, others are even
in 1/10 milliseconds...
Since this report is derived from the original StatsPack report, we tend
to make the same stupid stuff and read all data from this
table, for at least two reasons: first we want to be sure to catch all
useful values for all Oracle versions, and second the overhead of separating
the data would be larger then the use of it. But in order to help you to
distinguish the one from the other, the following list gives all events
known to me together with a short explanation, if available.
Events and explanations
CPU used by this session : the term "this session" is
misleading; this statistic applies to "all sessions". It is the amount of
CPU time (in 10s of milliseconds) used between when a user call started
and ended. Since some events are done in shorter time, 0 ms are added to
the statistics in those cases.
CR blocks created : A buffer in the buffer cache was cloned.
The most common reason for cloning is that the buffer is held in an
incompatible mode.
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.
DBWR checkpoint buffers written : The number of buffers
that were written for checkpoints.
DBWR checkpoints : Number of times the DBWR was asked to
scan the cache and write all blocks marked for a checkpoint.
DBWR free buffers found : The number of buffers that the
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.
DBWR lru scans : The number of times that the DBWR does a
scan of the LRU queue looking for 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 .
DBWR make free requests : Number of messages received
requesting DBWR to make some more free buffers for the LRU.
DBWR revisited being-written buffer : The number of times
that the 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 the same 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 already
marked as being written.
DBWR summed scan depth : The current scan depth (number of
buffers examined by DBWR) is added to this statistic every time the DBWR
scans the LRU for dirty buffers. Divide by DBWR lru scans to
find the average scan depth.
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.
SQL*Net roundtrips to/from client : Total number of Net8
messages sent to and received from the client.
SQL*Net roundtrips to/from dblink : Total number of Net8
messages sent over and received from a database link.
background checkpoints completed : The number of
checkpoints completed by the background. This statistic is incremented
when the background successfully advances the thread checkpoint.
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 .
bytes received via SQL*Net from client : The total number
of bytes received from the client over Net8.
bytes received via SQL*Net from dblink : The total number
of bytes received from a database link over Net8.
bytes sent via SQL*Net to client : The total number of
bytes sent to the client from the foreground process(es).
bytes sent via SQL*Net to dblink : The total number of
bytes sent over a database link.
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.
change write time : The elapsed time for redo write for
changes made to CURRENT blocks in 1/10s of milliseconds.
cleanouts and rollbacks - consistent read gets : The
number of times the CR gets require both block rollbacks, and block
cleanouts.
cleanouts only - consistent read gets : The number of
times the CR gets require only block cleanouts, no rollbacks.
cluster key scan block gets : The number of blocks
obtained in a cluster scan.
cluster key scans : The number of cluster scans that were
started.
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.
commit cleanout failures: buffer being written : The
number of times a cleanout at commit was attempted but the buffer was
currently being written.
commit cleanout failures: callback failure : The number of
times the cleanout callback function returns FALSE.
commit cleanout failures: cannot pin : The total number of
times a commit cleanout was performed but failed because the block could
not be pinned.
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.
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.
commit cleanouts : The total number of times the cleanout
block at commit time function was performed.
commit cleanouts successfully completed : The number of
times the cleanout block at commit time function successfully completed.
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.
consistent gets : The number of times a consistent read
was requested for a block. See also consistent changes above.
data blocks consistent reads - undo records applied : The
number of undo records applied to CR rollback data blocks.
db block changes : Closely related to consistent changes,
this statistic counts the total number of changes that were made to all
blocks in the SGA that were part of an update or delete operation. These
are changes that are generating redo log entries and hence will be
permanent changes to the database if the transaction is committed. This
statistic is a rough indication of total database work. This statistic
indicates (possibly on a per-transaction level) the rate at which buffers
are being dirtied.
db block gets : This statistic tracks the number of blocks
obtained in CURRENT mode.
deferred (CURRENT) block cleanout applications : The
number of times cleanout records are deferred, piggyback with changes,
always current get.
dirty buffers inspected : The number of dirty buffers
found by the foreground while the foreground is looking for a buffer to
reuse.
enqueue conversions : The total number of enqueue converts.
enqueue deadlocks : The total number of enqueue deadlocks
between different sessions.
enqueue releases : The total number of enqueue releases.
enqueue requests : The total number of enqueue gets.
enqueue timeouts : The total number of enqueue operations
(get and convert) that timed out before they could complete.
enqueue waits : The total number of waits that happened
during an enqueue convert or get because the enqueue could not be granted
right away.
execute count : The total number of calls (user and
recursive) that execute SQL statements.
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 .
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.
immediate (CR) block cleanout applications : The number of
times cleanout records are applied immediately during CR gets.
immediate (CURRENT) block cleanout applications : The
number of times cleanout records are applied immediately during current
gets.
logons cumulative : The total number of logons since the
instance started.
logons current : The total number of current logons.
no work - consistent read gets : The number of times CR
gets require no block cleanouts nor rollbacks.
opened cursors cumulative : The total number of opened
cursors since the instance has started.
opened cursors current : The total number of current open
cursors.
parse count (hard) : The total number of parse calls (real
parses). A hard parse means allocating a workheap and other memory
structures, and then building a parse tree. A hard parse is a very
expensive operation in terms of memory use.
parse count (total) : Total number of parse calls (hard
and soft). A soft parse is a check to make sure that the permissions on
the underlying object have not changed.
parse time cpu : The total CPU time used for parsing (hard
and soft) in 10s of milliseconds.
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.
physical reads : The total number of data blocks read from
disk. This equals the number of physical reads direct plus
all reads into buffer cache.
physical writes : The total number of data blocks written
to disk. This equals the number of physical writes direct
plus all writes from buffer cache.
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.
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.
redo entries : This statistic increments each time redo
entries are copied into the redo log buffer.
redo log space requests : The active log file is full and
Oracle is waiting for disk space to be allocated for the redo log entries.
Space is created by performing a log switch. Small log files in relation
to the size of the SGA or the commit rate of the workload can cause
problems. When the log switch occurs, Oracle must ensure that all
committed dirty buffers are written to disk before switching to a new log
file. If you have a large SGA full of dirty buffers and small redo log
files, a log switch must wait for DBWR to write dirty buffers to disk
before continuing. Also, examine the log file space and
log file space switch wait events in V$SESSION_WAIT .
redo log space wait time : The total elapsed time of
waiting for redo log space request in 10s of milliseconds.
redo ordering marks : The number of times that an SCN had
to be allocated to force a redo record to have a higher SCN than a record
generated in another thread using the same block.
redo size : The total amount of redo generated in bytes.
redo synch time : The elapsed time of all redo sync write
calls in 10s of milliseconds.
redo synch writes : Usually, redo that is generated and
copied into the log buffer need not be flushed out to disk immediately.
The log buffer is a circular buffer that LGWR periodically flushes.
Redo sync writes increments when changes being applied must
be written out to disk due to a commit.
redo wastage : Number of bytes wasted because redo blocks
needed to be written before they are completely full. Early writing may be
needed to commit transactions, to be able to write a database buffer or to
switch logs.
redo write time : The total elapsed time of the write from
the redo log buffer to the current redo log file in 10s of milliseconds.
redo writer latching time : The elapsed time need by LWGR
to obtain and release each copy latch in 10s of milliseconds. This is only
used if the initialization parameter LOG_SIMULTANEOUS_COPIES
> 0.
redo writes : Count of the total number of writes by LGWR
to the redo log files.
rollback changes - undo records applied :
rollbacks only - consistent read gets :
session connect time : The connect time for the session in
1/100 seconds. This is the wall clock time of when the logon to this
session occurred.
session logical reads : This statistic is basically
db block gets + consistent gets .
session pga memory : This statistic shows the current PGA
size for a session. It is useful only in session statistics, and thus has
no meaning here.
session pga memory max : This statistic shows the peak PGA
size for a session. It is useful only in session statistics, and thus has
no meaning here.
session uga memory : This statistic shows the current UGA
size for a session. It is useful only in session statistics, and thus has
no meaning here.
session uga memory max : This statistic shows the peak UGA
size for a session. It is useful only in session statistics, and thus has
no meaning here.
sorts (disk) : If the number of disk writes is non-zero
for a given sort operation, then this statistic is incremented. Sorts that
require I/O to disk are quite resource intensive. Try increasing the size
of the initialization parameter SORT_AREA_SIZE when the ratio
for "In-memory Sorts" in the "Instance Efficiency Percentage" segment of
the report is low.
sorts (memory) : If the number of disk writes is zero,
then the sort was performed completely in memory and this statistic is
incremented. This is further indication of sorting activity in the
application workload. You cannot do much better than memory sorts, except
maybe no sorts at all. Sorting is usually caused by selection criteria
specifications within table join SQL operations.
sorts (rows) : The total number of rows sorted.
summed dirty queue length : The sum of the dirty LRU queue
length after every write request. Divide by writes requests
to get the average queue length after write completion.
table fetch by rowid : When rows are fetched using a ROWID
(usually recovered from an index), each row returned increments this
counter. This statistic is an indication of row fetch operations being
performed with the aid of an index. Because doing table scans usually
indicates either non-optimal queries or tables without indexes, this
statistic should increase as the above issues have been addressed in the
application.
table fetch continued row : When a row that spans more
than one block is encountered during a fetch, this statistic is
incremented. Retrieving rows that span more than one block increases the
logical I/O by a factor that corresponds to the number of blocks than need
to be accessed. Exporting and re-importing may eliminate this problem.
Taking a closer look at the STORAGE parameters
PCT_FREE and PCT_USED . This problem cannot be
fixed if rows are larger than database blocks (for example, if the
LONG datatype is used and the rows are extremely large).
table scan blocks gotten : During scanning operations,
each row is retrieved sequentially by Oracle. Each block encountered
during the scan increments this statistic. This statistic informs you of
the number of database blocks that you had to get from the buffer cache
for the purpose of scanning. Compare the value of this parameter to the
value of consistent gets to get a feeling for how much of the
consistent read activity can be attributed to scanning.
table scan rows gotten : This statistic is collected
during a scan operation, but instead of counting the number of database
blocks, it counts the rows being processed.
table scan (cache partitions) : Count of range scans on
tables that have the CACHE option enabled.
table scan (direct read) : Count of table scans performed
with direct read (bypassing the buffer cache).
table scans (long tables) : Long (or conversely short)
tables can be defined as tables that do not meet the short table criteria
as described in table scans (short tables) below.
table scan (rowid ranges) : Count of table scans with
specified ROWID endpoints. This is performed for Parallel
Query.
table scans (short tables) : Long (or conversely short)
tables can be defined by optimizer hints coming down into the row source
access layer of Oracle. The table must have the CACHE option
set.
transaction rollbacks : The number of transactions being
successfully rolled back.
transaction tables consistent read rollbacks : The number
of times transaction tables are CR rolled back.
transaction tables consistent reads - undo records applied :
The number of undo records applied to CR rollback transaction tables.
user calls : Oracle allocates resources (Call State
Objects) to keep track of relevant user call data structures every time
you log in, parse or execute. When determining activity, the ratio of user
calls to RPI calls, indicates how much internal work gets generated as a
result of the type of requests the user is sending to Oracle.
user commits : When a user commits a transaction, the redo
generated that reflects the changes made to database blocks must be
written to disk. Commits often represent the closest thing to a user
transaction rate.
user rollbacks : This statistic stores the number of times
users manually issue the ROLLBACK statement or an error
occurs during users' transactions.
write requests : This statistic stores the number of times
DBWR takes a batch of dirty buffers and writes them to disk.
|