Oracle表碎片整理操作步骤详解

高水位线(HWL)下的许多数据块都是无数据的,但全表扫描的时候要扫描到高水位线的数据块,也就是说oracle要做许多的无用功!因此oracle提供了shrink space碎片整理功能。对于索引,可以采取rebuild online的方式进行碎片整理,一般来说,经常进行DML操作的对象DBA要定期进行维护,同时注意要及时更新统计信息!

一:准备测试数据,使用HR用户,创建T1表,插入约30W的数据,并根据object_id创建普通索引,表占存储空间34M

代码如下:

SQL> conn /as sysdba
已连接。
SQL> select default_tablespace from dba_users where username='HR';

DEFAULT_TABLESPACE
------------------------------------------------------------
USERS

SQL> conn hr/hr
已连接。

SQL> insert into t1 select * from t1;
已创建 74812 行。

SQL> insert into t1 select * from t1;
已创建 149624 行。

SQL> commit;
提交完成。

SQL> create index idx_t1_id on t1(object_id);
索引已创建。

SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL 过程已成功完成。

SQL> select count(1) from t1;

COUNT(1)
----------
    299248

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';
SUM(BYTES)/1024/1024
--------------------
             34.0625

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID';
SUM(BYTES)/1024/1024
--------------------
                   6

二:估算表在高水位线下还有多少空间可用,这个值应当越低越好,表使用率越接近高水位线,全表扫描所做的无用功也就越少!

DBMS_STATS包无法获取EMPTY_BLOCKS统计信息,所以需要用analyze命令再收集一次统计信息

代码如下:

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';

BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      4302            0     299248

SQL> analyze table t1 compute statistics;
表已分析。

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';

BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      4302           50     299248

SQL> col table_name for a20
SQL> SELECT TABLE_NAME,
  2         (BLOCKS * 8192 / 1024 / 1024) -
  3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  4    FROM USER_TABLES
  5   WHERE table_name = 'T1';

TABLE_NAME           Data lower than HWM in MB
-------------------- -------------------------
T1                                  5.07086182

三: 查看执行计划,全表扫描大概需要消耗CPU 1175

代码如下:

SQL> explain plan for select * from t1;
已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   299K|    28M|  1175   (1)| 00:00:15 |
|   1 |  TABLE ACCESS FULL| T1   |   299K|    28M|  1175   (1)| 00:00:15 |
--------------------------------------------------------------------------

四:删除大部分数据,收集统计信息,全表扫描依然需要消耗CPU 1168

代码如下:

SQL> delete from t1 where object_id>100;
已删除298852行。

SQL> commit;
提交完成。

SQL> select count(*) from t1;

COUNT(*)
----------
       396

SQL>  exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL 过程已成功完成。

SQL> analyze table t1 compute statistics;
表已分析。

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';

BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      4302           50        396

SQL> explain plan for select * from t1;
已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   396 | 29700 |  1168   (1)| 00:00:15 |
|   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |  1168   (1)| 00:00:15 |
--------------------------------------------------------------------------

五:估算表在高水位线下还有多少空间是无数据的,但在全表扫描时又需要做无用功的数据

代码如下:

SQL> SELECT TABLE_NAME,
  2         (BLOCKS * 8192 / 1024 / 1024) -
  3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  4    FROM USER_TABLES
  5   WHERE table_name = 'T1';

TABLE_NAME           Data lower than HWM in MB
-------------------- -------------------------
T1                                  33.5791626

六:对表进行碎片整理,重新收集统计信息

代码如下:

SQL> alter table t1 enable row movement;
表已更改。

SQL> alter table t1 shrink space cascade;
表已更改。

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';

SUM(BYTES)/1024/1024
--------------------
                .125

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID
';

SUM(BYTES)/1024/1024
--------------------
               .0625

SQL> SELECT TABLE_NAME,
  2         (BLOCKS * 8192 / 1024 / 1024) -
  3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  4    FROM USER_TABLES
  5   WHERE table_name = 'T1';

TABLE_NAME           Data lower than HWM in MB
-------------------- -------------------------
T1                                  33.5791626

SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL 过程已成功完成。

