Oracle数据结构

Oracle的数据存储结构：

  • 表空间（tablespace）：Oracle中最大的逻辑存储单位

    数据表空间和索引表空间的分开存储，并不一定能够提高执行效率，只是方便数据的管理。真正影响执行效率的是磁盘IO通道。

  • 数据文件（data file）：表空间物理存储载体（裸设备除外）

  • 段（segment）：Oracle中所有占用空间的对象总称

  • extent：段的组成单位

  • 数据块（data block）：extent的组成单位，是Oracle存储和数据操作的最小单位

数据块

结构：

  • 块头

    • 数据块地址

    • 表目录

    • 行目录

    • ITL SLOT

      ITL（Interest Transaction List）：感兴趣的事务列表 。用来记录事务修改的当前数据块中的数据行的锁。

一个数据块中会有多个ITL Slot。

  • 数据

    • Free
    • Used

数据块的大小可调，64位服务器最大可以设置到32K。32位服务器最大可以设置到16K。

数据块的块头有多个 ITL slot。

数据块的数据行部分会有多个数据行，数据块有 blockID，行有RowID。

数据块中的数据行

结构：

  • Row Header（行头）

    • Row Overhead（行的开销）
    • 列数量
    • 簇ID（如果是聚簇的话）
    • RowID of Chained Row Pleces（if any）

  • Column Data

    • 列长度
    • 列值

基于RowID的数据查询性能非常好：

  1. select rowID from t where a=1;

RowID的格式：

以 rowID 为 AAAPecAAFAAAABSAAA 为例

  1. 前5位，数据对象号：dba_objects视图中可以查到该对象号
  2. Data Object Number: AAAPec
  4. 接着3位，文件号
  5. Relative File Number: AAF
  7. 接着6位，数据块号
  8. Block Number: AAAABS
  10. 最后3位，数据行号
  11. Row Number: AAA

根据数据对象号、文件号、数据块号、数据行号可以唯一的定位到一行数据。

可以通过 DBMS_ROWID 包查看、转换RowID，示例：

  1. select 
  2. t.*,rowid,
  3. dbms_rowid.rowid_object(rowid),  -- 通过rowID获取对象号
  4. dbms_rowid.rowid_relative_fno(rowid),  -- 通过rowID获取数据文件号
  5. dbms_rowid.rowid_block_number(rowid),  -- 通过rowID获取数据块号
  6. dbms_rowid.rowid_row_number(rowid)  -- 通过rowID获取数据行号
  7. from TBL_EMPLOYEE t;
  9. -- 查看该行数据所对应的表名
  10. select * from dba_objects where object_id='';
  11. -- 查看该行数据对应的数据文件名
  12. select * from dba_data_files where file_id='';

数据块结构

Oracle数据块的大小：

  • 2K
  • 4K
  • 8K（默认值）
  • 16K（32位服务器最大可以设置16K）
  • 32K（64位服务器最大可以设置32K）

对于一次需要读取大量数据的OLAP系统，数据块设置的大一些,可以减少一些IO。性能会好一些，

OLTP系统中，可以把数据块设置的小一些。OLTP系统操作数据块比较频繁，数据块上存储有锁的信息，如果大量数据存储在同一个数据块上，就可能在一个数据块上出现很多锁。而且数据块越小，产生的redo的数据块也会越小。

数据块存储属性

数据块的存储属性的设置堆OLTP系统影响很大，对OLAP系统影响不大。

PctFree：（Free Percent）当数据块中的数据量达到这个值时，将不允许继续插入数据。

PctUsed：（Used Percent）当数据块中数据占用空间小于这个比例时，数据块会被再次使用。

Oracle会维护一个 FreeList 空闲列表，空闲列表中存放空闲的（有空间可以插入数据的）数据块，oracle插入数据时会到FreeList中查找空闲数据块进行插入。

