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

Oracle Tips by Burleson Consulting

This table records latch misses for the Oracle database. When we observe latches, we must remember that there are two types of latches:

·        Willing-to-wait latchA willing-to-wait latch will repeatedly try to reacquire the latch. The redo allocation latch is a good example.

·        Immediate latchThese latches must acquire a latch immediately or the task will abort. The redo copy latch is a good example of an immediate latch.

Latch sleeps are very important because they indicate the number of times we had to sleep because we could not get a latch. The latches with the highest sleep values are the ones to concentrate on. Latch misses are recorded in the nwfail_count column of this table, and misses are important because they indicate system resource shortages:

L 4-1

   SQL> desc stats$latch_misses_summary;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 PARENT_NAME                               NOT NULL VARCHAR2(50)
 WHERE_IN_CODE                             NOT NULL VARCHAR2(64)
 NWFAIL_COUNT                                       NUMBER
 SLEEP_COUNT                                        NUMBER

In Oracle9i, we see the addition of a new column to the stats$latch_misses_summary table.

 WTR_SLP_COUNT                                      NUMBER>>

There are more than a dozen types of latches, but there are only a few that impact Oracle performance. Here is a brief listing of some important latches and the remedy to shortages.

Latch Name

Willing-to-Wait?

Action

Redo copy

Yes

Increase redo log size

Redo allocation

No

Increase log_small_entry_max_size

Library cache

Yes

Increase shared_pool_size

Shared pool

Yes

Increase shared_pool_size

We will be discussing Oracle latches in detail in Chapter 9.

Here is a sample STATSPACK report from the information in this table.

rpt_latch_misses.sql

L 4-2

SNAP_DAT PARENT_NAME                  WHERE_IN_C SUM_NWFAIL  SUM_SLEEP
-------- ---------------------------- ---------- ----------  ---------
12-12-02 cache buffers chains        kcbbxsv             0          3
         cache buffers chains        kcbget: pi          0          3
                                      n buffer
12-13-02 cache buffers chains        kcbbxsv             0         24
         channel operations
          parent latch          ksrwait()        0         96
         library cache               kglhdgn: c          0         48
                                      hild:
         library cache               kglic               0        384
         row cache objects           kqrpre: fi          0         24
                                      nd obj

The stats$sgastat_summary Table (Oracle8i only)

The stats$sgastat_summary table sums up some of the important system-wide values for the Oracle database.  This table was obsoleted in Oracle9i STATSPACK.

L 4-3

SQL> desc stats$sgastat_summary;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 SNAP_ID                                   NOT NULL NUMBER
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(64)
 BYTES                                              NUMBER

In the name column for this table we see that 22 rows are added for each STATSPACK snapshot:

L 4-4

SQL> select distinct name from stats$sgastat_summary;
NAME
----------------------------------------------------------------
DML locks
KGFF heap
KGK heap
KQLS heap
PL/SQL DIANA
PL/SQL MPCODE
PL/SQL SOURCE
PLS non-lib hp
SEQ S.O.
SYSTEM PARAMETERS
State objects
branches
character set object
db_block_buffers
db_block_hash_buckets
dictionary cache
distributed_transactions-
enqueue_resources
event statistics per sess
fixed allocation callback
fixed_sga
free memory
ktlbk state objects
library cache
log_buffer
long op statistics array
message pool freequeue
miscellaneous
sessions
sql area
state objects
table columns
table definiti
temporary tabl
transaction_branches
transactions
trigger defini
trigger inform
trigger source
type object de
view columns d

The stats$sgastat_summary table is most beneficial for long-term trending reports in STATSPACK. Here is a sample from the data in this table showing the change in size of SGA areas.

rpt_sga_summary.sql

L 4-5