这个时候,只剩下0.1M的无用功了,执行计划中,全表扫描也只需要消耗CPU 3
SQL> SELECT TABLE_NAME,
  2         (BLOCKS * 8192 / 1024 / 1024) -
  3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  4    FROM USER_TABLES
  5   WHERE table_name = 'T1';

TABLE_NAME           Data lower than HWM in MB
-------------------- -------------------------
T1                                  .010738373

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   396 | 29700 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

总共只有5个块,空块却有50个,明显empty_blocks信息过期
SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';

BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
         5           50        396

SQL> analyze table t1 compute statistics;
表已分析。

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';

BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
         5            3        396

(0)

相关推荐

  • oracle表的简单操作步骤

    1.创建表 create table test_1(num number(2) not null,name VARCHAR2(20) not null,primary key(num)); 创建了一个两个字段的表,num和name,都设置为非空,num设为主键,在创建表的时候经常一个"缺失左括号"的错误信息,估计是VARCHAR2类型后面没有加数据长度,加上就好了.这是用sql语句创建表,感觉要比右键新建要麻烦,可能有的时候不得不这样建表. 2.复制表 create table tes

  • shell脚本操作oracle删除表空间、创建表空间、删除用户

    oracle下表空间的导出,用户的删除,表空间删除,用户新建,表空间新建,数据导入的shell使用非oracle用户执行该脚本参数说名$1:base表空间的用户名$2:同步表空间的用户名使用场景测试用,base表空间用于升级建立一些固化数据.同步表空间用于测试用,每次去和base表空间拉平数据 复制代码 代码如下: #!/bin/shoraclehome=$ORACLE_HOMEecho $oraclehomelocaldir="/oracle/data"echo $localdir#

  • Oracle 表空间查询与操作方法

    一.查询篇 1.查询oracle表空间的使用情况 select b.file_id 文件ID, b.tablespace_name 表空间, b.file_name 物理文件名, b.bytes 总字节数, (b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩余, sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b where a.

  • Oracle数据库中表空间的基本管理操作小结

    DB存储层次结构 (画了个草图,将就看一下...XD) 管理表空间 -system 存放数据字典信息,必须的,创建数据库时第一个创建 -sysaux 10g新,必须的,辅助分担system的负荷,系统管理如oem等三方工具等 -undo 存储回滚段信息,提供事务回滚功能 -temp 存放用户排序的临时数据 -index 存放用户表上的索引信息 -other 不同用户表数据 获取表空间和数据文件信息 表空间信息:DBA_TABLESPACES        V$TABLESPACE        

  • Oracle的数据表中行转列与列转行的操作实例讲解

    行转列 一张表 查询结果为 --行转列 select years,(select amount from Tb_Amount as A where month=1 and A.years=Tb_Amount.years)as m1, (select amount from Tb_Amount as A where month=2 and A.years=Tb_Amount.years)as m2, (select amount from Tb_Amount as A where month=3

  • Oracle表碎片整理操作步骤详解

    高水位线(HWL)下的许多数据块都是无数据的,但全表扫描的时候要扫描到高水位线的数据块,也就是说oracle要做许多的无用功!因此oracle提供了shrink space碎片整理功能.对于索引,可以采取rebuild online的方式进行碎片整理,一般来说,经常进行DML操作的对象DBA要定期进行维护,同时注意要及时更新统计信息! 一:准备测试数据,使用HR用户,创建T1表,插入约30W的数据,并根据object_id创建普通索引,表占存储空间34M 复制代码 代码如下: SQL> conn

  • oracle net manager 数据传输安全步骤详解

    简介 Net Manger可以管理服务命名和监听程序. 所谓监听程序就是一个Oracle服务器上进程,负责监听/响应客户端对Oracle服务的请求.之前我们在安装数据库实例时,同时已经安装了一个监听程序,这点我们知道就行. 本篇重点给大家介绍oracle net manager 数据传输安全,具体操作步骤如下所示: oracle net manager来加密客户端与数据库之间或中间件与 数据库之间的网络传输数据 第一步:开始-->所有程序 -->oracle --> 配置和移植工具 --

  • 树莓派3B+安装64位ubuntu系统和docker工具的操作步骤详解

    想在树莓派3B上安装一些64位应用(例如64位JDK),因此首先要安装64位的操作系统,今天咱们就一起来实战: 原文地址:https://blog.csdn.net/boling_cavalry/article/details/80716098 环境要求一台树莓派3B设备:网络环境是内网,需要有线网络和网线一根:当树莓派通过网线连接到内网后,能够在路由器管理软件上查到树莓派的IP,例如我用的是小米路由器,通过路由器管理页面或者小米路由器的APP就能看到树莓派的接入情况:读卡器:电脑一台,系统是w

  • Spring Cloud Eureka 注册与发现操作步骤详解

    在搭建Spring Cloud Eureka环境前先要了解整个架构的组成,常用的基础模式如下图: 服务提供者:将springboot服务编写好以后,通过配置注册中心地址方式注册,提供给消费者使用. 注册中心:服务的中间桥梁,服务提供者将服务注册.服务消费者可以通过注册信息调用需要使用的服务. 服务消费者:通过规定的调用方式,读取注册中心的注册信息,调用相应的服务. 根据后续的服务复杂度进化以后,可以看到服务提供者也可以是服务消费者,服务消费者也可以是服务提供者.根据不同的业务情况是可以互相调用的

  • JMeter对数据库的查询操作步骤详解

    提示: 关于JMeter如何连接MySQL数据库,前面文章已经详解的讲解过了.因为配置数据库连接是比较固定的步骤,这里就不重复讲解了. 本篇文章主要详细说明,使用JDBC Request组件,如何对数据库进行查询的各种操作. JMeter中查询语句的操作步骤: 添加测试计划. 添加线程组,设置线程组的次数. 添加JDBC Connection Configuration组件,配置数据库连接. 添加JDBC Request请求,编写SQL语句,使用参数化.运行脚本,发送SQL请求. 添加察看结果树

  • MongoDB多表关联查询操作实例详解

    本文实例讲述了MongoDB多表关联查询操作.分享给大家供大家参考,具体如下: Mongoose的多表关联查询 首先,我们回忆一下,MySQL多表关联查询的语句: student表: calss表: 通过student的classId关联进行查询学生名称,班级的数据: SELECT student.name,student.age,class.name FROM student,class WHERE student.classId = class.id Mongoose多表联合查询(还是以众所

  • Laravel5.1 框架表单验证操作实例详解

    本文实例讲述了Laravel5.1 框架表单验证操作.分享给大家供大家参考,具体如下: 当我们提交表单时 通常会对提交过来的数据进行一些验证.Laravel在Controller类中使用了一个traint:ValidatesRequest.方便我们在控制器中使用验证器. 下面我们就来看一个验证表单的例子. 1 准备 1.1 创建路由 Route::resource('/post', 'PostController'); 1.2 创建控制器 php artisan make:controller

  • vsftp建立虚拟用户不同目录分配不同权限操作步骤详解

    vsftpd服务器同时支持匿名用户.本地用户和虚拟用户三类用户账号,使用虚拟用户账号可以提供集中管理的FTP根目录,方便了管理员的管理,同时将用于FTP登录的用户名.密码与系统用户账号区别开,进一步增强了FTP服务器的安全性. 1.在/etc/vsftpd/vsftpd.conf加入或者更改以下配置语句: 复制代码 代码如下: anonymous_enable=NO (当然你也可以设成YES,同时允许匿名用户登陆)local_enable=YES (必须置YES,因为虚拟用户是映射到virtua

  • 详细聊聊Oracle表碎片对性能有多大的影响

    目录 前言 ️1.创建测试表 1.1建立表空间 1.2创建ASSM表空间 1.3创建表及索引 ️2.查看表统计信息 ️3.空块占用空间 ️4.查看执行计划 ️5.删除大量数据 ️6.再次查看执行计划 ️7.再次空块占用空间 ️8.整理表碎片 ️9.效果确认 ️10.技能拓展 总结 前言 本文通过对Oracle 表碎片整理,对比了前后对数据库性能的影响. ️ 1.创建测试表 1.1 建立表空间 SYS@EDB> select TABLESPACE_NAME,FILE_NAME from dba_d

随机推荐