 |
|
What is Missing from STATSPACK?
Oracle Database Tips by Donald Burleson |
It is important to note that
STATSPACK fails to capture all of the information that you require to fully
analyze performance problems. As we discussed in Chapter 1, Oracle performance
problems are sometimes related to external issues such as disk I/O bottlenecks,
RAM swapping, CPU enqueues, and network bottlenecks. To make up for these
shortcoming and extend STATSPACK to measure environmental performance, we can
add new STATSPACK tables for the following areas:
We will look at extending the STATSPACK structure to
capture these external statistics in later chapters, but for now let's focus on
the details of the existing STATSPACK tables.
STATSPACK Subordinate Table Structures
Starting from the
stats$snapshot table, we see a wealth of other tables that capture information
from the in-memory v$ structures. It's important to note that some of these will
only have single snapshot rows, whereas other tables will have many rows. For
example, the stats$sesstat table will contain many rows, one row for each
statistic measured at the time that the snapshot was taken. Hence, it is
important to distinguish in the overall structure of the STATSPACK tables those
tables that have individual rows per snapshot and those that have multiple rows.
While at first glance the structure and complexity of the
STATSPACK tables may seem overwhelming, remember that this book will provide
dozens of prewritten scripts that you will be able to run to get the relevant
information for your purposes. Also bear in mind that the script templates
provided in this book can lay the foundation for additional queries.
The next sections will describe the layout and data
within the subordinate STATSPACK tables. The STATSPACK tables are divided into
four areas: the summary tables that report on system-wide summaries, the system
tables that contain system-wide information relating to the Oracle instance, the
transaction tables that contain information relating to Oracle transaction
processing, and the event tables that record Oracle system events.
STATSPACK Summary Tables
There are several
system-wide summary tables that can be used from within STATSPACK, as shown in
Figure 4-1.
Figure 4-19: System summary tables for STATSPACK
These summary tables are useful for several reporting
areas that we will discuss in detail in Chapter 14. For example, the
stats$parameter table shows all of the init.ora parameters at the time of
the snapshot, and this table is very useful when comparing the performance of
the database before and after a change to an initialization parameter. Let's
examine the data inside each of these tables.
Changes in
STATSPACK tables for Oracle9i
An ongoing issue with a
large number of rows in the stats$sql_summary table has been addressed as of
release 8.1.7 and into Oracle9i.
Obsolete STATSPACK tables in Oracle9i
Because the stats$ tables
correspond directly to the v$ views, the stats$buffer_pool table disappeared
when this data was consolidated into the v$buffer_pool_statistics view. Hence,
in Oracle9i we use the stats$buffer_pool_statistics view for details on data
buffer behavior.
New STATSPACK tables in Oracle9i
The STATSPACK developers
have finally overcome the Oracle8i problem of a giant stats$sql_summary table by
normalizing the SQL text into a new set of STATSPACK tables. Let's look at each
new table.
The stats$sql_statistics table in Oracle9i
STATSPACK has introduced a
new table that provides a one-row summary of all SQL examined during a
snapshot. Here is a description of the stats$sql_statistics table:
SQL> desc
stats$sql_statistics
Name Null? Type
--------------------------------- -------- ----------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
TOTAL_SQL NOT NULL NUMBER
TOTAL_SQL_MEM NOT NULL NUMBER
SINGLE_USE_SQL NOT NULL NUMBER
SINGLE_USE_SQL_MEM NOT NULL NUMBER
This table will have one row
per snapshot, and you can use this table to get an hourly summary of all SQL
activity by the hour:
rpt_sqlstat.sql
set lines 80;
set pages 999;
column mydate heading 'Yr. Mo Dy Hr.' format a16
column c1 heading "Tot SQL" format 999,999,999
column c2 heading "Single Use SQL" format 999,999
column c3 heading "Percent re-used SQL" format 999,999
column c4 heading "Total SQL RAM" format 999,999,999
break on mydate skip 2;
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
total_sql c1,
single_use_sql c2,
(single_use_sql/total_sql)*100 c3,
total_sql_mem c4
from
stats$sql_statistics sq,
stats$snapshot sn
where
sn.snap_id = sq.snap_id
;
This report is especially
useful for tracking total SQL burden on the system as well as the percentage of
SQL that has been re-used.
Yr. Mo Dy Hr. Tot
SQL Single Use SQL Pct re-used SQL Total SQL RAM
---------------- -------- -------------- --------------- -------------
2001-12-08 20 14,432 228 93 3,948,134
2001-12-08 21 23,322 28 98 4,938,438
2001-12-08 22 8,573 1,228 96 7,958,736
2001-12-08 23 7,564 998 91 1,978,834
2001-12-09 00 2,123 738 99 2,978,937
2001-12-09 01 4,234 601 91 5,046,034
The stats$sqltext table in Oracle9i
The stats$sqltext table was first created in Oracle
8.1.7 to remove the huge storage overhead associated with the stats$sql_summary
table. Unlike prior versions of STATSPACK, the Oracle9i version only stores the
SQL source has value in stats$sql_text.
SQL> desc
stats$sqltext
Name Null? Type
--------------------------------- -------- ----------
HASH_VALUE NOT NULL NUMBER
TEXT_SUBSET NOT NULL VARCHAR2(31)
PIECE NOT NULL NUMBER
SQL_TEXT VARCHAR2(64)
ADDRESS RAW(8)
COMMAND_TYPE NUMBER
LAST_SNAP_ID NUMBER
Now, let's move on and look at the other standard
STATSPACK tables.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
and include the URL for the page.
