Re: Looking for Scripts for Database Month Growth / Tablespace Growth.
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: sfaroult@xxxxxxxxxxxx
- Date: Tue, 18 Jan 2011 16:38:44 +0000
Stephane
Being lazy really. If a tables stats aren't updated (because the stale
threshold hasn't been reached) are the num_rows stats updated anyway? I'd
*expect* that they weren't and so you might get some data like this
as_of num_rows
01-01-11 1000000
07-01-11 1000000
14-01-11 1250000
21-01-11 1250000
28-01-11 1250000
04-02-11 1600000
....
For forecasting purposes it probably doesn't matter, but for executives the
question "why did we add 350,000 rows at the start of feb when we'd not
added any data for the previous 3 weeks" is likely to arise :). "as_of"
should probably be the date of the stat change or else a caveat should be
attached to reports - especially for very large relatively slowly changing
tables.
On Tue, Jan 18, 2011 at 3:03 PM, Stephane Faroult <sfaroult@xxxxxxxxxxxx>wrote:
> Sheldon,
>
> I happen to be working on a video tutorial and I'm just on something
> related. IMHO the best thing to do is to relate the size of tables (and
> their associated indexes) to the number of lines, because the number of
> lines is often the only thing that makes sense business-wise and for which
> people are willing to venture future figures.
>
> What I suggest is that you create as system something such as
>
> create table volume_history(
> as_of date not null,
> table_owner varchar2(30) not null,
> table_name varchar2(30) not null,
> num_rows number,
> K_table number,
> K_index number,
> constraint volume_history_pk
> primary key (table_owner, table_name, as_of))
> tablespace sysaux
> /
>
> Then connect as SYS, grant SELECT on DBA_TABLES, DBA_SEGMENTS and
> DBA_INDEXES to SYSTEM (otherwise procedure creation will fail)
> and create (as SYSTEM again) something like this (I assume you have no lobs
> - otherwise you should involve DBA_LOBS in the query - you can also remove
> other schemas than SYS and SYSTEM - or keep them!):
>
> create or replace procedure record_volume
> as
> begin
> insert into volume_history(as_of, table_owner, table_name,
> num_rows, k_table, k_index)
> select trunc(sysdate),
> table_owner,
> table_name,
> sum(num_rows) num_rows,
> round(sum(table_size)/1024) K_table,
> round(sum(index_size)/1024) K_index
> from (select t.owner table_owner,
> t.table_name,
> t.num_rows,
> s.bytes table_size,
> 0 index_size
> from dba_tables t
> inner join dba_segments s
> on s.owner = t.owner
> and s.segment_name = t.table_name
> where t.owner not in ('SYSTEM', 'SYS')
> union all
> select i.table_owner,
> i.table_name,
> to_number(null) num_rows,
> 0 table_size,
> s.bytes index_size
> from dba_indexes i
> inner join dba_tables t
> on t.owner = i.table_owner
> and t.table_name = i.table_name
> inner join dba_segments s
> on s.owner = i.owner
> and s.segment_name = i.index_name
> where t.owner not in ('SYSTEM', 'SYS'))
> group by table_owner, table_name;
> commit;
> end;
> /
>
>
> Use DBMS_SCHEDULER to run it at a frequency that is equal to or greater
> than stats collection (once a week is more than enough, IMHO)
> and you have some data that you can export to a CSV file from time to time
> to draw the graphics that usually enrapture management.
>
> Of course, you can collect data about tablespace size etc. in the very same
> way. And if you add the database name to the data, you can even consolidate
> everything at one place if needed.
>
> Hope that helps,
>
>
> Stephane Faroult
> RoughSea Ltd <http://www.roughsea.com>
> Konagora <http://www.konagora.com>
> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
>
> On 01/18/2011 01:08 PM, Sheldon Quinny wrote:
>
> Hi Everyone...
>
> I take care of 4 - Oracle 10GR2 Database on Windows Platform.
>
> I am looking for scripts or ways which can be implemented to monitor the
> growth of the tablespaces and database..[Monthly,Yearly so on..]
>
> Please can someone share with me the Growth Rate Monitoring Methods so I
> can get it working out here.
>
>
> Thank you,
> Sheldon..
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
