| |
 |
|
Oracle growth reports
Oracle Tips by Burleson Consulting |
Oracle provides time-series segment statistics
for tables and index in the 10g Automated workload Repository tables
(AWR). These can quickly extract data for Oracle growth
reports.
Note: for tracking table and index growth over
time, see the
dba_hist_seg_stat table.
If you are pre-Oracle10g you can write your own
tables to periodically examine and store object sizes for database
growth reports by creating your own STATSPACK extension tables (See
book "Oracle Tuning: The Definitive Reference",
for ready to use scripts to create a stats$tab_stats and
stats$idx_stats tracking tables).
In Oracle 10g, total database growth reports
are is easy because the dba_hist_seg_stat tables provides the delta
value for every table and index (Note: the "total" columns
are not implemented as of Oracle 10.0.2).
Below is a great script to display the total
Oracle growth between two periods. This script can be
modified to display overall database growth trends for capacity
planning purposes. You can also see the growth of key tables
and indexes with this Oracle growth tracking script.
Oracle table growth reports
set
feedback off
set
pages 80
set
linesize 150
spool
/tmp/weekly_growth.txt
ttitle "Total Disk Used"
select sum(space_used_delta) / 1024 / 1024 "Space used (M)",
sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%'
"Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
see code depot for full
script
where
end_interval_time > trunc(sysdate) - &days_back
and
sn.snap_id = a.snap_id
and
b.object_id = a.obj#
and
b.owner = c.owner
and
b.object_name = c.segment_name
and
c.owner = '&schema_name'
and
space_used_delta > 0;
ttitle "Total Disk Used by Object Type"
select c.segment_type, sum(space_used_delta) / 1024 / 1024
"Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Space (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%'
"Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
see code depot for full
script
where
end_interval_time > trunc(sysdate) - &days_back
and
sn.snap_id = a.snap_id
and
b.object_id = a.obj#
and
b.owner = c.owner
and
b.object_name = c.segment_name
and
space_used_delta > 0
and
c.owner = '&schema_name'
group
by rollup(segment_type);
spool
off
A sample of this report show the total database
growth between the two snapshot periods.
 |
If you like Oracle tuning, check-out my
latest book "Oracle Tuning: The Definitive Reference",
the best deal at 30% off, buying directly from the
publisher.
Packed with almost 1,000 pages of Oracle performance tuning
techniques, it's the foolproof way to find and correct
Oracle bottlenecks. |
|
