MSSM & ASSM (Manual & Automatic Segment Space Management)
---------------------------------------------------------
MSSM
----
a) The free space are maintained by the freelist.
b) Can define the PCTFREE AND PCTUSED parameter.
ASSM
----
a) The free space are mainitained by the bitmaps
b) Can define only the pctfree parameter.
Data blcok format.
A data block will contain the below divisions
a) Header
- The header contains general block information, such as the block address and the type of segment (for example, data or index).
b) Table directory
- This portion of the data block contains information about the table having rows in this block.
c) Row directory
- This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).
the above 3 are collectively called as block overhead.
d) Free Space
- Free space is allocated for insertion of new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a nonnull value).
In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries.
A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block.
The space required for transaction entries is operating system dependent; however, transaction entries in most operating systems require approximately 23 bytes.
e) Row data
- This portion of the data block contains table or index data. Rows can span blocks.
Eg:-
SQL> create tablespace TEST_ASSM datafile '/oracle/oradata/NTESTSRV/test_assm01.dbf' size 500m extent management local autoallocate segment space management auto;
Tablespace created.
SQL> create tablespace TEST_MSSM datafile '/oracle/oradata/NTESTSRV/test_mssm01.dbf' size 500m extent management local autoallocate segment space management manual;
Tablespace created.
SQL> alter user test_user1 quota unlimited on TEST_ASSM;
User altered.
SQL> alter user test_user1 quota unlimited on TEST_MSSM;
User altered.
SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name in ('TEST_ASSM','TEST_MSSM');
TABLESPACE_NAME SEGMEN
------------------------- ------
TEST_ASSM AUTO
TEST_MSSM MANUAL
SQL> CONN TEST_USER1
SQL> CREATE TABLE "TEST_USER1"."TEST_MSSM_TBL"
( "EMPNO" NUMBER,
"ENAME" VARCHAR2(30),
"EAGE" NUMBER,
"GENDER" VARCHAR2(6),
"DEPT" VARCHAR2(30)
) PCTFREE 10 PCTUSED 50 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TEST_MSSM"
/
Table created.
SQL> CREATE TABLE "TEST_USER1"."TEST_ASSM_TBL"
( "EMPNO" NUMBER,
"ENAME" VARCHAR2(30),
"EAGE" NUMBER,
"GENDER" VARCHAR2(6),
"DEPT" VARCHAR2(30)
) PCTFREE 10 PCTUSED 50 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TEST_ASSM"
/
Table created.
-- In the above example we have created two tables, one table has been stored in the tablespace which used ASSM and another
on the tablespace which user MSSM.
We have specified the same value for block level and extent levels.
Let see the below data.
SQL> select table_name, PCT_FREE,PCT_USED, INITIAL_EXTENT, FREELISTS , tablespace_name from dba_tables where table_name in ('TEST_MSSM_TBL','TEST_ASSM_TBL');
TABLE_NAME PCT_FREE PCT_USED INITIAL_EXTENT FREELISTS TABLESPACE_NAME
------------------------------ ---------- ---------- -------------- ---------- -------------------------
TEST_ASSM_TBL 10 65536 TEST_ASSM
TEST_MSSM_TBL 10 50 65536 1 TEST_MSSM
As we can see from the above output the PCT_USED and FREELIST values are blank for TEST_ASSM_TBL i.e. the table which been stored on the
tablespace which uses ASSM.
MSSM
----
From the above we can say that the MSSM used the freelists to hold/manage the free blocks which can be used for the furture inserts, also
the PCT_USED parameter is used to define the percent of data that can hold in a block. Here we have mentioned as 50% which means
the block will be listed in the freelist when the data in that particular block is less than 50%. When it reaches the 50% the block address
will be removed from the freelist and this block will not be further used for the inserts. If the percent of data becomes less than
50% because of the deletes then again the block address will be added/listed in the freelist and will be used by the future inserts.
ASSM
---
The segments which are managed by automatic segmenet space management will be using the bitmaps to hold the details of available blocks
instead of freelists, also you cannot alter the pct_used value which means the data can grow upto the pct which is 100-pct_free,
which includes overheads and rowdata. Thus the block level fragmentation on these segments are highly avoided when you use the ASSM.
PCT_FREE - The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block.
in the above example we have specified 10 for the PCT_FREE parameter which means 10% on each blcok will be reserved for the furture updates.
PCT_USED - The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block.
After a data block is filled to the limit determined by PCTUSED, Oracle Database considers the block unavailable for the insertion of new rows until the
percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle Database uses the free space of the data block only for
updates to rows already contained in the data block.
Row Chaining and Migrating
In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit
into one data block when it is first inserted. In this case, Oracle Database stores the data for the row in a chain of data blocks (one or more)
reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining
in these cases is unavoidable.
However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the
block's free space is already completely filled. In this case, Oracle Database migrates the data for the entire row to a new data block, assuming
the entire row can fit in a new block. Oracle Database preserves the original row piece of a migrated row to point to the new block containing
the migrated row. The rowid of a migrated row does not change.
When a row is chained or migrated, I/O performance associated with this row decreases because Oracle Database must scan more than one data block to
retrieve the information for the row.
Lets continue the example..
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1;
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_MSSM_TBL 0 9 8
Here we can say the table has been allocated with 1 extent and it has 8 blocks.
Lets alter the table to hold more than 8k of data in one row..
SQL> alter table test_assm_tbl add (details varchar2(4000), full_details varchar2(4000));
Table altered.
SQL> alter table test_mssm_tbl add (details varchar2(4000), full_details varchar2(4000));
Table altered.
SQL> desc test_assm_tbl;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPNO NUMBER
ENAME VARCHAR2(30)
EAGE NUMBER
GENDER VARCHAR2(6)
DEPT VARCHAR2(30)
DETAILS VARCHAR2(4000)
FULL_DETAILS VARCHAR2(4000)
SQL> desc test_mssm_tbl;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPNO NUMBER
ENAME VARCHAR2(30)
EAGE NUMBER
GENDER VARCHAR2(6)
DEPT VARCHAR2(30)
DETAILS VARCHAR2(4000)
FULL_DETAILS VARCHAR2(4000)
-- Now we have added to more columns with varchar2(4000).
Lets insert some values.
SQL> begin
for i in 1..1000 loop
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',4000));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',4000));
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
-- Now we have inserted 1000 rows in each table.. lets see the block utilization for both the tables.
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_ASSM_TBL 2 25 8
TEST_ASSM_TBL 3 33 8
TEST_ASSM_TBL 4 41 8
TEST_ASSM_TBL 5 49 8
TEST_ASSM_TBL 6 57 8
TEST_ASSM_TBL 7 65 8
TEST_ASSM_TBL 8 73 8
TEST_ASSM_TBL 9 81 8
TEST_ASSM_TBL 10 89 8
TEST_ASSM_TBL 11 97 8
TEST_ASSM_TBL 12 105 8
TEST_ASSM_TBL 13 113 8
TEST_ASSM_TBL 14 121 8
TEST_ASSM_TBL 15 129 8
TEST_ASSM_TBL 16 137 128
TEST_ASSM_TBL 17 265 128
TEST_ASSM_TBL 18 393 128
TEST_ASSM_TBL 19 521 128
TEST_ASSM_TBL 20 649 128
TEST_ASSM_TBL 21 777 128
TEST_ASSM_TBL 22 905 128
TEST_ASSM_TBL 23 1033 128
TEST_ASSM_TBL 24 1161 128
TEST_ASSM_TBL 25 1289 128
TEST_ASSM_TBL 26 1417 128
TEST_ASSM_TBL 27 1545 128
TEST_ASSM_TBL 28 1673 128
TEST_ASSM_TBL 29 1801 128
TEST_ASSM_TBL 30 1929 128
TEST_MSSM_TBL 0 9 8
TEST_MSSM_TBL 1 17 8
TEST_MSSM_TBL 2 25 8
TEST_MSSM_TBL 3 33 8
TEST_MSSM_TBL 4 41 8
TEST_MSSM_TBL 5 49 8
TEST_MSSM_TBL 6 57 8
TEST_MSSM_TBL 7 65 8
TEST_MSSM_TBL 8 73 8
TEST_MSSM_TBL 9 81 8
TEST_MSSM_TBL 10 89 8
TEST_MSSM_TBL 11 97 8
TEST_MSSM_TBL 12 105 8
TEST_MSSM_TBL 13 113 8
TEST_MSSM_TBL 14 121 8
TEST_MSSM_TBL 15 129 8
TEST_MSSM_TBL 16 137 128
TEST_MSSM_TBL 17 265 128
TEST_MSSM_TBL 18 393 128
TEST_MSSM_TBL 19 521 128
TEST_MSSM_TBL 20 649 128
TEST_MSSM_TBL 21 777 128
TEST_MSSM_TBL 22 905 128
TEST_MSSM_TBL 23 1033 128
TEST_MSSM_TBL 24 1161 128
TEST_MSSM_TBL 25 1289 128
TEST_MSSM_TBL 26 1417 128
TEST_MSSM_TBL 27 1545 128
TEST_MSSM_TBL 28 1673 128
TEST_MSSM_TBL 29 1801 128
TEST_MSSM_TBL 30 1929 128
-- Around 30 extents has been used, also since the tablespace extement managment is local and auto allocate the size of the
extents been automatically dedicded by oracle as we can see from the extent id 16 the no. of blocks shows 128.
SQL> select segment_name, bytes/1024/1024 "MB" from dba_segments where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL');
SEGMENT_NAME MB
--------------------------------------------------------------------------------- ----------
TEST_ASSM_TBL 16
TEST_MSSM_TBL 16
The current size of each segment is 16 MB.
SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_ASSM_TBL');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_MSSM_TBL');
PL/SQL procedure successfully completed.
SQL> select table_name, avg_row_len/1024 "K", (avg_row_len*num_rows)/1024/1024 "M" from dba_tables where table_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL');
TABLE_NAME K M
------------------------------ ---------- ----------
TEST_ASSM_TBL 7.85546875 7.6713562
TEST_MSSM_TBL 7.85546875 7.6713562
The avg size of 1 record is 7.8 KB, and actual space required to store 1000 records of size 7.6 KB rows is 7.67 MB, but the
segment size shows 16 MB, which is more than 50% , why is that so?
As discussed earlier, in each block 10% space will be allocated for free space. Lets calculate..
SQL> select segment_name, sum(blocks) "Total Blocks", sum(blocks)*8192/1024/1024 "Blocks Size in MB" from dba_extents where segment_name
in ('TEST_ASSM_TBL','TEST_MSSM_TBL') group by segment_name;
SEGMENT_NAME Total Blocks Blocks Size in MB
--------------------------------------------------------------------------------- ------------ -----------------
TEST_MSSM_TBL 2048 16
TEST_ASSM_TBL 2048 16
So 10% on 16MB is 1.6 MB, so 7.67 MB + 1.6 MB = 9.27 MB,
16 mb - 9.27 mb = 6.73
Still we could see 6.73 mb of high space usage. Lets find out..
Ok..
Lets see how many empty blocks are there...
the ROWID_BLOCK_NUMBER function is used to return just the block number in the table.
Lets display the data for the first 10 rows.
SQL> select rowid, dbms_rowid.rowid_block_number(rowid) from test_assm_tbl where empno <= 10;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAN3vAAHAAAAAOAAA 14
AAAN3vAAHAAAAAQAAA 16
AAAN3vAAHAAAAARAAA 17
AAAN3vAAHAAAAASAAA 18
AAAN3vAAHAAAAAVAAA 21
AAAN3vAAHAAAAAXAAA 23
AAAN3vAAHAAAAAbAAA 27
AAAN3vAAHAAAAAcAAA 28
AAAN3vAAHAAAAAeAAA 30
AAAN3vAAHAAAAAgAAA 32
10 rows selected.
SQL> select rowid, dbms_rowid.rowid_block_number(rowid) from test_mssm_tbl where empno <= 10;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAN3wAAIAAAAALAAA 11
AAAN3wAAIAAAAANAAA 13
AAAN3wAAIAAAAAPAAA 15
AAAN3wAAIAAAAARAAA 17
AAAN3wAAIAAAAATAAA 19
AAAN3wAAIAAAAAVAAA 21
AAAN3wAAIAAAAAXAAA 23
AAAN3wAAIAAAAAZAAA 25
AAAN3wAAIAAAAAbAAA 27
AAAN3wAAIAAAAAdAAA 29
10 rows selected.
As from the above we could see one row is placed in two blocks...
eg .. the rowid AAAN3wAAIAAAAALAAA shows the block number as 11, the subsequence rowid shows as 13,
which means the data of rowid AAAN3wAAIAAAAALAAA is not enough to fit in to one blocks and been
placed into two blocks.
The size of one row is 7.85 kb which cannot be fit in to one block hence oracle used two blocks to store 1 rec.
Which is the chanined rows.. lets see..
SQL > create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
SQL> analyze table test_assm_tbl list chained rows;
Table analyzed.
SQL> analyze table test_mssm_tbl list chained rows;
Table analyzed.
SQL> select table_name, count(*) from chained_rows group by table_name;
TABLE_NAME COUNT(*)
------------------------------ ----------
TEST_MSSM_TBL 1000
TEST_ASSM_TBL 1000
--Yes...as we could see it is beacuse of the chanied rows, this is mainly becuase of the size of 1 row which is 7.85 kb,
-- lets delete all the rows now..
SQL> delete from TEST_MSSM_TBL ;
1000 rows deleted.
SQL> delete from TEST_ASSM_TBL;
1000 rows deleted.
SQL> commit;
Commit complete.
-- Here we have delete all the rows now the freelists will be updated for MSSM and bitmaps been upated for ASSM.
Lets insert data which can fit in 1 block.
SQL> begin
for i in 1..1000 loop
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',3000));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',3000));
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_ASSM_TBL');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_MSSM_TBL');
PL/SQL procedure successfully completed.
SQL> select table_name, avg_row_len/1024 "K", (avg_row_len*num_rows)/1024/1024 "M" from dba_tables where table_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL');
TABLE_NAME K M
------------------------------ ---------- ----------
TEST_ASSM_TBL 6.87890625 6.71768188
TEST_MSSM_TBL 6.87890625 6.71768188
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_ASSM_TBL 2 25 8
TEST_ASSM_TBL 3 33 8
TEST_ASSM_TBL 4 41 8
TEST_ASSM_TBL 5 49 8
TEST_ASSM_TBL 6 57 8
TEST_ASSM_TBL 7 65 8
TEST_ASSM_TBL 8 73 8
TEST_ASSM_TBL 9 81 8
TEST_ASSM_TBL 10 89 8
TEST_ASSM_TBL 11 97 8
TEST_ASSM_TBL 12 105 8
TEST_ASSM_TBL 13 113 8
TEST_ASSM_TBL 14 121 8
TEST_ASSM_TBL 15 129 8
TEST_ASSM_TBL 16 137 128
TEST_ASSM_TBL 17 265 128
TEST_ASSM_TBL 18 393 128
TEST_ASSM_TBL 19 521 128
TEST_ASSM_TBL 20 649 128
TEST_ASSM_TBL 21 777 128
TEST_ASSM_TBL 22 905 128
TEST_ASSM_TBL 23 1033 128
TEST_ASSM_TBL 24 1161 128
TEST_ASSM_TBL 25 1289 128
TEST_ASSM_TBL 26 1417 128
TEST_ASSM_TBL 27 1545 128
TEST_ASSM_TBL 28 1673 128
TEST_ASSM_TBL 29 1801 128
TEST_ASSM_TBL 30 1929 128
TEST_MSSM_TBL 0 9 8
TEST_MSSM_TBL 1 17 8
TEST_MSSM_TBL 2 25 8
TEST_MSSM_TBL 3 33 8
TEST_MSSM_TBL 4 41 8
TEST_MSSM_TBL 5 49 8
TEST_MSSM_TBL 6 57 8
TEST_MSSM_TBL 7 65 8
TEST_MSSM_TBL 8 73 8
TEST_MSSM_TBL 9 81 8
TEST_MSSM_TBL 10 89 8
TEST_MSSM_TBL 11 97 8
TEST_MSSM_TBL 12 105 8
TEST_MSSM_TBL 13 113 8
TEST_MSSM_TBL 14 121 8
TEST_MSSM_TBL 15 129 8
TEST_MSSM_TBL 16 137 128
TEST_MSSM_TBL 17 265 128
TEST_MSSM_TBL 18 393 128
TEST_MSSM_TBL 19 521 128
TEST_MSSM_TBL 20 649 128
TEST_MSSM_TBL 21 777 128
TEST_MSSM_TBL 22 905 128
TEST_MSSM_TBL 23 1033 128
TEST_MSSM_TBL 24 1161 128
TEST_MSSM_TBL 25 1289 128
TEST_MSSM_TBL 26 1417 128
TEST_MSSM_TBL 27 1545 128
TEST_MSSM_TBL 28 1673 128
TEST_MSSM_TBL 29 1801 128
TEST_MSSM_TBL 30 1929 128
62 rows selected.
-- so no new extents has been allocated, all the space reclaimed by the delete has been used.
SQL> truncate table chained_rows;
Table truncated.
SQL> analyze table test_assm_tbl list chained rows;
Table analyzed.
SQL> analyze table test_mssm_tbl list chained rows;
Table analyzed.
SQL> select table_name, count(*) from chained_rows group by table_name;
no rows selected
-- Now we could see there are no any chained rows as the once record is fit into one block.
Lets see the use of assm..
SQL> truncate table TEST_ASSM_TBL;
Table truncated.
SQL> truncate table TEST_MSSM_TBL;
Table truncated.
begin
for i in 10..60 loop
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
end loop;
commit;
end;
/
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_MSSM_TBL 0 9 8
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_assm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
12 8
13 8
14 8
15 8
16 8
17 8
20 3
7 rows selected.
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_mssm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
10 8
11 8
12 8
13 8
14 8
15 8
16 3
-- Around 8 records are stored in one block. Let delete 3 row from each block.
-- Now could see around 2 rows are stored in 1 block. Lets try to delete one rec from each block.
begin
for i in 10..60 loop
if i mod 3 = 0 then
delete from test_assm_tbl where empno = i;
delete from test_mssm_tbl where empno = i;
end if;
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_assm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
12 5
13 6
14 5
15 5
16 6
17 5
20 2
7 rows selected.
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_mssm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
10 6
11 5
12 5
13 6
14 5
15 5
16 2
7 rows selected.
Now the blocks will be fragmented becuase of the delete operation. Lets add some data.
begin
for i in 10..60 loop
if i mod 3 = 0 then
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
end if;
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_MSSM_TBL 0 9 8
TEST_MSSM_TBL 1 17 8
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_assm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
12 5
13 6
14 5
15 8
16 8
17 8
18 8
19 1
20 2
9 rows selected.
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_mssm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
10 6
11 5
12 5
13 6
14 5
15 5
16 8
17 8
18 3
9 rows selected.
-- As we can see from the above in test_assm_tbl (The segment managed by ASSM) the new rows inserted in the old blocks i.e. even if we have
some space in the block it will used by the inserts.
But for the table test_mssm_tbl (Manual Segment space Management) the new rows are not inserted in the old blocks instead those are inserted
in the new blocks, it is because we have specified by pct_used parameter to 50, since already the block is holding some value which is
greater than the 50 % hence these blocks are removed from the freelists. Thus ASSM help in avoiding the fragmention.
---------------------------------------------------------
MSSM
----
a) The free space are maintained by the freelist.
b) Can define the PCTFREE AND PCTUSED parameter.
ASSM
----
a) The free space are mainitained by the bitmaps
b) Can define only the pctfree parameter.
Data blcok format.
A data block will contain the below divisions
a) Header
- The header contains general block information, such as the block address and the type of segment (for example, data or index).
b) Table directory
- This portion of the data block contains information about the table having rows in this block.
c) Row directory
- This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).
the above 3 are collectively called as block overhead.
d) Free Space
- Free space is allocated for insertion of new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a nonnull value).
In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries.
A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block.
The space required for transaction entries is operating system dependent; however, transaction entries in most operating systems require approximately 23 bytes.
e) Row data
- This portion of the data block contains table or index data. Rows can span blocks.
Eg:-
SQL> create tablespace TEST_ASSM datafile '/oracle/oradata/NTESTSRV/test_assm01.dbf' size 500m extent management local autoallocate segment space management auto;
Tablespace created.
SQL> create tablespace TEST_MSSM datafile '/oracle/oradata/NTESTSRV/test_mssm01.dbf' size 500m extent management local autoallocate segment space management manual;
Tablespace created.
SQL> alter user test_user1 quota unlimited on TEST_ASSM;
User altered.
SQL> alter user test_user1 quota unlimited on TEST_MSSM;
User altered.
SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name in ('TEST_ASSM','TEST_MSSM');
TABLESPACE_NAME SEGMEN
------------------------- ------
TEST_ASSM AUTO
TEST_MSSM MANUAL
SQL> CONN TEST_USER1
SQL> CREATE TABLE "TEST_USER1"."TEST_MSSM_TBL"
( "EMPNO" NUMBER,
"ENAME" VARCHAR2(30),
"EAGE" NUMBER,
"GENDER" VARCHAR2(6),
"DEPT" VARCHAR2(30)
) PCTFREE 10 PCTUSED 50 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TEST_MSSM"
/
Table created.
SQL> CREATE TABLE "TEST_USER1"."TEST_ASSM_TBL"
( "EMPNO" NUMBER,
"ENAME" VARCHAR2(30),
"EAGE" NUMBER,
"GENDER" VARCHAR2(6),
"DEPT" VARCHAR2(30)
) PCTFREE 10 PCTUSED 50 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TEST_ASSM"
/
Table created.
-- In the above example we have created two tables, one table has been stored in the tablespace which used ASSM and another
on the tablespace which user MSSM.
We have specified the same value for block level and extent levels.
Let see the below data.
SQL> select table_name, PCT_FREE,PCT_USED, INITIAL_EXTENT, FREELISTS , tablespace_name from dba_tables where table_name in ('TEST_MSSM_TBL','TEST_ASSM_TBL');
TABLE_NAME PCT_FREE PCT_USED INITIAL_EXTENT FREELISTS TABLESPACE_NAME
------------------------------ ---------- ---------- -------------- ---------- -------------------------
TEST_ASSM_TBL 10 65536 TEST_ASSM
TEST_MSSM_TBL 10 50 65536 1 TEST_MSSM
As we can see from the above output the PCT_USED and FREELIST values are blank for TEST_ASSM_TBL i.e. the table which been stored on the
tablespace which uses ASSM.
MSSM
----
From the above we can say that the MSSM used the freelists to hold/manage the free blocks which can be used for the furture inserts, also
the PCT_USED parameter is used to define the percent of data that can hold in a block. Here we have mentioned as 50% which means
the block will be listed in the freelist when the data in that particular block is less than 50%. When it reaches the 50% the block address
will be removed from the freelist and this block will not be further used for the inserts. If the percent of data becomes less than
50% because of the deletes then again the block address will be added/listed in the freelist and will be used by the future inserts.
ASSM
---
The segments which are managed by automatic segmenet space management will be using the bitmaps to hold the details of available blocks
instead of freelists, also you cannot alter the pct_used value which means the data can grow upto the pct which is 100-pct_free,
which includes overheads and rowdata. Thus the block level fragmentation on these segments are highly avoided when you use the ASSM.
PCT_FREE - The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block.
in the above example we have specified 10 for the PCT_FREE parameter which means 10% on each blcok will be reserved for the furture updates.
PCT_USED - The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block.
After a data block is filled to the limit determined by PCTUSED, Oracle Database considers the block unavailable for the insertion of new rows until the
percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle Database uses the free space of the data block only for
updates to rows already contained in the data block.
Row Chaining and Migrating
In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit
into one data block when it is first inserted. In this case, Oracle Database stores the data for the row in a chain of data blocks (one or more)
reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining
in these cases is unavoidable.
However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the
block's free space is already completely filled. In this case, Oracle Database migrates the data for the entire row to a new data block, assuming
the entire row can fit in a new block. Oracle Database preserves the original row piece of a migrated row to point to the new block containing
the migrated row. The rowid of a migrated row does not change.
When a row is chained or migrated, I/O performance associated with this row decreases because Oracle Database must scan more than one data block to
retrieve the information for the row.
Lets continue the example..
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1;
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_MSSM_TBL 0 9 8
Here we can say the table has been allocated with 1 extent and it has 8 blocks.
Lets alter the table to hold more than 8k of data in one row..
SQL> alter table test_assm_tbl add (details varchar2(4000), full_details varchar2(4000));
Table altered.
SQL> alter table test_mssm_tbl add (details varchar2(4000), full_details varchar2(4000));
Table altered.
SQL> desc test_assm_tbl;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPNO NUMBER
ENAME VARCHAR2(30)
EAGE NUMBER
GENDER VARCHAR2(6)
DEPT VARCHAR2(30)
DETAILS VARCHAR2(4000)
FULL_DETAILS VARCHAR2(4000)
SQL> desc test_mssm_tbl;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPNO NUMBER
ENAME VARCHAR2(30)
EAGE NUMBER
GENDER VARCHAR2(6)
DEPT VARCHAR2(30)
DETAILS VARCHAR2(4000)
FULL_DETAILS VARCHAR2(4000)
-- Now we have added to more columns with varchar2(4000).
Lets insert some values.
SQL> begin
for i in 1..1000 loop
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',4000));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',4000));
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
-- Now we have inserted 1000 rows in each table.. lets see the block utilization for both the tables.
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_ASSM_TBL 2 25 8
TEST_ASSM_TBL 3 33 8
TEST_ASSM_TBL 4 41 8
TEST_ASSM_TBL 5 49 8
TEST_ASSM_TBL 6 57 8
TEST_ASSM_TBL 7 65 8
TEST_ASSM_TBL 8 73 8
TEST_ASSM_TBL 9 81 8
TEST_ASSM_TBL 10 89 8
TEST_ASSM_TBL 11 97 8
TEST_ASSM_TBL 12 105 8
TEST_ASSM_TBL 13 113 8
TEST_ASSM_TBL 14 121 8
TEST_ASSM_TBL 15 129 8
TEST_ASSM_TBL 16 137 128
TEST_ASSM_TBL 17 265 128
TEST_ASSM_TBL 18 393 128
TEST_ASSM_TBL 19 521 128
TEST_ASSM_TBL 20 649 128
TEST_ASSM_TBL 21 777 128
TEST_ASSM_TBL 22 905 128
TEST_ASSM_TBL 23 1033 128
TEST_ASSM_TBL 24 1161 128
TEST_ASSM_TBL 25 1289 128
TEST_ASSM_TBL 26 1417 128
TEST_ASSM_TBL 27 1545 128
TEST_ASSM_TBL 28 1673 128
TEST_ASSM_TBL 29 1801 128
TEST_ASSM_TBL 30 1929 128
TEST_MSSM_TBL 0 9 8
TEST_MSSM_TBL 1 17 8
TEST_MSSM_TBL 2 25 8
TEST_MSSM_TBL 3 33 8
TEST_MSSM_TBL 4 41 8
TEST_MSSM_TBL 5 49 8
TEST_MSSM_TBL 6 57 8
TEST_MSSM_TBL 7 65 8
TEST_MSSM_TBL 8 73 8
TEST_MSSM_TBL 9 81 8
TEST_MSSM_TBL 10 89 8
TEST_MSSM_TBL 11 97 8
TEST_MSSM_TBL 12 105 8
TEST_MSSM_TBL 13 113 8
TEST_MSSM_TBL 14 121 8
TEST_MSSM_TBL 15 129 8
TEST_MSSM_TBL 16 137 128
TEST_MSSM_TBL 17 265 128
TEST_MSSM_TBL 18 393 128
TEST_MSSM_TBL 19 521 128
TEST_MSSM_TBL 20 649 128
TEST_MSSM_TBL 21 777 128
TEST_MSSM_TBL 22 905 128
TEST_MSSM_TBL 23 1033 128
TEST_MSSM_TBL 24 1161 128
TEST_MSSM_TBL 25 1289 128
TEST_MSSM_TBL 26 1417 128
TEST_MSSM_TBL 27 1545 128
TEST_MSSM_TBL 28 1673 128
TEST_MSSM_TBL 29 1801 128
TEST_MSSM_TBL 30 1929 128
-- Around 30 extents has been used, also since the tablespace extement managment is local and auto allocate the size of the
extents been automatically dedicded by oracle as we can see from the extent id 16 the no. of blocks shows 128.
SQL> select segment_name, bytes/1024/1024 "MB" from dba_segments where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL');
SEGMENT_NAME MB
--------------------------------------------------------------------------------- ----------
TEST_ASSM_TBL 16
TEST_MSSM_TBL 16
The current size of each segment is 16 MB.
SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_ASSM_TBL');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_MSSM_TBL');
PL/SQL procedure successfully completed.
SQL> select table_name, avg_row_len/1024 "K", (avg_row_len*num_rows)/1024/1024 "M" from dba_tables where table_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL');
TABLE_NAME K M
------------------------------ ---------- ----------
TEST_ASSM_TBL 7.85546875 7.6713562
TEST_MSSM_TBL 7.85546875 7.6713562
The avg size of 1 record is 7.8 KB, and actual space required to store 1000 records of size 7.6 KB rows is 7.67 MB, but the
segment size shows 16 MB, which is more than 50% , why is that so?
As discussed earlier, in each block 10% space will be allocated for free space. Lets calculate..
SQL> select segment_name, sum(blocks) "Total Blocks", sum(blocks)*8192/1024/1024 "Blocks Size in MB" from dba_extents where segment_name
in ('TEST_ASSM_TBL','TEST_MSSM_TBL') group by segment_name;
SEGMENT_NAME Total Blocks Blocks Size in MB
--------------------------------------------------------------------------------- ------------ -----------------
TEST_MSSM_TBL 2048 16
TEST_ASSM_TBL 2048 16
So 10% on 16MB is 1.6 MB, so 7.67 MB + 1.6 MB = 9.27 MB,
16 mb - 9.27 mb = 6.73
Still we could see 6.73 mb of high space usage. Lets find out..
Ok..
Lets see how many empty blocks are there...
the ROWID_BLOCK_NUMBER function is used to return just the block number in the table.
Lets display the data for the first 10 rows.
SQL> select rowid, dbms_rowid.rowid_block_number(rowid) from test_assm_tbl where empno <= 10;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAN3vAAHAAAAAOAAA 14
AAAN3vAAHAAAAAQAAA 16
AAAN3vAAHAAAAARAAA 17
AAAN3vAAHAAAAASAAA 18
AAAN3vAAHAAAAAVAAA 21
AAAN3vAAHAAAAAXAAA 23
AAAN3vAAHAAAAAbAAA 27
AAAN3vAAHAAAAAcAAA 28
AAAN3vAAHAAAAAeAAA 30
AAAN3vAAHAAAAAgAAA 32
10 rows selected.
SQL> select rowid, dbms_rowid.rowid_block_number(rowid) from test_mssm_tbl where empno <= 10;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAN3wAAIAAAAALAAA 11
AAAN3wAAIAAAAANAAA 13
AAAN3wAAIAAAAAPAAA 15
AAAN3wAAIAAAAARAAA 17
AAAN3wAAIAAAAATAAA 19
AAAN3wAAIAAAAAVAAA 21
AAAN3wAAIAAAAAXAAA 23
AAAN3wAAIAAAAAZAAA 25
AAAN3wAAIAAAAAbAAA 27
AAAN3wAAIAAAAAdAAA 29
10 rows selected.
As from the above we could see one row is placed in two blocks...
eg .. the rowid AAAN3wAAIAAAAALAAA shows the block number as 11, the subsequence rowid shows as 13,
which means the data of rowid AAAN3wAAIAAAAALAAA is not enough to fit in to one blocks and been
placed into two blocks.
The size of one row is 7.85 kb which cannot be fit in to one block hence oracle used two blocks to store 1 rec.
Which is the chanined rows.. lets see..
SQL > create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
SQL> analyze table test_assm_tbl list chained rows;
Table analyzed.
SQL> analyze table test_mssm_tbl list chained rows;
Table analyzed.
SQL> select table_name, count(*) from chained_rows group by table_name;
TABLE_NAME COUNT(*)
------------------------------ ----------
TEST_MSSM_TBL 1000
TEST_ASSM_TBL 1000
--Yes...as we could see it is beacuse of the chanied rows, this is mainly becuase of the size of 1 row which is 7.85 kb,
-- lets delete all the rows now..
SQL> delete from TEST_MSSM_TBL ;
1000 rows deleted.
SQL> delete from TEST_ASSM_TBL;
1000 rows deleted.
SQL> commit;
Commit complete.
-- Here we have delete all the rows now the freelists will be updated for MSSM and bitmaps been upated for ASSM.
Lets insert data which can fit in 1 block.
SQL> begin
for i in 1..1000 loop
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',3000));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',4000),dbms_random.string('U',3000));
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_ASSM_TBL');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats ('TEST_USER1','TEST_MSSM_TBL');
PL/SQL procedure successfully completed.
SQL> select table_name, avg_row_len/1024 "K", (avg_row_len*num_rows)/1024/1024 "M" from dba_tables where table_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL');
TABLE_NAME K M
------------------------------ ---------- ----------
TEST_ASSM_TBL 6.87890625 6.71768188
TEST_MSSM_TBL 6.87890625 6.71768188
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_ASSM_TBL 2 25 8
TEST_ASSM_TBL 3 33 8
TEST_ASSM_TBL 4 41 8
TEST_ASSM_TBL 5 49 8
TEST_ASSM_TBL 6 57 8
TEST_ASSM_TBL 7 65 8
TEST_ASSM_TBL 8 73 8
TEST_ASSM_TBL 9 81 8
TEST_ASSM_TBL 10 89 8
TEST_ASSM_TBL 11 97 8
TEST_ASSM_TBL 12 105 8
TEST_ASSM_TBL 13 113 8
TEST_ASSM_TBL 14 121 8
TEST_ASSM_TBL 15 129 8
TEST_ASSM_TBL 16 137 128
TEST_ASSM_TBL 17 265 128
TEST_ASSM_TBL 18 393 128
TEST_ASSM_TBL 19 521 128
TEST_ASSM_TBL 20 649 128
TEST_ASSM_TBL 21 777 128
TEST_ASSM_TBL 22 905 128
TEST_ASSM_TBL 23 1033 128
TEST_ASSM_TBL 24 1161 128
TEST_ASSM_TBL 25 1289 128
TEST_ASSM_TBL 26 1417 128
TEST_ASSM_TBL 27 1545 128
TEST_ASSM_TBL 28 1673 128
TEST_ASSM_TBL 29 1801 128
TEST_ASSM_TBL 30 1929 128
TEST_MSSM_TBL 0 9 8
TEST_MSSM_TBL 1 17 8
TEST_MSSM_TBL 2 25 8
TEST_MSSM_TBL 3 33 8
TEST_MSSM_TBL 4 41 8
TEST_MSSM_TBL 5 49 8
TEST_MSSM_TBL 6 57 8
TEST_MSSM_TBL 7 65 8
TEST_MSSM_TBL 8 73 8
TEST_MSSM_TBL 9 81 8
TEST_MSSM_TBL 10 89 8
TEST_MSSM_TBL 11 97 8
TEST_MSSM_TBL 12 105 8
TEST_MSSM_TBL 13 113 8
TEST_MSSM_TBL 14 121 8
TEST_MSSM_TBL 15 129 8
TEST_MSSM_TBL 16 137 128
TEST_MSSM_TBL 17 265 128
TEST_MSSM_TBL 18 393 128
TEST_MSSM_TBL 19 521 128
TEST_MSSM_TBL 20 649 128
TEST_MSSM_TBL 21 777 128
TEST_MSSM_TBL 22 905 128
TEST_MSSM_TBL 23 1033 128
TEST_MSSM_TBL 24 1161 128
TEST_MSSM_TBL 25 1289 128
TEST_MSSM_TBL 26 1417 128
TEST_MSSM_TBL 27 1545 128
TEST_MSSM_TBL 28 1673 128
TEST_MSSM_TBL 29 1801 128
TEST_MSSM_TBL 30 1929 128
62 rows selected.
-- so no new extents has been allocated, all the space reclaimed by the delete has been used.
SQL> truncate table chained_rows;
Table truncated.
SQL> analyze table test_assm_tbl list chained rows;
Table analyzed.
SQL> analyze table test_mssm_tbl list chained rows;
Table analyzed.
SQL> select table_name, count(*) from chained_rows group by table_name;
no rows selected
-- Now we could see there are no any chained rows as the once record is fit into one block.
Lets see the use of assm..
SQL> truncate table TEST_ASSM_TBL;
Table truncated.
SQL> truncate table TEST_MSSM_TBL;
Table truncated.
begin
for i in 10..60 loop
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
end loop;
commit;
end;
/
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_MSSM_TBL 0 9 8
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_assm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
12 8
13 8
14 8
15 8
16 8
17 8
20 3
7 rows selected.
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_mssm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
10 8
11 8
12 8
13 8
14 8
15 8
16 3
-- Around 8 records are stored in one block. Let delete 3 row from each block.
-- Now could see around 2 rows are stored in 1 block. Lets try to delete one rec from each block.
begin
for i in 10..60 loop
if i mod 3 = 0 then
delete from test_assm_tbl where empno = i;
delete from test_mssm_tbl where empno = i;
end if;
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_assm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
12 5
13 6
14 5
15 5
16 6
17 5
20 2
7 rows selected.
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_mssm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
10 6
11 5
12 5
13 6
14 5
15 5
16 2
7 rows selected.
Now the blocks will be fragmented becuase of the delete operation. Lets add some data.
begin
for i in 10..60 loop
if i mod 3 = 0 then
insert into test_assm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
insert into test_mssm_tbl values (i,dbms_random.string('U',30),32,'M','IT',dbms_random.string('U',400),dbms_random.string('U',410));
end if;
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where segment_name in ('TEST_ASSM_TBL','TEST_MSSM_TBL') order by 1, 2;
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_ASSM_TBL 0 9 8
TEST_ASSM_TBL 1 17 8
TEST_MSSM_TBL 0 9 8
TEST_MSSM_TBL 1 17 8
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_assm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
12 5
13 6
14 5
15 8
16 8
17 8
18 8
19 1
20 2
9 rows selected.
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from test_mssm_tbl group by dbms_rowid.rowid_block_number(rowid) order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
10 6
11 5
12 5
13 6
14 5
15 5
16 8
17 8
18 3
9 rows selected.
-- As we can see from the above in test_assm_tbl (The segment managed by ASSM) the new rows inserted in the old blocks i.e. even if we have
some space in the block it will used by the inserts.
But for the table test_mssm_tbl (Manual Segment space Management) the new rows are not inserted in the old blocks instead those are inserted
in the new blocks, it is because we have specified by pct_used parameter to 50, since already the block is holding some value which is
greater than the 50 % hence these blocks are removed from the freelists. Thus ASSM help in avoiding the fragmention.
Hi
ReplyDeleteTry to provide short notes.
Way of presentation is really worse.
BG color really irritates to go through entire content.
No one is interested to read entire content. Make it sense.
Thanks
very good, great content, I was looking for this kind of interpretation already a long time ago!
ReplyDeleteCongratulations
Really Good Content, appreciate your efforts
ReplyDeleteReally Good content ... Thanks a lot..
ReplyDeleteThank you so much. It 's very good.
ReplyDelete