Databases Reference
In-Depth Information
The tables used to collect the data have names which start with
STATS$
, and are based on
the corresponding
V$
dynamic performance views. For example, the table
STAT$DB_CACHE_
ADVICE
has the same columns of the view
V$DB_CACHE_ADVICE
, with three columns
added in front of them,
SNAP_ID
,
DBID
,
INSTANCE_NUMBER
, which are used to identify the
snapshot, the database, and the instance respectively.
If you want to use Statspack in an Oracle Real Application Cluster (RAC)
environment, you have to launch
STATSPACK.snap
connecting to every
instance you want to gather data from.
The report is divided into several sections:
F
General information about the database instance and the snapshots used
F
Cache sizes (buffer cache, shared pool, and log buffer)
F
Load profile (instance events per second and per transaction)
F
Instance efficiency indicators (buffer cache and shared pool statistics)
F
Top five timed events, showing the first five events sorted by total wait time
in seconds
F
Host CPU and Instance CPU, showing the load on the CPU
F
Virtual Memory Paging and Memory Statistics
F
Wait events, foreground, background, and both foreground and background
grouped together
F
SQL ordered by different criteria, by CPU, by elapsed time for DB, by gets, by
executions, by parse calls, by sharable memory, by version count
F
Instance activity statistics
F
Tablespace and file I/O
F
Memory, buffer pool, and PGA statistics
F
Latch activity
F
Dictionary cache statistics
F
Library cache activity
F
SGA activity
F
init.ora
parameters
There's more...
We can configure Statspack to collect different amounts of data and to produce a report
on specific SQL; we wish to automate snapshot collection, too.
