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

 

 


 

 

 

 

 
 

STATSPACK Control Tables

Oracle Tips by Burleson Consulting

As we can see from this high-level entity relation model, the main anchor for the STATSPACK tables is a table called stats$database_instance. This table contains the database ID, the instance number, and the database server host name for the database that you are measuring. By associating the host name with this table, the Remote DBA can populate database information from several database servers into a single collection mechanism. While the STATSPACK developers have not yet implemented a mechanism for collecting STATSPACK data from many databases in a distributed environment, they have laid the foundation for this functionality in this table.

Here is the structure of this table in Oracle8 and Oracle8i.

L 2-3

SQL> desc STATS$DATABASE_INSTANCE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 DB_NAME                                   NOT NULL VARCHAR2(9)
 INSTANCE_NAME                             NOT NULL VARCHAR2(16)
 HOST_NAME                                          VARCHAR2(64)

In Oracle9i, the table changes structure to include the database startup time, the snap ID, and the parallel default.

SQL> desc STATS$DATABASE_INSTANCE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 STARTUP_TIME                              NOT NULL DATE
 SNAP_ID                                   NOT NULL NUMBER(6)
 PARALLEL                                  NOT NULL VARCHAR2(3)
 VERSION                                   NOT NULL VARCHAR2(17)
 DB_NAME                                   NOT NULL VARCHAR2(9)
 INSTANCE_NAME                             NOT NULL VARCHAR2(16)
 HOST_NAME                                          VARCHAR2(64)

For each database instance, we have many occurrences of the stats$snapshot table. This table contains the snapshot ID, the database ID, the instance number, and also the time the snapshot was taken. The stats$snapshot table is going to be very important in all of the scripts in our book because it contains the time that the snapshot was taken. Hence, all of the scripts that will be presented in this book must join into the stats$snapshot table so that you can correlate the time of the snapshot with the individual snapshot details.

Here is the table description in Oracle8 and Oracle8i.

L 2-4

SQL> desc STATS$SNAPSHOT;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 SNAP_TIME                                 NOT NULL DATE
 STARTUP_TIME                              NOT NULL DATE
 SESSION_ID                                NOT NULL NUMBER
 SERIAL#                                            NUMBER
 SNAP_LEVEL                                         NUMBER
 UCOMMENT                                           VARCHAR2(160)
 EXECUTIONS_TH                                      NUMBER
 PARSE_CALLS_TH                                     NUMBER
 DISK_READS_TH                                      NUMBER
 BUFFER_GETS_TH                                     NUMBER

In Oracle9i, we see the addition of the shareable memory threshold, the version count threshold, and the all_init value.

SQL> desc STATS$SNAPSHOT;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 SNAP_TIME                                 NOT NULL DATE
 STARTUP_TIME                              NOT NULL DATE
 SESSION_ID                                NOT NULL NUMBER
 SERIAL#                                            NUMBER
 SNAP_LEVEL                                         NUMBER
 UCOMMENT                                           VARCHAR2(160)
 EXECUTIONS_TH                                      NUMBER
 PARSE_CALLS_TH                                     NUMBER
 DISK_READS_TH                                      NUMBER
 BUFFER_GETS_TH                                     NUMBER
 SHARABLE_MEM_TH                                    NUMBER
 VERSION_COUNT_TH                                   NUMBER
 ALL_INIT                                           VARCHAR2(5)

In the stats$snapshot table, we see that there are three levels for STATSPACK data collection. In the next chapter we will see how these levels control what data is placed into the STATSPACK tables. We will also examine the four threshold columns (executions_th, parse_calls_th, disk_reads_th, buffer_gets_th) and see how these thresholds can be used to limit the number of stats$sql_summary rows that are added when a STATSPACK snapshot is executed.

Note that the STATSPACK definition table now include sharable_mem_th, all_init and version_count_th values, but these are added for future planning and have no function in Oracle9i STATSPACK.

The all_init variable

To capture the non-default initialization parameters in STATSPACK you need to set the all_init value to TRUE when taking a snapshot (this is the default). This captures every initialization parameter, although the standard reports just list the non-default parameters. The problem is caused by STATSPACK using the v$system_parameter vierw instead of the v$parameter view.  The STATSPACK developers are treating this as a bug in the v$system_parameter and therefore the problem was not fixed until Oracle9i.

 

 

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