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

Optimize it through case study-- Oracle data blocks (data block)

Block Overview Oracle manages storage space in database data files (datafile) in blocks (data block). A data block is the smallest (logical) data unit in a database. Corresponding to data blocks, the minimum physical unit of storage for all data at the operating system level is bytes (byte). Each operating system has a parameter called block capacity (block size). Each time Oracle acquires data, it always accesses the entire Oracle block, rather than accessing the data according to the capacity of the operating system block.

The standard block (data block) capacity in the database is specified by the initialization parameter DB_BLOCK_SIZE. In addition, users can specify five non-standard block capacities (nonstandard block size). The block capacity should be set to an integral multiple of the operating system block capacity (and less than the maximum limit for block capacity) in order to reduce unnecessary Imax O operations. Oracle blocks are the smallest unit of storage that Oracle can use and allocate.

See also: Oracle documents for specific operating systems contain more information about block capacity (data block size) multiple block capacity (Multiple Block Sizes)

The internal structure of the data block structure in Oracle is similar regardless of whether the data block stores table (table), index (index) or cluster table (clustered data).

This figure shows the various components of the data block, including: data block head (including standard and variable content) (common and variable header), table catalog area (table directory), row catalog area (row directory), free space area (free space), row data area (row data). The following sections will explain the components separately. The two arrows in the figure indicate that the capacity of the free space area in a data block is variable.

Data blocks (including standard and variable content)

The header contains summary information about the block, such as the block address (block address) and the type of segment (segment) to which the block belongs (for example, a table or index).

Table catalogue area

If a data table stores rows in this data block, the information of the data table will be recorded in the table catalog area (table directory) of the data block.

Row catalogue area

This area contains information about the rows of data stored in the block (the address of each row fragment (row piece) in the row data area (row data area)). [a complete data row or only part of a data row may be saved in a data block, so row piece is used in this article]

When the row catalog area (row directory) space of a data block (data block) is used, even if the row is deleted (delete), the row catalog area space will not be reclaimed. For example, when a data block that once contained 50 records is emptied, the row directory area of its header still takes up 100 bytes (byte) of space. Row catalog area space is reused only when new data is inserted (insert) into the block.

Management overhead data block (data block header), table catalog area (table directory), row directory area (row directory) are collectively referred to as administrative overhead (overhead). The capacity of some of the overhead is fixed, while the total capacity of some of the overhead is variable. The average capacity of fixed and variable management overhead in data blocks is between 84 and 107 bytes (byte).

The row data area (row data) in the row data block (data block) contains the actual data of the table or index. A row of data can span multiple blocks. This leads to "Row Chaining" and "Row Migrating".

The free space zone uses the space in the free space zone (free space) when a new data row is inserted, or when more space is needed to update the data row (for example, the last field of a row was trailing null, but now it is updated to a non-empty value).

If a data block (data block) belongs to a data segment (data segment) of a table or cluster table, or an index segment (index segment) of an index, transaction entries (transaction entry) may also be stored in its free space area. If the data rows (row) in a data block are being created by INSERT,UPDATE,DELETE, and SELECT... FOR UPDATE statement access, this data block needs to save the transaction entry. The storage space required for transaction entries depends on the operating system. In common operating systems, transaction entries require about two kinds of SQL statements to increase the available space in the data block: the DELETE statement, and the UPDATE statement that updates the existing data value to a smaller capacity value. The space released by the above two operations can be used by subsequent INSERT statements under the following two conditions:

If the INSERT statement is in the same transaction (transaction) as the above two operations, and after the statement that frees space, then the INSERT statement can use the freed space.

If the INSERT statement and the free space statement are in different transactions (for example, they are submitted by different users), the INSERT statement will use the freed space only if the free space statement is committed and the inserted data must use this data block.

The space released in the data block (data block) may not be contiguous with the available space area (free space). Oracle merges the free space into the free space zone only if the following conditions are met: (1) the INSERT or UPDATE statement selects a data block with enough free space to hold new data, and (2) but the free space in this block is not contiguous and the data cannot be written to contiguous space in the data block. Oracle merges the free space in the data block only when the above conditions are met, in order to avoid the impact of too frequent space consolidation on database performance.

