Oracle数据库空间管理和规划

本文希望通过系统地介绍这方面的有关概念 让大家能更好地规划使用数据空间 正确使用Oracle提供的有关功能特性 提高应用的执行效率

数据库空间的有效使用和维护不仅是数据库管理的重要工作 也是大多数开发人员所关心的内容 因为它直接关系到数据库性能的发挥 同时数据碎片是经常令人头疼的问题 碎片不仅造成空间的浪费 更重要的是会直接影响应 用程序的响应速度

Oracle提供了不少方法用于数据空间的使用 监控和维护 同时也在各版本中陆续对这方面的功能进行了增强 目的在于简化这方面工作的复杂度 提高应用的运行效率

一 相关概念

数据库的空间在逻辑上分为多个表空间 每个表空间则由系统中的一个或多个物理数据文件构成 Oracle存储数据的基本单位是块 其大小在建库时由DB_BLOCK_SIZE参数确定 一个或多个连续的块构成一个区片（EXTENT） 它作为数据对象存储的基本单位来使用 在Oracle中 每个基本数据对象使用的空间称为段（Segment） 段存放在唯一的表空间上 每个段实际上是一系列区片（更为准确地是数据块）的集合 每个简单数据对象对应一个段 对于分区对象如分区表 索引 则每个（子）分区对应一个段 由各个（子）分区共同构成一个完整的数据对象 因此 可以把表空间看作桶 里面放著许多段 一个段只能放在一个桶中 而不能跨越多个桶

二 表空间的使用

表空间碎片的产生

由于同一个表空间中存放有多个数据段 各个数据段可以有不同的区片尺寸 不同段的区片可以交叉存放 当这些段中的区片经过分配（如创建表） 释放（如删除表）后 就可能使表空间中原本连续的空闲数据块变成不连续 而区片必须由连续的数据块构成 这时 当某一段需要分配新的区片时 就有可能出现虽然表空间空闲数据块的总和大于所需区片的大小 但却无法找到一串连续的块来供此区片分配使用 这种情况就称为表空间的Extent Fragmentation 我们经常会遇到这种情况 明明从DBA_FREE_SPACE中计算表空间还有几百MB 但其中的某一个表却无法再扩展几个MB的空间

消除表空间碎片

Oracle在段的区片分配上为用户提供了很大的灵活性 然而如果未能正确使用创建表空间和数据对象的各个可选择参数 则在最后将不可避免的要面对区片碎片的问题 Oracle 的Bhaskar Himatsingka 和 Juan Loaiza 为此提出了SAFE（Simple Algorithm for Fragmentation Elimination ）配制规则 通过遵循这套规则 区片级碎片可以完全的避免 而实际上 Oracle i引入的新特性 Local Managed Tablespace就是SAFE规则在Oracle Server的内置实现 SAFE原则概括起来包括

对每个表空间上的段使用相同的区片尺寸 段参数INITIAL=NEXT PCTINCREASE= 可以通过使用Create Tablespace 的 MINIMUM EXTENT 子句来确保分配的区片是此参数的倍数

仅在表空间级指定INITIAL NEXT参数 在创建数据段时不要指定这些参数

区片的大小根据段大小来确定 原则是均衡顺序扫描的效率和空间的利用率 同时确保段的区片数目控制在 之下 根据此原则 在进行相应测试之后 确定以下区片选取规则

段大小(Oracle ) 区片大小(Oracle ) 段大小(Oracle ) 区片大小(Oracle ) &M K &M K M G M M G M &G M &G M

有此数据库中可以只使用三种区片大小的表空间 在对象创建之前需对其大小进行评估 并放到相应的表空间中

Oracle 引入了本地管理表空间 它在管理和性能上都优于传统的字典管理表空间 它已融合了规则 要使用此特性 在CREATE TABLESPACE语句中指定EXTENT MANAGEMENT LOCAL子句

段的区片数目上限应在 之下 DML *** 作在此区片数目范围内不会有明显的性能差异 但某些DDL *** 作的速度则与区片的数目关系较大 因此合理的区片数目应保持在 之下 对于持续不断扩展的段 应监控区片数目 在必要时移至其它表空间

对于特别大的数据段应控制在 G G（Oracle 为 G G）之间 它们应存放到单独的表空间上 同时对于这些特大段应考虑使用分区拉提高性能

用户的临时表空间应使用TEMPORARY类型

当系统的事务规模比较均衡时可以对回滚段使用OPTIMAL参数 否则应避免制定OPTIMAL参数 而定期监控回滚段的大小 并在必要时重建

临时段和回滚段绝对不要将用户数据存放到SYSTEM表空间 它是专为永远不会Drop和Truncate的系统数据对象而设计的

创建表空间时指定数据文件的大小应=区片整数倍+ 数据块 对于Local Managed Tablespace则为区片整数倍+ K

当表空间使用统一的区片大小时 不要对其进行空间整理 重整的结果不仅耗费精力而且可能会使性能变差 对于未使用统一的区片尺寸的表空间应通过Export/Import重整

i 提供了Alter Table …Move [Tablespace…]命令可用于快速重整表 Alter Index …Rebuild…[Tablespace…] 命令可用于快速重建索引

有关使用单个区片的误导

在许多关于碎片整理的文档中建议在Export时使用Compress=Y选项 将表中的所有数据调整到一个区片中 期望在Import后获得良好性能 由此让许多人产生一个观点 认为当表中数据全部存放到一个区片中时 可以获得良好性能 实际上单区片段只在以下条件成立时 才具有优越性

数据主要以（全段）扫描方式访问

段所对应的数据块在物理磁盘上连续存放 Oracle可以发布较大的顺序磁盘读 *** 作

通过对这两条进行分析可以发现 一方面数据库中大部分表是通过索引来访问 另一方面现在的数据库文件一般在物理上使用了RAID 或RAID + 技术 数据以条带化方式分布到多个物理磁盘上 逻辑上的单个区片和多区片在物理上并无本质上的区别 另外 从Oracle的角度来看 管理几百个区片的段是非常轻松的并不会有性能的下降 由此可见将整个段放到一个区片中并无明显好处 而这种做法却会导致表空间碎片的产生

三 表数据段的使用

表空间的组织

Heap表的空间由一系列区片链接而成 每个数据块除块头外其余部分可用于存放数据 在创建表时可以指定以下参数

PCTFREE 块中保留用于UPDATE *** 作的空间百分比 当数据占用的空间达到此上限时 新的数据将不能再插入到此块中

PCTUSED 指定块中数据使用空间的最低百分比 当一个块在达到PCTFREE 之后经历了一些DELETE *** 作 在其空间使用下降到PCTUSED后便可以重新被用于INSERT数据 这就是PCTFREE/PCTUSED参数的含义

调整PCTFREE PCTUSED参数的目标一方面是提高性能 另一方面则主要是提高空间使用效率 避免出现块中存在有许多未用的空间 但却无法找到一个块可以被用于插入新数据行的情况发生

PCTFREE的使用

在Oracle中表的每一行数据由唯一的ROWID标记 而Oracle支持的数据类型中有一些长度是可变的 如VARCHAR 当对这些数据进行UPDATE时 如果块中的可用空间不能容纳UPDATE后的数据行时 Oracle将会把此行移到其它数据块 同时保留此数据行的ROWID不变 并在原有块中建一指针指向行迁移后的位置 在这种情况下读取一行数据将需要访问 个数据块 从而导致性能下降 PCTFREE保留的空间就是为确保更改后的数据行可以仍存放于原有数据块中 避免行迁移的情况发生

由此 如果PCTFREE设置不足时可能产生行迁移 而另一方面如果PCTFREE设置过高 将会造成空间浪费 因此正确设置PCTFREE需要对表中数据的使用进行分析 对于数据长度不会变化或极少更新的情况 可以采用较小的PCTFREE 对于其它大多数情况应采用稍大的PCTFREE（PCTFREE的缺省值是 如果不好估计需预留的空间 可以使用 的范围） 不要为节约块中的空间而使用较小的PCTFREE值

PCTUSED的使用

当块的使用的空间下降到PCTUSED后 此块被重新放回空闲链表（Freelist）中 作为后续Insert的候选块 同样 设置PCTUSED需要视数据行的特性和Insert Update Delete的模式而定 但必须遵守的原则是 db_block_size * （ PCTFREE PCTUSED）必须比行的长度大

对于数据行长度变化较大的情况 应使用最大行长度来计算PCTUSED 并且应使用较低的PCTUSED值 因为在执行Insert时 如果数据块的可用空间不能装下一行数据 当块的使用的空间是在PCTUSED之上 Oracle将把此块从Freelist中移走 当块的使用的空间是在PCTUSED之下 Oracle将会扩展段空间 因此 PCTUSED如果设得过高 将导致段的不断扩展