例如：PCTFREE=20，PCTUSED=40

  1. 数据块占用容量小于80%时，才可以进行数据插入。因为PCTFREE参数限定必须保留20%的可用空间用于块内已有数据的更新。

    当数据块容量大于20%时，Oracle会将此数据块从 FreeList 中移除，所以无法继续向该数据块中插入数据。

  1. 对数据块中已有数据的更新操作可以使用数据块中的保留空间。只有当数据块内的占用空间比例低于40%才能向其中插入数据。

    当数据块容量大于20%时，还可以对数据块中的数据进行update操作，update之后如果数据大小变大，可以继续使用剩余的20%的空间。

如果该数据块发生了delete，数据块中的数据量变小，但是即使数据块中的数据量delete到小于80%，依然无法插入新数据。

只有数据块中的数据量delete到40%以下时，oracle才会将该数据块重新放到 FreeList中，此时才可以向该数据块中插入新数据。

数据块在FreeList中的流程如下：

PctUsed参数已经不再经常使用了，因为Oracle使用了ASSM（自动段管理）。

行链接和行迁移

行链接：（Row Chaining），如果一个数据行在数据块中放不下了，那么这个数据行能放下的部分还在该数据块中存放，后面会加一个指针链接，将剩下的放不下的部分存入另一个数据块。

行迁移：（Row Migration），如果一个数据行在数据块中放不下了，就在这个数据块中存放一个指针链接，然后将这个数据行整个迁移存入另一个数据块中。

行链接通常发生在 insert 语句中：当需要插入的一行数据的数据量超过了整个块的大小（例如要在4K的数据块中插入一条16K的数据），此时会发生行链接。

行迁移通常发生在 update 语句中：update的字段值比较大，当前的数据块中存放不了这一行数据，但是其他数据块中可以存放，则发生行迁移。行迁移后 rowID不会发生改变，只是这个rowID指向的数据块行只存放了一个指针指向另一个块的数据行。

数据块空闲空间的合并

对OLTP有影响，对OLAP影响不大。

可以手工对数据块进行空闲空间合并（将数据块中的碎片的空闲空间合并到一块），Oracle会在以下情况下自动进行空闲空间合并：

  • 当一个插入或者更新操作的行在一个数据块中有足够的空闲空间

  • 并且这个空闲空间是碎片状态，无法满足一行数据的使用

    数据块中的空闲空间不连续，呈现碎片化空闲空间

Oracle不总是自动整理碎片（合并空闲空间），因为这会导致一定的系统资源开销。

索引数据块的整理

合并索引的数据块碎片，相关SQL：

  1. -- 合并同一个Branch的数据块
  2. alter index COALESCE;
  3. -- 重建整个索引段
  4. alter index REBUILD

块的读操作

逻辑读（logical reads）：从内存中读取数据块。包括 current reads（DB block gets） 和 consistent reads。

物理读（physical reads）：从磁盘读取数据块到内存

逻辑读可能会触发物理读，但是逻辑读不包含物理读。

清空内存中的数据，可以使后面的查询强制发生物理读：

  1. alter system flush buffer_cache;

单块读：（Single block read）每次从磁盘读取一个数据块。

对应的等待事件： db file sequential read

通常是使用主键等索引或者操作数据块header时，会使用单块读。

多块读：（multi-blocks-read）每次从磁盘读取多个数据块。

对应的等待事件：db file scattered read

每次读取的数据块由参数 DB_FILE_MULTIBLOCK_READ_COUNT 确定。

不通过索引而进行全表读时会使用多块读。

LRU（Least Recently Used）

LRU：维护一个列表，区分使用比较频繁的数据和不经常使用的数据

LRU使用中部插入算法，数据从中部插入列表。

LUR列表分为热端和冷端。

中部插入点为冷端的头部，也就是热端的尾部。

Oracle通过touch count对数据被访问的次数进行计数，被访问次数越多的数据越向热端移动。

数据块Touch Count

Touch Count存放在数据块头中。每一个 buffer（内存数据块）头中，包含以下信息：

  • touch count
  • timestamp（SCN）

