Search My Oracle Blog

Custom Search

15 July, 2009

Sizing OR Growing a Table in AUTOALLOCATE

The recommended extent management clauses in Tablespaces are :
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
EXTENT MANAGEMENT LOCAL UNIFORM SIZE xM; 

The second method allows you to specify *fixed* (ie, "uniform") extent sizes for all segments in the tablespace. However, I prefer the former as it generally  (**) uses Extent Sizes of 64K, 1MB, 8MB and 64MB as a table/index segment grows. This allows you to "mix" small tables/indexes with larger ones in the same tablespace.
(** : for exceptions to the "generally" rule see Examples Of Odd Extent Sizes In Tablespaces With AUTOALLOCATE )

Here are some simple methods of using AUTOALLOCATE to "create" or "grow" a *larger* table, not relying on very many 64KB extents.

First, I create the Tablespace :
SQL> create tablespace test_autoallocate
2  datafile '/oracle_fs/Databases/ORT24FS/test_autoallocate.dbf' size 1000M autoextend on next 100M maxsize 4000M
3  extent management local autoallocate;

Tablespace created.

SQL> 

The first table I demonstrate is automatically created with a single 64KB extent :
SQL> create table first_table (column_1 varchar2(5)) tablespace test_autoallocate;

Table created.

SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'FIRST_TABLE' order by extent_id;

EXTENT_ID BYTES/1024
---------- ----------
      0         64

SQL> 

For the second table, Oracle pre-allocates 16 extents of 64KB, precisely because I explicitly specified a storage clause demanding a 1MB allocation :
SQL> create table second_table (column_1 varchar2(5)) tablespace test_autoallocate storage (initial 1M);

Table created.

SQL>  select extent_id, bytes/1024 from user_extents where segment_name = 'SECOND_TABLE' order by extent_id;

EXTENT_ID BYTES/1024
---------- ----------
        0         64
        1         64
        2         64
        3         64
        4         64
        5         64
        6         64
        7         64
        8         64
        9         64
       10         64
       11         64
       12         64
       13         64
       14         64
       15         64

16 rows selected.

SQL> 

In this third table, I explicitly requested 400MB allocation, so Oracle skipped from 64KB extents to 8MB extents, giving me 50 such extents :
SQL> create table third_table (column_1 varchar2(5)) tablespace test_autoallocate storage (initial 100M next 100M minextents 4);

Table created.

SQL> select bytes/1024, count(*) from user_extents where segment_name = 'THIRD_TABLE' group by bytes/1024 order by 1;

BYTES/1024   COUNT(*)
---------- ----------
      8192         50

SQL> 

In this final example, I *grow* a table from a single 64KB extent to approximately 104MB, comprising of 1MB and 8MB extents : (Since the extent size of the last extent is already 8MB, Oracle leaves it at 8MB and does not truncate it down).
SQL> create table grow_this_table (column_1 varchar2(5)) tablespace test_autoallocate;

Table created.

SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'GROW_THIS_TABLE' order by extent_id;

 EXTENT_ID BYTES/1024
---------- ----------
         0         64

SQL> alter table grow_this_table allocate extent (size 100M);

Table altered.

SQL> select bytes/1024, count(*) from user_extents where segment_name = 'GROW_THIS_TABLE' group by bytes/1024 order by 1;

BYTES/1024   COUNT(*)
---------- ----------
        64          1
      1024         56
      8192          6

SQL> 


Thus, you can see that it is very easy to use the AUTOALLOCATE rules to "pre-create" or "grow" a table very large. You don't really need to create multiple tablespaces of different Uniform sizes (as some do with 3 or 4 differently Uniformed sizes of 10MB, 50MB, 100MB, 400MB etc). A simple AUTOALLOCATE policy would suffice.
.
.
.

3 comments:

B. Polarski said...

-Very easy, but this is also plenty of management for little benefits.

-Auto allocate on ASSM when dealing with blob and high concurrency is very bad performances. you will get plenty 'HW enqueue', more than you ever wanted. It is said to be fixed in 11.1.0.6 could not yet check.

Hemant K Chitale said...

I don't confuse AutoAllocate and ASSM. I'm not one for recommending ASSM at all. See http://hemantoracledba.blogspot.com/2008/08/assm-or-mssm-impact-on-inserts.html or the occassions when I've mentioned it on forums.oracle.com

Enrique said...

Hi,
Great explanation, finally I figure out a little better how extents and storage management works at tablespace level... I was thinking I'm a junior dba, but I realize that I'm just a padawan dba.

Thanks a lot, I will follow this blog closer.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com