Remote DBA Plans
Remote DBA Service

Reports on Tables and Indexes

Oracle Tips by Burleson Consulting

The following reports are designed to show the Remote DBA changes within the status of individual objects and the overall space usage for the database as a whole. For example, STATSPACK reports can be run against the stats$tab_stats and stats$idx_stats tables to show the total number of bytes allocated within individual tablespaces within the database.

Let’s start with the simple STATSPACK report and then move on to the more advanced reporting. One of the advantages of doing weekly snapshots of table and index statistics is that we are able to write easy comparisons between snapshots. The following report is designed to find the most recent snapshot data, go back one snapshot period, and produce a report showing the growth of all significant tables within the Oracle database.

Take a close look at the following report. In the report, you can see how we select the most recent snapshot data from the STATSPACK tables and then use a technique with a temporary table in order to find the immediately previous snapshot. This is an important technique to remember when you start writing your own customized STATSPACK reports and you want to compare the two most recent snapshots within your database.

rpt_bytes.sql

--*********************************************************

-- First we need to get the second-highest date in tab_stats
--*********************************************************
set lines 80;
set pages 999;
set feedback off;
set verify off;
set echo off;

drop table d1;

create table d1 as
select distinct
   to_char(snap_time,'YYYY-MM-DD') mydate
from
   stats$tab_stats
where
   to_char(snap_time,'YYYY-MM-DD') <
   (select max(to_char(snap_time,'YYYY-MM-DD')) from stats$tab_stats)
;

--*********************************************************
-- The second highest date is select max(mydate) from d1;
--*********************************************************

set heading off;

prompt Object growth - Comparing last two snapshots
prompt
prompt This report shows the growth of key tables
prompt for the past week.

select 'Old date = '||max(mydate) from d1;
select 'New date = '||max(to_char(snap_time,'YYYY-MM-DD')) from stats$tab_stats;

break on report ;
compute sum of old_bytes on old.table_name;

set heading on;

column old_bytes format 999,999,999
column new_bytes format 999,999,999
column change    format 999,999,999

select
   new.table_name,
   old.bytes                old_bytes,
   new.bytes                new_bytes,
   new.bytes - old.bytes    change
from
   stats$tab_stats old,
   stats$tab_stats new
where
   old.table_name = new.table_name
and
   new.bytes > old.bytes
and
   new.bytes - old.bytes > 10000
and
   to_char(new.snap_time, 'YYYY-MM-DD') =
          (select max(to_char(snap_time,'YYYY-MM-DD')) from stats$tab_stats)
and
   to_char(old.snap_time, 'YYYY-MM-DD') =
           (select max(mydate) from d1)
and
   new.table_name not like 'STATS$%'
order by
   new.bytes-old.bytes desc
;
--*********************************************************
-- First we need to get the second-highest date in idx_stats
--*********************************************************
set lines 80;
set pages 999;
set feedback off;
set verify off;
set echo off;

drop table d1;

create table d1 as
select distinct
   to_char(snap_time,'YYYY-MM-DD') mydate
from
   stats$idx_stats
where
   to_char(snap_time,'YYYY-MM-DD') <
    (select max(to_char(snap_time,'YYYY-MM-DD')) from stats$idx_stats)
;

--*********************************************************
-- The second highest date is select max(mydate) from d1;
--*********************************************************

set heading off;

prompt Object growth - Comparing last two snapshots
prompt
prompt This report shows the growth of key indexes
prompt for the past week.

select 'Old date = '||max(mydate) from d1;
select 'New date = '||max(to_char(snap_time,'YYYY-MM-DD')) from
tats$idx_stats;

break on report ;
compute sum of old_bytes on old.table_name;

set heading on;

column old_bytes format 999,999,999
column new_bytes format 999,999,999
column change    format 999,999,999

select
   new.index_name,
   old.bytes                old_bytes,
   new.bytes                new_bytes,
   new.bytes - old.bytes    change
from
   stats$idx_stats old,
   stats$idx_stats new
where
   old.index_name = new.index_name
and
   new.bytes > old.bytes
and
   new.bytes - old.bytes > 10000
