| |
 |
|
Using v$datafile to track database growth
Oracle Database Tips by Donald BurlesonJune 14, 2015
|
Question: I am aware of using
dba_hist_seg_stat and STATSPACK extension table to track
database growth, put I hear that it is possible to do rudimentary
size tracking with the v$datafile view. Can I write a
script using v$datafile to see the database size changes?
Answer: 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 for creating Oracle database growth reports
and you can get size information from numerous sources:
- dba_hist_seg_stat
- v$datafile
-
dba_hist_tablespace_stat
- stats$tab_stats (custom created table)
In general, a production Oracle size report should have a
summary level and a drill-down so that you can see the growth of key
tables:
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
v$datafile queries for database size
Y ou can use the
v$datafile.creation_time column to find out how many datafiles
were added in any given month.
select
to_char(CREATION_TIME,'RRRR') year,
to_char(CREATION_TIME,'MM') month,
sum(bytes) Bytes
from
ee code depot for full
script
v$datafile
group by
to_char(CREATION_TIME,'RRRR'),
to_char(CREATION_TIME,'MM')
order by
1, 2;
This extended version of the v$datafile query to track
database size over time:
clear columns
set verify off
col tot_mon noprint new_value range
-- Compute how many months have gone since the database was
created
select ceil(months_between(sysdate, created)) tot_mon
from v$database
/
col maxinc noprint new_value max_inc
-- Compute the maximum number of times a file created in
'autoextend' mode
-- has grown
select max(round((d.bytes - d.create_bytes) / f.inc /
d.block_size)) maxinc
from sys.file$ f,
v$datafile d
where f.inc > 0
and f.file# = d.file#
and d.bytes > d.create_bytes
/
col GB format 9999990.00
col volume format A60
with extended_files as
(select file#,
nvl(lag(file_size, 1) over
(partition by file#
order by file_size), 0)
prior_size,
file_size,
block_size
from (select f.file#,
f.create_blocks + x.rn * f.inc
file_size,
f.block_size
from (select f.file#,
d.create_bytes / d.block_size
create_blocks,
f.inc,
d.bytes /
d.block_size blocks,
d.block_size
from sys.file$ f,
v$datafile d
where f.inc > 0
and f.file# = d.file#
and d.bytes > d.create_bytes
and rownum > 0) f,
(select rownum - 1 rn
from dual
connect by level <= &max_inc + 1) x
where (f.create_blocks + x.rn *
f.inc) <= f.blocks))
select "MONTH",
round(cumul/1024, 2) GB,
-- Draw a histogram
rpad('=', round(60 * cumul /
current_M), '=') volume
from
see code depot for full
script
(select to_char(cal.mon, 'MON-YYYY') "MONTH",
sum(nvl(evt.M, 0)) over (order by
cal.mon range unbounded
preceding) cumul,
tot.curr_M current_M,
cal.mon
from -- current database size (data
size)
(select round(sum(bytes)/1024/1024)
curr_M
from v$datafile) tot,
-- all the months since the
database was created
(select add_months(trunc(sysdate,
'MONTH'), -rn) mon
from (select rownum - 1 rn
from dual
connect by level <= &range)) cal,
-- all the months when the size of
the database changed
(select size_date,
round(sum(bytes)/1024/1024) M
from (-- files in autoextend mode
select file#, max(bytes) bytes,
size_date
from (select file#, bytes,
trunc(min(ctime), 'MONTH')
size_date
-- Get the oldest creation date of
tables or indexes
-- that are located in extensions.
-- Other segment types are
ignored.
from (select s.file#,
f.file_size * f.block_size bytes,
o.ctime
from sys.seg$ s,
extended_files
f,
sys.tab$ t,
sys.obj$ o
where s.file# = f.file#
and s.type# = 5
and s.block#
between f.prior_size and
f.file_size
and s.file# = t.file#
and s.block# = t.block#
and t.obj# = o.obj#
union all
select s.file#,
f.file_size * f.block_size bytes,
o.ctime
from sys.seg$ s,
extended_files f,
sys.ind$ i,
sys.obj$ o
where s.file# = f.file#
and s.type# = 6
and s.block# between f.prior_size
and
f.file_size
and s.file# = i.file#
and s.block# = i.block#
and i.obj# = o.obj#)
group by file#, bytes)
group by file#,
size_date
union all
-- files that are not in autoextend
mode
select d.file#,
d.create_bytes bytes,
trunc(d.creation_time, 'MONTH')
size_date
from v$datafile d,
sys.file$ f
where nvl(f.inc, 0) = 0
and f.file# = d.file#)
group by size_date) evt
where evt.size_date (+) = cal.mon)
order by mon
/
For more complete Oracle sizing
scripts,
see the code depot
download for full scripts
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
|