lishixinzhi/Article/program/Oracle/201311/18776

ORACLE数据库中 表是最基本的内容 可以说 表设计的好坏直接跟数据库的性能相关 所以 在设计表的时候 除了要遵循其固有的数据库准则之外 还需要看个人的数据库管理经验 下面我就把这些经验分享一下 或许对大家有所帮助 一 表该存放在哪里? 我们都知道 在ORACLE数据库中 使利用空间这个概念来管理表对象的 在数据库创建的时候 数据库中已经建立了一些表空间 那么当我们新建立表的时候 这个新表的位置该放在什么地方呢?这就好像吃饭时的坐的位置一样 是有讲究的 一般来说 我们在新建表的时候 至少要遵循如下建议 一是在数据库创建的时候 在数据库中已经有了一个SYSTEM的表空间 一般情况下 这个表空间中 只包含数据字典及Oracle系统对象 如果我们将我们的表建立在这个空间上的话 那是要降低数据库的性能的 所以 一般我们是不建议用户把表格建立在这个空间上 但是 若我们不只一个人维护数据库 如有八个人共同设计数据库系统时 如何才能保证其他用户不在SYSTEM表空间中建立数据库表格呢?最好的办法就是通过权限控制 如我们可以给每个数据库设计人员指定一个默认的表空间 让他们只能在这个表空间中建立表格 如此的话 就能防止他们在SYSTEM表空间中建立自己的数据表格 从而对数据库的运行性能产生不良影响 所以 若给每个用户设置默认表空间的话 那么用户在建立具体的表时 不用具体指定表空间了 二是我们在为某个应用设计数据库的时候 最好先对表的空间进行规划 一般情况下 不要把数据表随意的分散到不同的表空间中去 如我们在为一个ERP系统设计数据库的时候 若把采购部门相关的表跟销售部门相关的表放到两个不同的表空间中去 这是不明智的做法 这么处理的话 会降低某些数据库管理和维护 *** 作的效率 如数据的备份与恢复 *** 作而且 也无法集中管理属于某个特定应用的数据 所以 我们一般建议 在规划数据库表空间的时候 把相同应用的表放在同一个表空间中去 如果要区分不同部门或者不同模块的表的话 我们可以在表的命名上动脑子 如我们在设计ERP系统的数据库中 可以根据其应用模块的不同 在前面加上前缀来进行识别 如跟系统基本配置相关的表 我们可以用AD为前缀而跟销售部门相关的表 我们可以加上SA前缀等等 如此的话 这些表具体是属于哪个模块的 就一清二楚了 完全没有必要为此设置不同的表空间 这是ORACLE数据库初学者经常会犯的错误 主要是对ORACLE表空间的定义不是很熟悉所导致的 二 对预计存储数量比较大的表时 要给与额外的重视 有些表非常的大 我们这边说的大 不一定是说结构复杂 而是指在这个表格中 预期会存储比较多的数据 为了提高对这个表格的处理效率 我们在事先要做出一定的安排 否则的话 后续对这些大表进行查询 插入等 *** 作的话 速度会很慢 所以 我们就有必要在数据库设计的时候 先预先估计一下表的数据存储量 把一些数据量大的表格 做一些额外的设置 如在ERP软件的数据库设置中 一般来说 产品数据与物料清单数据这两个表的数据量会比较大而从长远看的话 销售订单 采购订单 生产订单 记账凭证等这种单据类相关的表格其数据量也会比较大 一年两年可能感觉不出来 但是 到十年后 这个纪录数量就会很庞大 而像ERP系统这种大型的信息化管理项目 用个几十年时很正常的事情 而且 为了记录的完整性 也不建议用户把以前的数据删除 所以 为这种应用进行数据库设计的时候 要充分考虑这些大表的性能问题 具体的来说 设计大表的时候 可以考虑遵循如下的建议 一是不要为大表设置存储的限制 在ORACLE数据库中 可以为每张表格设置存储配额限制 如此的话 表最大容量就不能超过这个限制 对于一些数据容量比较小的表格 这么设置时合理的 可以提高空间的利用率 但是 若数据量比较大的话 就不建议事先设置表的存储空间了 如ERP系统的销售订单表 其刚开始可能记录量很小 第一年预计只有 G的记录容量 但是 估计在十年后 这个记录容量就会达到 G了 在这种情况下 我们怎么来给其设置存储空间呢?一开就设置 G空间 这也是不合理的 而且 设置存储空间 就意味着有可能产生存储碎片 从而影响到数据查询的效率 所以 在数据库表的设计过程中 若某些应用的表可能会有比较大的数据容量时 建议不要对其存储空间做出任何的限制 二是要为这大表分配足够的临时空间 如我们使用ERP系统时 要查询产品资料信息 我们都知道 产品信息的话 有些企业这个纪录数非常的庞大 而且在查询时 我们还会经常的进行排序 *** 作 如有时候会按照产品编码对查询出来的数据进行排序 当记录少的话 还好但是 当记录多的话 这个排序动作 要求具有比较大的临时存储空间 所以 当某个表预计会有很大的记录数量的时候 我们就要给其分配足够多的临时空间 临时空间的存储参数设置取决于临时表空间的默认储存参数设置 我们可以更改这些参数 以达到我们对要求 若没有给大表分配足够多的临时空间的话 则排序的动作将会很慢 而且很可能不成功 三是要考虑将表与表的索引分离存放 大表所对应的索引通常也比较大 一般来说 索引的数量是随着表记录的数量增加而增加 两者是接近于一个正比例的关系 所以 通常表的记录容量大的时候 索引数量也会很庞大 针对这种情况 我们考虑突破我们上面讲的表空间的规则定义 而考虑把表和他的索引分别存储于不同的表空间中 甚至在条件允许的情况下 分别存储于不同的硬盘中 这么做的好处是什么呢?最大的好处是让索引比较容易的获得所需要的连续的存储空间 从而提高输入输入的效率 通俗的说 就是可以提高数据的查询效率 如不这么处理的话 查询大容量的记录的话 数据库可能需要花费 秒而如此设计的话 就可能把时间缩短为 秒 这是一个很明显的性能改善 三 如何给表命名? 上面我在讲如何为表分配存储空间的时候 已经讲到过这方面的问题 下面 我就将对这个问题进行详细的描述 以帮助数据库管理员掌握一套好的数据库命名规则 首先 毋庸置疑的 在为标命名的时候 要遵循ORACLE数据库的基本命名规则 如不能以数字开头为表命名 如不能利用数据库的关键字为表命名 如表的名字不能重复等等 这些是最基本的要求 就不用我多费口舌了 除了要遵循这些基本的命名规则外 在实际工作中 为了数据库后续的维护等方面出发 我们还是要遵循一些额外的规则 这些规则跟ORACLE定义的规则不同 我们所讲的规则没有约束力 可以说 只是业界的一些共识而已 你若不怎么处理 ORACLE数据库也不会说你错误 只是后续维护的时候 会比较麻烦而已 一是在对数据库命名的时候 最好能跟体现表的分类关系 如最常见的 我们在设计数据库的时候 表都是按系统的具体模块来区分的 如根据前端系统要求的不同 数据库的表大致可以分为系统基本配置表 销售模块表 采购模块表 报表模块表等等 我们可以根据这些模块的不同 分别给与不同的前缀来区分 这么做的好处是很明显的 如一看到表最大名字 就可以知道这个表是属于哪个应用的 哪个模块的 这无疑可以提高数据库设计与前台软件开发的效率 同时 数据库中默认的排序规则是按名字来排序的 所以 为表格设置类别前缀的话 可以把同一类的表格排在一起 方便我们察看 二是对表格命名的时候 要考虑可读性 而不能随便阿狗阿猫的乱取名字 最常见的是 那些刚学数据库的人 在表命名的时候 如要建几张测试表 就会随便命名如TEST TEST 之类的 虽然这只是测试 但是 也不符合我们的命名过则 要做测试的话 那就以TEST开头 然后后面加上具体要测试的内容 如此的话 我们才可以通过表的名字知道该表具体的用途 而不用打开表去看里面具体的结构或者注释才能知道我们需要的信息 所以 在设计表的名字的时候 还要关注一下其的可读性 lishixinzhi/Article/program/Oracle/201311/18317

