Chapter 09–Storage Structure and Relationships

1,table,index最少对应一个segments，对于table

partition 一个partition对应一个segment

2，entents是逻辑上连续的blocks

3，oracle存储的单位block,是操作系统块的整数倍，在表空

间一级规定(STRINGS spfileray.ora |grep -i

DB_BLOCK_SIZE，标准是8k，可以是2、4、8、16、32k，标准

块大小8k数据库创建完之后是不能修改的，非标准的大小可

以在创建表空间的时候规定，临时与系统表空间一定是使用

 

Database Block

• Minimum unit of I/O

• Consists of one or more operating system blocks

• Set at tablespace creation

• DB_BLOCK_SIZE is the default block size

 

Multiple Block Size Support

• A database can be created with a standard block

size and up to four nonstandard block sizes.

• Block sizes can have any power-of-two2的幂此方 value

between 2 KB and 32 KB.

 

The block size of the SYSTEM tablespace is termed the standard block size.

 

 

Standard Block Size

• Set at database creation using the DB_BLOCK_SIZE

parameter; cannot be changed without re-creating

the database

• Used for SYSTEM and TEMPORARY tablespaces

• DB_CACHE_SIZE specifies the size of the DEFAULT

buffer cache for standard block size:

– Minimum size = one granule (4 MB or 16 MB)

– Default value = 48 MB

 

A granule is a unit of contiguous virtual memory allocation. The size of a granule

depends on the estimated total SGA size whose calculation is based on the value of the SGA_MAX_SIZE parameter: 4 MB if estimated SGA size is < 128 MB, 16 MB otherwise.

 

 

Nonstandard Block Size

• Configure additional caches with the following

dynamic parameters:

– DB_2K_CACHE_SIZE for 2 KB blocks

– DB_4K_CACHE_SIZE for 4 KB blocks

– DB_8K_CACHE_SIZE for 8 KB blocks

– DB_16K_CACHE_SIZE for 16 KB blocks

– DB_32K_CACHE_SIZE for 32 KB blocks

• DB_nK_CACHE_SIZE is not allowed

if nK is the standard block size.

• Minimum size for each cache is one granule.

 

The default value for DB_nK_CACHE_SIZE parameter is zero.

 

Creating Nonstandard

Block Size Tablespaces

CREATE TABLESPACE tbs_1

DATAFILE 'tbs_1.dbf'

SIZE 10M BLOCKSIZE 4K;

 

DESCRIBE dba_tablespaces

Name Null? Type

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

TABLESPACE_NAME NOT NULL VARCHAR2(30)

BLOCK_SIZE NOT NULL NUMBER

...

 

You cannot specify nonstandard block sizes for a temporary tablespace (that

is, if you also specify TEMPORARY) or if you intend to assign this tablespace as the temporary tablespace for any users.

 

 

 

Multiple Block Sizing Rules

• All partitions of a partitioned object must reside in

tablespaces of the same block size.

• All temporary tablespaces, including the permanent

ones that are being used as default temporary

tablespaces, must be of standard block size.

• Index-organized table overflow and out-of-line LOB

segments can be stored in a tablespace with a block

size different from the base table.

 

 

 

 

 

 

 

Oracle data blocks contain:

• Block header: The header contains the data block address, table directory, row

directory, and transaction slots that are used when transactions make changes to rows in the block. Block headers grow from the top down.

• Data space: Row data is inserted into the block from the bottom up.

• Free space: The free space in a block is in the middle of the block. Thus both the

header and the row data space can grow when necessary. The free space in a block is contiguous initially. However, deletions and updates may fragment the free space in the block. The free space in the block is coalesced（合并） by the Oracle server when necessary.

 

 

 

 

INITRANS and MAXTRANS: Specify the initial and the maximum number of transaction slots that are created in an index or a data block. The transaction slots are used to store information about transactions that are making changes to the block at a point in time. A transaction uses only one transaction slot, even if it is changing more than one row or index entry.

INITRANS: 最小并发修改数量。Guarantees a minimum level of concurrency. It defaults to 1 for a data segment and 2 for an index segment, guarantees a minimum level of concurrency. For example, if set to 3, INITRANS ensures that at least three transactions can concurrently make changes to the block. If necessary, additional transaction slots can be allocated from the free space in the block to permit more concurrent transactions to modify rows in the block.

MAXTRANS: 最小并发修改数量。Default value is 255, sets the limit for the number of concurrent transactions that can make changes to a data or an index block. When set, this value restricts use of space for transaction slots and therefore guarantees that there is sufficient space in the block for use by row or index data.

PCTFREE: 最小空闲空间。This parameter specifies for a data segment, the percentage of space in each data block that is reserved for growth resulting from updates to rows in the block. The default for PCTFREE is 10%.

PCTUSED: 最小使用空间。For a data segment, this parameter represents the minimum percentage of used space that the Oracle server tries to maintain for each data block of the table. A block is put back on the free list when its used space falls below PCTUSED. The free list of a segment is a list of blocks that are candidates for accommodating future inserts. A segment, by default, is created with one free list. Segments can be created with a higher number of free lists by setting the FREELISTS parameter of the storage clause. The default for PCTUSED is 40%.

Both PCTFREE and PCTUSED are calculated as percentages of available data space, that is, the block space that remains after deducting the header space from the total block size.

 

Data Block Management

Two methods are available for managing data blocks:

• Automatic segment-space management

• Manual management

 

Automatic Segment-Space Management

• It is a method of managing free space inside

database segments.

• Tracking in-segment free and used space is done

using bitmaps as opposed to using free lists.

• This method provides:

– Ease of management

– Better space utilization

– Better performance for concurrent INSERT operations

 

 

Automatic Segment-Space Management

• Bitmap segments contain a bitmap that describes

the status of each block in the segment with respect

to its available space.

• The map is contained in a separate set of blocks

referred to as bitmapped blocks (BMBs).

• When inserting a new row, the server searches the

map for a block with sufficient space.

• As the amount of space available in a block

changes, its new state is reflected in the bitmap.

 

 

 

Configuring Automatic Segment-Space Management

• Automatic segment-space management can be

enabled at the tablespace level only, for locally

managed tablespaces.

CREATE TABLESPACE data02

DATAFILE ‘/u01/oradata/data02.dbf’ SIZE 5M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K

SEGMENT SPACE MANAGEMENT AUTO;

• After a tablespace is created, the specifications

apply to all segments created in the tablespace.

 

Bitmapped segments are specified through the SEGMENT SPACE MANGEMENT AUTO clause of the CREATE TABLESPACE command, which cannot be subsequently altered.Any specifications of PCTUSED, FREELIST, and FREELIST GROUPS are ignored if defined.

Segments that can be bitmap-managed are regular tables, indexes, index-organized tables (IOT), and LOBs.

 

Manual Data Block Management

• Allows you to configure data blocks manually using

parameters such as:

– PCTFREE

– PCTUSED

– FREELIST

• The only method available in previous Oracle

versions

 

 

 

视频中，布老师用了两个命令：delete from 表名称和truncate table 表名称来删除一个表中的数据，前者删除所有内容后不会将watermark回位，后者可以。

 

 

 

 

 

 

这个图的主要意思：

当pctfree大于20%，insert会占用freelist所指示的空间，当pctfree小于20%时，freelist就会关闭，insert就不能占用freelist空间，insert就会失败或者只能在已经使用的block中寻找可用空间。当数据被删除，pctused小于40%，db会自动打开freelist，后续的insert会占用freelist所指示的空间。

这个机制主要保护原来已有数据的update行为。

Pctfree的值取决于db中的数据行为，如果数据文件频繁更新，并且row的大小会增加，则pctfree就需要设置大一些儿，pctused就要小一些。

 

 

Obtaining Storage Information

Information about storage can be obtained by

querying the following views:

• DBA_EXTENTS

• DBA_SEGMENTS

• DBA_TABLESPACES

• DBA_DATA_FILES

• DBA_FREE_SPACE

 

标准块)。内存的一页就是磁盘的一个块
4，规定block存储结构的参数

 

