Experiment: ASSM Internal storage Research Disclosure

This article paste: http://www.itpub.net/thread-152489-1-1.html, one of the experiments I did from the redo, the ASSM has a more profound understanding. Dump information is not written for space reasons.

Before 9.2.0, the management and allocation of the remaining space of the table are all done by the connection list freelist, because there is a serial problem in freelist, so it is easy to cause the contention of the segment head and waste of space (in fact this is not obvious), the most important is because of the need DBA Spend a lot of energy managing these contentions and monitoring the space utilization of the tables.

Automatic segment Space Management (ASSM), which first appeared in Oracle920. With ASSM, the connection list freelist replaced by a bitmap, which is a binary array that quickly and efficiently manages storage extensions and remaining chunks (free blocks), thereby improving the nature of segmented storage, ASSM the segments created on the table space, and another salutation called bitmap Managed segments (BMB section).

Let's take a look at how bitmap freelist is implemented. I will start by using the section Space management automatic parameter creation tablespace:

Create tablespace demo datafile '/oradata/ltest/demo01.dbf ' size 50m

Extent Management Local

Segment Space management auto;

Once you have defined the tablespace, tables and indexes can be easily moved to the new tablespace, with ASSM Local admin tablespace skipping any values specified for pctused, next, and freelists.

When a table or index is assigned to this tablespace, the pctused value for the standalone object is ignored, and Oracle9i uses a bitmap array to automatically manage the freelist of tables and indexes in tablespace. This next extension clause is obsolete for tables and indexes created within the LMT tablespace, because the locally managed tablespace manages them. However, the initial parameter is still needed because Oracle cannot know in advance the size of the initial table load. For ASSM, the minimum initial value is three blocks.

The new management mechanism uses bitmaps to track or manage each block allocated to the object, and how much space is left in each block based on the state of the bitmap, such as >75%,50%-75%,25%-50% and <25%, which means that the bitmap actually employs four state bits to replace the previous pctused, When to use the data block is determined by the set of Pctfree.

One of the great advantages of using ASSM is that bitmap freelist can certainly mitigate the burden of buffer busy wait, a problem that was a serious problem in previous versions of Oracle9i.

In the absence of multiple freelist, each Oracle table and index has a block of data in the header of the table that manages the remaining chunks used by the object and provides a block of data for any new data rows created by any SQL insert declaration. Buffer busy waiting occurs when the data block within the data buffer is not available because it is locked by another DML transaction. When you need to insert multiple tasks into the same table, the tasks are forced to wait, while Oracle assigns the remaining blocks at the same time.

With ASSM, Oracle claims to significantly improve the performance of DML concurrency, because different parts of the same bitmap can be used simultaneously, eliminating the ability to serialize the search for the rest of the space. Based on Oracle's test results, using bitmap freelist eliminates all fragmentation headers (Resource) contention, and can get super fast concurrent inserts.

Although ASSM shows an exciting feature and simplifies the work of Oracle DBAs, there are some limitations to Oracle9i's bitmap segmentation:

Ø once the DBA is assigned, it cannot control the storage behavior of independent tables and indexes within the tablespace.

Ø You cannot use ASSM to create a temporary tablespace. This is determined by the transient nature of the temporary segment of the sort.

Ø only locally managed tablespace can be managed using bitmap segmentation.

Ø performance problems may arise when using ultra-high-volume DML (such as inserts, updates, and deletes), but ASSM has also begun to be widely used in the latest version, such as over 9204.

1 Experimental Process

1.1 Creating a table space

Sql> Create tablespace demo datafile '/oradata/ltest/demo01.dbf ' size 50m extent management Local segment spaces Managem ENT auto;

Tablespace created

1.2 Creating a table

Sql> CREATE table Demotab (x number) tablespace demo storage (initial 1000K);

Table created

Sql> Select T.table_name,

2 t.initial_extent/1024 "initial_extent (K)",

3 T.next_extent,

4 T.pct_free,

5 t.pct_used

6 from User_tables t

7 where t.table_name = ' demotab ';

table_name Initial_extent (K) next_extent Pct_free pct_used

------------------------------ ----------------- ----------- ---------- ----------

Demotab 1000 10

