More on extent allocation in LMT's

From: Niall Litchfield <niall.litchfield_at_btinternet.com>
Date: Sun, 10 Mar 2002 20:47:40 +0000 (UTC)
Message-ID: <a6ggpb$pcd$1@knossos.btinternet.com>

spooled below are the results of testing on 9i on windowsXP. Points to note.

1. datafiles have a limit of 32gb. This means (because i didn't do my maths) that the test finished a bit prematurely.
2. extents sizes show up as 64k ,1m,8m,64m. (there might obviously be higher values (256m maybe).
3. the aim of the test was to extend 5 tables more or less randomly to simulate real life load. The test could be extended to include allocating and deallocating extents and seeing if the distribution of size changed at all.

SQL> @c:\scripts\createtabs
SQL> drop table t0;

Table dropped.

SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

SQL> drop table t3;

Table dropped.

SQL> drop table t4;

Table dropped.

SQL> create table t0 (col1 varchar2(20)) tablespace test;

Table created.

SQL> create table t1 (col1 varchar2(20)) tablespace test;

Table created.

SQL> create table t2 (col1 varchar2(20)) tablespace test;

Table created.

SQL> create table t3 (col1 varchar2(20)) tablespace test;

Table created.

SQL> create table t4 (col1 varchar2(20)) tablespace test;

Table created.

SQL> @c:\scripts\extentallocation
SQL> declare
  2
  3 strSQL varchar2(255);
  4 n number;
  5
  6 begin
  7
  8 dbms_random.initialize(46579856);
  9
 10 for i in 1..5000 loop

 11        n := dbms_random.random;
 12        n := abs(mod(n,5));
 13        strSQL := 'alter table t'||n||' allocate extent';
 14        execute immediate strSQL;

 15 end loop;
 16 end;
 17 /
declare
*
ERROR at line 1:
ORA-01653: unable to extend table NIALL.T1 by 8192 in tablespace TEST ORA-06512: at line 14

SQL> @c:\scripts\extentsizes
  1 select SUBSTR(segment_name,1,5) TABLE_NAME,bytes/1024 extentsize,count(*)
  2 from dba_extents
  3 where segmenT_name in ('T0','T1','T2','T3','T4')   4* group by segment_name,bytes
SQL> / TABLE EXTENTSIZE COUNT(*)
----- ---------- ----------

T0            64         16
T0          1024         63
T0          8192        120
T0         57344          1
T0         65536         58
T1            64         16
T1          1024         63
T1          8192        121
T1         65536        104
T2            64         16
T2          1024         63

TABLE EXTENTSIZE COUNT(*)
----- ---------- ----------

T2          8192        120
T2         65536         72
T3            64         16
T3          1024         63
T3          8192        120
T3         65536        116
T4            64         16
T4          1024         63
T4          8192        120
T4         65536         80

21 rows selected.

SQL> spoo off

--
Niall Litchfield
Oracle DBA
Audit Commission UK

Received on Sun Mar 10 2002 - 14:47:40 CST