Monday, January 13, 2014

ASSM and MSSM - Example

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.






5 comments:

  1. Hi

    Try 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

    ReplyDelete
  2. very good, great content, I was looking for this kind of interpretation already a long time ago!
    Congratulations

    ReplyDelete
  3. Really Good Content, appreciate your efforts

    ReplyDelete
  4. Really Good content ... Thanks a lot..

    ReplyDelete