oracle学习笔记 存储结构_段区块

这节课讲oracle的段区块

一）段区块的两个知识点

第一个知识点 区的分配

一个段建完以后即create table建完以后
这时oracle会给它分配一个区

区就是物理上连续的多个块
一般我们来讲是八个块一个区

这个段有了8个块以后
这八个块基本上都是空的块

这个表建了段以后没有数据
然后我们执行insert
最后空间满了以后
我们需要再给它分配另外一个区
我们再给它分配一个区也是八个块
接着也在使用

第二个知识点 段空间管理方式

我们把一个区给一个段以后，段有了空闲空间了
然后在这个基础上，段如何使用里面的空闲空间

二）使用EM建立表空间

把IE打开

我们用EM建表空间
讲一下段和区的管理方式

sys用户以SYSDBA身份登录EM

进入EM后
进入 管理 选项卡

oracle的EM的管理用的很多

我们打开
数据库管理部分
存储 下的
表空间

在表空间网页默认列出了当前有的表空间
可看到系统已经有了很多的表空间

下面创建一个表空间
点击表空间页的 创建 按钮

进入 创建 表空间 网页
这里有创建表空间的选项

1） 一般信息 标签页

名称 输入 data1

区管理 部分
设置区的管理方式，就是给段如何分配区
它有两种方式：
第一种方式叫本地管理
第二种方式叫字典管理

区管理方式有两种
一种是本地一种是字典
我们现在不要用字典了都用本地

表空间名字有了
我们知道我们建的表空间是永久表空间是普通表空间
就是在类型部分选择
永久

状态部分选择
读写
就是在表空间上我们可以读可以写

数据文件部分
有个 使用大文件表空间 选项

大文件表空间（bigfile tablespace）是在Oracle 10g中推出的
是相对传统的默认创建的小文件表空间（smallfile tablespace）而说的
最显著的差别就是一个大文件表空间只能对应一个数据文件，并且不允许再加入新的数据文件
一个Oracle数据库可以同时包含大文件表空间和小文件表空间

smallfile tablespace最多可以包含1024个数据文件，每个datafile 有最多4M个data blocks
Bigfile Tablespace只对应一个数据文件，但是文件可以达到 4G 个数据块大小，这个数据文件的最大容量是小数据文件的1024倍
以data blocks大小是8KB计算
smallfile tablespace中的datafiles最大是8KB*4M=32GB，而一个小文件表空间最多有1,024个datafiles，所以一个小文件表空间最大32GB*1024=32TB
bigfile tablespace 虽只有一个数据文件但最大也可以达到 块大小乘以最多块数 8KB*4G=32TB
所以这两种表空间的最大总容量大小是一样的

由于oracle每个数据库最多使用64K个数据文件
因此使用大文件表空间时数据库中表空间的极限个数是使用小文件表空间时的1024倍
使用大文件表空间时的总数据库容量也是使用小文件表空间时的1024倍

虽然引入了大文件表空间它的使用还是有限制的
SYSTEM和SYSAUX tablespaces总是smallfile tablespaces
由用户创建的非默认的表空间，必须使用local本地extent管理方式，段segment空间必须使用auto自动方式，才能使用大文件表空间
但这两个条件在Undo或者临时Bigfile表空间的时候，是允许例外的

虽然大文件表空间中的数据文件基本上可以无限大，但是也要有可以与它对应的存储空间与之对应
当表空间正在使用的磁盘组可能没有足够的空间，且扩展表空间的唯一办法是向另一个磁盘组加入数据文件时，应避免使用大文件表空间。
大文件表空间应该和自动存储管理（Automatic  Storage Management）或其他逻辑卷管理工具（logical volume manager）配合使用，
这些工具应该能够支持动态扩展逻辑卷，也能支持striping（数据跨磁盘分布）或RAID

我们知道一个表空间由一个或多个数据文件组成
我们需要给表空间加数据文件

点击数据文件部分的 添加 按钮
进入 添加数据文件 网页

文件名 输入 data_01.dbf
文件目录保持默认
文件大小默认100M
当然实际生产环境里面，我们建的时候一般比较大一些，不可能一百兆

重用现有文件 选项
选中意味着如果在这个目录底下有data_01.dbf这么一个相同名字的文件
就会把原有文件覆盖
这样比较危险

数据文件满后自动扩展 (AUTOEXTEND)
表空间的空间是数据文件空间的总和
比如一个表空间我建了一个表
这个表增加以后
表空间满了数据文件也就满了
如果你选中了数据文件满后自动扩展
这时oracle会自动的扩空间

表空间空间监控维护是oracle DBA的一个重要的工作
虽然简单但是一定要做

一般这个地方在增量里可以选中一百兆
将来空间满了马上给它分配100M
如果100M用完了再分配100M

虽然这个时候看上去
有了这个选项以后以后空间我们可以不管了
因为满了它自动扩张
但是这个选项我们选上
是防止oracle因为空间不够而出现处理数据失败的情况
不要指望它去解决空间问题

我们一定要经常去观察，去查看oracle表空间有没有满
如果是达到85%以上，我们抓紧时间给它增加空间
而不要使用这个选项

我们举一个很简单的例子

一个表在做insert插入数据的时候
表空间的空间不够了
这时数据文件需要自动扩展
这时候oracle要扩张100M出来需要时间
因为发生物理IO了需要时间

这个insert正好到了空间满了
这个insert要成功的话需要这100M空间物理扩出
这时这个insert会一直等着这100M空间扩完
所以orale用户会发现这个insert执行的比较慢
影响用户体验
这个简单理解就行了

oracle表空间
可以选 数据文件满后自动扩展选项
添加100M
只是尽量的不要使用这个选项

点 继续 按钮
返回 创建 表空间 网页
一个数据文件创建完成
数据文件可以添加多个，可以继续按添加按钮添加

这是 创建 表空间 一般信息标签页的内容

2）存储标签页

下面进入存储标签页

区管理用的是本地管理
本地管理 区分配 又有两种
一种自动 一种统一大小
这两个都可以选，两个都没有问题，性能都很好

段空间管理
选 自动
不要选 手动

区管理有数据字典和本地
我们选择本地
本地里面又有自动和统一两种方式
段管理是手工和自动管理
我们选自动

启用事件记录 选 是
就是是否启动redolog

3）生成的SQL

这时点一下
显示SQL 按钮

在 显示 SQL 网页
显示了一个SQL语句：

CREATE SMALLFILE TABLESPACE "DATA1" DATAFILE '/u01/app/oracle/oradata/jiagulun/data1_01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

就是建立表空间的sql语句
我们使用图形界面的时候
虽然点点以后可以直接点执行
但是我的建议是
你选项设置完以后把所有的操作完成以后
最终看一下所有的操作对应的sql语句是什么

通过sql语句，你可以知道oracle要做什么

把网页上的sql语句复制出来
这是建表空间的语句

CREATE SMALLFILE TABLESPACE "DATA1"
DATAFILE '/u01/app/oracle/oradata/jiagulun/data1_01.dbf'
SIZE 100M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO

SMALLFILE表示是小文件类型的表空间

DATAFILE '/u01/app/oracle/oradata/jiagulun/data1_01.dbf'
数据文件是/u01/app/oracle/oradata/jiagulun/data1_01.dbf

SIZE 100M大小是100M 可以复用REUSE
AUTOEXTEND ON可以自动扩张 NEXT 100M每次扩张100M
MAXSIZE UNLIMITED最大扩张无极限
LOGGING在这个表空间的所有操作都会记日志
EXTENT MANAGEMENT LOCAL区的管理方式是LOCAL就是本地
SEGMENT SPACE MANAGEMENT AUTO段的管理方式自动

区的管理本地，本地又分自动和统一
因为自动是默认的，所以上面语句没有写
如果是统一的话上面语句会写UNIFORM SIZE 32K
就是
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32K
这种方式

这样这个语句就可以直接执行了

sql语句看过后返回 创建表空间 网页
点击 确定
这时oracle会建一个表空间出来
过程会慢一些
这时oracle会有IO，从磁盘上划空间出来

4）创建表空间使用的选项的总结

区管理一定要选本地
段管理一定选自动

区管理里面的本地
可以选择本地的自动或本地统一都可以

段选择自动

区管理毫无疑问选本地 本地里选自动和统一都可以
段管理毫无疑问选自动

5）各种选项的详细说明

创建表空间时的各种选项的具体意义有些老师没有讲
本人查了一些资料，简单的说一下

表空间对区的管理有两种方式
字典管理表空间(Dictionary-Managed Tablespace简称DMT)
本地管理表空间(Locally Managed Tablespace简称LMT)

DMT中使用两个字典来记录Extents的使用情况：SYS.FET$记录空闲的Extents，SYS.UET$记录使用的Extents
使用数据字典管理存储空间的分配
当表空间分配新的区，或者回收已分配的区时，ORACLE会对数据字典对应的表进行查询、更新，且使用单线程，速度慢，并且产生回退和重做信息。

LMT是8i以后出现的一种新的表空间的管理模式，
不再利用数据字典表来记录表空间里面的区的使用状况，而是在每个表空间的数据文件的头部加入了一个位图区，在其中记录每个区的使用状况
每个BIT代表数据区，通过改变bit值来表示Extents的分配使用或释放
当表空间分配新的区，或者回收已分配的区时，ORACLE会对文件中的位图进行更新，所以不会产生回滚和重做信息

字典管理的缺点：
并发等待：当并发性很高的时候，将产生数据字典的争用
空间碎片：导致系统性能减弱浪费大量的表空间
本地表空间管理的优势：
不需要使用字典SYS.FET$和SYS.UET$上的递归SQL调用；
减少数据字典的竞争；不会产生UNDO记录；不再需要周期性合并操作。

本地管理的表空间中有两种段空间管理方式：
自动段空间管理（ASSM：auto segment space management）
手动段空间管理（MSSM：manual segment space management）