Oracle数据库管理员应按如下方式对Oracle数据库系统做定期监控

( ) 每天 对Oracle数据库的运行状态 日志文件 备份情况 数据库的空间使用情况 系统资源的使用情况进行检查 发现并解决问题

( ) 每周 对数据库对象的空间扩展情况 数据的增长情况进行监控 对数据库做健康检查 对数据库对象的状态做检查

( ) 每月 对表和索引等进行Analyze 检查表空间碎片 寻找数据库性能调整的机会 进行数据库性能调整 提出下一步空间管理计划 对ORACLE数据库状态进行一次全面检查

每天的工作

( ) 确认所有的INSTANCE状态正常登陆到所有数据库或例程 检测ORACLE后台进程: $ps –ef|grep ora

( ) 检查数据文件的状态记录状态不是 online 的数据文件 并做恢复

      Select file_name  status from dba_data_files where status= UNAVAILABLE

( ) 检查日志文件和trace文件记录alert和trace文件中的错误

连接到每个需管理的系统

使用 telnet 对每个数据库 cd到bdump目录 通常是$ORACLE_BASE//bdump 使用Unix tail 命令来查看alert_ log文件 如果发现任何新的ORA 错误 记录并解决

( ) 检查数据库当日备份的有效性

对RMAN备份方式: 检查第三方备份工具的备份日志以确定备份是否成功

