Remote DBA Plans
Remote DBA Service

Monitoring Oracle Tables and Indexes with STATSPACK

Oracle Tips by Burleson Consulting

While the STATSPACK utility does a very good job of monitoring the Oracle databases as a whole, it falls far short on collection of data about the individual tables and indexes within the database. The Oracle Remote DBA is always interested in seeing what is going on within tables and indexes so that he or she can get an idea of the growth of the tables, and keep track of the behavior of each individual object within the database.

Fortunately, it is quite simple to extend the STATSPACK utility to accommodate table information; however, the approach is quite different than the standard STATSPACK snapshots. Whereas the standard STATSPACK snapshots will sample the v$ control structures within the Oracle instance to collect values, a STATSPACK extension for tables and indexes must periodically sample the Remote DBA_tables and Remote DBA_indexes views from the data dictionary.

When extending STATSPACK to capture statistics for tables and indexes, it is very important to remember that the data dictionary statistics are only current up to the last analyze for the table or index. Hence, it is very important that the Oracle Remote DBA issue the analyze table and analyze index commands immediately prior to running the STATSPACK extension snapshots for tables and indexes.

Unlike the Oracle system statistics that are usually captured by STATSPACK on an hourly basis, the information for tables and indexes can be captured on a weekly basis.

Allocating the STATSPACK Extension Tables

The following script will allocate two tables called stats$tab_stats and stats$idx_stats to hold our weekly snapshots of the table and index metadata. Please note the use of indexes within these tables to avoid full table scans during subsequent report queries.

create_object_tables.sql

connect perfstat/perfstat;

drop table perfstat.stats$tab_stats;

create table perfstat.stats$tab_stats
(
   snap_time       date,
   server_name     varchar2(20),
   db_name         varchar2(9),
   tablespace_name varchar2(40),
   owner           varchar2(40),
   table_name      varchar2(40),
   num_rows        number,
   avg_row_len     number,
   next_extent     number,
   extents         number,
   bytes           number
)
tablespace perfstat
storage (initial 1m next 1m maxextents unlimited)
;

drop table perfstat.stats$idx_stats;

create table perfstat.stats$idx_stats
(
   snap_time         date,
   server_name       varchar2(20),
   db_name           varchar2(9),
   tablespace_name   varchar2(40),
   owner             varchar2(40),
   index_name        varchar2(40),
   clustering_factor number,
   leaf_blocks       number,
   blevel            number,
   next_extent       number,
   extents           number,
   bytes             number
)
tablespace perfstat
storage (initial 1m next 1m maxextents unlimited)
;

drop index
   perfstat.tab_stat_date_idx;

create index
   perfstat.tab_stat_date_idx
on
   perfstat.stats$tab_stats
( snap_time )
tablespace perfstat
storage (initial 1m next 1m maxextents unlimited)
;

drop index
   perfstat.idx_stat_date_idx;
create index
   perfstat.idx_stat_date_idx
on
   perfstat.stats$idx_stats
( snap_time )
tablespace perfstat
storage (initial 1m next 1m maxextents unlimited)
;

Note that these STATSPACK extension tables contain the name of the database server. In a distributed environment, you can collect STATSPACK table and index information from a variety of servers and transfer the data into a centralized repository.

Collecting the STATSPACK Snapshot for Tables and Indexes

The next step is to populate our STATSPACK extension tables with table and index data from the Oracle data dictionary. In order to get accurate statistics, we must begin by analyzing all of our tables and indexes. Next, we extract the data from the data dictionary and populate the stats$tab_stats and stats$index_stats tables.

The following Korn shell script can be executed once each week to analyze the table and indexes and collect the table and index data. Note that we must set the oratab file location and pass the proper ORACLE_SID when executing this script.

get_object_stats.ksh

#!/bin/ksh

# Validate the Oracle database name with
# lookup in /var/opt/oracle/oratab
TEMP=`cat /var/opt/oracle/oratab|grep \^$1:|\
cut -f1 -d':'|wc -l`
tmp=`expr TEMP`     # Convert string to number
if [ $tmp -ne 1 ]
then
   echo "Your input $1 is not a valid ORACLE_SID."
   exit 99
fi

# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
MON=`echo ~oracle/obj_stat`
export MON

# Get the server name
host=`uname -a|awk '{ print $2 }'`

$ORACLE_HOME/bin/sqlplus –s perfstat/perfstat<<!

set heading off;
set feedback off;
set echo off;
set pages 999;
set lines 120;

--****************************************************************
-- First, let's get the latest statistics for each table
--****************************************************************
spool $MON/run_analyze.sql
select 'analyze table '||owner||'.'||table_name||' estimate statistics
sample 500 rows;'
from
   Remote DBA_tables
where
   owner not in ('SYS','SYSTEM','PERFSTAT');
-- ******************************
-- Analyze all indexes for statistics
-- ******************************
select 'analyze index '||owner||'.'||table_name||' compute statistics;'
from
   Remote DBA_indexes
where
   owner not in ('SYS','SYSTEM','PERFSTAT');
spool off;

set echo on;
set feedback on;

@$MON/run_analyze

connect perfstat/perfstat;

--****************************************************************
-- Now we grab the table statistics
--****************************************************************
insert into perfstat.stats\$tab_stats
(
select
     SYSDATE,
     lower('${host}'),
     lower('${ORACLE_SID}'),
     t.tablespace_name,
     t.owner,
     t.table_name,
     t.num_rows,
     t.avg_row_len,
     s.next_extent,
     s.extents,
     s.bytes
from
   Remote DBA_tables   t,
   Remote DBA_segments s
where
   segment_name = table_name
   and
   s.tablespace_name = t.tablespace_name
   and
   s.owner = t.owner
   and
   t.owner not in ('SYS','SYSTEM')
--   and
--   num_rows > 1000
);

--****************************************************************
-- Now we grab the index statistics
--****************************************************************
insert into perfstat.stats\$idx_stats
(
   select
      SYSDATE,
      lower('${host}'),
      lower('${ORACLE_SID}'),
      i.tablespace_name,
      i.owner,
      i.index_name,
      i.clustering_factor,
      i.leaf_blocks,
      i.blevel,
      s.next_extent,
     s.extents,
      s.bytes
   from Remote DBA_indexes i,
        Remote DBA_segments s,
        Remote DBA_tables   t
   where
      i.table_name = t.table_name
   and
      segment_name = index_name
   and
      s.tablespace_name = i.tablespace_name
   and
      s.owner = i.owner
   and
      i.owner not in ('SYS','SYSTEM')
--   and
--      t.num_rows > 1000
);

exit

!

CAUTION: Be sure that you have the correct setting for your optimizer_mode before analyzing table and indexes. If you have optimizer_mode=CHOOSE, and you do not have table statistics, analyzing the tables will cause your SQL to switch from rule-based to cost-based optimization.

Note that this script also has commented out code to restrict the population of rows to tables that contain more than 1,000 rows. This is because the Remote DBA may only be interested in collecting statistics on the most active tables within their database.

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.