BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter