- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- database storage capacity planning
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2005 07:01 PM
тАО09-06-2005 07:01 PM
database storage capacity planning
automated process to calculate table storage requirements/sizes? We have reviewed the Oracle Note:10640.1
but the results do not give the correct values.
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2005 09:20 PM
тАО09-06-2005 09:20 PM
Re: database storage capacity planning
Here are some simple rules I remember now:
- A different tablespace for tables and indexes. Also consider a pair of tablespaces for each different module.
- The size of the datafiles you should use depends on several factors and it is very difficult to estimate. But since you are talking about a datawharouse, you should size each datafile with 2Gb for starting...
- About tables, the important aspect to keep few large extents. I have a little script that helps me to control this for all tables:
select s.owner, s.segment_name, s.extents, s.max_extents
from dba_segments s
where s.extents >= 10 and
s.tablespace_name != 'SYSTEM' and
s.owner != 'SYS' and
s.segment_type = 'TABLE' and
not exists (select 1 from dba_tblexts e
where nvl( e.extents,0) + nvl( e.extents_tratados,0) + 2 >= nvl( s.extents,0) and
s.segment_name = e.segment_name and
s.owner = e.owner);
I've created this dba_tblexts table to know how many extents I've verified and considered normal for each table: if an extent for a table is created each month it may be normal but if this happens each week, it may be because the "next extent" size in the table storage definitions is too small and you should increase it...
Anyway, if you have table with many extents, you cannot recreate it but you can always export and import it with the "merge" option to create a single extent at import time.
For indexes, it is much more easy because you can recreate them: I normally recreate indexed with more than 10 extents.
That's what I remember for now. Hope this'll help you!
Best Regards,
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2005 09:28 PM
тАО09-06-2005 09:28 PM
Re: database storage capacity planning
We need to establish storage capacity for tables and therefore require a formula to determine a tables size in MB/GB.
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2005 11:24 PM
тАО09-06-2005 11:24 PM
Re: database storage capacity planning
select segment_name, sum(bytes) ebytes
from dba_extents
where owner not in ('SYS', 'SYSTEM') and
segment_type = 'TABLE'
group by segment_name
order by sum(bytes) desc
Best Regards,
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2005 11:27 PM
тАО09-06-2005 11:27 PM
Re: database storage capacity planning
select segment_name "Table Name", round( sum(bytes) / 1024 / 1024) "Table Size (Mb)"
from dba_extents
where owner not in ('SYS', 'SYSTEM') and
segment_type = 'TABLE'
group by segment_name
order by round( sum(bytes) / 1024 / 1024) desc
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-06-2005 11:38 PM
тАО09-06-2005 11:38 PM
Re: database storage capacity planning
Thanks for the feedback. We already know how to determine the current size a table is taking. What we are attempting to determine is to project what a tables size would be say in 2006 given that say the number of records in that table is "x". Is there know way of projecting a tables size using the number of expected rows;average row length/size; and db block size?
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-07-2005 12:12 AM
тАО09-07-2005 12:12 AM
Re: database storage capacity planning
Hmmm, I would simply assume a linear relation of row-count and used-space.
x rows now, taking y GB? Then for 2x rows anticipate 2y GB.
For datawarehouse applications I would go much more aggressive on the file sizes then Eric suggest. How about starting at 20GB?
Also, be sure to try to have Oracle manage as much as possible: reduce the number of tablespaces, reduce the number of device, Stripe And Mirror Everything (SAME).
KISS: if a whole set of disks / storage quantity is going to be dedicated for the Oracle DB, then just hand it over. Don;t try to help to much by dividing it up in morcels.
I assume you are not yet running 10g, but you may want to look ahead at 10g new features to understand which direction Oracle is going. (which I'm tempted to summarize as 'give oracle the keys to the castle and it will take over from there' :-).
Just an opinion,
hth,
Hein.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-07-2005 12:19 AM
тАО09-07-2005 12:19 AM
Re: database storage capacity planning
For example, I've a table A with 1097651 rows sizing 264 Mb. This gives 4158 rows per Mb.
I know the average rows per year with the following query:
select max(trunc( creation_date)) - min(trunc( creation_date)) "Table Days",
count(*) "N.├В┬║ Rows",
round( count(*) / (max( trunc( creation_date)) - min(trunc( creation_date))) * 365) "Avg. Rows / Year"
from A
For table A, I get the following result:
Table Days N.├В┬║ Rows Avg. Rows / Year
1753 1097651 228547
So, the table will increase: 228547 (Row/Year) / 4158 (Row/Mb) = 55Mb!!
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-07-2005 12:35 AM
тАО09-07-2005 12:35 AM
Re: database storage capacity planning
just use estimates based on your experience and forecast the appropriate size or each tables! Don't be shocked!
Then create your Locally Managed Tablespaces.
when it comes to tables, then I would suggest you LEAVE THE STORAGE PARAMETERS OFF. If you are going with a simple "index tablespace" and "data tablespace" -- you might just as well go with "a single tablespace" and put everything in it (no performance gains to be
had by separating the two structures -- none). Then just use auto-allocation and stripe the tablespace across as many devices as possible.
You can also use tools like TOAD to generate estimates based on your own inputs.
Again, sizing tables of your own table is not an exact science.
good luck!
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-07-2005 12:40 AM
тАО09-07-2005 12:40 AM
Re: database storage capacity planning
Thanks for the feedback. Unfortunately when requesting additional storage space one needs to provide solid proof our how one came to the requested storage amount. Therefore we need a solid formula to forecast storage capacity growth.
Thanks in advance!