and
   to_char(new.snap_time, 'YYYY-MM-DD') =
          (select max(to_char(snap_time,'YYYY-MM-DD')) from stats$idx_stats)
and
   to_char(old.snap_time, 'YYYY-MM-DD') =
           (select max(mydate) from d1)
and
   new.index_name not like 'STATS$%'
order by
   new.bytes-old.bytes desc
;

Now that we have seen the script, let’s take a look at some of the useful information that this report provides. Most Remote DBAs run this report weekly so they can get an object summary report in their mailbox every Monday morning to show them the growth of individual tables and indexes within the Oracle database. These kinds of reports are also interesting to MIS management, especially the parts of the report that show the overall growth of the database. Let’s go through each piece of the output from this report individually so we can see exactly what kind of useful information is being gathered inside the STATSPACK extension tables.

The first report shown next gives a summary of table and index growth over the past seven days (or the period between collections). The report starts by displaying information about the most recent snapshot data, and the previous state for which the snapshot was compared. From this we see the table name, the number of bytes in the prior snapshot, and the number of bytes in the new snapshot. We also see the total change in size for the tables during the elapsed time between snapshots. This report provides the Remote DBA with useful information about the rate of growth of key tables within their database, and also provides capacity planning information that is useful for managers who might need to order additional disk or other hardware resources in time to accommodate the growth of the object within the database.

rpt_bytes.sql

Object growth - Comparing last two snapshots

This report shows the growth of key tables
for the past week.

Old date = 2001-01-15

New date = 2001-01-22

TABLE_NAME                             OLD_BYTES    NEW_BYTES       CHANGE
----------------------------------- ------------ ------------ ------------
MTL_TRANSACTION_ACCOUNTS              40,484,864   43,679,744    3,194,880
GL_JE_LINES                           18,653,184   21,315,584    2,662,400
MTL_MATERIAL_TRANSACTIONS             35,692,544   38,354,944    2,662,400
WIP_REQUIREMENT_OPERATIONS            23,445,504   26,107,904    2,662,400
GL_BALANCES                            4,808,704    6,406,144    1,597,440
WF_ITEM_ATTRIBUTE_VALUES              18,653,184   20,250,624    1,597,440
PLAN_TABLE                               122,880    1,597,440    1,474,560
SQLTEMP                                  122,880    1,597,440    1,474,560
GL_IMPORT_REFERENCES                   6,938,624    8,003,584    1,064,960
MTL_DEMAND_INTERFACE                   5,873,664    6,938,624    1,064,960
MTL_CST_ACTUAL_COST_DETAILS           15,458,304   16,523,264    1,064,960
GL_INTERFACE                           1,613,824    2,678,784    1,064,960
WF_ITEM_ACTIVITY_STATUSES              6,938,624    8,003,584    1,064,960
SO_EXCEPTIONS                            868,352    1,622,016      753,664
AP_INVOICE_DISTRIBUTIONS_ALL           5,341,184    5,873,664      532,480
SO_LINES_ALL                           2,146,304    2,678,784      532,480
WF_NOTIFICATION_ATTRIBUTES             4,276,224    4,808,704      532,480
WIP_TRANSACTION_ACCOUNTS              18,653,184   19,185,664      532,480
RA_CUSTOMER_TRX_ALL                      548,864    1,081,344      532,480
MTL_DEMAND                             2,146,304    2,678,784      532,480
AP_EXPENSE_REPORT_HEADERS_ALL            303,104      589,824      286,720
MRP_RELIEF_INTERFACE                     303,104      589,824      286,720
GL_CONS_FLEXFIELD_MAP                     16,384      303,104      286,720
RA_REMIT_TOS_ALL                          16,384      180,224      163,840
SO_PICKING_LINE_DETAILS                  442,368      573,440      131,072
PO_LINES_ALL                           3,031,040    3,080,192        9,152
SO_FREIGHT_CHARGES                        49,152       81,920       32,768
SO_PICKING_BATCHES_ALL                   409,600      442,368       32,768

Object growth - Comparing last two snapshots

This report shows the growth of key indexes
for the past week.

Old date = 2001-01-15
New date = 2001-01-22