Sql> Select T.segment_name, t.extent_id, T.bytes, T.blocks, t.block_id

2 from Dba_extents t

3 Where t.segment_name = ' demotab ';

Segment_name extent_id BYTES BLOCKS block_id

----------------- ---------- ---------- ---------- ----------

Demotab 0 65536 8 9

Demotab 1 65536 8 17

Demotab 2 65536 8 25

Demotab 3 65536 8 33

Demotab 4 65536 8 41

Demotab 5 65536 8 49

Demotab 6 65536 8 57

Demotab 7 65536 8 65

Demotab 8 65536 8 73

Demotab 9 65536 8 81

Demotab 10 65536 8 89

Demotab 11 65536 8 97

Demotab 12 65536 8 105

Demotab 13 65536 8 113

Demotab 14 65536 8 121

Demotab 15 65536 8 129

Rows selected

As you can see, next_extent and pct_used are both empty. Where next_extent is empty, because Allocation_type is system not uniform.

1.3 Analysis of the storage structure of the table

sql> exec show_space (' Demotab ');

Total Blocks .................. 128

Total Bytes .................. 1048576

Total MBytes .................. 1

Unused Blocks ........ .......... .... 125

Unused Bytes ........ .......... .... 1024000

Unused Kbytes ........ .......... .... 1000

Used Blocks ........ .......... .... 3

Used Bytes ........ .......... .... 24576

Used Kbytes ........ .......... .... 24

Last Used Ext Fileid ............ 9

Last Used Ext blockid ............ 9

Last Used blocks ................ ... 3

The segment is analyzed below

FS1 Blocks (0-25) ............ 0

FS2 Blocks (25-50) ............ 0

FS3 Blocks (50-75) ............ 0

FS4 Blocks (75-100) ............ 0

Unformatted Blocks ......... ...... 0

Full Blocks ............. 0

Pl/sql procedure successfully completed

1.4 Dump data Block

sql> alter system dump DATAFILE 9 Block 9;

System Altered

sql> alter system dump DATAFILE 9 block 10;

System Altered

sql> alter system dump DATAFILE 9 Block 11;

System Altered

Starting with the 9th block, the file header occupies 64K of space equal to 8 blocks. This information can also be seen from the dba_extents, which actually demotab this segment starting with the 9th block.

Problem. Why is there only 16 pieces of information left here? This requires further study (look down).

Analysis Block 10, called second level BITMAP blocks. This is interesting, and it records the address (DBA) that might appear with block 9. Did not say on the above, block 9 can only record 16 pieces of information, that 16 blocks after the information how to do. The BMB segment uses a distributed strategy where, in the 25th block, Oracle will add a new block to record 16 blocks (see the test later), then block 10 records the addresses of these blocks, such as:

0x06800019 Free:5 Inst:1 represents the next block's address is 19 (hexadecimal), in fact, is the decimal 25. Then we imagine that the next one is 25 + 16 = 41. should be hexadecimal 29, and then look at the contents of block 10, you can know the effect of this block.

Then look at Block 11, which is the section header of the table, where the interval map of the table is recorded, including the 16-interval distribution address of the table segment, if there is no table, there is no block, this block and the ASSM bitmap block itself does not have much relationship, in each segment of the head will have one, because this is the first segment of the table space, So the position was placed in Block 11.

Can see in fact in ASSM's table, cancel the original freelist, actually added a lot of blocks to manage the block of data, these blocks are no longer distributed in the head of the block, but distributed inside the table, every 16 blocks (this number and the different circumstances may vary), There is a block to record the data retention of the 16 blocks.

The level of bitmap data can be divided into three levels, when there is one or more first-level bitmap blocks (such as Block 9,19), the two-level bitmap (such as Block 10) block to save the address of a bitmap block, similarly, a two-level bitmap block is not enough to use and there are more than two-level bitmap blocks, The address of the level two bitmap block will be saved by a level three bitmap block (similar to the index structure, because a block of level three bitmap requires a lot of data blocks, so there is no discussion of the three-level bitmap block). The structure of the entire bitmap array forms a tree structure, enabling Oracle to track the location of all bitmap data blocks.

1.5 analysis after inserting data

sql> INSERT INTO Demotab select RowNum from Dba_objects;

