Shulou(Shulou.com)06/01 Report--

11.4 BLOCK (block)

11.4.1 Features of BLOCK (data blocks):

BLOCK is the smallest unit for Oracle to operate storage space IO. The management method of BLOCK is the concrete embodiment of zone management and segment management.

1. Automatic management, such as creating a tablespace time zone for local management, and setting the storage space of the segment to AUTO (that is, ASSM), and all blocks of the tablespace are automatically managed by bitmap. This is the system default.

2. Free list mode (MSSM) introduces the concept of FREELIST, and the two parameters PCTFREE and PCTUSED control the size of the available storage area to avoid the occurrence of row migration. These two parameters can be set when the tablespace is created or in the schema object (table, index) that creates the database. The priority set in the schema object is higher than that in the tablespace. That is, if there is no setting in the table and index, the block is managed according to the setting of the tablespace, and if the tablespace is not set, the block is managed automatically.

Data block: oracle 11g standard block: 8k, support 2-32k, composed of block header, free space, data

Block header:

ITL: transaction slot, which can have multiple ITL to support concurrent transactions. Whenever a transaction wants to update data in a data block, it must first get an ITL slot, and then write information such as the current transaction ID, the undo block address used by the transaction, SCN number, and whether the current transaction is committed or not into the ITL slot.

Initrans: the number of initialization transaction slots. The table defaults to 1 and index defaults to 2.

Maxtrans: maximum number of transaction slots (default 255)

ROW DIR: the row directory that points to the offset at the beginning and end of the idle line.

Test site: the possibility to increase the size is that the row entries increases and more ITL space is added.

Management of data blocks in the form of free lists:

Freelist: the available blocks for inserting data are registered in the free list, located at the beginning of the section. Look for the list first when inserting table row data.

Pctfree: the percentage of space reserved for a block to prevent the length of one or more column values from being increased in future update operations. When this value is reached, the block information is cleared from freelist.

Pctused: the percentage of water level used for a block that returns the block to the available list to wait for more insertions. When this value is reached, the block information is added to the freelist.

. This parameter is not used under ASSM.

Line link: a situation in which a row is stored in multiple blocks because the length of the row exceeds the amount of free space in a block, that is, a row link is a row that spans multiple blocks.

The pointer in the original block is required because the ROWID entry of the index still points to the original location. Row migration is caused by update statements when there is insufficient pctfree space, and it is independent of insert and delete statements (test site).

How do you know that a row link or row migration has occurred?

Look at the AVG_ROW_LEN column and CHAIN_CNT column of dba_tables, and when CHAIN_CNT has a value, look at AVG_ROW_LEN, which represents the average length of the row (byte), and if the AVG_ROW_LEN block size, then the linked row.

Look at the row where the migration or join occurred, use analyze table xx list chained rows;, but the command needs to insert the result into the chained_rows table, and create this table with a utlchained.sql or utlchn1.sql script. See the analyze command in sql referenve.

SQL > create table T1 (C1 varchar2 (20))

SQL >

Begin

For i in 1..1000 loop

Insert into T1 values (null)

End loop

End

/

Analyze the T1 table to make sure there is no row migration.

SQL > analyze table T1 compute statistics

SQL > select pct_free,pct_used,avg_row_len,chain_cnt from user_tables where table_name='T1'

AVG_ROW_LEN, which represents the average length of the row (byte)

Fill in these empty columns, analyze T1, and have row migration.

SQL > update T1 set c1='timran is my drt'

SQL > analyze table T1 compute statistics

SQL > select pct_free,pct_used,avg_row_len,chain_cnt from user_tables where table_name='T1'

Move table, and then analyze T1, row migration disappears. Think about it: is segment reorganization effective for line links?

SQL > alter table T1 move

SQL > analyze table T1 compute statistics

SQL > select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'

Test points: for most purposes, you should use the process analysis table in the DBMS_STATS package, but to view row links or row migration information, you can only detect it through the ANALYZE command.

11.4.2 relationship between tables and data blocks (block)

1) what is a high water mark?

High Water level Line (high-water mark,HWM)

In a database, if you think of a table as a series of blocks arranged from left to right, the high water mark is the rightmost block that once contained the data. In principle, HWM will only grow, and even if all the data in the table is deleted, the HWM will not decrease.

2) there are two solutions to reduce HWM:

Moving tables, the move method, moves tables from one table space to another (you can also move within this table space).

Syntax: alter table T1 move [tablespace users]

Advantages: it can remove fragments in the data block and lower the high water mark.

Cons: move requires extra (double) space.

The table is locked during move, and other users cannot do DML or DDL operations on the table.

After move, the relevant indexes are not available, and the indexes on the table need to be rebuilt (test points).

Shrink is also called segment reassembly, and the underlying layer of table contraction is achieved through matching INSERT and DELETE operations.

It is divided into two different phases: the compression phase and the HWM reduction phase. (PPT-II-491)

Syntax: alter table T2 shrink space [cascade] [compact]

Two premises: 1) the table space in which the table is located is managed in ASSM mode, because the bitmap method records the actual fullness information about the block (test point). 2) row movement is enabled on the table.

If you send alter table T2 shrink space compact;, then you have only completed the first phase. This is the compression phase. The first phase of delete insert can be completed at the peak of business.

After the peak, again alter table T2 shrink space; will enter the second phase soon because most of the work in the compression phase has been completed, and there will be a brief lock wait for the DML operation.

Test:

Create tablespace timran datafile'/ u01qoradata size TIMRAN 11g Universe Timran01.dbf'Unim

Create table scott.t2 tablespace timran as select * from dba_objects

Scott:

Select max (rownum) from T2

Select table_name, blocks, empty_blocks, num_rows from user_tables where table_name='T2'

Analyze table t2 compute statistics

Delete t2 where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.