在oracle9.20以前使用MSSM，表的剩余空间的管理与分配都是由连接列表freelist来完成的
ASSM首次出现在Oracle920里，连接列表freelist被位图所取代，在段的头部使用用来记录位图的数据块BMB（Bitmapped Block）来记录Block的使用情况

MSSM段通过在段头保留数据块用于自由列表freelist，自由列表用来管理对象所使用的剩余区块，并为新数据行提供数据块
ASSM段通过扫描位图来查找可用的block并跟踪或管理每个分配到对象的块

MSSM需要freelists、freelist groups、PCTFREE、PCTUSED参数
而ASSM仅保留了PCTFREE参数

PCTFREE决定块使用了多少就不再用在新行插入了，即什么时候该利用该数据块

段使用自动管理原因：
freelist存在串行的问题，当多个事务并发请求空间时，竞争将会出现，容易引起段头的争用与空间的浪费
但主要还是因为需要DBA花费大量的精力去管理这些争用并监控表的空间利用
而位图是一个二进制的数组，能够迅速有效地管理存储扩展和剩余区块，肯定能够减轻缓冲区忙等待（buffer busy wait）的负担
使用位图数组会显著地消除所有对段头的竞争，还能获得超快的并发插入性能
但ASSM也有一些局限性，如：
大型对象不能够使用ASSM；不能够使用ASSM创建临时表空间；超高容量的DML可能会出现性能上的问题...

性能优劣都是相对的，都是有条件的，应谨慎选择！

三）表空间及段区块的一些sql语句和视图

我的t2表现在只有2行
所以我又建了一个和老师演示的t2表所用块数类似的表test2来演示后面的sql语句

SQL> create table test2(id number,name varchar2(8));

Table created.

SQL> declare  v_sql varchar2(50);
begin  for i in 1..200000 loop
v_sql := 'insert /*hello*/ into test2(id) values (:1)';
execute immediate v_sql using i;
end loop;
commit;
end;  2    3    4    5    6    7
  8  /

PL/SQL procedure successfully completed.

后面用这个test2表替换老师讲的t2表进行演示

1）查询段情况的语句

select segment_name,blocks,extents,bytes,segment_type,tablespace_name
from dba_segments where segment_name='TEST2';

执行结果：
SQL> select segment_name,blocks,extents,bytes,segment_type,tablespace_name
from dba_segments where segment_name='TEST2';  2

SEGMENT_NAME     BLOCKS    EXTENTS      BYTES SEGMENT_TYPE       TABLESPACE_NAME
------------ ---------- ---------- ---------- ------------------ ---------------
TEST2               384         18    3145728 TABLE              SYSTEM

oracle有个段段的名字叫TEST2
这个段占了384个数据块
占了18个区
大小为3145728位就是3M多
段类型SEGMENT_TYPE是一个TABLE表
TABLESPACE_NAME为表空间名这里是SYSTEM系统表表空间里面

结果中区的值分配了多少就是多少，不是实际数据占的区的数目
块和位数是所有已分配区里面的所有的块数和所有的块数所占的字节数
这里的值不是实际数据所占的大小

这个语句结果信息是这个逻辑段的整体信息，是系统给它整体分配的资源数目
它查询的是dba_segments字典表，是和逻辑段相关的
它的结果不是test2表实际数据实时使用了多少物理资源的信息，不是表中具体的数据使用了多少资源

表中真实数据的使用大小可以在 USER_TABLES 或 DBA_TABLES系统字典表中得到
这两个表要得到当前的情况需要对这个要查询信息的表进行统计分析以后数值才会相对比较准确

2）表分析语句

analyze table test2 compute statistics;
或
exec dbms_stats.gather_table_stats('SYS','TEST2');

这两个都是对test2这个表进行统计分析
表插入很多数据以后，数据情况不会马上反馈出来
使用这两个任何一个分析都可以

3）表情况查询语句

SELECT table_name,
NUM_ROWS, --表中的记录数
BLOCKS, --表中数据所占的数据块数
EMPTY_BLOCKS, --表中的空块数
AVG_SPACE, --数据块中平均的使用空间
CHAIN_CNT, --表中行连接和行迁移的数量
AVG_ROW_LEN --每条记录的平均长度
FROM USER_TABLES where table_name='TEST2';

FROM USER_TABLES从USER_TABLES这个表
查一下这个表有多少行NUM_ROWS
BLOCKS有多少块
EMPTY_BLOCKS有多少空块
AVG_SPACE每个块的平均的使用空间
CHAIN_CNT还有多少的行链接和行迁移
AVG_ROW_LEN平均行的长度

通过它我们可以大体知道一个表的一些信息
这个表有多少行，已使用多少块，有多少空块
还有这一个块平均使用空间有多少
等等这些会查出来

在表建立完刚插入数据后还没有对表分析前使用此语句
SQL> SELECT table_name,NUM_ROWS,BLOCKS, EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT, AVG_ROW_LEN
FROM USER_TABLES where table_name='TEST2';  2

TABLE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ------------ ---------- ---------- -----------
TEST2

没有任何的结果说明表中的信息还不是实时的