在每3秒钟内，数据块从以下两种情况以来，被touch的次数：

  • 数据块最后一次被读入内存，即touch count的开始
  • touch count被重置（reset）

当buffer达到冷端的最尾端时：

  • 如果touch count >= 2，buffer将被转移到热端
  • 否则将被作为空闲buffer被使用

单数据块读

示例：

开始时，buffer中的数据块：

单数据块读取时，buffer头部也存储SCN，但是此处不关心SCN值，主要关心TouchCount。

Block编号 92 34 72 45 中间插入点 52 71 66 49
Touch Count 0 3 4 2 左边热端，右边冷端 1 2 0 0

发出一个查询，查询42号数据块。

42号数据块在buffer中不存在，冷端最末尾的49号数据块的 TouchCount为0，所以该块空间将被重用。

从磁盘读取42号数据块的内容，擦除buffer中49号数据块占用的空间，将42号数据块内容写入buffer中（从中间插入点插入），并将buffer中42号数据块TouchCount设置为1。

Block编号 92 34 72 45 中间插入点 42 52 71 66
Touch Count 0 3 4 2 左边热端，右边冷端 1 1 2 0

发出一个查询，查询11号数据块。

11号数据块在buffer中不存在，同样的，擦除66号数据块在buffer中占用的空间，从中间插入点将11号数据块内容插入到Buffer中，设置TouchCount为1。

Block编号 92 34 72 45 中间插入点 11 42 52 71
Touch Count 0 3 4 2 左边热端，右边冷端 1 1 1 2

发出一个查询，查询42号数据块。

42号数据块位于Buffer中，将其Touch Count加1。

Block编号 92 34 72 45 中间插入点 11 42 52 71
Touch Count 0 3 4 2 左边热端，右边冷端 1 2 1 2

发出一个查询，查询33号数据块。

33号数据块在buffer中不存在。

但是冷端最末尾的71号数据块TouchCount为2，Oracle会将71号数据块移动到热端头部，并将其TouchCount重置为0重新计数。

Block编号 71 92 34 72 中间插入点 45 11 42 52
Touch Count 0 0 3 4 左边热端，右边冷端 2 1 2 1

此时冷端最尾端的52号数据块的TouchCount为1，可以从Buffer中擦除。

将33号数据块从磁盘中读出，从中间插入点插入进buffer中。

Block编号 71 92 34 72 中间插入点 33 45 11 42
Touch Count 0 0 3 4 左边热端，右边冷端 1 2 1 2

一致性读

示例：

开始时，buffer中的数据块：

此处演示不同SCN的查询，表格中忽略TouchCount的值

Block编号 40 56 中间插入点 17 34 27 95 33 85
SCN号 左边热端，右边冷端 150

发出一个查询，需要查询之前时间的数据块，利用Undo重构SCN为132时的27号数据块。

擦除buffer中85号数据块的位置，将SCN为150的27号数据块复制一份。

将复制出来的27号数据块，利用 Undo 进行回滚重构，回滚到SCN为132时的值。不允许直接操作buffer中原来的SCN为150的27号数据块，否则会阻塞写操作。

将得到的SCN为132的27号数据块插入到buffer中。（buffer中存储不同SCN的同一个数据块，叫做数据库的多版本）

Block编号 40 56 中间插入点 27 17 34 27 95 33
SCN号 左边热端，右边冷端 132 150

多数据块读

多数据块读时，如果设置 DB_FILE_MULTIBLOCK_READ_COUNT 为 4，则一次读取4个数据块。

示例流程如下：

  1. 同单块读一样，Oracle从Buffer中获取冷端尾部获取4个数据块的位置

  2. 从磁盘上读取4个数据块到buffer内存中，从1号到4号依次插入到冷端的开始位置 | Block编号 | x | x | 中间插入点 | 4数据块 | 3数据块 | 2数据块 | 1数据块 | x | x | | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- |

  1. 由SQL执行读操作，从1号到4号依次读取buffer中的这4个数据块，但是一个数据块读取完成后，会立即被移动到冷端的最尾端。所以4个数据块读取完成后，Buffer为： | Block编号 | x | x | 中间插入点 | x | x | 1号数据块 | 2号数据块 | 3号数据块 | 4号数据块 | | —- | —- | —- | —- | —- | —- | —- | —- | —- | —- |