INDEX_NAME                               OLD_BYTES    NEW_BYTES       CHANGE
------------------------------------- ------------ ------------ ------------
WF_ITEM_ATTRIBUTE_VALUES_PK             30,900,224   33,562,624    2,662,400
MTL_CST_ACTUAL_COST_DETAILS_N1           4,276,224    6,406,144    2,129,920
MTL_TRANSACTION_ACCOUNTS_N6             14,393,344   15,990,784    1,597,440
MTL_TRANSACTION_ACCOUNTS_N2             12,263,424   13,328,384    1,064,960
WIP_OPERATION_RESOURCES_N1               2,146,304    3,211,264    1,064,960
WIP_REQUIREMENT_OPERATIONS_U1            7,471,104    8,536,064    1,064,960
MTL_TRANSACTION_ACCOUNTS_N3              9,068,544   10,133,504    1,064,960
WF_ITEM_ACTIVITY_STATUSES_N1             4,276,224    5,341,184    1,064,960
MTL_TRANSACTION_ACCOUNTS_N5             11,730,944   12,795,904    1,064,960
GL_BALANCES_N2                           1,736,704    2,596,864      860,160
GL_BALANCES_N3                           1,818,624    2,473,984      655,360
GL_BALANCES_N1                           2,146,304    2,678,784      532,480
GL_JE_LINES_N1                           3,743,744    4,276,224      532,480
MTL_MATERIAL_TRANSACTIONS_N1             6,938,624    7,471,104      532,480
MTL_MATERIAL_TRANSACTIONS_N15            8,003,584    8,536,064      532,480
MTL_MATERIAL_TRANSACTIONS_N3             8,003,584    8,536,064      532,480
MTL_MATERIAL_TRANSACTIONS_N7             4,808,704    5,341,184      532,480
MTL_MATERIAL_TRANSACTIONS_N5             5,873,664    6,406,144      532,480
WIP_REQUIREMENT_OPERATIONS_N2            5,873,664    6,406,144      532,480
WIP_REQUIREMENT_OPERATIONS_N1            8,003,584    8,536,064      532,480
WIP_OPERATION_RESOURCES_U1               1,613,824    2,146,304      532,480
WIP_OPERATION_RESOURCES_N2               1,613,824    2,146,304      532,480
WF_NOTIFICATIONS_ATTR_PK                 6,938,624    7,471,104      532,480
MTL_TRANSACTION_ACCOUNTS_N1              6,938,624    7,471,104      532,480
MTL_MATERIAL_TRANSACTIONS_U2             7,471,104    8,003,584      532,480
MTL_MATERIAL_TRANSACTIONS_N9             8,003,584    8,536,064      532,480
WIP_REQUIREMENT_OPERATIONS_N3            6,938,624    7,471,104      532,480
MTL_MATERIAL_TRANSACTIONS_N8             6,938,624    7,471,104      532,480
MTL_MATERIAL_TRANSACTIONS_N2             6,406,144    6,938,624      532,480
MTL_MATERIAL_TRANSACTIONS_N12            3,211,264    3,743,744      532,480
MTL_CST_ACTUAL_COST_DETAILS_U1           7,471,104    8,003,584      532,480
MRP_WIP_COMPONENTS_N1                    1,613,824    2,146,304      532,480
MRP_FORECAST_UPDATES_N1                    548,864    1,081,344      532,480
GL_IMPORT_REFERENCES_N3                  3,211,264    3,743,744      532,480
GL_IMPORT_REFERENCES_N1                  1,818,624    2,146,304      327,680
AP_HOLDS_N1                                 16,384      303,104      286,720
AP_INVOICE_PAYMENTS_N4                     589,824      876,544      286,720
GL_JE_LINES_U1                           2,310,144    2,596,864      286,720
PO_REQUISITION_HEADERS_N1                   16,384      303,104      286,720
PO_REQUISITION_HEADERS_U1                   16,384      303,104     286,720
WIP_OPERATIONS_N2                        1,163,264    1,449,984      286,720
RCV_TRANSACTIONS_N6                        221,184      425,984      204,800
SO_EXCEPTIONS_N1                           237,568      434,176      196,608
MRP_RELIEF_INTERFACE_N1                    180,224      344,064      163,840
MTL_DEMAND_INTERFACE_N4                    507,904      671,744      163,840
MTL_DEMAND_INTERFACE_N6                    507,904      671,744      163,840
MTL_DEMAND_INTERFACE_N9                    507,904      671,744      163,840
WIP_DISCRETE_JOBS_N7                       344,064      507,904      163,840
WIP_DISCRETE_JOBS_N1                       180,224      344,064      163,840
MTL_DEMAND_INTERFACE_N8                    671,744      835,584      163,840
MTL_DEMAND_INTERFACE_N5                    507,904      671,744      163,840
MTL_DEMAND_INTERFACE_N2                    835,584      999,424      163,840
SO_EXCEPTIONS_U1                           114,688      196,608       81,920
FND_CONCURRENT_REQUESTS_N4               3,735,552    3,768,320       32,768
FND_CONCURRENT_REQUESTS_N5                 475,136      507,904       32,768
FND_CONC_RELEASE_CLASSES_U1                606,208      638,976       32,768
MTL_SUPPLY_DEMAND_TEMP_N1                  999,424    1,015,808       16,384

