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$librarycache Table

Oracle Tips by Burleson Consulting

As you may know from our introduction to Oracle, the library cache is the memory space where SQL statements are parsed and executed. Each one of the values in the stats$librarycache table is for a specific event.

Following is a list of all of the possible values for the library cache. As we will discuss in detail in Chapter 9, the important thing is that the hit ratio for each one of these library cache entries remains above 90 percent for online transaction databases. If not, some tuning of the Oracle shared pool within the SGA will be necessary. Data warehouses and decision support systems may have much lower data buffer hit ratios because of the high amount of full-table scans.

Also note that the DLM parameters in the stats$librarycache table only apply to systems running Oracle Parallel Server. If you're not running OPS, you don't need to be concerned with any of the DLM values in this table.

L 4-12

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 NAMESPACE                                 NOT NULL VARCHAR2(15)
 GETS                                               NUMBER
 GETHITS                                            NUMBER
 PINS                                               NUMBER
 PINHITS                                            NUMBER
 RELOADS                                            NUMBER
 INVALIDATIONS                                      NUMBER
 DLM_LOCK_REQUESTS                                  NUMBER
 DLM_PIN_REQUESTS                                   NUMBER
 DLM_PIN_RELEASES                                   NUMBER
 DLM_INVALIDATION_REQUESTS                          NUMBER
 DLM_INVALIDATIONS                                  NUMBER

The stats$waitstat Table

The system wait statistics described in the stats$waitstat table can be useful if you suspect that your database is undergoing resource bottlenecks.

By looking at the total time, you can often determine which one of the wait statistics is causing a bottleneck within your Oracle database. We will return to the use of the wait statistics table later on in the chapter where we investigate SGA tuning.

The most common wait event we will be taking a look at in our chapter on object tuning is waits on the freelists. One of the best ways to find out if you've got an object that has improper storage parameter settings is to take a look at freelist waits. If your freelist waits are very high, there is a good chance you have tables that have competing INSERT or UPDATE tasks and these tables do not have enough freelists defined. This is arguably the most important section in the report because it shows how long Oracle is waiting for resources. This will be the starting point for looking at tuning Oracle. Again, we will go into detail on tuning waits in Chapter 9.

L 4-13

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 CLASS                                     NOT NULL VARCHAR2(18)
 WAIT_COUNT                                         NUMBER
 TIME                                               NUMBER

Here is a sample report on the data from stats$waitstat, showing various classes and the wait counts and times for each class.


L 4-14

Yr.  Mo Dy Hr CLASS                WAIT_COUNT         TIME
------------- -------------------- ---------- ------------
2001-09-21 15 data block                    3            0
2001-10-02 15 data block                    3            0
2001-10-02 15 undo block                    8            0
2001-12-11 18 undo header                  19            4

The stats$enqueuestat Table

It's important to remember when you take a look at the stats$enqueuestat table that enqueue waits are a normal part of Oracle processing. It is only when you see an excessive amount of enqueue waits for specific processes that you need to be concerned in the tuning process.

Oracle locks protect shared resources and allow access to those resources via a queuing mechanism. A large amount of time spent waiting for enqueue events can be caused by various problems, such as waiting for individual row locks or waiting for exclusive locks on a table. Look at the highly contended enqueues in the enqueue activity section of the STATSPACK report to determine which enqueues are waited for. At snapshot time, this table is populated by querying the x$ksqst view:

L 4-15

  SELECT  ksqsttyp "Lock",
          ksqstget "Gets",
          ksqstwat "Waits"

Here is a description for this table in Oracle8 and Oracle8i:

L 4-16

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(2)
 GETS                                               NUMBER
 WAITS                                              NUMBER

In Oracle9i the table name changes to stats$enqueue_stat.

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 EQ_TYPE                                   NOT NULL VARCHAR2(2)
 TOTAL_REQ#                                         NUMBER
 TOTAL_WAIT#                                        NUMBER
 SUCC_REQ#                                          NUMBER
 FAILED_REQ#                                        NUMBER
 CUM_WAIT_TIME                                      NUMBER

There are 26 lock types that could be captured in the stats$enqueuestat table, but only a handful of these are meaningful for Oracle tuning:

  • CI (Cross-instance lock) The CI lock is called the cross-instance lock, but
    it is not an Oracle Parallel Server lock. The name of this lock is misleading because it doesn't deal with distributed transactions. Rather, the CI lock is used to invoke specific actions in background processes on a specific instance or all instances. Examples would include checkpoints, log switches, or when the instance is shut down.

  • CU (Cursor bind lock) This is a cursor bind lock that is set whenever a cursor is used in an SQL statement.

  • JQ (Job queue lock) When a job is submitted using dbms_job.submit, the running job is protected by a JQ enqueue lock.

  • ST (Space management enqueue lock) This lock is usually associated
    with too much space management activity due to insufficient extent sizes. The ST enqueue needs to be held every time the session is allocating or deallocating extents.

  • TM (DML enqueue lock) This is a general table lock. Every time a session wants to lock a table (for an UPDATE, INSERT, or DELETE), a TM enqueue is requested. These locks are normally of very short duration, but they can be held for long periods when updating a table when foreign-key constraints have not been properly indexed.

  • TX (Transaction lock) A transaction is set when a change begins and is held until the transaction issues a COMMIT or ROLLBACK. When simultaneous tasks want to update the same rows, the TX locks allow the tasks to enqueue, waiting until the row is freed.

  • US (User lock) This lock is set when a session has taken a lock with the dbms_lock.request function. Application developers sometimes use this function to set serialization locks on parallelized tasks.

You can use the standard statsrep.sql script, or the custom rpt_enqueue.sql script to identify possible lock contention issues over time. Here is a sample report against the stats$enqueuestat table:


L 4-17

Yr.  Mo Dy Hr NAME                     GETS        WAITS
------------- -------------------- -------- ------------
2002-12-11 16 TX                      1,784            2
2002-12-11 18 TM                      1,789           20

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