因为多数据块读通常发生在没有索引的全表读，Oracle认为这种读取的结果只会用这一次，没有再用的价值，不会被经常使用，所以读取完成后立即移动到了冷端的最尾端，而不再去考虑统计TouchCount。

extent

extent是由一组连续的数据块组成，多个extent组成一个段（segment）。

当段创建时，Oracle会为它分配一个初始extent。当初始extent满了之后，Oracle会继续分配后续的extents。

一个段的extents可以分布在多个数据文件上，也可以通过SQL指定。

extents扩展的数量有两种模式：ASSM（Oracle自动段管理）、MSSM（手工指定方式）

段—segment

在Oracle中，凡是分配了空间的对象，都称之为段：

  • 表，表分区（对于一个分区表来说，数据是放在表分区上，一个表分区是一个段，表本身只是一个标签）
  • 索引，索引分区
  • 大对象（LOB）（例如表中有一个Clob字段，Oracle会为这个Clob字段单独分一个段）

段分为：

  • 数据段
  • 临时段（排序等操作需要一个中间状态，这些中间状态数据放在临时段，这些数据不记录到redo log中）
  • 回滚段

临时段

临时段（临时表空间）：

  • 排序、hash、merge….（需要一个中间数据处理区域）

    创建索引，order by，hash join，merge join

  • 只有在内存空间不足时，Oracle才会在临时表空间上创建临时段

    排序等操作本身是可以放在内存的PGA区中的sort area中，用来做排序。但是内存中的Sort Area可能不是很大，需要使用磁盘上的临时表空间。

  • 临时段上的操作并不记录 Redo Log

临时表

临时表是临时段的一种。

临时表只存在于某个会话或者事务的生命周期里，此时临时表中的数据只对当前这个会话可见。

临时表经常被用于存放一个操作的中间数据（数据处理的中间环节）。

临时表由于不产生redo，能够提高数据操作的性能。

临时表的创建：

  1. -- 创建临时表
  2. create global temporary table t_temp
  3. on commit delete rows  -- 设置提交时的操作
  4. as
  5. select * from dba_objects;

on commit的设定：

  • delete rows：临时表中的数据是基于事务的，当事务提交或者回滚后，临时表中的数据将被清空

  • preserve rows：临时表中的数据是基于会话的，当会话结束时，临时表中的数据被清空。

示例：

  1. -- 设置提交后动作为delete rows
  2. create global temporary table t_temp on commit delete rows as select * from dba_objects;
  4. -- 此时查询到的结果为0条记录。
  5. -- 虽然创建临时表时指定将dba_objects数据插入了临时表，但是因为create语句是ddl操作，ddl操作隐含了commit操作，所以临时表创建后等同于进行了commit，又因为设置的提交后动作为delete rows，所以此时查询到的数据为0条
  6. select count(*) from t_temp;
  8. -- 向临时表中插入数据
  9. insert into t_temp select * from dba_objects;
  11. -- 此时可以查询到临时表中的数据
  12. select count(*) from t_temp;
  14. -- 事务提交
  15. commit;
  17. -- 提交后，临时表中的数据被清空
  18. select count(*) from t_temp;

临时表也可以创建索引：

  1. -- 在临时表上创建索引
  2. create index idx_t_temp on t_temp(object_id);

临时表的位置：

临时表的位置无法通过user_tables查看其所在的表空间。

临时表上的索引无法通过user_indexes查看其所在的表空间。

  1. -- 可以查询到临时表的信息，但是看不到其所在的表空间
  2. select * from user_tables where table_name='T_TEMP';
  4. -- 可以查看到索引的信息，但是看不到其所在的表空间
  5. select * from user_indexes where index_name='IDX_T_TEMP';
  7. -- 在段信息表中查询不到这两个对象
  8. select * from user_segments where segment_Name in ('T_TEMP','IDX_T_TEMP');