Note that this report is sequenced so that the tables with the most growth appear at the top of the report.

The next report is a very useful summary report on database table and index activity. Just as in the previous report, this report starts by displaying the most recent snapshot date and the preceding snapshot date. This is done so that the reader of the report knows the duration between reports. Note that this report summarizes all of the information for all tables and indexes within the database. As part of the display, we see the database name, followed by counts of the numbers of tables and indexes within the database. Next, we see counts of the total number of bytes within tables and the total number of bytes within indexes for the database.

The second section of this report is the most interesting of all. Here we see the total number of bytes in the prior snapshot as compared to the total number of bytes in the most recent snapshot. The difference between these two values is computed and displayed in bytes.

This type of capacity planning report is covered more thoroughly in Chapter 14, but for now let’s just note that we can easily get the total amount of growth of table and index bites between any two snapshot periods that we desire.

The object statistics report script is displayed next. Pay careful attention to the use of temporary tables within the script. We will learn in Chapter 11 that the use of these temporary tables can be used to greatly speed the performance of the query.

What makes this report challenging is that we are comparing summaries between two distinct ranges of rows within the stats$tab_stats table as shown in Figure 10-25.

Figure 10-103: Comparing summaries of two ranges with the stats$tab_stats table

Whereas this query could be performed by joining the stats$tab_stats table against itself, the use of the temporary tables for computing summary information dramatically speeds the execution of the query.

For example, note the following t1 temporary table. This temporary table is used to store the counts for all tables for the first snapshot period. Once four temporary tables are created with the counts for tables and indexes for the two periods, it is a very quick query to consolidate these counts into a single display.

In fact, without the temporary tables, this query will run for more than four minutes. With the use of the temporary tables, the response time for running this report is almost instantaneous.

rpt_object_stats.sql

connect perfstat/perfstat;

set lines 80;
set pages 999;
set feedback off;
set verify off;
set echo off;

--*********************************************************
-- This report compares the max(snap_time) to the second-highest date
--*********************************************************

--*********************************************************
-- First we need to get the second-highest date in tab_stats
--*********************************************************
drop table d1;

create table d1 as
select distinct
   to_char(snap_time,'YYYY-MM-DD') mydate
from
   stats$tab_stats
where
   to_char(snap_time,'YYYY-MM-DD') <
    (select max(to_char(snap_time,'YYYY-MM-DD')) from stats$tab_stats)
;

--*********************************************************
-- The second highest date is select max(mydate) from d1;
--*********************************************************

set heading off;

prompt '*********************************************'
select ' Most recent date '||
          max(to_char(snap_time,'YYYY-MM-DD'))
from stats$tab_stats;
select ' Older date '||
          max(mydate)
from d1;
prompt '*********************************************'

set heading on;

drop table t1;
drop table t2;
drop table t3;
drop table t4;

create table t1 as
select db_name, count(*) tab_count, snap_time from stats$tab_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD')) from
tats$tab_stats)
group by db_name, snap_time;

create table t2 as
select db_name, count(*) idx_count, snap_time from stats$idx_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD')) from
tats$idx_stats)
group by db_name, snap_time;

