我的Oracle 9i学习日志（13）-- 存储结构和关系-原创手记-慕课网

存储结构和关系

block大小还有些限制：如果操作系统块大小小于32KB则不能指定DB_32K_CACHE_SIZE的值，如果操作系统块大小的值小于2KB则不能指定DB_2K_CACHE_SIZE的值。

Segments类型:表、分区表、聚簇、索引、Index-Organized Table（IOT）、索引分区、还原segments、临时segments、LOB segments、嵌套表、引导程序segments等。

存储子句优先级：segment>tablespace>oracle default，除了MINIMUM EXTENT 和UNIFORM SIZE等参数之外，因为这些参数无法在segments这一级规定。如果在segment级别没有明确指定参数值则按tablespace的默认设置，如果tablespace级别没有明确指定参数值则按Oracle缺省设置。

注意：

如果存储参数改变了，那么新的选项只适用于还未被分配的segments。

一些参数无法再tablespace级别指定，必须在segments级别指定。

如果最小extent大小在tablespace级别指定，则这个大小会应用到所有这个表空间内extent分配的segments。

extent是表空间内某个段使用的一块空间。在下列情况下被分配：

当segment被创建、扩展或改变；在下列情况下被释放：当segment被删除、改变或Truncated。

extents空闲和已用：

在tablespace创建时Data files的第一块block或头几块blocks被称为头部。

数据库block：

最小的I/O单元

由一个或多个操作系统block组成

在tablespace创建时设定

DB_BLOCK_SIZE指示了默认的block大小

SQL> show parameter db_block_size

NAME TYPE VALUE

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

db_block_size integer 8192

Oracle 9i支持4个不同block size，2KB、4 KB、8 KB、16 KB、32 KB。其中有一个是默认大小，它的值取决于操作系统，并且无法改变。System表空间和所有的temp表空间所用的block大小都是db_block_size大小，被称为标准块大小其他的称为非标准块大小。所以Oracle总共可以支持4种非标准块大小。

不同的block size用于在不同block size的数据库之间转移或传输数据；为了提高性能可在不同存储位置用不同的block size。

每一个块大小都对应一个DB CACHE SIZE参数，用来指定对应的缓存大小，以granule为单位分配。默认48MB。为了保持向后兼容，参数DB_BLOCK_BUFFERS依然可以使用，但是是静态参数。

Granule是虚拟内存分配的基本单位，大小取决于SGA_MAX_SIZE的值，SGA大小<128M那么granule就为4MB，其他则为16MB。

如果使用了非标准块，如4KB，则必须指定相应的cache大小，这里即DB_4K_CACHE_SIZE。DB_nK_CACHE_SIZE的值默认为0。但其中的标准块大小对应的这个参数的值不要指定，由DB_CACHE_SIZE指定。操作系统平台相关的

SQL> show parameter db_cache_size

NAME TYPE VALUE

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

db_cache_size big integer 33554432

SQL> show parameter db_cache_size

NAME TYPE VALUE

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

db_cache_size big integer 33554432

SQL> show parameter db_2k

NAME TYPE VALUE

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

db_2k_cache_size big integer 0

SQL> show parameter db_4k

NAME TYPE VALUE

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

db_4k_cache_size big integer 0

SQL> show parameter db_8k

NAME TYPE VALUE

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

db_8k_cache_size big integer 0

SQL> show parameter db_16k

NAME TYPE VALUE

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

db_16k_cache_size big integer 0

SQL> show parameter db_32k

NAME TYPE VALUE

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

db_32k_cache_size big integer 0

SQL> show parameter db_block_buff

NAME TYPE VALUE

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

db_block_buffers integer 0

创建非标准块大小表空间：

SQL> alter system set db_4k_cache_size=16777216 scope=memory;

alter system set db_4k_cache_size=16777216 scope=memory

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00384: Insufficient memory to grow cache

SQL> alter system set db_4k_cache_size=16777216 scope=spfile;

System altered.

SQL> shutdown

SQL> startup

SQL> show parameter db_4k

NAME TYPE VALUE

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

db_4k_cache_size big integer 16777216

SQL> create tablespace b4k datafile

2 '/u01/oradata/lty/ts/b4k.dbf'

3 size 10m blocksize 4k;

TABLESPACE_NAME BLOCK_SIZE

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

B4K 4096

规则：

• 分区对象的所有分区必须位于具有相同块大小的表空间中。

• 所有临时表空间必须采用标准块大小，包括用作缺省临时表空间的永久表空间。

• 按索引组织的表溢出(overflow)和外部LOB 段可以存储在块大小与基表不同的表空间中。

数据库块

块头：包含块地址，表目录，行目录和事务槽。块头增长方向是从上往下。

数据：数据增长方向为从下往上。

空闲空间：开始时连续的，在存数据后经过删除等操作可能变成不连续，在需要的时候可以让Oracle server进行合并。

几个主要参数:

Initrans与maxtrans：指定能同时对数据块进行更改的事务的最小数和最大数，initrans默认值为1（对数据）或2（对索引）。Maxtrans默认为255.

Pctfree与pctuserd：参见后面内容。

数据块管理方式：自动管理和手动管理两种方式，默认为手动管理。

自动段空间管理

• 一种在数据库段内管理空闲空间的方法。

• 对段内空闲和已用空间的跟踪是使用位图完成的（与使用空闲列表相对）。

• 此方法提供了：

– 更方便的管理：PCTUSED、FREELISTS、FREELIST GROUPS 均是自动管理的。

– 更高的空间使用率，所有对象都可以更有效地使用空间，尤其是行大小变化很大的对象。

–改进了对并发访问变化的运行时调整，改进的并发INSERT 操作性能。