段数据的压缩

Oracle允许对段进行压缩。通过将字段值中重复数据做关联实现的压缩，重复数据越多压缩效果越好。

段压缩的优点：

  • 减少存储空间

  • 减少处理的数据块

    • 减少内存占用
    • 提高I/O速度
    • 提高查询效率

缺点：因为要额外对数据做处理，在数据插入时，会消耗更多的资源和时间。

所以，数据不经常改变的海量数据的OLAP数据库，使用压缩效果比较好。

段数据压缩语法示例：

  1. -- 创建段数据压缩的表
  2. create table t_compress
  3. compress -- 段数据压缩
  4. as select * from dba_objects;
  6. -- 将压缩的表更改为不压缩
  7. alter table t_compress nocompress;
  9. -- 将没压缩的表更改为压缩
  10. -- 该语句使用了move。move是指对表中的数据进行重新排列，可以使用move操作清理表中的碎片。
  11. alter table t_compress move compress;
  13. -- 查看表是否进行了压缩：enabled压缩，disable无压缩
  14. select table_name, compression from user_tables where table_name='T_COMPRESS';

段压缩级别：

  • 表空间级：表空间创建时加了compress属性，则在该表空间上创建表时即使不加compress属性也会默认进行压缩。
  • 表级（索引）
  • 分区
  • 子分区

段的存储管理

段的管理方式：

  • MSSM：(Manual Segment Space Management)，手工设定对象的存储参数（pctFree、pctUsed、freeLists等）
  • ASSM：(Automic Segment space Management)，Oracle自动设定对象的存储参数。手工只可以设定pctFree参数，其他参数由Oracle自动设定

在MSSM方式下，oracle允许用户通过freeLists参数设置freeLsits的多少。并发量表较大时，设置多一些的freeLists可以减少Latch的争用。

MSSM对象创建：

  1. -- 创建手工段管理的表空间
  2. create tablespace ts_mssm
  3. segment space management manual;
  5. -- 创建表，并指定pctFree、pctUsed等参数
  6. create table t_m
  7. (
  8.     id int
  9. )
  10. pctFree 20
  11. pctUsed 20
  12. storage(freeLists 20 next 5M)
  13. tablespace ts_mssm;
  15. -- 查询表的相关属性
  16. Select pct_free,pct_used,freelists from user_tables where table_name='T_M';

手动段管理优点：

  • 给予DBA更大的空间管理余地
  • 对于一些数据块操作非常敏感的场景依然有用

手动段管理缺点：

  • 设置参数多
  • 参数设定值困难
  • 需要高水平的DBA

自动段管理通过三层BMB（bit map blcok，位图块）方式管理空间。

ASSM段头（Level 3 BMB）包含了每个extent的存储信息。

会话要插入数据时，会先访问 Level 3，Level 3评估每个Level 2的剩余空间之后，将会话分配给指定的Level 2，Level 2评估下属的每个Level 1的剩余空间后，将会话分配给指定的Level 1，Level 1将要插入的数据写入空闲的数据块。

自动段管理优势：

  • 简化管理：ASSM避免了手工设置大量的存储相关的参数，除了唯一的 pctFree
  • 增大并发度：如果有多个事务并发访问一个段，对于MSSM，由于使用 freeList列表进行空间管理，导致大量会话访问这些列表，出现资源争用和等待；ASSM由于不使用 freeList 方式管理空间，会有效避免这种资源争用和等待
  • RAC环境下能改善并发性能（不需要各个实例间互相访问大量的 freeList 表）

自动段管理劣势：

  • FTS（全表扫描）性能可能会下降
  • 大数据的加载会导致性能下降
  • 影响索引的集群因子（clustering factor）

表空间

Oracle 的表空间在逻辑上分为：

  • system表空间
  • sysaux表空间
  • undo表空间
  • 用户自定义表空间
  • temp表空间

物理上分为：

  • data Files
  • temp Files

