 |
|
The stats$sysstat Table
Oracle Tips by Burleson Consulting
|
You should note that the structure of this
STATSPACK table is identical to the v$sysstat structure, with 226
distinct statistic names in Oracle8i. Every snapshot that you take
with STATSPACK will add these 226 rows to the stats$sysstat table in
Oracle8i and 232 rows in Oracle9i.
L 4-18
SQL> desc
STATS$SYSSTAT;
Name Null? Type
----------------------------------------- --------
-------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STATISTIC# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(64)
VALUE NUMBER
While many of these statistics are seldom used
in tuning Oracle, there are some statistics that you will find quite
useful. In Chapter 14 you will see STATSPACK scripts that will report
on specific statistic names within stats$sysstat. Here are the most
important system statistics for Oracle tuning. Again, we will return
to this table in detail in Chapter 9 on instance tuning.
L 4-19
STATISTIC#
NAME
----------
------------------------------------------------------------
3 opened cursors current
9 session logical reads
12 CPU used by this session
13 session connect time
15 session uga memory
20 session pga memory
23 enqueue waits
24 enqueue deadlocks
39 consistent gets
40 physical reads
41 db block changes
44 physical writes
46 summed dirty queue length
67 hot buffers moved to head of LRU
84 prefetched blocks
85 prefetched blocks aged out before use
86 physical reads direct
87 physical writes direct
106 redo log space requests
107 redo log space wait time
151 table scans (long tables)
158 table fetch continued row
169 parse time cpu
174 bytes sent via SQL*Net to client
175 bytes received via SQL*Net from client
176 SQL*Net roundtrips to/from client
177 bytes sent via SQL*Net to dblink
178 bytes received via SQL*Net from dblink
179 SQL*Net roundtrips to/from dblink
181 sorts (disk)
203 OS User level CPU time
204 OS System call CPU time
211 OS Wait-cpu (latency) time
213 OS Major page faults
214 OS Swaps
222 OS System calls
223 OS Chars read and written
Uses for the stats$sysstat Table
This table is most commonly used when analyzing
overall database load under certain conditions. The common uses of
this table include:
-
Comparing OS major page faults to vmstat page-in values
-
Comparing OS
swaps to vmstat page-in values
-
Using OS chars
read and written to measure overall I/O load
on the database
-
Reviewing redo log
space behavior to measure configuration of
the online redo logs
-
Determining the
overall data buffer hit ratio using consistent gets
-
Monitoring
enqueue deadlocks to locate sources of contention
-
Monitoring
sorts (disk) to identify contention in the TEMP tablespace
-
Monitoring
table fetch continued row to seek chained rows
-
Monitoring SQL*Net
metrics to identify times of peak network usage
The stats$sesstat Table
Session statistics are captured from the
v$sesstat view. As you may remember from basic Remote DBA class, this table
only contains the statistic number and the value. To see the
corresponding name for the value, you need to JOIN into the v$statname
view. The statistics numbers for stats$sesstat are the same as the
stats$sysstat table.
CAUTION: The stats$sesstat table only takes a
snapshot of the sessions that were active at the exact moment that the
snapshot was executed. If your database has hundreds of small
transactions each minute, you will only see a small number of the
total transactions in this table. Also, elapsed-time comparisons are
meaningless with this table because of the transient nature of Oracle
sessions.
L 4-20
SQL> desc
STATS$SESSTAT;
Name Null? Type
----------------------------------------- --------
-------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STATISTIC# NOT NULL NUMBER
VALUE NUMBER
If you want to see the actual values for all 226 session
statistics (232 in Oracle9i), you must JOIN from the stats$sesstat
into the v$statname view. The statistic names are the same as for the
v$sysstat view.
Uses for stats$sesstat
Because of the incomplete information in this
table (it only snaps sessions that are active at the time of the
snapshot), this table has limited use for Oracle tuning. However, the
information can be used in long-term trend reports to display common
session information.
The
stats$sgastat Table
This is a simple table that provides the total
size of the SGA in bytes at the time that the snapshot is taken. This
table has limited use within Oracle tuning, and the same values can be
computed from summing the memory structures within the stats$parameter
table.
L 4-21
SQL> desc
STATS$SGASTAT;
Name Null? Type
----------------------------------------- --------
-------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
NAME NOT NULL VARCHAR2(64)
BYTES NUMBER
This table changes format in Oracle9i with the
addition of the pool parameter to hold the SGA pool sizes.
SQL> desc
STATS$SGASTAT;
Name Null? Type
----------------------------------------- -------- ------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
NAME NOT NULL VARCHAR2(64)
POOL VARCHAR2(11)
BYTES NUMBER
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. |
 |
|