• 限制：不能用于包含LOB 的表空间。

• 位图段包含一个位图，它描述了与段中的可用空间相关的每个块的状态。

• 该映射包含在单独的一组块中，这些块称为“位图块”(BMB)。

• 插入新行时，服务器就会在该映射中搜索具有足够空间的块。

• 当块中的可用空间数量发生变化时，位图中就会反映出它的新状态。

配置自动段空间管理

• 自动段空间管理仅能在表空间级别启用，用于在本地管理的表空间。

• 创建表空间后，这些规格将应用于在该表空间中创建的所有段。

CREATE TABLESPACE data02

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

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K

SEGMENT SPACE MANAGEMENT AUTO;

位图段是通过CREATE TABLESPACE 命令的SEGMENT SPACE MANGEMENT AUTO

子句指定的，此后不能更改这些段。如果定义了PCTUSED、FREELIST 和FREELIST GROUPS，则将其全部忽略。

可以用位图管理的段为：规则表、索引、按索引组织的表(IOT) 以及LOB。

图四

开始时块是空的会存在freelist里，当有数据要插入时，Oracle会搜索freelist找到合适的块将数据插入。当块的空闲空间<pctfree%（默认值为10）时就会脱离freelist，剩余空间仅用于自身数据的更新。当块内数据由于删除等原因，被使用的空间会缩小，当被使用的空间<ptuserd%（默认值为40）时，块又会从新回到freelist上。

对于MSSM（Manual segment-space management）有一个高水位：

图5

1、连续的块组成的空间。

2、存储了数据后，高水位线随之移动。

3、后又删除了一些数据，但高水位线不会回移。

4、当有新数据插入时，首先搜索灰色的那部分空间，如果没有合适的块可供数据插入，则会使用黄色的那部分，如果黄色的那部分不够则会使用白色的那部分，同时高水位线相应后移。

对于ASSM（Automatic segment-space management）还有个低水位线

图6

实验：验证手动管理方式的高水位线。

设置AUTOTRACE：AUTOTRACE 是SQL*Plus 中一个工具，可以显示所执行查询的解释计划（explain plan）以及所用的资源。

在sysdba权限下执行：

SQL> @ORACLE_HOME/rdbms/admin/utlxplan

Table created.

SQL> create public synonym plan_table for plan_table;

Synonym created.

SQL> grant all on plan_table to public;

Grant succeeded.

SQL> @/u01/oracle/sqlplus/admin/plustrce

SQL> grant plustrace to dba with admin option;

以luo用户登录，用户表空间为luo：

SQL> select tablespace_name, segment_space_management from user_tablespaces;

TABLESPACE_NAME SEGMEN

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

LUO MANUAL

17 rows selected.

SQL> select * from user_objects;

no rows selected

SQL> select segment_name from user_segments;

no rows selected

#表空间里很干净。

创建表：

SQL> create table test(id integer, name char(10));

Table created.

SQL> select count(*) from test;

COUNT(*)

----------

开启autotrace：

SQL> set autotrace on stat

查询：

SQL> select count(*) from test;

COUNT(*)

----------

Statistics

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

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

378 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

插入10万条记录：

SQL> begin

2 for i in 1 .. 1000000

3 loop

4 insert into test values(i, 'luo');

5 end loop

6 ;

7 commit;

8 end;

9 /

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

COUNT(*)

----------

1000000

Statistics

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

2 recursive calls

0 db block gets

5785 consistent gets

2880 physical reads

173340 redo size

379 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

删除表里的记录：

SQL> delete from test;

1000000 rows deleted.

Statistics

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

552 recursive calls

1033970 db block gets

3172 consistent gets

2631 physical reads

249849916 redo size

625 bytes sent via SQL*Net to client

516 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1000000 rows processed

SQL> select count(*) from test;

COUNT(*)

----------

Statistics

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

0 recursive calls

0 db block gets

2896 consistent gets

2851 physical reads

0 redo size

378 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

#删除记录后还是会扫描块。

SQL> commit;

Commit complete.

SQL> truncate table test;

Table truncated.

SQL> select count(*) from test;

COUNT(*)

----------

Statistics

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

0 recursive calls

1 db block gets

5 consistent gets

0 physical reads

40 redo size

378 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

#truncate可以使高水位线归零。

获得信息：

• DBA_EXTENTS

• DBA_SEGMENTS

• DBA_TABLESPACES

• DBA_DATA_FILES

• DBA_FREE_SPACE

练习:

1As user SYSTEM, run the lab09_01.sqlscript to create tables and indexes.

解析：http://www.hpux.co.kr/oracle_env/oracle_edu_script/ADM9iLABS/

在以上网站可以下载到lab09_01.

2 Identify the different types of segments in the database.

解析：select distinct segment_type from dba_segments;

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_segments

- Use the segment_name, segment_type, max_extents, extents keywords.

解析：select segment_name, segment_type, max_extents, extents from dba_segments where extents+5 > max_extents and segment_type <>’cache’;

4 Which files have space allocated for the EMP table?

解析：可根据dba_segments和dba_data_files

5 Run the lab09_05.sql script.

同1题

6 List the free space available by tablespace. The query should display the number of fragments, the total free space, and the largest free extent in each tablespace.

解析：select tablespace_name,count(*) as fragments,sum(bytes) as total,max(bytes) as t from dba_free_space group by tablespace_name;

7 List segments that will generate errors because of lack of space when they try to allocate an additional extent.

解析：select s.segment_name,s.segment_type,s.tablespace_name,s.next_extent from dba_segments s where not exists (select 1 from dba_free_space f where s.tablespace_name = f.tablespace_name having max(f.bytes) > s.next_extent)

笔记Oracle学习oracle技术