ORACLE_OCP之ORACLE数据库空间管理
- 文章目标:
- 描述Oracle数据库如何自动管理空间
- 通过压缩节省空间
- 主动监视和管理表空间空间使用情况
- 描述Oracle数据库中的段创建
- 控制延迟段的创建
- 使用段顾问
- 使用段收缩功能从表和索引中回收浪费的空间
- 管理可恢复空间分配
一、空间管理:概述
- 空间由Oracle数据库自动管理。 它会生成有关潜在问题的告警,并给出相关的解决方案建议。 功能包括:
- Oracle Managed Files (OMF)
- 具有位图(“本地管理”)和自动数据文件扩展名的空间管理
- 主动空间管理(默认阈值和服务器生成的警报)
- 空间回收(缩小段,重新定义在线表)
- 容量规划(增长报告)
二、Block Space Management-块空间管理
-
上图解析:
- 空间管理在涉及块级的可用空间管理时。使用自动分段空间管理,每个块都分为四个部分,分别命名为FS1(在可用空间的0%到25%之间),FS2(在25%到50%之间的可用空间),FS3(在50%到75%之间的可用空间)和FS4( 75%到100%)。
根据块中可用空间的级别,其状态将自动更新。这样,根据插入行的长度,您可以判断是否可以使用特定的块来满足插入操作。注意,“已满”状态意味着一个块不再可用于插入。
在上张示例中,左侧的块是FS3块,因为它具有50%到75%之间的可用空间。在执行一些插入和更新语句之后,将到达PCTFREE(虚线),并且不再可能在该块中插入新行。现在将该块视为FULL或FS1块。一旦其可用空间级别降到下一部分以下,便考虑再次插入该块。在上述情况下,一旦可用空间超过25%,它将获得状态FS2。注意:大对象(LOB)数据类型(BLOB,CLOB,NCLOB和BFILE)不使用PCTFREE存储参数。未压缩和OLTP压缩的块的默认PCTFREE值为10。基本压缩块的默认PCTFREE值为0。
- 空间管理在涉及块级的可用空间管理时。使用自动分段空间管理,每个块都分为四个部分,分别命名为FS1(在可用空间的0%到25%之间),FS2(在25%到50%之间的可用空间),FS3(在50%到75%之间的可用空间)和FS4( 75%到100%)。
-
行迁移
- 更新时:行长度增加,超过了块中的FREE SPACE。
- 数据需要存储在新块中。
- 保留行的原始物理标识符(ROWID)。
- Oracle数据库需要读取两个块才能检索数据。
- 段顾问(segment advisor)会查找包含已发生行迁移的段(表、索引)。
- 块内的零散自由空间发生自动合并。
-
在以下情况下,Oracle数据库会自动透明地合并数据块的可用空间:
- 1、INSERT或UPDATE语句尝试将具有足够可用空间的块用于新行
- 2、可用空间是零散的,因此行不能插入到块的连续部分中
- 合并之后,可用空间的数量与操作之前的数量相同,但是现在该空间是连续的。
三、段内的可用空间管理
- 由段中的位图块进行跟踪
- 优点:
- 更灵活地利用空间
- 运行时动态调整
- 位图块会进行多进程搜索
- 可用空间可以在数据库段内自动进行管理。使用的是位图块来跟踪段内可用空间或已用空间。要利用此功能,请在创建本地管理的表空间时指定“自动空间管理”。然后,您的规范将应用于随后在此表空间中创建的所有段。
- 自动段空间管理具有一组位图块(BMB),用于描述该段中数据块的空间利用率。 BMB以树状层次结构组织。包含对所有中间BMB的引用的层次结构的根级别存储在段头中。该层次结构的叶子节点表示属于该段的一组连续数据块的空间信息。此层次结构内的最大级别数为三。(根 枝 叶 使用的是B*TREE)
- 使用自动空间管理的好处包括:
- 更好地利用空间,尤其是对于行大小变化很大的对象
- 更好的运行时调整以适应并发访问的变化
- 多实例时性能或空间利用率等方面都会有更好的表现
段的类型
- 段是一种特点的结构他是由区(extent)组成的。 不同类型的段包括:
- 表段和集群段
- 索引段
- UNDO段回滚段
- 临时段
- 其中表段 集群段 索引段又被统称为永久段或持久段。
段是由ORACLE动态分配的。
Allocating Extents—区的分配
- 在数据文件的位图中搜索所需数量的相邻空闲块
- 分区大小的决定因素:
- 统一分配
- 自动分配
- Viewing extent map—可视分区图
- 获得相关的解除分配的建议
延迟段
-
DEFERRED_SEGMENT_CREATION = TRUE 是默认值.
-
段的创建细则如下:
- 建表 >数据字典操作
- DML(INSERT) > 创建段
-
查看延迟段的信息
SQL> SHOW PARAMETERS deferred_segment_creation NAME TYPE VALUE ------------------------------------ ----------- ------ deferred_segment_creation boolean TRUE SQL> CREATE TABLE seg_test(c number, d varchar2(500)); Table created. SQL> SELECT segment_name FROM user_segments; no rows selected
-
插入行并创建段:
SQL> INSERT INTO seg_test VALUES(1, 'aaaaaaa'); 1 row created. SQL> SELECT segment_name FROM user_segments; SEGMENT_NAME ------------------------------------------------------- SEG_TEST
-
控制延迟段的创建
- 使用DEFERRED_SEGMENT_CREATION 参数:
- 参数文件
- ALTER SESSION 会话级命令
- ALTER SYSTEM 系统级命令
- SEGMENT 创建语句:
- IMMEDIATE
- DEFERRED (default)
CREATE TABLE SEG_TAB3(C1 number, C2 number) SEGMENT CREATION IMMEDIATE TABLESPACE SEG_TBS; CREATE TABLE SEG_TAB4(C1 number, C2 number) SEGMENT CREATION DEFERRED;
Note:索引继承表特征。//索引继承创建索引时所在表的特征
- 限制与例外
- 段的按需创建:
- 仅适用于未分区的表和索引
- 不适用于索引组织表,聚簇表或其他特殊表
- 不适用于字典管理的表空间中的表
如果要将没有段的表从本地管理的表 迁移到 字典管理的表空间,则必须删除并重新创建它。
- 段的按需创建:
- 使用DEFERRED_SEGMENT_CREATION 参数:
-
附加的自动功能
- 无需用户干预:
-
无法使用的段空间的索引和索引分区
-
创建一个非段索引(没有分配段空间的索引):
CREATE INDEX test_i1 ON seg_test© UNUSABLE;
-
删除一个没有分配段空间的索引:
ALTER INDEX test_i UNUSABLE;
-
创建一个正常索引(包含段):
ALTER INDEX test_i REBUILD;
-
- 无需用户干预:
四、表压缩:概述
- 通过压缩所有数据降低存储成本:
- 基本压缩,用于直接路径插入操作: 10x
- 适用于所有DML操作的高级行压缩: 2–4x
- 直接路径插入操作的压缩
- 使用以下语句CREATE TABLE … COMPRESS BASIC …;
- 建议用于批量加载的数据仓库/DSS/
- 最大化块中的连续可用空间
- DML操作的高级行压缩
-
使用以下语句
CREATE TABLE … ROW STORE COMPRESS ADVANCED …;
-
建议用于活动的OLTP环境
-
-
指定表压缩
- 您可以为以下表指定压缩:
- 整个堆组织的表
- 分区表(每个分区可以具有不同的压缩类型或压缩级别。)
- 嵌套表的存储
- 您不能:
- 在超过255个列的表上指定基本和高级行压缩
- 如果将表压缩定义为直接路径加载,那么你不可以删除列。但是如果将表压缩,定义为高级行压缩的表上,你则可以执行相关的DROP操作
- 您可以为以下表指定压缩:
-
使用压缩顾问
- The Compression Advisor(压缩顾问):
- 分析对象,以估算不同压缩方法所节省的空间
- 帮助确定应用程序的正确压缩级别
- 推荐各种压缩策略:
- 为特定数据集选择正确的压缩算法
- 在特定列上排序以增加压缩率
- 提出不同压缩算法之间的权重
- 找到适用于OLTP级数据库的压缩(通过企业管理器EMCC)
- The Compression Advisor(压缩顾问):
-
使用DBMS_COMPRESSION包
- 确定最佳压缩比:
BEGIN DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('USERS','SH','SALES', NULL,DBMS_COMPRESSION.COMP_FOR_OLTP, blkcnt_cmp, blkcnt_uncmp, rowcnt_cmp, rowcnt_uncmp, comp_ration, comptype); DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || blkcnt_cmp); DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || blkcnt_uncmp); DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || rowcnt_cmp); DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || rowcnt_uncmp); DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype); DBMS_OUTPUT.PUT_LINE('Compression ratio = '||comp_ratio||' to 1');
- 确定最佳压缩比:
五、阈值和解决空间问题
- 解决相关的空间问题方法:
- 增加或修改数据文件大小
- 设置 AUTOEXTEND ON
- 收缩压缩相关对象
- 减少 UNDO_RETENTION -> UNDO表空间
- 在临时表空间中检查长时间运行的查询
六、监视表空间空间使用情况
- 只读和脱机表空间:不设置警报。
- 临时表空间:阈值对应于会话当前使用的空间。
- UNDO表空间:阈值对应于活动和未到期回滚段上的数据块使用的空间。
- 自动扩展文件:阈值基于最大文件大小。
七、Shrink Segment 收缩段
- 以上中的图描述了表收缩操作的两个阶段。
- 第一阶段-执行压缩:
在此阶段,行将尽可能移至该段的左侧。在内部,行由数据包移动以避免锁定问题。在行被移动之后,收缩操作的第二阶段开始。 - 第二阶段-调整高水位线:
在此阶段,将调整高水位线(HWM),并释放未使用的空间。如果您有长时间运行的查询届时可能会被收缩过程影响,并尝试从已回收的块中读取数据,这时COMPACT子句很有用。当指定SHRINK SPACE COMPACT子句时,收缩操作的进度将保存在相应段的位图块中。这意味着下次在同一段上执行收缩操作时,Oracle数据库会记住已经完成的操作。然后,您可以在非高峰时段重新发出不带COMPACT查询语句。 - 至此SHRINK SPACE子句以完成第二阶段。
- 第一阶段-执行压缩:
收缩操作的结果
- 改善性能和空间利用率
- 维持索引
- 触发器未执行
- 已发生行迁移的行数量可能会减少。(迁移的行的数量可能会减少)
- 建议重建索引组织表的二级索引
在ASSM(自动段管理)段中回收空间
- 在线和就地操作
- 仅适用于ASSM表空间中的段
- 适用段的类型:
- 堆组织表(普通表)和索引组织表(由索引构建起来的表)
- 索引
- 分区和子分区(分区表和分区表中的子分区)
- 物化视图和物化视图日志
自动段顾问
- 由设置为在默认维护窗口期间运行的Scheduler(调度)作业启动:
- 周一至周五的工作日晚上,从10:00 PM到2:00 AM
- 星期六和星期日,两个窗口都从6:00 AM开始,持续20个小时
- 检查数据库统计信息,对段数据进行抽样,然后选择以下对象进行分析:
- 表空间已超过严重或警告阈值
- 活动(操作)最多的段
- 增长率最高的段
使用SQL收缩段
ALTER TABLE employees ENABLE ROW MOVEMENT;
ALTER TABLE employees SHRINK SPACE CASCADE;
ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE);
ALTER TABLE employees OVERFLOW SHRINK SPACE;
使用EMCC收缩段
八、管理可恢复空间分配
-
可恢复语句:
- 使您能够暂停大型操作,而不会报错
- 使您有机会在操作暂停时解决问题,而不是重新开始
- 在以下情况下被暂停:
- 空间不足
- 达到最大空间使用率
- 超出空间配额(超过用户自身可使用的最大空间)
- 可恢复语句可以被挂起和恢复多次.
-
使用可恢复空间分配
- 如果查询,DML操作和某些DDL操作遇到空间不足的错误,则可以恢复它们.
- 可通过SQL,PL / SQL,SQL * Loader和数据泵实用程序或Oracle调用接口(OCI)发出可恢复的语句。
- 只有通过以下操作之一启用了会话,语句才能以可恢复模式执行:
- RESUMABLE_TIMEOUT初始化参数设置为非零值.
- 发出ALTER SESSION ENABLE RESUMABLE语句:
ALTER SESSION ENABLE RESUMABLE;
INSERT INTO sales_new SELECT * FROM sh.sales;
ALTER SESSION DISABLE RESUMABLE;
为可恢复语句设置超时时间:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
恢复暂停的语句
-
例
- INSERT语句遇到错误,表明表已满。
- INSERT语句被挂起,并且没有错误传递给客户端。
- (可选)执行AFTER SUSPEND触发器。
- (可选)激活SQLERROR异常以中止该语句。
- 如果未中止该语句,并且已将可用空间成功添加到表中,则INSERT语句将继续执行。
-
ORACLE自动检测暂停的语句。
-
用户可通过以下SQL查询暂停语句:
SELECT name, sql_text FROM user_resumable;
-
暂停可恢复语句时,不会向客户端报错。
-
为了采取纠正措施,Oracle数据库提供了其他方法来通知用户该问题并提供有关情况的信息。
可恢复空间语句的适用语句类型
- 可恢复以下操作:
- 查询:SELECT语句用尽了临时空间(用于排序区域)
- DML:INSERT,UPDATE和DELETE语句
- 以下DDL语句:
CREATE TABLE … AS SELECT
CREATE INDEX
ALTER INDEX … REBUILD
ALTER TABLE … MOVE PARTITION
ALTER TABLE … SPLIT PARTITION
ALTER INDEX … REBUILD PARTITION
ALTER INDEX … SPLIT PARTITION
CREATE MATERIALIZED VIEW