-----Original Message-----
From: smurthy@venatorgroup.com [mailto:smurthy@venatorgroup.com]
Sent: Tuesday, August 11, 1998 2:58 PM
To: akaplan@interaccess.com
Subject: Initial, next, min and max extents
Mr. Kaplan,
You have been a tremendous help to almost all DBAs. I have request for
you. Hope you can give us light ASAP.
We have a tablespace that is 18GB in size. It has several tables. Some of
the tables are growing very quickly to 5GB and even the Index is also
growing to 5GB. In this situation, to have efficient performance and less
fragmentation what should be the storage parameters for these large
tables.. Row size for one table is 52, approximate rows are 10 Million.
Row size for the second table is 101, approximate number of rows are 33
million. There are some smaller as well as medium sized tables also in the
same tablespace. If you can suggest the storage parameters for the above
two tables I would greately appreciate.
Table 1 - Total number of rows 10 Million, row length is 52.
Table 2 - Total number of rows 33 Million, row length is 52.
Tables are growing and growing everyday. Tables are not created in
parallel. Oracle Version 7.3.3 running on IBM RS6000/AIX.
Thanks,
Sita K. Murthy.
-------------- Reply --------------------
Sita,
What you need depends on two other factors:
1) What is your db_block_size?
2) What is the table usage? Is it mostly inserts, updates, deletes, and/or
selects?
The amount of updates (or the combination of deletes and inserts) whereby the
size of the row changes should determine the PCTFREE/PCTUSED.
The INIT/NEXT should be determinant on this answer in conjunction with the
db_block_size. Also, you want to size the table for expected future growth. Do
you want to prepare for 6 months of growth? 1 year?
You can see how large your tables are:
SELECT SUM(BYTES)/1024/1024 MEGS
FROM USER_EXTENTS
WHERE SEGMENT_NAME = 'table_name':
This will give you the current total size allocated for the table.
Assuming that the records are not updated, or deleted often, I'll assume a
PCTUSED 90, PCTFREE 10. Also, I'll assume an 8K block size. So, for both table
1 and table 2, each block would contain about 120 records. Table 1 has 10
million rows, so it should be 10 million / 120 = 83,333 blocks. This comes out
to 651 Megs. Table 2 has 33 million records, and is thus 3.3 times the size,
and should be 2.148 gigabytes.
Remember, adjust this according to #1 and #2 above.
Best of luck,
-Ari Kaplan
www.arikaplan.com
Back to Ari Kaplan's Home Page