50391 rows Inserted

Sql> commit;

Commit Complete

sql> exec show_space (' Demotab ');

Total Blocks .................. 128

Total Bytes .................. 1048576

Total MBytes .................. 1

Unused Blocks ........ .......... .... 40

Unused Bytes ........ .......... .... 327680

Unused Kbytes ........ .......... .... 320

Used Blocks ........ .......... .... 88

Used Bytes ........ .......... .... 720896

Used Kbytes ........ .......... .... 704

Last Used Ext Fileid ............ 9

Last Used Ext blockid ............ 89

Last Used blocks ................ ... 8

The segment is analyzed below

FS1 Blocks (0-25) ............ 0

FS2 Blocks (25-50) ............ 0

FS3 Blocks (50-75) ............ 1

FS4 Blocks (75-100) ............ 3

Unformatted Blocks ......... ...... 0

Full Blocks ............. 76

Pl/sql procedure successfully completed

You can see:

Total Blocks .................. 128

Unused Blocks ........ .......... .... 40

Used Blocks ........ .......... .... 88

In other words, this insertion took 88 blocks.

But look at the data below, that is, the data block is only 1 + 3 + 76 = 80, there are 8 differences in where? There are 3 segments in the header, and 5 should be the bitmap blocks that are distributed in the table (assuming). will be further analysed.

Use 88 blocks, in fact, is 11 intervals, 11 intervals, each bitmap block can only manage 16 blocks (this point in different environments and segments of the size of the situation will be different), here should be used 6 (88/16 after the whole plus 1) Single block to be able to manage over, 6 of which belong to the paragraph of the first 3 blocks of one, That's exactly 80 + 3 + (6-' 1) = 88. Further analysis later.

2 dump bitmap block

Information from the dump can be seen:

Second-level bitmap block: 10

First-level bitmap block: 9,25,41,57,73,89

Segment Head BLOCK: 11

2.1 Analysis of Level two bitmap blocks

......

0x02400009 free:1 inst:1--> corresponding block 9

0x02400019 free:1 inst:1--> corresponding block 25

0x02400029 free:1 inst:1--> corresponding block 41

0x02400039 free:1 inst:1--> corresponding block 57

0x02400049 free:1 inst:1--> corresponding block 71

0x02400059 free:5 inst:1--> corresponding block 89

0x02400069 Free:5 inst:1

0x02400079 Free:5 inst:1

......

2.2 Analysis of the first-level bitmap block

Block 09: A one-level bitmap block + a two-level bitmap block + 1 segment head block + 13 full block

Block 25: One-level bitmap block + 15 full block

Block 41: One-level bitmap block + 15 full block

Block 57: One-level bitmap block + 15 full block

Block 71: One-level bitmap block + 15 full block

Block 57: One-level bitmap block + 3 75-100% free block + 1 50-75% free Block + 3 full block (and 8 are unformatted)

The conclusion is:

Blocks (50-75%) has 1, Blocks (75-100%) has 3, full Blocks has 76. The results are the same as those calculated using the Show_space stored procedure.

2.3 Analysis Section head block

In segment Header 11:

......

Low Highwater Mark:

Highwater:: 0x0240000c ext#: 0 blk#: 3 ext Size:8

#blocks in SEG. HDR ' s freelists:0

#blocks below:0

MAPBLK 0x00000000 offset:0

Level 1 BMB for high HWM block:0x02400059

Level 1 BMB for low HWM block:0x02400009

......

Conclusion:

1. The high water point at the bottom starts from 12 (16 C) blocks (preceded by 8 file headers + 2 bitmap blocks + 1 segment headers).

2. A high level of bitmap block high water point is 89 (16 in 59), low water point is 9 (16).

......

Segment type:1 nl2:1 blksz:8192 fbsz:0

L2 Array Start offset:0x00001434

3 bmb:0x00000000

L2 Hint for inserts:0x0240000a

Last Level 1 bmb:0x02400079

Last level II bmb:0x0240000a

Last level III bmb:0x00000000

Map Header:: Next 0x00000000 #extents: obj#: 53313 flag:0x10000000

INC # 0

Extent Map

......

Conclusion: From the above information can see the last use of the first level, level two, three-level bitmap block address.