Remote DBA Plans
Remote DBA Service

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

SQL> desc STATS$LIBRARYCACHE;
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

SQL> desc STATS$WAITSTAT;
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.

rpt_waitstat.sql

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"
    FROM X$KSQST where KSQSTWAT>0;

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

L 4-16

SQL> desc STATS$ENQUEUESTAT;
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.

SQL> desc 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:

rpt_enqueue.sql

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.