Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

Tracking Oracle database growth

These concepts and scripts are presented in "Oracle Tuning: The Definitive Reference" by Rampant TechPress.


Tracking database growth is easy in Oracle using the AWR tables, but in previous releases you must create and populate special growth tables. Also see AWR solutions to table growth reports. and tracking row counts and block changes within tables.  Also we have the ability to track the growth of the whole schema and database and database growth reports.

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

  Most recent date 2001-01-22                                               

  Older date       2001-01-15
'*********************************************'


                  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                


                              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   

 
                
Tracking Oracle growth in Oracle9i

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.

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 extension tables contain the name of the database server. In a distributed environment, you can collect table and index information from a variety of servers and transfer the data into a centralized repository.

Collecting growth data for Oracle Tables and Indexes

The next step is to populate our 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 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:

--****************************************************************
-- Now we grab the index statistics
--****************************************************************
 
-- add analyze and table collection commands here
 
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 dba_indexes  i,
        dba_segments s,
        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
);

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 DBA may only be interested in collecting statistics on the most active tables within their database.

Reports on Tables and Indexes

The following reports are designed to show the DBA changes within the status of individual objects and the overall space usage for the database as a whole. For example, 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 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 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 reports and you want to compare the two most recent snapshots within your database.


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 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 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 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.

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


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
 

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.

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.

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.

connect perfstat/perfstat;

see code depot for full scripts

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
see code depot for full scripts


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
see code depot for full scripts



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
   dba_tables
where
see code depot for full scripts

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 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 dba_tables.bytes, and does not show the whole size of the database. This has always been a confounding issue for 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.

Also see dba_hist_tablespace_stat tips and these notes for tracking Oracle database growth. Also see how to use v$datafile to track database growth

These concepts and scripts are presented in "Oracle Tuning: The Definitive Reference" by Rampant TechPress.

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks