Re: Maxextents unlimited on LMT ?

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..."