Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Maxextents unlimited on LMT ?

Re: Maxextents unlimited on LMT ?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 10 Jul 2002 20:48:09 +0100
Message-ID: <3D2C8F79.6BFD@yahoo.com>


Pete Sharman wrote:
>
> In article <3D2B34C3.1E50_at_yahoo.com>, Connor says...
> >
> snip
> >
> >So the quote was:
> >
> >"The number of extents in a locally managed tablespace has absolutely
> >none (very negligible in the worst of cases) impact on performance"
> >
> >Could you pass this test onto the development lead...
> >
> >SQL> create tablespace dummy
> > 2 datafile 'e:\orasw\db1\dummy.dbf' size 2001m
> > 3 extent management local uniform size 32k;
> >
> >SQL> create user x identified by x;
> >
> >User created.
> >
> >SQL> alter user x quota 1000m on dummy;
> >
> >User altered.
> >
> >SQL> set timing on
> >SQL> create table x.p ( x number ) tablespace dummy
> > 2 storage ( initial 99m next 99m minextents 10 );
> >
> >Table created.
> >
> >Elapsed: 00:02:04.57
> >SQL> drop table x.p;
> >
> >Table dropped.
> >
> >Elapsed: 00:01:92.14
> >system_at_db1>
> >
> >2 MINUTES!!!! WHAT A SHAMBLES!
> >
> >When they put the quota system in a bitmap as well, then I'll be
> >convinced :-)
> >
> >Cheers
> >Connor
> >--
> >==============================
> >Connor McDonald
> >
> >http://www.oracledba.co.uk
> >
> >"Some days you're the pigeon, some days you're the statue..."
>
> Hmm, not sure what you ran this on (maybe you've turned off the FAST=TRUE
> parameter!). Here's what I get on my PC with 9.2:
>
> SQL> create tablespace dummy
> 2 datafile 'c:\temp\dummy.dbf' size 2001m
> 3 extent management local uniform size 32k;
> create tablespace dummy
> *
> ERROR at line 1:
> ORA-01119: error in creating database file 'c:\temp\dummy.dbf'
> ORA-27044: unable to write the header block of file
> OSD-04008: WriteFile() failure, unable to write to file
> O/S-Error: (OS 112) There is not enough space on the disk.
>
> Damn ran out of space. Clean up and rerun:
>
> SQL> /
>
> Tablespace created.
>
> SQL> create user x identified by x;
>
> User created.
>
> SQL> alter user x quota 1000m on dummy;
>
> User altered.
>
> SQL> set timing on
> SQL> create table x.p ( x number ) tablespace dummy
> 2 storage ( initial 99m next 99m minextents 10 );
>
> Table created.
>
> Elapsed: 00:01:08.02
> SQL> drop table x.p;
>
> Table dropped.
>
> Elapsed: 00:00:04.07
> SQL> drop tablespace dummy including contents and datafiles;
>
> Tablespace dropped.
>
> Note the difference in timings. What version were you on?
>
> HTH. Additions and corrections welcome.
>
> Pete
>
> SELECT standard_disclaimer, witty_remark FROM company_requirements;

Dunno what version (!) - I've recently grabbed a second disk (120G) for the home PC (only cost 90) so I've currently got

8.1.6.3
8.1.7.3
9.0.1.2
9.0.1.3
9.2 

all under Win2K... It was one of them! I'm guessing 8.1.7.3 (since its the top-most icon on the desktop!)

As you can see - I put a lot of careful and rigorous thought into this test :-)

But - I'll do it again under 9.2 - results below:

SQL> create tablespace dummy
  2 datafile 'e:\orasw\db1\dummy.dbf' size 2001m   3 extent management local uniform size 32k;

Tablespace created.

SQL> create user x identified by x;

User created.

SQL> alter user x quota 1000m on dummy;

User altered.

SQL> set timing on
SQL> create table x.p ( x number ) tablespace dummy   2 storage ( initial 99m next 99m minextents 10 );

Table created.

Elapsed: 00:02:22.06
SQL> drop table x.p;

Table dropped.

Elapsed: 00:00:03.04
SQL> Interesting - they've souped up the the DROP part...

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."
Received on Wed Jul 10 2002 - 14:48:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US