对EXPORT备份方式: 检查exp日志文件以确定备份是否成功

对其他备份方式: 检查相应的日志文件

( ) 检查文件系统的使用(剩余空间) 如果文件系统的剩余空间小于 % 需删除不用的文件以释放空间

    　$df –k

( ) 检查表空间的使用情况

       SELECT tablespace_name  max_m  count_blocks free_blk_cnt  sum_free_m to_char( *sum_free_m/sum_m   ) ||  %  AS pct_free FROM (SELECT tablespace_name sum(bytes)/ /  AS sum_m FROM dba_data_files GROUP BY tablespace_name) (SELECT tablespace_name AS fs_ts_name  max(bytes)/ /  AS max_m  count(blocks) AS count_blocks  sum(bytes/ / ) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name ) WHERE tablespace_name = fs_ts_name

( ) 检查剩余表空间

   　SELECT tablespace_name  sum ( blocks ) as free_blk   trunc ( sum ( bytes ) /( * ) ) as free_m max ( bytes ) / ( ) as big_chunk_k  count (*) as num_chunks FROM dba_free_space GROUP BY tablespace_name

( ) 监控数据库性能

运行bstat/estat生成系统报告或者使用statspack收集统计数据

( ) 检查数据库性能 记录数据库的cpu使用 IO buffer命中率等等

使用vmstat iostat glance top等命令

( ) 日常出现问题的处理

每周的工作

( ) 监控数据库对象的空间扩展情况

根据本周每天的检查情况找到空间扩展很快的数据库对象 并采取相应的措施

删除历史数据

扩表空间

alter tablespace add datafile size

调整数据对象的存储参数

   　next extent pct_increase

( ) 监控数据量的增长情况

根据本周每天的检查情况找到记录数量增长很快的数据库对象 并采取相应的措施

删除历史数据

扩表空间

  　alter tablespace add datafile   size

( ) 系统健康检查

检查以下内容:　         

      init ora controlfile redo log file archiving sort area size tablespace(system temporary tablespace fragment) datafiles(autoextend location) object(number of extent next extent index) rollback segment logging &tracing(alert log max_dump_file_size sqlnet)

( ) 检查无效的数据库对象　

   　col owner for a col object_name for a SELECT owner  object_name  object_type FROM dba_objects WHERE status=  INVALID

( ) 检查不起作用的约束

      SELECT owner  constraint_name  table_name  constraint_type  status FROM dba_constraints WHERE status =  DISABLED  AND constraint_type =  P  

( ) 检查无效的trigger　

      SELECT owner  trigger_name  table_name  status FROM dba_triggers WHERE status =  DISABLED

每月的工作

( ) Analyze Tables/Indexes/Cluster　

      *** yze table estimate statistics sample   percent

( ) 检查表空间碎片

根据本月每周的检查分析数据库碎片情况 找到相应的解决方法

( ) 寻找数据库性能调整的机会

比较每天对数据库性能的监控报告 确定是否有必要对数据库性能进行调整

( ) 数据库性能调整

如有必要 进行性能调整

( ) 提出下一步空间管理计划

lishixinzhi/Article/program/Oracle/201311/18051

---