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_at_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 employer