Oracle表碎片查询以及整理(高水位线)
1.表碎片的来源
当针对一个表的删除操作很多时，表会产生大量碎片。删除操作释放的空间不会被插入操作立即重用，甚至永远也不会被重用。
2.怎样确定是否有表碎片
收集表统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCHEMA_NAME',tabname=> 'TABLE_NAME');
-- 确定碎片程度
/* Formatted on 2017/9/21 14:14:37 (QP5 v5.240.12305.39476) */
SELECT table_name,
ROUND ( (blocks * 8), 2) "高水位空间 k",
ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",
ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",
ROUND (
( blocks * 8
- (num_rows * avg_row_len / 1024)
- blocks * 8 * 10 / 100),
2)
"浪费空间 k"
FROM user_tables
WHERE temporary = 'N'
ORDER BY 5 DESC;
查询部分结果如下：
TABLE_NAME 高水位空间 k 真实使用空间 预留空间(pctfree) k 浪费空间 k
----------------------- ---------- ------- ------------ ----------
SYSLOG 1377160 1161895.17 137716 77548.83
KSOARETMXLS 992648 831316.03 99264.8 62067.17
GOODACCTDT 494888 410832.89 49488.8 34566.31
CONTACTDOC 186856 135784.86 18685.6 32385.54
INVNTRYEXECDT 32216 570.9 3221.6 28423.5
INVNTRYAPPDT 21128 958.87 2112.8 18056.33
RETBILLMT 301120 254871.05 30112 16136.95
LOCATACCTDT 301096 255276.74 30109.6 15709.66
FORMREVIEW 129760 101530.8 12976 15253.2
RETGOODSDT 374560 323306.4 37456 13797.6
DAYGOODSCOLL 137920 110779.87 13792 13348.13
3.查看表上次收集统计信息时间
select table_name,last_analyzed from dba_tables where owner = 'SCHEMA_NAME'
4.收集整个 Schema 中对象的统计信息
SQL> exec dbms_stats.gather_schema_stats(ownname=>'SCHEMA_NAME');
5.为什么要整理表碎片
Oracle 对数据段的管理有一个高水位(HWM, High Water Mark)的概念。高水位是数据段中使用过和未使用过的数据块的分界线。高水位以下的数据块是曾使用过的，以上的是从未被使用或初始化过的。
当 Oracle 进行全表扫描(FTS, Full table scan)的操作时，它会读高水位下的所有数据块。如果高水位下还有很多空闲空间(碎片)，读取这些空闲数据块会降低操作的性能。
行链接和行迁移
行链接 Row Chaining：当插入数据量大的行的，如果一个Block不能存放一条记录，该记录的一部分会存储到同个Extent中的其他Block，这些block形成一个数据块链。
行迁移 Row Migration：当Update的时候导致记录长度增加了，存储的Block已经满了，就会发生行迁移。Oracle会迁移整行数据到一个能够存储下整行数据的Block中，迁移的原始指针指向新的存放行数据的 Block，ROWID不变。
当数据行发生链接(chain)或迁移(migrate)时，对其访问将会造成 I/O 性能降低，因为Oracle为获取这些数据行的数据，必须访问更多的数据块(data block)。
表碎片导致的问题
查询响应时间(尤其是全表扫描)变慢
产生大量行迁移
浪费空间
6.如何整理表碎片
10g之前
两种方法：
导出表，删除表，再导入表
alter table move
一般选择第二种，但是不能在线进行而且需要重建索引。
10g之后：
从 10g 开始，提供一个 shrink 命令，需要表空间是基于自动段管理的。
可以分成两步操作：
-- 整理表，不影响DML操作
SQL> alter table TABLE_NAME enable ROW MOVEMENT;--启动行移动功能
SQL> alter table TABLE_NAME shrink space compact; --只整理碎片 不回收空间
-- 重置高水位，此时不能有DML操作
SQL> alter table TABLE_NAME shrink space; --整理碎片并回收空间，并调整水位线。业务少时执行
SQL> alter table TABLE_NAME disable ROW MOVEMENT;--关闭行移动
也可以一步到位：
alter table TABLE_NAME shrink space;
shrink 的优势：
不需要重建索引。
可以在线操作。
不需要空闲空间，alter move需要跟当前表一样大小的空闲空间。
注意：
segment shrink执行的两个阶段：
1、数据重组(compact):
通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。
由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。
2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。
此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。
注意：shrink space语句两个阶段都执行。
shrink space compact只执行第一个阶段。
如果系统业务比较繁忙，
可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。
Oracle表复杂查询
转自:https://www.cnblogs.com/w-gao/p/7288293.html Oracle表复杂查询 聚合函数 max(字段值) -- 求最大值 min(字段值) -- 求最小值 ...
ORACLE表空间查询和管理【转】
红色是自由指定的~~--查询表空间SELECT D.TABLESPACE_NAME, SPACE "SUM_SPACE(M)", SPACE - NVL(F ...
Oracle 表空间查询与操作方法
一.查询篇 1.查询oracle表空间的使用情况 select b.file_id 文件ID, b.tablespace_name 表空间, b.file_name 物理文件名, b.bytes ...
Oracle 数据库表空间碎片查询和整理
dba_free_space 显示的是有free 空间的tablespace ,如果一个tablespace 的free 空间不连续,那每段free空间都会在dba_free_space中存在一条记录 ...
oracle表空间表分区详解及oracle表分区查询使用方法(转+整理)
欢迎和大家交流技术相关问题: 邮箱: jiangxinnju@163.com 博客园地址: http://www.cnblogs.com/jiangxinnju GitHub地址: https://g ...
oracle表空间查询维护命令大全之中的一个(数据表空间)史上最全
表空间是数据库的逻辑划分,一个表空间仅仅能属于一个数据库. 全部的数据库对象都存放在建立指定的表空间中.但主要存放的是表, 所以称作表空间.在oracle 数据库中至少存在一个表空间.即S ...
Oracle 表复杂查询之多表合并查询
转自:https://www.cnblogs.com/GreenLeaves/p/6635887.html 本文使用到的是oracle数据库scott方案所带的表,scott是oracle数据库自带的 ...
常用oracle表空间查询语句
--查询数据库表空间使用情况 select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/ ...
转： Oracle表空间查询
1.查询数据库中的表空间名称 1)查询所有表空间 select tablespace_name from dba_tablespaces; select tablespace_name from us ...
随机推荐
Java Swing
参考帖子http://chenchaobox.blog.163.com/blog/static/6043759020127845945569/ http://wenku.baidu.com/link? ...
nginx、php-fpm、mysql用户权限解析
nginx本身不能处理PHP,它只是个web服务器.当接收到客户端请求后,如果是php请求,则转发给php解释器处理,并把结果返回给客户端.如果是静态页面的话,nginx自身处理,然后把结果返回给客户 ...
操作iframe
iframe是在页面中嵌套的子页,当前页面(这里称为父页)和嵌套页面(这里称为子页)可以相互控制: 当父页控制子页用contentWindow,用法为 对象.contentWindow.documen ...
LeetCode: Sum Root to Leaf Numbers [129]
[题目] Given a binary tree containing digits from 0-9 only, each root-to-leaf path could represent a n ...
C++回顾day03---<;类型转换>;
一:C++类型转换 (一)static_cast<>() 静态类型转换:基本类型可以转换但是指针类型不允许.可以进行隐式类型转换 double n=1.23 int m=static_ca ...
peizhiwenjian
自定义配置文件 如果你不想使用application.properties作为配置文件,怎么办?完全没问题 java -jar myproject.jar --spring.config.locati ...
Hass.io: add-on Configurator
{ "username": "admin", "password": "admin", "cert ...
PyQt5--ButtonDrag
# -*- coding:utf-8 -*- ''' Created on Sep 21, 2018 @author: SaShuangYiBing Comment: ''' import sys f ...
巧用style的另类写法
看到style,不少人可能会说这个我知道,就是控件写属性的话可以通过style来实现代码的复用,单独把这些属性及其参数写成style就可以便捷的调用. <?xml version="1 ...
[OpenCV] Samples 06: logistic regression
logistic regression,这个算法只能解决简单的线性二分类,在众多的机器学习分类算法中并不出众,但它能被改进为多分类,并换了另外一个名字softmax, 这可是深度学习中响当当的分类算法 ...
扫一扫
3472
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
