Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle file overhead question
On 29 Sep 1998 04:03:10 GMT, tph_at_rmi.net (Tom "Tom" Harrington) wrote:
>In article <6uoc40$ia7$1_at_nnrp1.dejanews.com>, doshis_at_my-dejanews.com wrote:
>>All,
>>
>> If you have a datafile = 100M you can not give 100M as your initial extent
>>due to overhead.
>
>Isn't that just a pain in the ass?
>
>Here's a little Perl script that'll do it for you:
>
>----------------------------------------------------------------------
>#!/bin/perl
>
>$datafile_size = $ARGV[$#ARGV];
>print "$datafile_size\n";
>
>$total_size = $datafile_size + ($datafile_size>>2) * abs(cos($datafile_size)) +
> ($datafile_size>>4) * log($datafile_size);
>print "$total_size\n";
>----------------------------------------------------------------------
>
>Save it as "overhead", and run it like this:
>
>% overhead 100
>149.188992923121
>
>So, the 100M datafile requires 149.2M for the initial datafile size.
>It seems excessive, but it works. I got this by sending email to the
>Online Oracle Help Desk. It's an experimental program developed at a
>university, but it seems to work pretty well. Send email to
>oracle_at_cs.indiana.edu; put "tellme" in the subject, and your question
>in the body of the message.
I'm not quite sure where did you get those figures from, but they are definitely *wrong*. I did a test on Personal Oracle 7.3.2, Win95, db_block_size = 2K:
SQL> connect system
Enter password: *******
Connected.
SQL> CREATE TABLESPACE blah DATAFILE 'd:\orawin95\database\blah1.dbf'
2 SIZE 100M;
Tablespace created.
SQL> CREATE TABLE blah (c1 NUMBER) TABLESPACE blah
2 STORAGE (INITIAL 102400K NEXT 100K PCTINCREASE 0);
CREATE TABLE blah (c1 NUMBER) TABLESPACE blah
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace
BLAH
SQL> CREATE TABLE blah (c1 NUMBER) TABLESPACE blah
2 STORAGE (INITIAL 102398K NEXT 100K PCTINCREASE 0);
Table created.
So as you can see, on an empty 100M tablespace (datafile) there is exactly 1 db block (2K in my example) overhead for file header. So I was able to create an extent of size 100M - 2K = 10238K!
>>How to estimate/calculate overhead?
>
>I have a feeling this'll go right overhead, IYKWIM, AIDTYD.
>
>> any help appreciated in advance.
>> thankx for yr time.
>
>Always happy to help.
>
>> Shailesh Doshi
>> DBA
>
>DBA? Doing Business As what?
>
>--
>Tom "Tom" Harrington ----- tph@rmi.net ----- http://rainbow.rmi.net/~tph
> use Perl || die;
>Cookie's Revenge: ftp://ftp.rmi.net/pub2/tph/cr/cookies-revenge.sit.hqx
Hm, I just noticed this thread is also crossposted rec.humor.oracle.d! So if you've just been sarcastic with your numbers, neglect my message an consider me as being an ididot who can't recognize a joke at a first look.
Regards,
-- Jurij Modic <jmodic_at_src.si> Certified Oracle7 DBA (OCP) ================================================ The above opinions are mine and do not represent any official standpoints of my employerReceived on Tue Sep 29 1998 - 00:00:00 CDT