5, 块管理的2种方式：自动和手动
high_water_mark

6，设置打开统计信息：set autotrace on stat（课本第29页）
手动管理block：
插入1000000条记录，实验块管理、water 查看统计信息的物理读取块次数physical read
使用track table就会删除表的信息的同时把水位记录拉回删除的位置（区别于delect）

7，pctfree 参数表示如果一个块里面的剩余空间少于这个百分比，至这个块就不在freelist里面
pctused 如果一个块已用的空间小于这个数，就加到freeist
自动管理block方式只有pctfree起作用

high pctfree,low pctused  :数据经常发生变化的

low pctfree,high pctused:数据不经常发生变化

8，数据字典

创建对象时的extent的关于一些术语（storage语法）：

Initial：指定第一个extent的大小，默认值5个data block大小。

Next：指定第二个extent的大小，默认值5个data block大小。

Pctincrease：默认值50，意思是后续的extent比它前面的大50%，设为0，就是后续等于之前的。

Minextent：最少extent数量

Maxextent：最大extent数量

 

 

 

练习题：

 

2Identify the different types of segments in the database.

注意：selsct distinct

Distinct忽略相同的内容。

3 Write a query to check which segments are within five extents short of the maximum

extents. Ignore the bootstrap segment. This query is useful in identifying any

segments that are likely to generate errors during future data load.

Hints这个提示是不对的

- Select from DBA_EXTENTS.

- Use the segment_name, segment_type, max_extents, extents

keywords.

应该从dba_segments中找。

 select segment_name,segment_type,max_extents,extents from dba_segments where max_extents-extents<5 and ...

4 Which files have space allocated for the EMP table?

从dba_extents中的file id和dba_data_files的fileid是一致的，利用join语句

5

6

7