Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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:

  • Cache buffers lru chain latch

  • Enqueues latch

  • Redo allocation latch

  • Redo copy latch

  • Library cache latch

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.
 

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.


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.