| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Capacity Planning -- Expecting the DB growth !!!
I can forward a script that shows how to use DBMS_SPACE.
The other stuff I mentioned is not exactly in packagable form.
Just use:
exec show_space('SCOTT','EMP')
Jared
create or replace procedure show_space (
p_segname in varchar2
, p_owner in varchar2 default user
, p_type in varchar2 default 'TABLE'
, p_partition in varchar2 default null
)
l_free_blocks number;
l_total_blocks number;
l_total_bytes number;
l_unused_bytes number;
l_unused_blocks number;
l_LastusedExtFileId number;
l_LastUsedExtBlockId number;
l_last_used_block number;
l_segname varchar2(30);
l_owner varchar2(30);
l_type varchar2(20);
l_partition varchar2(30);
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line(rpad(p_label,40,'.')||p_num);
end;
begin
l_segname := upper(p_segname);
l_owner := upper(p_owner);
l_type := upper(p_type);
l_partition := upper(p_partition);
dbms_space.free_blocks (
segment_owner => l_owner
, segment_name => l_segname
, segment_type => l_type
, partition_name => l_partition
, freelist_group_id => 0
, free_blks => l_free_blocks
);
dbms_space.unused_space (
segment_owner => l_owner
, segment_name => l_segname
, segment_type => l_type
, partition_name => l_partition
, total_blocks => l_total_blocks
, total_bytes => l_total_bytes
, unused_blocks => l_unused_blocks
, unused_bytes => l_unused_bytes
, last_used_block => l_last_used_block
, last_used_extent_file_id => l_LastusedExtFileId
, last_used_extent_block_id => l_LastUsedExtBlockId
);
p( 'Free Blocks', l_free_blocks);
p( 'Total Blocks', l_total_blocks);
p( 'Total Bytes', l_total_bytes);
p( 'Unused Blocks', l_unused_blocks);
p( 'Unused Bytes', l_unused_bytes);
p( 'Last Used Ext FileId', l_LastusedExtFileId);
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId);
p( 'Last Used Block', l_last_used_block);
end;
show error procedure show_space
"Ruth Gramolini" <rgramolini_at_tax.state.vt.us>
Sent by: root_at_fatcity.com
12/18/2002 07:04 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Re: Capacity Planning -- Expecting the DB growth !!!
Jared,
Can you send me the script you use. I can't even figure out the syntax
for
DBMS_SPACE from the docs. I would be very grateful
Ruth
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Tuesday, December 17, 2002 3:58 PM
I use DBMS_SPACE to calculate the actual size of all data objects in our SAP databases. The data is collected once a week.
Once a month I use the collected data to create an aggregate table via Oracle's linear regression and partitioning SQL operators.
Run the projections for the current month, end of year, the 2 following 6 month periods, put the data in excel and chart it.
Earlier this year my projection for SAP running out of space was 6/28/02.
There were some delays in getting the new storage hardware installed.
Guess which day we ran out of space? ;)
That was just dumb luck that it happened the same day, but now people believe my projections. :)
I opted for the use of DBMS_SPACE, as that is the best way to find out how much space is actually being used for data. I didn't care about allocated empty blocks, or how big the tablespaces were.
There's now about 14 months of data for this, and though growth is not alwasy linear, it's still better than a SWAG.
Jared
"Reddy, Madhusudana" <Madhusudana.Reddy_at_bestbuy.com>
Sent by: root_at_fatcity.com
12/17/2002 11:25 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: RE: Capacity Planning -- Expecting the DB growth
!!!
Hello All,
It should include all as you said , but since I do not belong to a
capacity
planning group in my shop , I mostly concentrate on the DB growth . I have
taken Sales as business object to correlate with the DB size . I think in
our case both of them are directly proportional. ( Business objects may
vary
from shop to shop )
first step I have done is calculating the : Growth factor = avg. History
Db
growth / avg Hist Sales growth
Second Step : identifying the Sales projection for the next fiscal year ( Ie. I have to get sales growth percentage from the business team , for example 15% and multiply with sales history, for each week I guess.)
3rd step: Now I have sales projection and growth Factor , and can the projected DB growth .
Well The above is the thing I am working on by having some queries to get the data from the DB. Also I have to automate the whole process .
I would like to know how this Capacity planning followed in your shop. What's your answer when your group manager asks how much disk we need for holiday prep ?? Like this we can have many questions. I know somebody is having a better approach getting followed . Would appreciate if you share with us.
or somebody can better help me in """identifying """ the Q? from managers !!!!
Thank YOU all for your replies
Madhu
-----Original Message-----
Sent: Tuesday, December 17, 2002 12:19 PM
To: Multiple recipients of list ORACLE-L
Check the link Kirti has posted (orapub).
Is capacity planning only on the database size ? In my mind it also includes :
Transaction description (online and batch)
Transaction frequency (online and batch)
Transaction window
Networking requirements
Number of users (all and concurrent)
Overall disk space (inside and outside the
database)
Availability
> ATTACHMENT part 2 image/gif name=Blank Bkgrd.gif
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: stephane_paquette_at_yahoo.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reddy, Madhusudana INET: Madhusudana.Reddy_at_bestbuy.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: rgramolini_at_tax.state.vt.us Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Dec 18 2002 - 11:50:35 CST
 |
 |