create table t3 as
select db_name, sum(bytes) tab_bytes, snap_time from stats$tab_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD')) from
tats$tab_stats)
group by db_name, snap_time;

create table t4 as
select db_name, sum(bytes) idx_bytes, snap_time from stats$idx_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD')) from
tats$idx_stats)
group by db_name, snap_time;

--*********************************************************
-- This report displays the most recent counts & size totals
--*********************************************************

column tab_bytes format 999,999,999,999
column idx_bytes format 999,999,999,999
column tab_count format 99,999
column idx_count format 99,999

clear computes;
compute sum label "Total" of tab_count on report;
compute sum label "Total" of idx_count on report;
compute sum label "Total" of tab_bytes on report;
compute sum label "Total" of idx_bytes on report;

break on report;

ttitle 'Most recent database object counts and sizes'

select
   a.db_name,
   tab_count,
   idx_count,
   tab_bytes,
   idx_bytes
from
   perfstat.t1 a,
   perfstat.t2 b,
   perfstat.t3 c,
   perfstat.t4 d
where
   a.db_name = b.db_name
and
   a.db_name = c.db_name
and
   a.db_name = d.db_name
;

--*********************************************************
-- These temp tables will compare size growth since last snap
--*********************************************************
drop table t1;
drop table t2;
drop table t3;
drop table t4;

create table t1 as
select db_name, sum(bytes) new_tab_bytes, snap_time from stats$tab_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD')) from
tats$tab_stats)
group by db_name, snap_time;

create table t2 as
select db_name, sum(bytes) new_idx_bytes, snap_time from stats$idx_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(to_char(snap_time,'YYYY-MM-DD')) from
tats$idx_stats)
group by db_name, snap_time;

create table t3 as
select db_name, sum(bytes) old_tab_bytes, snap_time from stats$tab_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(mydate) from d1)
group by db_name, snap_time;

create table t4 as
select db_name, sum(bytes) old_idx_bytes, snap_time from stats$idx_stats
where    to_char(snap_time, 'YYYY-MM-DD') =
           (select max(mydate) from d1)
group by db_name, snap_time;

--*********************************************************
-- This is the size comparison report
--*********************************************************
column old_bytes format 999,999,999,999
column new_bytes format 999,999,999,999
column change    format 999,999,999,999

compute sum label "Total" of old_bytes on report;
compute sum label "Total" of new_bytes on report;
compute sum label "Total" of change    on report;

break on report;
ttitle 'Database size change|comparing the most recent snapshot dates';

select
   a.db_name,
   old_tab_bytes+old_idx_bytes old_bytes,
   new_tab_bytes+new_idx_bytes new_bytes,
   (new_tab_bytes+new_idx_bytes)-(old_tab_bytes+old_idx_bytes) change
from
   perfstat.t1 a,
   perfstat.t2 b,
   perfstat.t3 c,
   perfstat.t4 d
where
   a.db_name = b.db_name
and
   a.db_name = c.db_name
and
   a.db_name = d.db_name
;

--*********************************************************
-- This is the standard chained row report
--
-- This is for columns without long columns
-- because long columns often chain onto adjacent data blocks
--*********************************************************

column c1 heading "Owner"   format a9;
column c2 heading "Table"   format a12;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows"    format 999,999,999;
column c7 heading "Chains" format 999,999,999;
column c8 heading "Pct"     format .99;

set heading off;
select 'Tables with > 10% chained rows and no LONG columns.' from dual;
set heading on;

select
   owner              c1,
   table_name         c2,
   pct_free           c3,
   pct_used           c4,
   avg_row_len        c5,
   num_rows           c6,
   chain_cnt          c7,
   chain_cnt/num_rows c8
from
   Remote DBA_tables
where
   owner not in ('SYS','SYSTEM','PERFSTAT')
and
   chain_cnt/num_rows > .1
and
table_name not in
(select table_name from Remote DBA_tab_columns
   where
data_type in ('RAW','LONG RAW')
)
and
chain_cnt > 0
order by chain_cnt desc
;

--*********************************************************
-- This chained row report is for tables that have long
-- columns. The only fix for this chaining is increasing
-- the db_block_size
--*********************************************************
set heading off;
select 'Tables with > 10% chained rows that contain LONG columns.' from
ual;
set heading on;

