Re: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 4 Mar 2011 17:54:06 +0000
Message-ID: <AANLkTikjYBHwjubDd_dYndDsaFLQ_fq9NN__VS4dTJZy_at_mail.gmail.com>

Other than the fact that typically fragmentation and extent counts don't tend to matter that much really is there a reason for the recommendation that I'm missing. It's certainly the case that autoallocate *can* leave you in the old position where there is sufficient free space to extend an object but the extent size chosen won't fit any of the gaps left. Uniform by definition can't hit this issue. Given that I've as yet been unable to find a reason to prefer n extents over m extents where m >>> n, but I have been able to find plenty of reasons to limit datafile size my personal bias is for uniform extents (of 1mb or whatever the platform read size is but that's a pedantic detail). Oracle consistently recommend AUTOALLOCATE but I've not yet had a convincing rationale. My test from 2004 probably not run since is below - it only works because of the unusual pattern of extend|drop and the small limit filesize, IIRC the sizes chosen were such that in 2004 my 32m tablespace would exactly fill not error as below.. (8k db block size)

SQL> select banner from v$version;
BANNER

---

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 -
Production
CORE 11.2.0.2.0 Production

TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 -
Production
SQL>
SQL> create tablespace auto_alloc_test
  2 datafile 'c:\temp\auto_alloc.dbf' size 32832k   3 extent management local;
Tablespace created.

SQL>
SQL> /*
SQL> create the tables
SQL> */
SQL>
SQL> begin

  2 for i in 1..32 loop
  3 execute immediate 'create table table'||i||'(col1 number,col2 number) tablespace auto_alloc_test';
  4 end loop;
  5 end;
  6 /
PL/SQL procedure successfully completed. SQL>
SQL> select sum(bytes)/1024 free_k from dba_free_space where tablespace_name='AUTO_ALLOC_TEST';

FREE_K

---

29760
SQL>
SQL> begin
  2 for i in 1..15 loop

  3        for j in 1..32 loop
  4         execute immediate 'alter table table'||j||' allocate extent';
  5        end loop;

  6 end loop;
  7 end;
  8 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TABLE18 by 8 in tablespace AUTO_ALLOC_TEST
ORA-06512: at line 4

SQL>
SQL> select sum(bytes)/1024/1024 free_M from dba_free_space where tablespace_name='AUTO_ALLOC_TEST';

FREE_M

---

SQL>
SQL> begin
  2 for i in 1..32 loop

  3        if i mod 2 = 0 then
  4         execute immediate 'drop table table'||i;
  5        end if;

  6 end loop;
  7 end;
  8 /
PL/SQL procedure successfully completed. SQL>
SQL> select sum(bytes)/1024/1024 free_mb from dba_free_space where tablespace_name='AUTO_ALLOC_TEST';

FREE_MB

---

15.5
SQL>
SQL> alter table table1 allocate extent; alter table table1 allocate extent
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TABLE1 by 128 in tablespace AUTO_ALLOC_TEST SQL>
SQL> drop tablespace auto_alloc_test including contents and datafiles; Tablespace dropped.
SQL>
SQL> spool off

On Fri, Mar 4, 2011 at 3:53 PM, Greg Rahn <greg_at_structureddata.org> wrote:

> I'd would recommend AUTOALLOCATE over UNIFORM unless you require very
> large extents (>64MB). If require larger extents for a segment, just
> use the INITIAL attribute.
>
> On Thu, Mar 3, 2011 at 10:42 AM, Mandal, Ashoke
> <ashoke.k.mandal_at_medtronic.com> wrote:
> > I would like find out your personal experience on using AUTOALLOACTE for
> > extent management in Oracle 10g. I usually use UNIFORM extent size but
> > wondering what are the drawbacks for using AUTOALLOACTE.
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 04 2011 - 11:54:06 CST