 |
|
The stats$latch_misses_summary Table
Oracle Tips by Burleson Consulting
|
This table records latch misses for the Oracle
database. When we observe latches, we must remember that there are two
types of latches:
·
Willing-to-wait latch A
willing-to-wait latch will repeatedly try to reacquire the latch. The
redo allocation latch is a good example.
·
Immediate latch These
latches must acquire a latch immediately or the task will abort. The
redo copy latch is a good example of an immediate latch.
Latch sleeps are very important because they indicate
the number of times we had to sleep because we could not get a latch.
The latches with the highest sleep values are the ones to concentrate
on. Latch misses are recorded in the nwfail_count column of this
table, and misses are important because they indicate system resource
shortages:
L 4-1
SQL>
desc stats$latch_misses_summary;
Name Null? Type
----------------------------------------- --------
-------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
PARENT_NAME NOT NULL VARCHAR2(50)
WHERE_IN_CODE NOT NULL VARCHAR2(64)
NWFAIL_COUNT NUMBER
SLEEP_COUNT NUMBER
In Oracle9i, we see the addition of a new column to the
stats$latch_misses_summary table.
There are more than a dozen types of latches, but there
are only a few that impact Oracle performance. Here is a brief listing
of some important latches and the remedy to shortages.
|
Latch Name |
Willing-to-Wait? |
Action |
|
Redo copy |
Yes |
Increase redo log size |
|
Redo allocation |
No |
Increase log_small_entry_max_size |
|
Library cache |
Yes |
Increase shared_pool_size |
|
Shared pool |
Yes |
Increase shared_pool_size |
We will be discussing Oracle latches in detail in
Chapter 9.
Here is a sample STATSPACK report from the
information in this table.
rpt_latch_misses.sql
L 4-2
SNAP_DAT
PARENT_NAME WHERE_IN_C SUM_NWFAIL SUM_SLEEP
-------- ---------------------------- ---------- ----------
---------
12-12-02 cache buffers chains
kcbbxsv
0 3
cache buffers
chains kcbget: pi 0 3
n buffer
12-13-02 cache
buffers chains kcbbxsv 0 24
channel
operations
parent latch
ksrwait() 0
96
library
cache kglhdgn: c 0 48
hild:
library
cache kglic 0 384
row cache
objects kqrpre: fi 0 24
nd obj
The
stats$sgastat_summary Table (Oracle8i only)
The stats$sgastat_summary table sums up some of the
important system-wide values for the Oracle database. This table was
obsoleted in Oracle9i STATSPACK.
L 4-3
SQL> desc
stats$sgastat_summary;
Name Null? Type
----------------------------------------- --------
------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
NAME NOT NULL VARCHAR2(64)
BYTES NUMBER
In the name column for this table we see that 22 rows
are added for each STATSPACK snapshot:
L 4-4
SQL> select
distinct name from stats$sgastat_summary;
NAME
----------------------------------------------------------------
DML locks
KGFF heap
KGK heap
KQLS heap
PL/SQL DIANA
PL/SQL MPCODE
PL/SQL SOURCE
PLS non-lib hp
SEQ S.O.
SYSTEM PARAMETERS
State objects
branches
character set object
db_block_buffers
db_block_hash_buckets
dictionary cache
distributed_transactions-
enqueue_resources
event statistics per sess
fixed allocation callback
fixed_sga
free memory
ktlbk state objects
library cache
log_buffer
long op statistics array
message pool freequeue
miscellaneous
sessions
sql area
state objects
table columns
table definiti
temporary tabl
transaction_branches
transactions
trigger defini
trigger inform
trigger source
type object de
view columns d
The stats$sgastat_summary table is most beneficial for
long-term trending reports in STATSPACK. Here is a sample from the
data in this table showing the change in size of SGA areas.
rpt_sga_summary.sql
L 4-5
NAME MIN_BYTES MAX_BYTES CHG_BYTES
------------------------- ------------ ------------ ------------
KQLS heap 1,827,000 8,520,208 6,693,208
PL/SQL DIANA 6,601,096 13,886,264 7,285,168
PL/SQL MPCODE 1,430,384 3,216,128 1,785,744
PL/SQL SOURCE 1,008 8,672 7,664
dictionary cache 1,910,280 5,253,304 3,343,024
free memory 139,558,232 194,640,008 55,081,776
library cache 7,395,640 24,541,760 17,146,120
miscellaneous 665,112 916,864 251,752
sql area 3,368,200 23,797,248 20,429,048
state objects 126,600 278,448 151,848
table columns 35,064 387,104 352,040
table definition 5,496 29,416 23,920
temporary table 608 8,576 7,968
trigger definition 7,016 97,072 90,056
trigger source 7,000 49,760 42,760
view columns
1,112 92,032 90,920
The
stats$sql_summary Table
The SQL statistics summary is one of the most important
tables within the STATSPACK facility. As we discussed in Chapter 1,
tuning of SQL can often have a profound impact on the performance of
your Oracle system, and the stats$sql_summary table provides the text
of each SQL statement and a detailed description of the resources used
by each and every SQL statement that meets the necessary thresholds
conditions to be captured in a snapshot.
We will be returning to this table many times
in this text, and especially in Chapter 11. For now, all we need to
know is that this table tracks the number of executions, the number of
parse calls, and the number of data blocks read and written for each
SQL statement. This information can be an invaluable tool when it
comes time to tune the SQL within your Oracle database.
It also bears mentioning that the
stats$sql_summary table is the most highly populated of all of the
STATSPACK tables. If your threshold values are set very low and you
have a busy database, it's not uncommon to get 300 to 500 rows added
to the stats$sql_summary table each and every time STATSPACK requests
a snapshot. Hence, it is very important that the Remote DBA remove unwanted
rows from the stats$sql_summary table once they are no longer used for
SQL tuning.
Here is the version of stats$sql_summary for
Oracle8 and Oracle8i.
L 4-6
SQL> desc
STATS$SQL_SUMMARY;
Name Null? Type
----------------------------------------- --------
-------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
SORTS NUMBER
MODULE VARCHAR2(64)
LOADED_VERSIONS NUMBER
EXECUTIONS NUMBER
LOADS NUMBER
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
ADDRESS NOT NULL RAW(8)
HASH_VALUE NOT NULL NUMBER
VERSION_COUNT NUMBER
Oracle9i has numerous additional columns for
additional details about the SQL. Here is the table description for
Oracle9i.
SQL> desc
STATS$SQL_SUMMARY
Name Null? Type
----------------------------------------- -------- --------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
TEXT_SUBSET NOT NULL VARCHAR2(31)
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
SORTS NUMBER
MODULE VARCHAR2(64)
LOADED_VERSIONS NUMBER
EXECUTIONS NUMBER
LOADS NUMBER
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
ADDRESS RAW(8)
HASH_VALUE NOT NULL NUMBER
VERSION_COUNT NUMBER
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
For details on the use of the stats$sql_summary table
for Oracle tuning, please refer to Chapter 11.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
