Daniel - Excellently put. The other factor you can usually only estimate is
the row size, until you get some test data. The old engineer in me says that
if you can only guess at the row size and number of rows, then don't pretend
the exact formula is going to do much for you. I started with a slide rule
which was only accurate to 2-1/2 significant digits. When people started
using calculators, they thought they had 10 significant digits. But if even
one of your input variables is only accurate to 2 digits, then that is
probably all the accuracy you're going to wind up with.
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Monday, March 17, 2003 8:14 AM
To: Multiple recipients of list ORACLE-L
Jai,
The formula you are using is reasonably accurate, but not very useful.
One of the main problems I have found with this approach is that the number
of rows is not usually known. The business side and designers might have an
idea of data sizes, but the reality is that most times they are not
accurate. This makes a fine-grained sizing approach ultimately innacurate.
Use locally managed tablespaces and create estimated sizes. Add in
enough space for variances and keep an eye on them. When in doubt, err on
the side of overallocation.
--
Daniel W. Fink
http://www.optimaldba.com <http://www.optimaldba.com>
IOUG-A Live! April 27 - May 1, 2003 Orlando, FL
Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL
Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> wrote:
Dear All,
I have a task on hand to size a database for our application. I have used
the Oracle rule to find out the rowsize of rows in a table.
1. Space available for the data (SAD) = data block size - block
header size = db_block_size - kcbh - ub4 - ktbbh - (initrans
-1)* ktbit - kdbh
2. Available Data Space (ADS) = ceil(SAD * (1-pctfree/100)) - kdbt
3. Space used per row (SPR) = (Total size of the columns data length)
+ (1 byte per column with length < 250 )+ (3 * 1 byte per
column with length >= 250)
4. Row Size (ROWSZ) = 3 * ub1 + SPR
5. Space used per row(SPROW) = max(ub1 * 3 + ub4 + sb2, ROWSZ) + sb2
6. Total Size of the table = Expected Number of records * SPROW
I hope this formula is correct.
How can I do the sizing of indexes. Shouldn't I take into account the 10
bytes consumed by the ROWID pseudocolumn apart from the column(s) that are
indexed ?
TIA for all your help.
Best Regards
Jai
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
