Solved

How I estimate usage disk space of My Database.

Posted on 2001-06-21
12
907 Views
Last Modified: 2008-02-01
I will make specification of Billing System and offer to board. I have ER-Diagram. But I don't have DB Server. So I must make price of Hardware Server and Software. Now I don't know usage disk space requirement for Oracle Database Server. How I estimate usage disk space for my database server.
Please.
0
Comment
Question by:teetaro
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 4

Expert Comment

by:srikant033100
ID: 6213295
For estimation of disk space first u have to calculate the space requirement for each of the table .

Take each table and then calculate the average rowlenght and then the average no of rows likely to have in that table that will give u the space requirement of a table similary u have to go through each and every table or else go for the big table and have some sample estimation for the small table and then have some extra space and also keep some grace

srikant
0
 
LVL 4

Expert Comment

by:srikant033100
ID: 6213300
In the above calcuation u should also keep in mind system file space requirements and indexes and temp space for sorting and other things. Its more like overall guess work

srikant
0
 

Author Comment

by:teetaro
ID: 6213398
Thank you for srikant 's proposed answer.
But I want to know disk space used for each field type  (such as date ,number ,varchar2) on Oracle DB Server too.
So I can calculate space requirement for each of the table.
0
 
LVL 4

Expert Comment

by:srikant033100
ID: 6213438
The storage occury by field are

char(n)- n byes
varchar2(n) - n byes but if u specify varchar2(20) and put only 10 char then it take only 10 byes

number max 21 byes but generally it takes not more than 10-15 byes

rowid 6 byes
datetime 18 byes

Srikant
0
 
LVL 2

Expert Comment

by:renuraj
ID: 6213453
VSIZE function gives the diskspace used for each field type.

For Ex:

select vsize(empno), vsize(ename), vsize(hiredate) from emp
where rownum < 3;

Output:

VSIZE(EMPNO) VSIZE(ENAME) VSIZE(HIREDATE)
------------ ------------ ---------------
           3            6               7
           3            5               7

In this way, after calculating the row size for each table and no of rows estimated for each table gives the diskspace occupied by table.

Regards,
0
 
LVL 2

Expert Comment

by:renuraj
ID: 6213457
VSIZE returns the number of bytes in the internal representation

This is in addition to the above proposed answer.

Regards,
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:srikant033100
ID: 6213484
Hi but he doesnot have the database in place that's why the vsize will not help

srikant
0
 
LVL 1

Expert Comment

by:tvspriya
ID: 6213524
Example: Table Name Allocations
 ALLOC_ID                        NOT NULL NUMBER(20)
 REQUEST_ID                      NOT NULL NUMBER(20)
 STATUS_ID                       NOT NULL NUMBER(20)
 CREATION_DATE                   NOT NULL DATE
 CREATED_BY                      NOT NULL VARCHAR2(30)

Normally, Date takes 7, Varchar2 takes the same size as specified size and Number also the same. So for the above mentioned Table stucture the Average Row Length is 97, add some buffer so multiply that value with 1.2, you get 116.4 put it as 117 add 3 for Row Header, you get 120. This way you have to calculate all the tables' average row length. Then subsequently you can calculate the estimated Table storage size based on this Average row length multiplied with the expected number of rows for a period normally DBA will do this for 6 months. Summ up all these values you will get the disk space required. Ensure you add some buffer at the end to be on the safe side. Hope this helpls you.

tvspriya
0
 

Author Comment

by:teetaro
ID: 6213733
Thank you for renuraj 's proposed answer.
But it doesn't suit directly my question because I don't have any DB Server yet.
Thank you for tvspriya 's comment.
Your comment make me clear ,
but if I specify field type as NUMBER(20,2) ,how much storage does it take?
And I suspect about Date field ,you said "Date takes 7 bytes" ,but [Srikant] said Datetime take 18 bytes ,
Whome should I believe?
0
 
LVL 2

Expert Comment

by:renuraj
ID: 6213815

If NLS_DATE_FORMAT is set as DD-MON-YY then date field takes 7 bytes and if it is set as DD-MM-YYYY then date field takes as 8 bytes.  This depends on the NLS_DATE_FORMAT set.

Srikant mentioned it for both date and time, which takes 18 bytes (maximum) to store date as well as time .

Regards,  
 
0
 

Accepted Solution

by:
davidgornshtein earned 80 total points
ID: 6214206

Hi,
  Your question a bit complicated, but I will
  give you basics for the answer, I mean
  basic type sizes in addition to already explained
  and all overhaeds.
  Number defined in oracle as variable length sequence
  where first byte is exponent + sign (msb) and
  all other bytes are mantissa represented in excess 100
  and last byte is 0x66 for negatives.
  Generally, regular number (integer) takes about ~3-6
  bytes, float ~6-12 bytes.
 
  char and varchar2 explained well in "srikant" comment.
  however date is 7 bytes:
   first two bytes: centry and year (excess 100 format)
   next 2 bytes: month and day
   last 3 bytes: hours, minutes and seconds (24 hours
   excess 1 format)

  "18 bytes" it's mistake  !!!
  Example for all the guys:

  SQL> select dump(to_date('12:12:12  
    12:12:1999', 'hh24:mi:ss dd:mm:yyyy')) from dual;

DUMP(TO_DATE('12:12:1212:12:1
-------------------------
Typ=13 Len=8: 7,207,12,12,12,12,12,0

Real length is 7 first byte is length itself - overhead
as I will explain later.

  each value (number, char, varchar2) have 1 byte length
  overhead is stored as |column len | data
  each row have 3 bytes of row header overhead
  real life example:
upper line it's binary data, lower explanation.

2C  01     03     02                           C1 33
Row Flg     Lck b.     Cols# [ 2]     col  0: [ 2]  c1 33    

 C1 33 it's number, 50 in dec.

each rowid in index in oracle 8 is 10 bytes:
example rowid = AAAA73AADAAAGuqAAa
---------------------------------------------------
10 00 00 14 247 00 192 107 170 00 26

Block header overhead for datablock is 48 Bytes -
it is block header with 1 default ITL -
Cache/Transaction layer header.
 
You will define tables with some pctfree
it's additional overhead.
Block Size * pctfree/100

Each additonal ITL (defined by INITRANS in create
table clause)
have additional overhead of 24 bytes per data block.

You could also have additional overhead of segment
header - one block, if you have multiple freelist groups
each such group have header block.

Each chained or migrated row is 10 bytes overhead,
but you will have such rows only after some time
of work.

So, you will have additional overhead of
10 * (Chained rows per block + migrated row per block)

Your rollback segments tablespace
in prodution DB should be at least 2GB.
System tablespace at least 500 MB.
Tempoarary tablespace at least 2GB.  

Generaly, there are no magic answer to your question,
but this answer is most close to realty :-)
If you have additional questions about the terms
I used - e-mail is good invention :-).

Best regards.
David Gornshtein
Oracle Certified Professional
Oracle & MS Sql Server senior DBA
davidg@extent.com
Extent Technologies Ltd.
972-3-7530635    
0
 

Author Comment

by:teetaro
ID: 6226488
Thank you everybody for your comment/answer.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now