 |
|
The stats$parameter Table
Oracle Tips by Burleson Consulting |
The stats$parameter table
contains the initialization parameters that were in effect when the snapshot was
taken. This is taken directly from the v$parameter view, and the values
correspond directly to the initialization parameters when the instance started.
This table is sometimes useful when you want to compare the before and after
performance after changing an initialization parameter. For example, after
changing
optimizer_mode, we might want to see changes in the physical I/O for
the database.
Uses for stats$parameter
The stats$parameter table is
most commonly used when performing comparisons of the database performance with
different init.ora parameter settings. The most common uses include:
-
Comparing the buffer hit ratio after increasing
db_block_buffers
-
Comparing I/O after changing
optimizer_mode
-
Comparing shared pool misses after increasing
shared_pool_size
-
Comparing disk sorts after increasing
sort_area_size
L 4-7
SQL> desc
STATS$PARAMETER;
Name Null? Type
----------------------------------------- -------- -------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
NAME NOT NULL VARCHAR2(64)
VALUE VARCHAR2(512)
ISDEFAULT VARCHAR2(9)
ISMODIFIED VARCHAR2(10)
In addition to the values
from v$parameter, the stats$parameter table also contains 285 hidden
initialization parameters. A hidden parameter is an internal
initialization parameter that is seldom modified by the DBA, and they always
begin with an underscore character. For example, to see the hidden parallel
parameters for parallel processing, you can enter the query here:
L 4-8
1 select name,
value from stats$parameter
2* where snap_id = 2000 and name like '_parallel%'
SQL> /
NAME VALUE
----------------------------------- ------------------------------
_parallel_adaptive_max_users 1
_parallel_default_max_instances 1
_parallel_execution_message_align FALSE
_parallel_fake_class_pct 0
_parallel_load_bal_unit 0
_parallel_load_balancing TRUE
_parallel_min_message_pool 64560
_parallel_recovery_stopat 32767
_parallel_server_idle_time 5
_parallel_server_sleep_time 10
_parallel_txn_global FALSE
_parallelism_cost_fudge_factor 350
It is sometimes useful to
compare the performance of the database after a change to an initialization
parameter, and you can use the stats$parameter table for this purpose. For
example, we might want to compare the average data buffer hit ratio before and
after a change to the db_block_buffers parameter. In this case we would
create a SQL query on stats$buffer_pool_statistics with a JOIN into the
stats$parameter table both before and after our change to db_block_buffers.
We will show this type of script in Chapter 9.
STATSPACK System Tables
The
STATSPACK system tables keep track of system-wide statistics, the library cache,
data on rollback segments, latches, sessions and a wealth of other data, as
shown in Figure 4-2.
Figure 4-20: The STATSPACK system tables
Let's take a close look at each of these tables and
examine the type of data contained in each.
The
stats$rollstat Table
The stats$rollstat table keeps
information on the activity of the Oracle rollback segments. The data from this
table is useful for the initial tuning of the rollback segment sizes and
initialization parameters, but once tuned, the data in this table is seldom
required:
L 4-9
SQL> desc
STATS$ROLLSTAT;
Name Null? Type
----------------------------------------- -------- -------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
USN NOT NULL NUMBER
EXTENTS NUMBER
RSSIZE NUMBER
WRITES NUMBER
XACTS NUMBER
GETS NUMBER
WAITS NUMBER
OPTSIZE NUMBER
HWMSIZE NUMBER
SHRINKS NUMBER
WRAPS NUMBER
EXTENDS NUMBER
AVESHRINK NUMBER
AVEACTIVE NUMBER
The
stats$latch Table
Internal to Oracle, latches are used
to serialize transactions and are closely tied to the OS semaphores. If the user
is planning to perform operations such as accessing data, this user must first
obtain all latch data from the table and then become the owner of the latch. If
the user's process is forced to wait for a latch because there isn't enough
space available, a slowdown will occur and we would experience internal
contention for latches.
As we can see from the listing of the v$latch view next,
there are many different kinds of latches that are used internally within
Oracle. Regardless of the types of latches the important thing is the hit ratio.
The key latches are:
If the hit ratio for any of these key latches is lower
than 99 percent, there is substantial latch contention within the database. The
contention for these key latches can be reduced in a variety of ways, including
tuning the database writer, tuning the redo log files, and reducing buffer cache
latch contention. We will be going into detail on these techniques in Chapter 9.
The latch hit ratio is the ratio of the total number of
latch misses to the number of latch gets for all latches. A low value for this
ratio indicates a latching problem, whereas a high value is generally good.
However, as the data is rolled up over all latches, a high latch hit ratio can
artificially mask a low get rate on a specific latch. Oracle tuning
professionals will cross-check this value with the top 5 wait events to see if
latch free is in the list, and refer to the latch sections of the report.
L 4-10
SQL> desc STATS$LATCH;
Name Null? Type
----------------------------------------- -------- -------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
NAME NOT NULL VARCHAR2(64)
LATCH# NOT NULL NUMBER
LEVEL# NUMBER
GETS NUMBER
MISSES NUMBER
SLEEPS NUMBER
IMMEDIATE_GETS NUMBER
IMMEDIATE_MISSES NUMBER
SPIN_GETS NUMBER
SLEEP1 NUMBER
SLEEP2 NUMBER
SLEEP3 NUMBER
SLEEP4 NUMBER
WAIT_TIME NUMBER
The
stats$latch_children Table
The stats$latch_children table is
only populated when a level 10 collection is requested. The STATSPACK
documentation suggests that a level 10 only be collected at the request of
Oracle technical support.
L 4-11
SQL> desc
STATS$LATCH_CHILDREN;
Name Null? Type
----------------------------------------- -------- -------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
LATCH# NOT NULL NUMBER
CHILD# NOT NULL NUMBER
GETS NUMBER
MISSES NUMBER
SLEEPS NUMBER
IMMEDIATE_GETS NUMBER
IMMEDIATE_MISSES NUMBER
SPIN_GETS NUMBER
SLEEP1 NUMBER
SLEEP2 NUMBER
SLEEP3 NUMBER
SLEEP4 NUMBER
WAIT_TIME NUMBER
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