select7
   owner              c1,
   table_name         c2,
   pct_free           c3,
   pct_used           c4,
   avg_row_len        c5,
   num_rows           c6,
   chain_cnt          c7,
   chain_cnt/num_rows c8
from
   Remote DBA_tables
where
   owner not in ('SYS','SYSTEM','PERFSTAT')
and
   chain_cnt/num_rows > .1
and
table_name in
(select table_name from Remote DBA_tab_columns
   where
data_type in ('RAW','LONG RAW')
)
and
chain_cnt > 0
order by chain_cnt desc
;

Next is the output from the object statistics report. Note we first display the most recent snapshot date with the one immediately preceding it. This is so the reader clearly understands the elapsed time between the snapshots. We immediately get an overall count of the number of tables and indexes that were in the database between the two snapshot periods. We also see the total number of bytes for tables and the total number of bytes for indexes as of the most recent snapshot date.

The next section of the report shows the change in the overall database size. This is the section of the report that the Remote DBA will e-mail to his or her vice presidents, CIO, and other people who are interested in tracking the overall growth of the database.

Some readers may note that this report shows only the sum of Remote DBA_tables.bytes, and does not show the whole size of the database. This has always been a confounding issue for Remote DBAs, where the actual bytes consumed by the tables is less than half the total size of their database. This is due to the fact that the object overhead (pctfree reserved spaces, indexes, unused spaces in extents and tablespaces) are not reflected in the table sizes. At the highest level, the size of the database will be the sum of all of the data blocks for all of the Oracle data files.

'*********************************************'

Most recent date 2001-01-22

Older date 2001-01-15

'*********************************************'

Mon Jan 22                                                             page    1
                  Most recent database object counts and sizes

DB_NAME   TAB_COUNT IDX_COUNT     TAB_BYTES        IDX_BYTES
--------- --------- --------- ------------- ----------------
prod          2,861     6,063 1,659,969,536    1,349,140,480
          --------- --------- ------------- ----------------
Total         2,861     6,063 1,659,969,536    1,349,140,480

Mon Jan 22                                                             page    1
                              Database size change
                    comparing the most recent snapshot dates

DB_NAME       OLD_BYTES        NEW_BYTES           CHANGE
--------- ------------- ---------------- ----------------
prod      2,873,147,392    3,009,110,016      135,962,624
          ---------------- ---------------- ----------------
Total     2,873,147,392    3,009,110,016      135,962,624

This report clearly shows that the bytes consumed for tables and indexes has grown by 136 megabytes during the past week.

The next section of this report is very interesting to the Remote DBA, and most Remote DBAs automatically e-mail themselves this report on Monday morning so that they can review the number of chained rows that currently exist within their database tables. This report shows the owner, the table name, and the settings for pctfree and pctused, followed by the average row length, the number of rows, the number of chain rows, and percent of total rows that are chained.

This report is indispensable to the Remote DBA. As you recall from earlier in this chapter, the Remote DBA must be able to track and remedy chained rows by doing periodic reorganizations and adjusting the setting for pctfree.

Also note that this chained row report is divided into two sections. The first section reports on tables with chained rows that have no long columns inside them. The second section of the report lists all tables with chained rows for datatypes that do contain RAW, or LONG RAW datatypes. This distinction between short rows and long rows is made because the Remote DBA is unable to correct row chaining in cases where the long columns make the table rows longer than the block size for the individual database. Hence, the Remote DBA is most interested in the first part of this report because he or she can reorganize these tables to remove the chains.

Tables with > 10% chained rows and no LONG columns.

Mon Jan 22                                                             page    1

Owner Table        PCTFREE PCTUSED avg row         Rows       Chains Pct
------ ------------ ------- ------- ------- ------------ ------------ ----
OE     SO_OBJECTS        10      70   1,858           87           64 .74
INV    MTL_ABC_COMP      10      70      73          367           44 .12
APPL   FND_PERFORMA      10      40 27,152           20           20 1.00


Mon Jan 22                                                             page    1

Tables with > 10% chained rows that contain LONG columns.

