Number of extents


With the advent of locally managed tablespaces, the number of extents is allegedly somewhat of a dead issue. And to a large extent this is true. However, this is not a blessing to throw all objects into millions of extents. From a commonsense perspective, if many objects are extending with great frequency, it becomes very difficult to determine whether extensions are worthy of further investigation or not. But also, high frequency (de)allocation of extents can have an impact on the performance when it comes to user quotas

Example:

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:141.84    (141 seconds!!!)
SQL> drop table x.p;

Table dropped.

Elapsed: 00:01:96.68      (96 seconds!!!)
SQL> spool off

The cause of the delay can be seen in a trace file. Every time an extent is allocated, the TSQ$ table is updated (which controls user tablespace quota allocations).

Whilst I abhor the term "rule of thumb", I would recommend as a "rule of thumb" that extent allocation on growing segments should occur approximately once per month. This makes capacity planning all that much simpler.