NAME                         MIN_BYTES    MAX_BYTES    CHG_BYTES
------------------------- ------------ ------------ ------------
KQLS heap                    1,827,000    8,520,208    6,693,208
PL/SQL DIANA                 6,601,096   13,886,264    7,285,168
PL/SQL MPCODE                1,430,384    3,216,128    1,785,744
PL/SQL SOURCE                    1,008        8,672        7,664
dictionary cache             1,910,280    5,253,304    3,343,024
free memory                139,558,232  194,640,008   55,081,776
library cache                7,395,640   24,541,760   17,146,120
miscellaneous                  665,112      916,864      251,752
sql area                     3,368,200   23,797,248   20,429,048
state objects                  126,600      278,448      151,848
table columns                   35,064      387,104      352,040
table definition                 5,496       29,416       23,920
temporary table                    608        8,576        7,968
trigger definition               7,016       97,072       90,056
trigger source                   7,000       49,760       42,760
view columns                    1,112       92,032       90,920

The stats$sql_summary Table

The SQL statistics summary is one of the most important tables within the STATSPACK facility. As we discussed in Chapter 1, tuning of SQL can often have a profound impact on the performance of your Oracle system, and the stats$sql_summary table provides the text of each SQL statement and a detailed description of the resources used by each and every SQL statement that meets the necessary thresholds conditions to be captured in a snapshot.

We will be returning to this table many times in this text, and especially in Chapter 11. For now, all we need to know is that this table tracks the number of executions, the number of parse calls, and the number of data blocks read and written for each SQL statement. This information can be an invaluable tool when it comes time to tune the SQL within your Oracle database.

It also bears mentioning that the stats$sql_summary table is the most highly populated of all of the STATSPACK tables. If your threshold values are set very low and you have a busy database, it's not uncommon to get 300 to 500 rows added to the stats$sql_summary table each and every time STATSPACK requests a snapshot. Hence, it is very important that the Remote DBA remove unwanted rows from the stats$sql_summary table once they are no longer used for SQL tuning.

Here is the version of stats$sql_summary for Oracle8 and Oracle8i.

L 4-6

SQL> desc STATS$SQL_SUMMARY;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 SQL_TEXT                                           VARCHAR2(1000)
 SHARABLE_MEM                                       NUMBER
 SORTS                                              NUMBER
 MODULE                                             VARCHAR2(64)
 LOADED_VERSIONS                                    NUMBER
 EXECUTIONS                                         NUMBER
 LOADS                                              NUMBER
 INVALIDATIONS                                      NUMBER
 PARSE_CALLS                                        NUMBER
 DISK_READS                                         NUMBER
 BUFFER_GETS                                        NUMBER
 ROWS_PROCESSED                                     NUMBER
 ADDRESS                                   NOT NULL RAW(8)
 HASH_VALUE                                NOT NULL NUMBER
 VERSION_COUNT                                      NUMBER

Oracle9i has numerous additional columns for additional details about the SQL.  Here is the table description for Oracle9i.

SQL> desc STATS$SQL_SUMMARY
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 TEXT_SUBSET                               NOT NULL VARCHAR2(31)
 SQL_TEXT                                           VARCHAR2(1000)
 SHARABLE_MEM                                       NUMBER
 SORTS                                              NUMBER
 MODULE                                             VARCHAR2(64)
 LOADED_VERSIONS                                    NUMBER
 EXECUTIONS                                         NUMBER
 LOADS                                              NUMBER
 INVALIDATIONS                                      NUMBER
 PARSE_CALLS                                        NUMBER
 DISK_READS                                         NUMBER
 BUFFER_GETS                                        NUMBER
 ROWS_PROCESSED                                     NUMBER
 COMMAND_TYPE                                       NUMBER
 ADDRESS                                            RAW(8)
 HASH_VALUE                                NOT NULL NUMBER
 VERSION_COUNT                                      NUMBER
 CPU_TIME                                           NUMBER
 ELAPSED_TIME                                       NUMBER
 OUTLINE_SID                                        NUMBER
 OUTLINE_CATEGORY                                   VARCHAR2(64)

For details on the use of the stats$sql_summary table for Oracle tuning, please refer to Chapter 11.


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