Owner Table        PCTFREE PCTUSED avg row         Rows       Chains Pct
------ ------------ ------- ------- ------- ------------ ------------ ----
EULC   DIS_DOCS          10      40   9,873            9            9 100

The next section of this report is used to track only the tables that have extended since the last snapshot period (usually the past week). As we stressed earlier in this chapter, one of the important jobs of the Remote DBA in terms of tuning tables is to have appropriate settings for the NEXT extend size for individual tables and indexes. Given that the Remote DBA has set appropriate sizes to manage the growth of objects within the database, this report is especially useful because it shows only those tables and indexes that have taken extents during the past week. The old adage goes “the squeaky wheel gets the grease,” and this report helps the Remote DBA identify the most active tables within the database in terms of insert activity. You should also note that this report displays the ORACLE_SID. This is because these STATSPACK extension tables can be populated from many remote databases using Net8 database links to a central STATSPACK repository.

Mon Jan 22                                                             page    1
                              Table extents report
                  Where extents > 200 or table extent changed
                        comparing most recent snapshots

DB      OWNER      TAB_NAME                          OLD_EXT    NEW_EXT
------- ---------- ------------------------------ ---------- ----------
prod    GL         GL_CONS_FLEXFIELD_MAP                   1          2
        AR         RA_REMIT_TOS_ALL                        1          2
        AP         AP_EXPENSE_REPORT_HEADERS_ALL           2          3
        MRP        MRP_RELIEF_INTERFACE                    2          3
        OE         SO_FREIGHT_CHARGES                      2          3
        GL         GL_INTERFACE                            4          6
        INV        MTL_DEMAND                              5          6
        APPLSYS    WF_NOTIFICATION_ATTRIBUTES              9         10
        GL         GL_BALANCES                            10         13
        OE         SO_PICKING_BATCHES_ALL                 13         14
        GL         GL_IMPORT_REFERENCES                   14         16
        OE         SO_PICKING_LINE_DETAILS                14         18
        WIP        WIP_TRANSACTION_ACCOUNTS               36         37
        GL         GL_JE_LINES                            36         41
        WIP        WIP_REQUIREMENT_OPERATIONS             45         50
        INV        MTL_MATERIAL_TRANSACTIONS              68         73
        INV        MTL_TRANSACTION_ACCOUNTS               77         83
        PO         PO_LINES_ALL                          366        372
Mon Jan 22                                                             page    1
                              Index extents report
                  Where extents > 200 or index extent changed
                          Comparing last two snapshots

DB      OWNER      IDX_NAME                          OLD_EXT    NEW_EXT
------- ---------- ------------------------------ ---------- ----------
prod    AP         AP_HOLDS_N1                             1          2
        PO         PO_REQUISITION_HEADERS_N1               1          2
        PO         PO_REQUISITION_HEADERS_U1               1          2
        MRP        MRP_FORECAST_UPDATES_N1                 2          3
        PO         RCV_TRANSACTIONS_N6                     2          3
        AP         AP_INVOICE_PAYMENTS_N4                  3          4
        WIP        WIP_DISCRETE_JOBS_N7                    3          4
        WIP        WIP_OPERATION_RESOURCES_N1              3          4
        MRP        MRP_WIP_COMPONENTS_N1                   4          5
        INV        MTL_DEMAND_INTERFACE_N4                 4          5
        INV        MTL_DEMAND_INTERFACE_N9                 4          5
        OE         SO_PICKING_HEADERS_N3                   4          5
        WIP        WIP_OPERATION_RESOURCES_U1              4          5
        GL         GL_BALANCES_N1                          5          6
        INV        MTL_DEMAND_INTERFACE_N8                 5          6
        APPLSYS    WF_ITEM_ACTIVITY_STATUSES_N1            5          6
        WIP        WIP_OPERATIONS_N2                       5          6
        OE         SO_PICKING_HEADERS_N6                   6          7
        GL         GL_BALANCES_N2                          7         10
        INV        MTL_MATERIAL_TRANSACTIONS_N5           12         13
        WIP        WIP_REQUIREMENT_OPERATIONS_N2          12         13
        GL         GL_IMPORT_REFERENCES_N1                12         14
        INV       MTL_MATERIAL_TRANSACTIONS_N2           13         14
        INV        MTL_MATERIAL_TRANSACTIONS_N1           14         15
        APPLSYS    WF_NOTIFICATIONS_ATTR_PK               14         15
        WIP        WIP_REQUIREMENT_OPERATIONS_N3          14         15
        APPLSYS    FND_CONCURRENT_REQUESTS_N5             15         16
        GL         GL_BALANCES_N3                         12         16
        INV        MTL_CST_ACTUAL_COST_DETAILS_U1         15        16
        INV        MTL_MATERIAL_TRANSACTIONS_N9           16         17
        WIP        WIP_REQUIREMENT_OPERATIONS_N1          16         17
        INV        MTL_TRANSACTION_ACCOUNTS_N3            18         20
        OE         SO_EXCEPTIONS_U1                       13         23
        INV        MTL_TRANSACTION_ACCOUNTS_N2            24         26
        OE         SO_EXCEPTIONS_N1                       28         52
        INV        MTL_SUPPLY_DEMAND_TEMP_N1              59         60
        APPLSYS    WF_ITEM_ATTRIBUTE_VALUES_PK            59         64
        EUL_MWC    EUL_EXP_EXP1_UK                       207        207
        APPLSYS    FND_CONCURRENT_REQUESTS_N2            214        214