这时分别使用上面的两个表分析语句分析表并再次查询

a）
分析表
SQL> exec dbms_stats.gather_table_stats('SYS','TEST2');

PL/SQL procedure successfully completed.
查询
SQL> SELECT table_name,NUM_ROWS,BLOCKS, EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT, AVG_ROW_LEN
FROM USER_TABLES where table_name='TEST2';  2

TABLE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ------------ ---------- ---------- -----------
TEST2          199720        305            0          0          0           4

b）
分析表
SQL> analyze table test2 compute statistics;

Table analyzed.

查询
SQL> SELECT table_name,NUM_ROWS,BLOCKS, EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT, AVG_ROW_LEN
FROM USER_TABLES where table_name='TEST2';  2

TABLE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ------------ ---------- ---------- -----------
TEST2          200000        305           78        865          0           8

可看到已有结果，这就是实际数据使用表的空间的情况
但两个分析语句分析出来的数据是有区别的
用analyze table test2 compute statistics;分析表得到的结果较全面和准确。

从结果看test2这个表有
NUM_ROWS为行数是200000行
BLOCKS占用块为305块
EMPTY_BLOCKS空块数为78块
AVG_SPACE平均每块865字节
CHAIN_CNT行链接行迁移是0
AVG_ROW_LEN行的平均长度是8

4）

select blocks,empty_blocks from dba_tables where table_name = 'TEST2';
执行结果：
SQL> select blocks,empty_blocks from dba_tables where table_name = 'TEST2';

    BLOCKS EMPTY_BLOCKS
---------- ------------
       305           78

也要在对表进行实时分析后才有准确结果

这是我们讲的比较有用的sql语句

这些脚本论坛上会有，可以去下载然后去使用

5）相关视图

在老师的讲义中列出了
表的相关数据字典视图
DBA_TABLES
DBA_OBJECT_TABLES
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS

和表空间相关的数据字典视图
1.V$TABLESPACE
2.V$ENCRYPTED_TABLESPACES
3.DBA_TABLESPACES,DBA_USER_TABLESPACES
4.DBA_TABLESPACE_GROUPS
5.DBA_SEGMENTS, USER_SEGMENTS
6.DBA_EXTENTS, USER_EXTENTS
7.DBA_FREE_SPACE, USER_FREE_SPACE
8.DBA_TEMP_FREE_SPACE
9.V$DATAFILE
10.V$TEMPFILE
11.DBA_DATA_FILES
12.DBA_TEMP_FILES
13.V$TEMP_EXTENT_MAP
14.V$TEMP_EXTENT_POOL
15.V$TEMP_SPACE_HEADER
16.DBA_USERS
17.DBA_TS_QUOTAS
18.V$SORT_SEGMENT
19.V$TEMPSEG_USAGE
20.V$SEGSTAT_NAME、V$SEGSTAT、V$SEGMENT_STATISTICS

和其它的一些sql语句

下面这个SQL可以帮助我们找到当前数据库中逻辑读最高的10个对象
select *
from (select object_name, statistic_name, value
 from V$SEGMENT_STATISTICS
where statistic_name = 'logical reads'
order by 3 desc)
where rownum < 11;

不一一演示了

6）dba_extents视图

dba_extents视图可以知道段中的区的使用情况

SQL> desc dba_extents
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER
这是这个视图的结构

语句select * from dba_extents where segment_name='TEST2';
可得到表TEST2的分配的区的一个情况

SQL> select * from dba_extents where segment_name='TEST2';

OWNER SEGMENT_NAME PARTITION_NAME  SEGMENT_TYPE       TABLESPACE_NAME  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
----- ------------ --------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS   TEST2                        TABLE              SYSTEM                   0          1      60793      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                   1          1      60801      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                   2          1      61193      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                   3          1      61201      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                   4          1      61209      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                   5          1      61217      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                   6          1      61225      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                   7          1      61233      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                   8          1      61241      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                   9          1      61249      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                  10          1      61257      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                  11          1      61265      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                  12          1      61273      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                  13          1      61281      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                  14          1      61289      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                  15          1      61297      65536          8            1
SYS   TEST2                        TABLE              SYSTEM                  16          1      61321    1048576        128            1
SYS   TEST2                        TABLE              SYSTEM                  17          1      61449    1048576        128            1

18 rows selected.

区的编号0到17，分配了18个区
所有的区FILE_ID都为1，都在1号文件上

可以通过查，找到1号文件
SQL> select FILE_NAME,FILE_ID from dba_data_files;

FILE_NAME                                             FILE_ID
-------------------------------------------------- ----------
/u01/app/oracle/oradata/jiagulun/users01.dbf                4
/u01/app/oracle/oradata/jiagulun/sysaux01.dbf               3
/u01/app/oracle/oradata/jiagulun/undotbs01.dbf              2
/u01/app/oracle/oradata/jiagulun/system01.dbf               1
/u01/app/oracle/oradata/jiagulun/example01.dbf              5

可知一号文件是system01.dbf

dba_extents视图结果中
BLOCK_ID是块的编号 它在一号文件中块的编号
第一行是0号区，分配的是1号文件的60793块，这个块是起始块
一共分配了BLOCKS块数是8个
第二个区就是1号区，是从1号文件的60801块开始8个块  
0到15号区都是分配了8个块
当分配第17个区就是编号为16的区的时候
oracle连续的分，分了好多的区，
这时候oracle就不是8个8个的分了，一次性的分了128个块
因为oracle发现，既然连续分了16个区了
oracle认为你这个数据文件将来会很大，也许给多分点区
这是区的自动管理
如果是区的统一管理8个block永远都是8个永远只分8个
这是通过它可以看到实际的文件的区的使用情况

四）高水位线

1）现实中的高水位线

大家都去过河里面都见过河见过水库

比如这是个水库，里面有水
下雨的时候水量会增加到一个高度
干了以后好几天没下雨了水位会下降到一个高度
但是高水位线在水库的边上会有一个水印
这表示水位最高的地方
位置最高的水印这就是高水位线

高水位线是水最高曾经到达什么地方

2）oracle的高水位线

oracle里面高水位线的使用

首先来讲一个段建立了
给它一个区，区里面有很多块
既然给这个段分配了区了，区里面的块自然会被这个段使用
我们做数据插入，一个块一个块的写入数据
这样区给了段以后段慢慢的会使用其中的块

当使用到一个位置
段使用区的最后一个块的位置，就叫高水位线
是这个段中分配给它的区的已使用的最后一个块的位置

比如一个段分配了两个区
一个区没用完又分配了一个区，这是可以的
这时高水位线仍是刚才的地方
如果继续使用，高水位线就会向后移动
高水位线就会移到分配的第二个区里面
再使用高水位线会一步步后移
这就是区的高水位线的问题
叫 high water mark 高水位线

3）高水位线的意义

高水位线是oracle对表执行全表扫描的最后一个位置

全表扫描就是我要访问一个表要对这个表所有的数据进行扫描

假设一个段我们给它分配了10万个块
但是这个段实际使用了2千个块
高水位线如果在2000这个位置的话
这时你访问这个表的时候oracle只访问2000个块就可以了
如果你的高水位线在第10万个块的位置
oracle在访问这个表的时候就会访问10万个块
也就是说为什么会出现我只用2000个块
但是它把十万个块都占满的情况

现在test2表有

SQL> select count(*) from test2;

  COUNT(*)
----------
    200000
一共20万行

我们把test2表的数据都删除就是清空test2表
所有数据都被删了
数据删了以后高水位线并没有下来

这个表我们有20万行
占了比如1000个块
你把数据删了但是高水位线还在1000个块的位置

把这1000个块给它彻底释放了是oracle的段的空间释放的问题

高水位线主要影响对表的全表扫描
所谓全表扫描就是对整个表进行全部的扫一遍访问一遍
就是对段高水位线以下的所有块进行一个全扫描

比如你有1000个块就扫描一千个块
这一千个块可能有800个是空的
但是没有办法，它扫描高水位线以下的
所以说如果一个表里面有空块的话，我们最好将高水位线降下来，这样最好
这是我们对表进行空间整理

4）高水位线实例

a）高水位线的影响

SQL> delete from test2;

200000 rows deleted.

SQL> commit;

Commit complete.
对test2表删除所有行，然后提交一下
这时test2表就成空的了

然后对这个表进行分析
SQL> analyze table test2 compute statistics;

Table analyzed.

分析完了以后再去查表的空间使用的情况
SQL> SELECT table_name,NUM_ROWS,BLOCKS, EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT, AVG_ROW_LEN
FROM USER_TABLES where table_name='TEST2';  2

TABLE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ------------ ---------- ---------- -----------
TEST2               0        305           78       6767          0           0

表目前是0行但是还是占305块
给以前一样
也就是我给它进行全表扫描访问，oracle还会访问305块

把执行计划打开看一下高水位线对表的影响

set autotrace on启用执行计划的输出
select count(*) from test2;会造成对test2表的全表扫描

SQL> set autotrace on
SQL> select count(*) from test2;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
Plan hash value: 634289536

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    68   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST2 |     1 |    68   (0)| 00:00:01 |
--------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        309  consistent gets
          0  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

结果中有这么一行
309  consistent gets
说明发生了309次的逻辑读

刚才已经看了test2表没有行了还是有305个块
对表进行全表扫描发现还是发生了309个读
实际里面没有了数据行了

进一步证明了
对表TABLE ACCESS FULL全表扫描
一个表TEST2它里面即使没有行
但是在高水位线占了300多个块
高水位线以下有300多个块对表进行全表扫描的时候即使里面没有行我们一样读300多个块

比较一下未进行全表删除前的执行计划
SQL> set autotrace on
SQL> select count(*) from test2;

  COUNT(*)
----------
    200000

Execution Plan
----------------------------------------------------------
Plan hash value: 634289536

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    73   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST2 |   200K|    73   (7)| 00:00:01 |
--------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        309  consistent gets
          0  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

consistent gets的值是一样的

两个执行计划中TABLE ACCESS FULL有区别
全部行删除后全表扫描的Rows是1
而有数据时TABLE ACCESS FULL操作的Rows是200K

通过这个我们验证了
如果这个高水位线把它降下来就有意义了

b）对执行计划的说明

Statistics统计表部分有一些信息
这个地方从各种信息来源包括网站和文档中的说法也有些分歧，只说一下自己总结的结果
仍以官方文档为准

现在主要用到了这三个

db block gets：Number of times a CURRENT block was requested
consistent gets：Number of times a consistent read was requested for a block
physical reads：Total number of data blocks read from disk

consistent gets：一致性请求，为对数据要求一致性的情况下对数据块的请求方式
                 对数据库单独查询不进行修改时一般都是一致性的请求
                 它要求得到的数据和查询开始的时间点的数据一致，
                 如果查询开始后结束前数据发生了变化，这时就要对数据进行回滚
                 使这个查询得到的数据是在查询开始点时的状态以达到数据一致性。
                 不管是否去回滚段中读，在概念上来讲，这种数据获取方式都叫 consistent gets
                 经常造成一致性读的语句是不带for update的select语句

db block gets：当前块方式的请求，要求请求出的块的真实数据是这个块的当前数据块，不会构造一致性块
               是语句执行的时间点上已存在的数据块，内容都是当前可用并真实的最新的
               当sql语句有对库数据的修改情况时，发生要求被请求块是当前块的请求。
               当被请求块有锁时就是正在被其它语句修改时，这个请求会等待，到其它语句修改完后才可得到
               当前请求得到数据块后，对数据进行修改、删除操作时，进程会给数据加上行级锁
               使用current mode的语句有INSERT、UPDATE、DELETE、SELECT FOR UPDATE等，这些操作要求数据块是当前块

physical reads：发生了从磁盘读数据块的情况

这里有一个buffer cache命中率的概念
有一个公式：
calculate the hit ratio for the buffer cache with the following formula:

1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')

consistent gets from cache: Number of times a consistent read was requested for a block from buffer cache.
从buffer cache中进行一致性请求块的数量，它是consistent gets的子集

db block gets from cache: Number of times a CURRENT block was requested from the buffer cache.
从buffer cache中进行当前状态块请求的数量，它是db block gets的子集

physical reads cache: Total number of data blocks read from disk into the buffer cache.
数据块从磁盘读入buffer cache的数量，它是physical reads的子集

要理解这个公式要弄明白逻辑读和物理读的概念，从这个公式也说明了逻辑读和物理读的区别

session logical reads: The sum of "db block gets" plus "consistent gets".
This includes logical reads of database blocks from either the buffer cache or process private memory.
逻辑读是"db block gets" 和"consistent gets"的和，是从内存中索取数据块

buffer cache的命中率的分母是
'consistent gets from cache' + 'db block gets from cache'
就是对buffer cache块的所有逻辑请求

A logical read is a read request for a data block from the SGA.
Logical reads may result in a physical read if the requested block does not reside with the buffer cache.
oracle每个对数据块的访问要先访问内存，对SGA中数据块的请求造成逻辑读
逻辑请求的块不在SGA中的buffer cache，就会引起块从磁盘到buffercache的物理读
对buffercache的操作物理读是逻辑读的一种结果并且物理读总数总是小于逻辑读

这个公式的意思就是
buffer cache的命中率为
（所有逻辑读减去物理读）和（所有逻辑读）的比值

由此可见
consistent gets和db block gets只是一种请求议向和要求
它由oracle实例发出，每个请求都是一个有要求的数据块获取方式
它们的值的和是造成了多少逻辑读
具体执行时执行了从内存的逻辑读还是发生了从磁盘的物理读还要看具体情况

c）降低高水位线的方法

降低高水位线有多种方法，这里列出较常用的几种：

1、expdp/impdp
先将表导出，这时对数据行进行了整理去除了空闲空间，高水位线也到了新的位置
再将导出的表导入

2、手动临时表
就是网络上说的CTAS方法create table ... as select ...

创建临时表保存数据
  create table temptable as select * from table_name;

临时表完成后又有两个办法继续

a、
彻底删除原表
  drop table table_name pruge;
重名名临时表为原表名
  alter table temptable rename to table_name;

b、
清空主表
  truncate table table_name;
重新导回数据
  insert into table_name select * from temptable;
丢掉临时表
  drop table temptable;

3、使用move

  alter table move tab_name;  在当前表空间中move
  alter table move tab_name tablespace tbs_name;  将其move到其他表空间
  move会锁表应该避免在业务高峰期操作
  还会影响index，完成后需要重建索引
  重建索引的命令ALTER INDEX INDEX_NAME REBUILD; INDEX_NAME是原表的索引名，需要自己查找
  move过程中需要考虑空间问题，因为过程中它相当于在表空间中又建了一个一样大小的表

4、shrink收缩
  为Oracle 10g新增功能
  alter table tab_name enable row movement;
  alter table tab_name shrink space;
  这个命令不需要额外的空间分配，只在原表内部进行，所以首先要使行row可以移动movement，就是需要先使行可迁移。
  只能用在自动段空间管理的段，完成后不需要再重构索引

5、truncate
  采用TRUNCATE语句删除一个表的数据的时候，类似于重新建立了表，不仅把数据都删除了，还把HWM给清空恢复为0。

在表中有数据要保留的情况下不能使用truncate，
其它方法都是对表进行重新的整理，和系统的磁盘碎片整理相似
第1、2种方法为手动操作，步骤较多，但操作起来比较安定
第3、4种较自动但有很多限制而且整个过程不能控制都由实例自动完成

d）实例降低高水位线

这里使用了降下来的方法的其中一个
我们对test2表进行truncate修剪一下

SQL> truncate table test2;

Table truncated.

truncate以后在对test2进行分析
SQL> analyze table test2 compute statistics;

Table analyzed.

分析完了再去查

SQL> SELECT table_name,NUM_ROWS,BLOCKS, EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT, AVG_ROW_LEN
FROM USER_TABLES where table_name='TEST2';  2

TABLE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ------------ ---------- ---------- -----------
TEST2               0          0            7          0          0           0

truncate完了以后test2几乎就不占块了
没有块了以后高水位线彻底下来了
truncate不但把表的内容清空
还会做一件事情把这个表的高水位线的位置降下来

我们在看一下执行计划
SQL> select count(*) from test2;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
Plan hash value: 634289536

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST2 |     1 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

我们发现
3  consistent gets
对块的读几乎没有了

高水位线要降下来因为高水位线影响着全表扫描的效率问题

五）oracle数据块的物理结构

1）块的结构

oracle块可抽象为一个几何中的一个块

它最上一部分是头部
头部包含的信息
1块的物理地址
2块属于哪个表属于哪个表空间
3块里面有事务槽ITL（Interested Transaction List）
4块头还有个信息行目录（Row Directory）

下面的oracle块部分是行
是块里面的行
放的时候从最下面开始一行行的连着串着放
是一个串从最底下串在一起向上堆放
块是从底下开始用的
块头是从上面开始用的
中间的空闲空间在中间

行不是我们讲的一行一行的
它从最底下开始
行和行之间是串起来用的

比如最底下这行即块中第一行
第一行的尾部在整个块的最尾端
从前面看首先是行的头部，它记录行的一些锁信息很短
接着是行的第一个列的列的长度然后是列值、第二个列的长度列值、下一个列的长度及列值
直到每一个列的长度和列值

这是第一行最底下哪行
上面的那行就是第二行在第一行的前面
包含行的头部、列宽列值、列宽列值、每一个列的列宽和列值
第二行的尾部和上一个行这里是第一行的头部紧密连在一起的
从第一个行开始所有的行都有一个长度但是连起来的

也就是在这个块里面的并没有严格的一行行的概念
而且行和行之间的长度也不一样
所有的行是连在一起的

我们要找这个块里面的第三行第四列
这里面物理上看没有行的概念因为所有的行串成一根

我要找第三行的话
在块的头部有一个叫行目录的东西
这个行目录是记录这个块里面的每一行的起点的起始位置

要找第三行
首先在块的头部里面的行目录这个位置
找到第三行的起点从哪个字节起点
比如找到了一个位置是起点
我要读第四列
起点后面紧跟着第一列的列宽
第一列的列宽跳过去把第一列跳过去
再找到第二列的列宽再跳过去
知道列宽就知道跳多远了
再第三列有第三列的列宽再跳过来
找到第四列以后，根据列宽把这个值取出来

这个过程在内存中进行不在磁盘
因为操作时oracle已把整个块读入到内存了

这就是块的存储结构
我们要从块里取数据的时候
首先根据行目录找到行的起点
根据要找那一列
根据列宽一步一步跳跳到你要找的那一列

这里有一个很细微的优化的一个地方
如果我把一个表里面的最经常访问的列放到第一列的话
访问速度会快一些
但是这个时间可以忽略不计几乎没有什么影响
但是从理论上可以
这就是块的一个结构

2）块的大小

oracle数据块默认是8K

SQL> show parameter block

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192
db_file_multiblock_read_count        integer     16

有一行
db_block_size                        integer     8192
数据块就是8K大小

文件系统的块大小用dumpe2fs /dev/sda1命令查看
结果非常的多我们可以分屏显示
dumpe2fs /dev/sda1|more

[root@redhat4 ~]# dumpe2fs /dev/sda1|more
dumpe2fs 1.35 (28-Feb-2004)
Filesystem volume name:   /
Last mounted on:          <not available>
Filesystem UUID:          7aef2b28-d7c2-4389-bd65-0961b542eab0
Filesystem magic number:  0xEF53
Filesystem revision #:    1 (dynamic)
Filesystem features:      has_journal ext_attr resize_inode dir_index filetype needs_recovery sparse_super large_file
Default mount options:    (none)
Filesystem state:         clean
Errors behavior:          Continue
Filesystem OS type:       Linux
Inode count:              3702784
Block count:              7397924
Reserved block count:     369896
Free blocks:              4780105
Free inodes:              3348913
First block:              0
Block size:               4096
Fragment size:            4096
Reserved GDT blocks:      1024
Blocks per group:         32768
Fragments per group:      32768
Inodes per group:         16384
Inode blocks per group:   512
.
.

