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