大文件表空间

普通的数据文件，受到数据块的限制。

数据块受到RowID的限制，每个数据文件最多只能包含 2的22次方 -1 （4M）个数据块：

  • 2k数据块，数据文件最大8G
  • 4k数据块，数据文件最大16G
  • 8k数据块，数据文件最大32G
  • ….

大数据文件，每个数据文件可以使用 2的32次方（4G）个数据块：

  • 2K数据块，数据文件最大8T
  • 4k数据块，数据文件最大16T
  • 8k数据块，数据文件最大32T
  • …..

大文件表空间的优势：

  • 减少数据库的数据文件个数（每个数据库64K个数据文件）
  • 方便文件的管理，不需要人工干预表空间的文件大小
  • 减少数据库对文件头同步的开销

创建语句：

  1. -- 创建大文件表空间
  2. create bigfile tablespace ts_big;
  4. -- 创建表
  5. create table t_big
  6. tablespace ts_big
  7. as select * from dba_objects;

表空间的管理方式

管理方式：

  • 本地管理表空间（Locally Managed Tablespaces）
  • 字典管理表空间（Dictionary-Managed Tablespaces）

本地管理表空间可以分为：ASSM、MSSM。

字典管理表空间：（已经过时不用了） 表空间中存储的信息记录在Oracle数据字典（System表空间）中，凡是DML操作，都需要访问System表空间和相关的视图。

创建语句：

  1. create tablespace ts_dictionary
  2.  datafile '/xxx.dbf' size 50M
  3.  extent management dictionary  -- 字典管理表空间
  4.  default storage(
  5.      initial 50k
  6.      next 50k
  7.      minextents 2
  8.      maxextents 50
  9.      pctincrease 0
  10.  );

本地管理表空间的优点：

  • 避免通过查询数据字典获得表空间的存储信息
  • 自动跟踪表空间上数据的变化并进行调整
  • 自动确定表空间上extent的大小

表空间的extent管理方式

extent管理方式：

  • autoAllocate：由Oracle自动决定新分配的extent的大小
  • uniform：统一extent尺寸

设置extent管理方式语句：

  1. -- 创建统一extent尺寸的表空间
  2. create tablespace ts_uni
  3. extent management local uniform
  4. size 2M;
  6. -- 创建自动管理extent大小的表空间
  7. create tablespace ts_auto
  8. extent management local autoallocate;

表空间的存储属性

数据管理方式：

  • local：本地管理表空间
  • dictionary：字典管理表空间

段管理：

  • ASSM：自动段管理
  • MSSM：手动段管理

extent管理：管理每一次扩展时，下一个extent的大小

  • autoallocate（或者system）：自动管理
  • uniform：统一尺寸

查询表空间信息：

  1. select * from user_tablespaces;

返回结果：

  • next_extents：每一次扩展时下一个entent的大小（如果是autoallocate自动分配，则该项为空）
  • allocation_type：extent分配方式，system表示自动管理，uniform表示统一尺寸
  • segment_space_management：段管理方式，auto自动管理（ASSM），manual手工管理（MSSM）
  • extent_management：表空间数据管理方式，local本地管理表空间，dictionary字典管理表空间

默认属性：

创建表空间时，所有参数都使用默认参数：

  1. create tablespace test;

创建的表空间的数据文件位置，位于参数db_create_file_dest中。

extent管理方式是system（autoallocate）。

段管理方式是auto（ASSM）。

表空间管理方式是Local。

表空间初始尺寸65536（6k）。

表空间的段属性

段对象的存储属性可以直接从所在的表空间上继承过来，也可以在段对象上单独设定自己的属性。

  1. -- 表（段对象）创建时，不指定段属性，可以直接从其所在的表空间上继承过来
  2. create table t1
  3. tablespace test
  4. as select * from dba_objects;
  6. -- 表（段对象）创建时，也可以单独指定自己的段属性
  7. create table t2
  8. storage(initial 2M next 2M)
  9. tablespace test 
  10. as select * from dba_objects;