In addition to change reports, you can also submit reports that show the current state of your tables. Here is a detail report of table metrics from the stats$tab_stats table, and reports on the data from your most recent snapshot.

rpt_tab.sql

column c1 heading "TABLE NAME"      format a15;
column c2 heading "EXTS"            format 999;
column c3 heading "FL"              format 99;
column c4 heading "# OF ROWS"       format 99,999,999;
column c5 heading "#_rows*row_len" format 9,999,999,999;
column c6 heading "SPACE ALLOCATED" format 9,999,999,999;
column c7 heading "PCT USED"        format 999;
column db_block_size new_value blksz noprint

select value db_block_size from v$parameter where name = 'db_block_size';

set pages 999;
set lines 80;

spool tab_rpt.lst

select
        table_name            c1,
        b.extents             c2,
        b.freelists           c3,
        num_rows              c4,
        num_rows*avg_row_len c5,
        blocks*&blksz          c6,
        ((num_rows*avg_row_len)/(blocks*&blksz))*100 c7
from
   perfstat.stats$tab_stats a,
   Remote DBA_segments b
where
b.segment_name = a.table_name
and
   to_char(snap_time,'yyyy-mm-dd') =
      (select max(to_char(snap_time,'yyyy-mm-dd')) from perfstat.stats$tab_stats)
and
   avg_row_len > 500
order by c5 desc
;

spool off;

This is a very interesting report for the Oracle Remote DBA. In this report, from our STATSPACK extension tables, we see the table name, the number of extents, the number of freelists, and the number of rows in the table, followed by additional information on the size of the table. This information includes a metric on the number of rows times the row length, which should be roughly equal to the amount of space the table is consuming. Next, we see the space allocated to the table, and the percent of the table that is used, which tells us roughly how much of the row space is consumed within the existing extents.

As we discussed earlier in this chapter, this type of report is very useful for identifying sparse tables when a table has multiple freelists. As you may recall, a sparse table is the table that is defined with multiple freelists, and because of a failure to parallelize the purge processes, the multiple freelists are unevenly balanced with free blocks. This causes the table to extend even though the data dictionary shows that the table has a tremendous amount of free space.

SQL> @rpt_tab

TABLE NAME      EXTS FL   # OF ROWS #_rows*row_len SPACE ALLOCATED PCT USED
--------------- ---- --- ----------- -------------- --------------- --------
PAGE_IMAGE       138   1      18,067     19,114,886      18,087,936       99
EC_CUSTOMER        3   1         367        219,099         393,216       56
MONOR_BOOKS        1   1          20         13,200         131,072       10
BOOK               1   1          19         12,711         131,072       10
EC_EMAIL_TEMP      1   1           6          3,780         131,072        3
EC_TEMPLATES       1   1           1            837         131,072        1

This report will show sparse tables as those tables that exhibit an increase in extents while at the same time appear to be largely empty.

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.