这里只列出第一屏的开始部分
有这么一行
Block size:               4096
说明操作系统数据块大小是4K

oracle的块的大小是8K
文件系统的块大小是4K

从硬盘角度来讲
512个字节0.5K是一个大小是一个扇区的大小

经过计算一个oracle块由两个文件系统块组成由16个扇区组成

六）行链接(Row Chaining)行迁移(Row Migrating)

正常情况下oracle尽最大可能的将一行放到一个块里面去
正常来讲oracle不允许行跨块
正常的一个块里面放多行

1）PCT free

PCT为英文per cent的缩写，意为百分数

比如一个块是8K
上面是块的头部，底下是行
慢慢用满以后
PCT free默认是10%
意味着 oracle默认在这个块中插数据的时候
当还有10%空间剩余的时候，oracle就不用这个块了，它就认为这个块满了
这就是PCT free

oracle要留这么一块10%的空闲空间
原因举例讲

比如有一个t表里面有一个name列
name列是VarChar字段varchar2(20)字段
原来name列存放的是skg这三个字母
我更新完了以后
name这个列成了xkgjiagulun
无形中更新了以后这个列变长了
我们知道所有的行是紧密的排在一起的
这个列update更新以后变长了整体空间要向上移
为了以后的update我们最好这个数据块预留一块空闲空间
将来update的时候空间可以自动往上调

所以说PCT free空间主要是为了将来我要对这个数据块进行update的时候
增加空间的时候不至于我的块中的数据从块出来

这种情况是有可能出现的

2）行迁移

一个块我留了pctfree预留空间10%
我插入的时候到了10%停了使用下一个块

但这个块经常被update
有可能出现这个块上面的空间满了再update的时候
这行里面某个列需要被增长被加大被加宽
加宽以后这个行在这个块里面搁不下了
这时候oracle把这个行整体全部移出来
把这个行拿出来放到另一个块里面去
一个新块可以容下

原来这个行oracle不删除而是空出来
写上这个行新的地址
就是这行移出来的地址写到原来的位置
这行原来位置的数据就成了一个地址指向新位置的地址

将来oracle读这个块的时候
读了第一行第二行，读到这一行的时候
它会跳出来去读另外一个块
从所在块把数据读出来然后再回去接着读
为了读原块oracle读了俩块

刚才这个现象叫行迁移

行迁移往往是因为PCT free过小
update在更新的时候一个行空间不够了
这个行整体会迁移出来
同时原迁移的位置上写上迁移到的地址
行迁移多了主要是因为update的问题
行迁移多了就会导致访问一个块的时候实际访问俩块
这是行迁移，行整体全迁出来

3）行链接

我们尽量的保证oracle把一个块里面放多行
尽最大的努力一个行完整的放到一个块里面去

会出现这种情况一个行非常长比如说是10K
这是有可能的
但我们知道这个块多大假如是8K

如果用一个块搁不下一行
这时候就需要
这个行在一个块里面搁一部分以后
然后写个地址指向新的位置
一个块里面放了8K，另一个块里面放了2K
这就是行链接

块小了行大了
可以建一个16K的表空间的数据块就可以搁的下了
这里面我最好32K的表空间
一个行10K长的话
表空间中的oracle数据块32K
一个块里面可以搁3行

无论是行链接和行迁移都不好
都会导致我读一个块的时候
我读一行会需要读俩块
都不好都不是非常好

4）如何发现行链接行迁移

oracle里面有一个sql语句

SELECT table_name,NUM_ROWS,BLOCKS, EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT, AVG_ROW_LEN
FROM USER_TABLES where table_name='TEST2';

里面有个CHAIN_CNT可以查这个表产生了多少行链接和行迁移

SQL> SELECT table_name,NUM_ROWS,BLOCKS, EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT, AVG_ROW_LEN
FROM USER_TABLES where table_name='TEST2';  2

TABLE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ------------ ---------- ---------- -----------
TEST2               0          0            7          0          0           0

从结果看目前
CHAIN_CNT 的值是 0
就是行迁移行链接数量值是0
说明在这方面做得比较好

如果这个值比较大的话
要去确认一下到底是行连接还是行迁移
要判断是行链接还是行迁移
很简单看行的平均长度，就是结果中AVG_ROW_LEN字段的值

如果行的平局长度大于块的长度的话
就是块大小的问题
说明块小了行长了就是行链接
需要增加块的大小

如果行的平均长度远远小于块的大小，但是CHAIN_CNT的值比较高
说明是行迁移造成的
需要对这个表进行重新整理
可以把这个表导出来再重新导进去

这就是我们零零散散的讲的段区块的知识
我们后面还会有一些对段区块进行一些知识的补充的一些内容

   2017年3月11日
                                                                                                                            文字：韵筝