Case 1: verify the maximum number of rows stored in Oracle data block free space

1) maximum available space of block

10:52:11 SYS@ prod > SELECT kvisval,kvistag,kvisdsc from sys.x$kvis

No rows selected

In general, 8k blocks have 8096 bytes of free space; generally, the minimum length of a row is 11 bytes (plus overhead), so 8k blocks can store up to 8096max 11cm 736 rows.

Create tablespace:11:21:46 SYS@ test1 > select tablespace_name,extent_management from dba_tablespaces managed by Dictionary TABLESPACE_NAME EXTENT_MAN---SYSTEM DICTIONARYSYSAUX LOCALUNDOTBS1 LOCALTEMP1 LOCALDICT1 DICTIONARY create table (pctfree=0): 11:21:55 scott@ test1 > create table t3pctfree 0tablespace dict1asselect * from T1 Number of rows viewing records on the block: 11:33:40 SCOTT@ test1 > select object_name,object_id from user_objects11:33:55 2 where object_name='T3' OBJECT_NAME OBJECT_ID---T3 1677511 SELECT SPARE1 FROM TAB$ where obj#=16775; SPARE1- 3308 SYS@ test1 > SELECT SPARE1 FROM TAB$ where obj#=16775; SPARE1- 736

Case 2: verify the number of rows stored per block

Create data: 10:31:30 SCOTT@ prod > begin for i in 1.. 10 loop insert into emp1 select * from emp1; end loop; end; / 10:31:38 SCOTT@ prod > select count (*) from emp1; COUNT (*)-14336 View Table Storage structure: 10:32:13 SCOTT@ prod > analyze table emp1 compute statistics Table analyzed.10:33:14 SCOTT@ prod > select table_name,num_rows,blocks,empty_blocks from user_tables10:33:40 2 where table_name='EMP1' TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS---EMP1 14336 91 5 View each Rows stored in blocks: 10:31:59 SCOTT@ prod > SELECT rid COUNT (rnum) rnum10:32:13 2 FROM (SELECT SUBSTR (ROWID, 1,15) rid, ROWID rnum FROM emp1) 10:32:13 3 GROUP BY rid RID RNUM---AAASa0AAEAAAAIL 14AAASa0AAEAAAAJN 170AAASa0AAEAAAAJZ 170AAASa0AAEAAAAJe 170.RNUM RID---AAASa0AAGAAAACv 17086 rows selected.

Case 3: parameters related to block access

Arraysize parameter

Arraysize defines the number of rows returned to the client at a time. When the arraysize row is scanned, the scan is stopped, the data is returned, and then the scan continues.

This process is called SQL*Net roundtrips to/from client in statistics. Because the default arraysize is 15 rows, there is a problem, because the number of records in a block is usually more than 15 lines, so if we scan each time according to 15 lines, one more data block will be scanned each time, and one data block may be scanned multiple times.

Repeated scans increase consistent gets and physical reads. Add physical reads, which is easy to understand. The more you scan, the more likely you are to be physically.

Consistent gets, this is the number of reads from undo, Oracle in order to ensure data consistency, when a query is very long, after the query, the data block is modified and has not yet been submitted, and when querying again, Oracle constructs the CR block according to Undo. This CR block can be understood as the state of the data block at some time before. In this way, the data from the query is consistent.

So the more blocks you scan repeatedly, the more CR blocks you need to build, and the more opportunities you have to read Undo and consistent gets.

If the data is interrupted every time it is sent to the client, the data will be re-scanned, which increases logical reads, so adjusting arraysize can reduce the number of transfers and reduce logical reads.

Default arraysize:

11:56:18 SCOTT@ prod > show arraysizearraysize 15 theoretically the arraysize is 15, and reading 170rows should be 12 times. 12:13:57 SCOTT@ prod > select 170 from dual; 15 from dual; 170 SCOTT@ prod 15-11.333333312 SCOTT@ prod > select * from emp1 where rownumshow arraysizearraysize 100012 Fraser 07 SCOTT@ prod > set autotrace trace12:07:48 SCOTT@ prod > select * from emp1 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.