Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Maxextents unlimited on LMT ?
I wish I could work out what the essential point of this test is, but for
the record here is 9i R2 on Windows 2000 Advanced Server, via a Windows XP
client, over a 10Mbps network (would have been 100Mbps, but read other posts
elsewhere). It was a Wednesday, and it was 6pm. A beef casarole was
simmering. Sibelius's Symphony No.2 was playing. Anything else you want to
know, ask my doctor:
SQL> connect system/nottellingyou_at_db9
Connected.
SQL> set timing on
SQL> create tablespace dummy
2 datafile 'c:\dummy.dbf' size 2001m
3 extent management local uniform size 32k;
Tablespace created.
Elapsed: 00:06:46.04 (I don;t have the fastest harddisks in the west, but I
do now have 0.5Tb of them!)
SQL> create user x identified by x;
User created.
SQL> alter user x quota 1000m on dummy;
User altered.
SQL> create table x.p ( x number ) tablespace dummy
2 storage ( initial 99m next 99m minextents 10 );
Table created.
Elapsed: 00:02:19.07
SQL> drop table x.p;
Table dropped.
Elapsed: 00:00:02.00
SQL> alter user x quota unlimited on dummy;
User altered.
SQL> create table x.p ( x number ) tablespace dummy
2 storage ( initial 99m next 99m minextents 10 );
Table created.
Elapsed: 00:01:07.02
SQL> drop table x.p;
Table dropped.
Elapsed: 00:00:02.02
Is the point that quota unlimited appears to halve the table creation time, because of all the checking Oracle has to do otherwise? (Forgive me, I've had a bad day).
If so, this re-inforces my argument I've advanced for a long while that much as I may think developers have odorous breath and sweaty palms, they are part of the team, are (allegedly) professionals, and don't deserve to have quotas slapped on them by upstart DBAs. I've long advocated unlimited quotas for everyone. And that's me being serious.
On the other hand, any developer who created a 100M table in a 32K-extent tablespace would soon have sweaty palms for quite a different reason.
Regards
HJR
"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
news:3d2bec8b$0$8508$cc9e4d1f_at_news.dial.pipex.com...
> "Pete Sharman" <peter.sharman_at_oracle.com> wrote in message
> news:agfi8v02fvu_at_drn.newsguy.com...
> > In article <3D2B34C3.1E50_at_yahoo.com>, Connor says...
> > 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:
>
> I get similar results to Connor on 9.01.
>
> SQL> create tablespace dummy
> 2 datafile 'c:\temp\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:01:04.05
> SQL> drop table x.p;
>
> Table dropped.
>
> Elapsed: 00:00:03.05
> SQL> alter user x quota unlimited on dummy;
>
> User altered.
>
> Elapsed: 00:00:00.00
> SQL> create table x.p ( x number ) tablespace dummy
> 2 storage ( initial 99m next 99m minextents 10 );
>
> Table created.
>
> Elapsed: 00:00:25.02
> SQL> drop table x.p;
>
> Table dropped.
>
> Elapsed: 00:00:01.00
> SQL>
>
>
Received on Wed Jul 10 2002 - 04:06:26 CDT