As we all know, Oracle allocates storage space logically using blocks, extents(contiguous block space), segments for tables, indexes, partitions, subpartitions in tablespace consisting of one of more physical datafiles.
Oracle 9i introduced LOCALLY MANAGED TABLESPACE, prior to that Oracle was using DICTIONARY MANAGED TABLESPACE. With introduction of LOCALLY MANAGED TABLESPACE , AUTOMATIC SEGMENT SPACE MANAGEMENT(ASSM) was also introduced, which removes the need for managing freelists and freelist groups by using bitmaps to describe the space usage of each block is within a segment.
Let us first of all understand how Oracle performs extent allocation for normal/non-partitioned tables.
Below is the sample script we generally use to create locally managed ASSM tablespace.
CREATE TABLESPACE DATA
DATAFILE '+DATA001/PGTEST/DATAFILE/ipydata01.dbf' SIZE 1400M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
Now, we will create non-partitioned table without storage clause and will insert data from dba_objects dictionary table to populate data.
SQL> set line 500 echo on
SQL>
SQL> drop table t_obj;
Table dropped.
SQL>
SQL>
SQL> CREATE TABLE "T_OBJ"
2 ("OWNER" VARCHAR2(128 BYTE),
3 "OBJECT_NAME" VARCHAR2(128 BYTE),
4 "SUBOBJECT_NAME" VARCHAR2(128 BYTE),
5 "OBJECT_ID" NUMBER,
6 "DATA_OBJECT_ID" NUMBER,
7 "OBJECT_TYPE" VARCHAR2(23 BYTE),
8 "CREATED" DATE,
9 "LAST_DDL_TIME" DATE,
10 "TIMESTAMP" VARCHAR2(19 BYTE),
11 "STATUS" VARCHAR2(7 BYTE),
12 "TEMPORARY" VARCHAR2(1 BYTE),
13 "GENERATED" VARCHAR2(1 BYTE),
14 "SECONDARY" VARCHAR2(1 BYTE),
15 "NAMESPACE" NUMBER,
16 "EDITION_NAME" VARCHAR2(128 BYTE),
17 "SHARING" VARCHAR2(18 BYTE),
18 "EDITIONABLE" VARCHAR2(1 BYTE),
19 "ORACLE_MAINTAINED" VARCHAR2(1 BYTE),
20 "APPLICATION" VARCHAR2(1 BYTE),
21 "DEFAULT_COLLATION" VARCHAR2(100 BYTE),
22 "DUPLICATED" VARCHAR2(1 BYTE),
23 "SHARDED" VARCHAR2(1 BYTE),
24 "CREATED_APPID" NUMBER,
25 "CREATED_VSNID" NUMBER,
26 "MODIFIED_APPID" NUMBER,
27 "MODIFIED_VSNID" NUMBER
28 ) SEGMENT CREATION IMMEDIATE
29 PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
30 NOCOMPRESS LOGGING
31 TABLESPACE "DATA" ;
Table created.
SQL>
SQL>
SQL> insert into t_obj (select * from dba_objects);
51684 rows created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> set line 200 pagesize 50
SQL> col owner for a10
SQL> col segment_name for a20
SQL> col partition_name for a25
SQL> col segment_type for a25
SQL> col tablespace_name for a15
SQL> col extent_id for 99
SQL> col file_id for 9999
SQL> col block_id for 9999999999
SQL> col bytes for 9999999999
SQL> col blocks for 99999
SQL>
SQL> select owner,segment_name,partition_name,segment_type,tablespace_name,extent_id,file_id,block_id,bytes,blocks
2 from dba_extents where owner='ADMIN' and segment_name='T_OBJ';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ------------- ----------------- --------------- --------------- --------- ------- ----------- ----------- ------
ADMIN T_OBJ TABLE DATA 0 4619 828416 65536 8
ADMIN T_OBJ TABLE DATA 1 4619 828424 65536 8
ADMIN T_OBJ TABLE DATA 2 4619 828432 65536 8
ADMIN T_OBJ TABLE DATA 3 4619 828440 65536 8
ADMIN T_OBJ TABLE DATA 4 4619 828448 65536 8
ADMIN T_OBJ TABLE DATA 5 4619 828456 65536 8
ADMIN T_OBJ TABLE DATA 6 4619 828464 65536 8
ADMIN T_OBJ TABLE DATA 7 4619 828472 65536 8
ADMIN T_OBJ TABLE DATA 8 4619 828480 65536 8
ADMIN T_OBJ TABLE DATA 9 4619 828488 65536 8
ADMIN T_OBJ TABLE DATA 10 4619 828496 65536 8
ADMIN T_OBJ TABLE DATA 11 4619 828504 65536 8
ADMIN T_OBJ TABLE DATA 12 4619 828512 65536 8
ADMIN T_OBJ TABLE DATA 13 4619 828520 65536 8
ADMIN T_OBJ TABLE DATA 14 4619 828528 65536 8
ADMIN T_OBJ TABLE DATA 15 4619 828536 65536 8
ADMIN T_OBJ TABLE DATA 16 4619 828544 1048576 128
ADMIN T_OBJ TABLE DATA 17 4619 828672 1048576 128
ADMIN T_OBJ TABLE DATA 18 4619 828800 1048576 128
ADMIN T_OBJ TABLE DATA 19 4619 828928 1048576 128
ADMIN T_OBJ TABLE DATA 20 4619 829056 1048576 128
ADMIN T_OBJ TABLE DATA 21 4619 829184 1048576 128
ADMIN T_OBJ TABLE DATA 22 4619 829312 1048576 128
ADMIN T_OBJ TABLE DATA 23 4619 829440 1048576 128
24 rows selected.
SQL>
In the above output, If you observe carefully under extent_id, bytes and blocks columna, you will see that there are first 16 extents allocated for this segment with size 64kb (65536 bytes) (8 blocksx8kb) from the 17th extent extent size is increased to 1MB (128 blocks* 8kb), please remember this database created with default block size of 8kb.
Remember when we created tablespace DATA in the beginning with EXTENT MANAGEMENT LOCAL and AUTOALLOCATE, below extent allocation behavior is because of the AUTOALLOCATE and please note that this behavior is not disclosed in oracle document and can change at any time, however since 9i to 19c this behavior is the evident:
- First 16 extents: 64 KB (1
MB in total).
- Next 63 extents: 1 MB (total
space is 16 MB).
- Next 126 extents: 8 MB
(until total space 1 GB).
- Then extent size 64 MB is
chosen.
and so on.
Extent allocation with AUTOALLOCATE in LMT may work best for most of the non-partitioned table however it may become little more complicated when used with partitioned tables, especially with multiple subpartitions. We will discuss about this in PART-2-Oracle Extent Allocation Mystery and wasted/fragmented space in partitioned table
.
No comments:
Post a Comment