首页 > 数据库 >Oracle普通堆表的不足之处

Oracle普通堆表的不足之处

时间：2023-09-02 14:31:26浏览次数：40  
标签：00 ---------- 不足之处 堆表 SQL Oracle redo select size

Oracle表的分类是多种多样的，除了普通表外，还有全局临时表、外部表、分区表、索引组织表等等具有其他特性的表。虽然普通表基本上可以实现所有的功能，但是这是说功能，而不是说性能。

如果我们善于在合适的场合选择合适的技术，这些“特殊”的表往往能在系统应用设计的性能方面，发挥出巨大的作用。

各种类型表都有优缺点，我们要善于取长补短，灵活利用，本篇文章，我们本着挑剔的态度来探讨普通表的缺点。

Oracle普通堆表的不足之处_Oracle

1. 表更新日志开销较大

如下语句可查询日志量：

select a.name,b.value
    from v$statname a,v$mystat b
    where a.statistic#=b.statistic#
    and a.name='redo size';

创建测试用户：

sqlplus "/ as sysdba"
grant all on v_$mystat to ljb;
grant all on v_$statname to ljb;
connect  ljb/ljb

为方便后续查询，我们创建如下视图：

--方便后续直接用select * from v$redo_size进行查询
create or replace view v$redo_size as
    select a.name,b.value
    from v$statname a,v$mystat b
    where a.statistic#=b.statistic#
    and a.name='redo size';

在进行实验前，我们可以先查询当前日志的大小，如下：

SQL> select * from v$redo_size;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             30416

这个值的单位是字节数

现在开始实验，以观察DML操作产生的日志量。

1.1 建表
--建表
create table t as select * from dba_objects;

SQL> select * from v$redo_size;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           1869132

SQL> select (1869132-30416)/1024/1024 as MB from dual;
        MB
----------
1.75353622

建表产生日志： (1869132-30416)/1024/1024≈1.75M

1.2 删除表数据
--删除表数据
delete from t;

--提交前
SQL> select * from v$redo_size;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           8198296

--提交后
SQL> select * from v$redo_size;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           8198532

SQL> select (8198532-1869132)/1024/1024 as MB from dual;
        MB
----------
6.03618622

删除表数据产生日志： (8198532-1869132)/1024/1024≈6.04M

1.3 插入数据
--插入数据
insert into t select * from dba_objects;

--提交前
SQL> select * from v$redo_size;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           9955752

--提交后
SQL> commit;
提交完成。
SQL> select * from v$redo_size;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           9955960

SQL> select (9955960-8198532)/1024/1024 as MB from dual;
        MB
----------
1.67601395

插入数据产生日志： (9955960-8198532)/1024/1024≈1.68M

1.4 更新数据
--更新数据
SQL> update t set object_id=rownum;

--提交前
SQL> select * from v$redo_size;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          14456528

--提交后
SQL> commit;
提交完成。
SQL> select * from v$redo_size;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          14456764

SQL> select (14456764-9955960)/1024/1024 as MB from dual;
        MB
----------
4.29230118

更新数据产生日志： (14456764-9955960)/1024/1024≈4.29M

这三个试验说明了对表的更新操作，无论是删除、插入还是修改，都会产生日志。

那么insert、update、delete三类语句，哪种记录redo log最多，哪种最少？ 由上面的实验结果可以看出：delete产生的redo日志最多（6.04M），其次是update（4.29M），最少的是insert（1.68M）。

2. delete无法释放空间

--观察未删除表时产生的逻辑读
SQL> drop table t purge;
SQL> create table t as select * from dba_objects ;
SQL> set autotrace on
SQL> select count(*) from t;
  COUNT(*)
----------
    259216

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  1177   (3)| 00:00:15 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  6679K|  1177   (3)| 00:00:15 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5923  consistent gets
          0  physical reads
          0  redo size
        349  bytes sent via SQL*Net to client
        471  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


--观察delete删除t表所有记录后，居然逻辑读不变都是5923
SQL> set autotrace off
SQL> delete from t ;

已删除259216行。

SQL> commit;

提交完成。

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

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


执行计划
----------------------------------------------------------
Plan hash value: 2966233522

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

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5923  consistent gets
          0  physical reads
          0  redo size
        346  bytes sent via SQL*Net to client
        471  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

记录数从259216减少到0条记录了，为什么逻辑读还是5923呢？

我们先继续下面的实验：

SQL> truncate table t;

表被截断。

SQL> select count(*) from t;

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


执行计划
----------------------------------------------------------
Plan hash value: 2966233522

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

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          6  recursive calls
          1  db block gets
         12  consistent gets
          0  physical reads
         96  redo size
        346  bytes sent via SQL*Net to client
        471  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

这里很显然看出:

  • delete 删除并不能释放空间，虽然delete将很多块的记录删除了，但是空块依然保留，Oracle 在查询时依然会去查询这些空块。
  • truncate 是一种释放高水平位的动作，这些空块被回收，空间也就释放了。

举个简单的例子，好比我来到XX大楼统计里面的人数，我从1楼找到20楼，每层的房间都打开去检查了一下，发现实际情况是一个人都没有。我很后悔自己累得半死却得出没人的结论，但问题是，你不打开房间，怎么知道没人呢，这就类似delete后空块的情况。而与truncate有些类似的生动例子就是，我想统计 XX 大楼里的人数，结果发现，XX 大楼被铲平了，啥房间都没有了，于是我飞快地得出结论，XX大楼里没有人。

不过truncate显然不能替代delete，因为truncate是一种DDL操作而非DML操作，truncate后面是不能带条件的，truncate table t where…是不允许的。 但是如果表中这些where条件能形成有效的分区，Oracle是支持在分区表中做truncate分区的，命令大致为 alter table t truncate partition ‘分区名’，如果where条件就是分区条件，那等同于换角度实现了truncate table t where…的功能。

当大量delete 删除再大量insert插入时，Oracle会去这些delete的空块中首先完成插入（直接路径插入除外），所以频繁delete又频繁insert的应用，是不会出现空块过多的情况的。

3. 表记录太大检索较慢

一张表其实就是一个SEGMENT，一般情况下我们都需要遍历该SEGMENT的所有BLOCK来完成对该表进行更新查询等操作，在这种情况下，表越大，更新查询操作就越慢！

有没有什么好方法能提升检索的速度呢？主要思路就是缩短访问路径来完成同样的更新查询操作，简单地说就是完成同样的需求访问BLOCK的个数越少越好。Oracle为了尽可能减少访问路径提供了两种主要技术，一种是索引技术，另一种则是分区技术。

我们先来说说索引技术： 当我们建成了一个索引，在SQL查询时我们首先会访问索引段，然后通过索引段和表段的映射关系，迅速从表中获取行列的信息并返回结果。

再来说说分区技术： 分区技术就是把普通表T表改造为分区表，比如以select * from t where created＞= xxx and created ＜=xxx 这个简单的SQL语句为例进行分析。 如果以created这个时间列为分区字段，比如从2010年1月到2012年12月按月建36个分区。早先的T表就一个T段，现在情况变化了，从1个大段分解成了36个小段，分别存储了2010年1月到2012年12月的信息，此时假如created＞= xxx and created ＜=xxx 这个时间跨度正好是落在2012年11月，那Oracle的检索就只要完成一个小段的遍历即可，假设这36个小段比较均匀，我们就可以大致理解为访问量只有原来的三十六分之一，大幅度减少了访问路径，从而高效地提升了性能。

4. 索引回表读开销很大

SQL> drop table t purge;
表已删除。

SQL> create table t as select * from dba_objects where rownum<=200;
表已创建。

SQL> create index idx_obj_id on t(object_id);
索引已创建。

SQL> set linesize 1000
SQL> set autotrace traceonly
SQL> select * from t where object_id<=10;
已选择9行。

执行计划
----------------------------------------------------------
Plan hash value: 134201588

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     9 |  1863 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T          |     9 |  1863 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJ_ID |     9 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<=10)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
         23  recursive calls
          0  db block gets
         40  consistent gets
          0  physical reads
          0  redo size
       1943  bytes sent via SQL*Net to client
        471  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL>

注意执行计划中有“TABLE ACCESS BY INDEX ROWID”关键字。

一般来说，根据索引来检索记录，会有一个先从索引中找到记录，再根据索引列上的ROWID定位到表中从而返回索引列以外的其他列的动作，这就是TABLE ACCESS BY INDEX ROWID 。

观察如果消除TABLE ACCESS BY INDEX ROWID的开销情况
SQL> select object_id from t where object_id<=10;

已选择9行。


执行计划
----------------------------------------------------------
Plan hash value: 188501954

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     9 |   117 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_OBJ_ID |     9 |   117 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"<=10)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        471  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL>

执行计划中没有“没有TABLE ACCESS BY INDEX ROWID”关键字了，逻辑读由40变为10，代价也由2变为1了！

因为语句从 select * from t where object_id＜=10 改写为 select object_id from t where object_id＜=10 了，不用从索引中回到表中获取索引列以外的其他列了。

可以发现性能有所提升。

避免回表从而使性能提升这是一个很简单的道理，少做事性能当然提升了。只是select * from t; 和select object_id from t;毕竟不等价，有没有什么方法可以实现写法依然是select * from t，但是还是可以不回表呢？

普通表是做不到的，能实现这种功能的只有索引组织表。

5. 有序插入却难有序读出

在对普通表的操作中，我们无法保证在有序插入的前提下就能有序读出。最简单的一个理由就是，如果你把行记录插入块中，然后删除了该行，接下来插入的行会去填补块中的空余部分，这就无法保证有序了。实验如下：

SQL> drop table t purge;
表已删除。

SQL> create table t  (a int,   b varchar2(4000) default  rpad('*',4000,'*'),   c varchar2(3000) default  rpad('*',3000,'*')   );
表已创建。

SQL> insert into t (a) values (1);
已创建 1 行。
SQL> insert into t (a) values (2);
已创建 1 行。
SQL> insert into t (a) values (3);
已创建 1 行。

SQL> select A from t;
         A
----------
         1
         2
         3

SQL> delete from t where a=2;
已删除 1 行。
SQL> insert into t (a) values (4);
已创建 1 行。
SQL> commit;
提交完成。

SQL> select A from t;
         A
----------
         1
         4
         3
SQL>

因为BLOCK大小默认是8KB，所以这里特意用rpad(‘‘,4000,’’), rpad(‘‘,3000,’’)来填充B、C字段，这样可以保证一个块只插入一条数据，方便做试验分析跟踪。

我们在查询数据时，如果想有序地展现，就必须使用order by ，否则根本不能保证顺序展现，而order by 操作是开销很大的操作，实验如下：

--order by 操作是开销很大的操作
SQL> set linesize 1000
SQL> set autotrace traceonly
SQL> select A from t;


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    39 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     3 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        374  bytes sent via SQL*Net to client
        471  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> select A from t order by A;


执行计划
----------------------------------------------------------
Plan hash value: 961378228

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    39 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |     3 |    39 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     3 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        374  bytes sent via SQL*Net to client
        471  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>

可以观察到，有排序的操作的统计信息模块有一个1 sorts (memory)，表示发生了排序，执行计划中也有SORT ORDER BY的关键字，不过最重要的是，没排序的操作代价为3，有排序的操作代价为4，性能上是有差异的，在大数量时将会非常明显。

关于order by 避免排序的方法有两种思路。 第一种思路是在order by 的排序列建索引。 第二种方法就是，将普通表改造为有序散列聚簇表，这样可以保证顺序插入，order by 展现时无须再有排序动作。

6.参考资料

标签：00,----------,不足之处,堆表,SQL,Oracle,redo,select,size
From： https://blog.51cto.com/u_11585528/7332905

相关文章

  • oracle 数据库自增长
    有的时候，我们在创建oracle自增序列时，会报错。这个时候不用慌，表创建完了就不用管了。然后我们可以根据下面的教程中的第二步，第三步，来创建序列。 --第一步创建表createtablet_user(user_idnumbernotnullprimarykey,user_namevarchar2(30),creditsnumber,user_pas......
  • Oracle - 运维相关总结
    读写分离读写分离的重点其实就是数据同步，能实现数据实时同步的技术很多。基于日志的Oracle复制技术，Oracle自身组件可以实现，同时也有成熟的商业软件。选商业的独立产品还是Oracle自身的组件功能，这取决于多方面的因素。比如团队的相应技术运维能力、项目投入成本、业务系统的负......
  • Oracle - 常见函数总结
    to_date()字符串转日期selectto_date('20050101','yyyyMMdd')todayfromdualto_char()将数值或日期型转化为字符selectto_char(12345678,'999,999,999,999')fromdual;selectto_char(sysdate,'yyyy-MM-dd')fromdual;to_number()......
  • Oracle - dblink 数据库之间的连接
    介绍dblink数据库链接顾名思义就是数据库的链接，就像电话线一样，是一个通道，当我们要跨本地数据库，访问另外一个数据库表中的数据时，本地数据库中就必须要创建远程数据库的dblink，通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。创建dblink--查询是否......
  • oracle 创建、查看、修改、删除、赋权directory目录
    如下：directory用于数据泵导入、导出创建的目录。1、查询directory目录select*fromdba_directories;2、创建或者修改directory目录createorreplacedirectory目录名称as'/存放目录路径'3、赋权directory目录grantread,writeondirectory目录名称t......
  • 从达梦数据库到Oracle数据库的性能测试数据迁移和导入优化
    为了在同样的数据基础上对比达梦数据库和Oracle数据库的业务性能，我们需要将达梦数据库的数据导入到Oracle数据库中。本文将提供一种思路来解决导入过程中遇到的问题及存在问题记录。数据库版本信息源数据库：达梦数据库（DM）V8目标数据库：Oracle数据库V11.2.0.4导出达梦数据库的......
  • oracle配置sde函数库
    1、创建sde用户并授权dropusersdecascade;createusersdeidentifiedbycabletech;grantcreateuser,dropuser,alteruser,createanyview,connect,resource,dba,createsession,createanysequencetosde;grantalteranyindextoSDE;grantaltersystemtoSD......
  • oracle 的 sqlplus 执行连接无反应
    背景：oracle集群突然出现了个事情，说是执行语句多给卡住了，然后sqlplus/assysdba 都没反应了，但是监听端口什么的也都还在解决过程：只能杀进程了，杀掉卡住的进程是最好的办法，下面是执行的语句ps-ef|grep"oracle"|grep "LOCAL=NO" |awk'{print$2}'|xargs kill-9两个......
  • Oracle数据迁移MySQL的三种简单方法
    Oracle数据迁移MySQL的三种简单方法更新时间：2023年06月05日10:58:16  作者：牛牛的笔记 对于许多企业而言,迁移数据库时最大的挑战之一是如何从一个数据库平台顺利迁移到另一个平台,下面这篇文章主要给大家介绍了关于Oracle数据迁移MySQL的三种简单方法,需要的朋友可以参......
  • Oracle EBS查看请求日志报404 not found错误，FNDWRR.exe资源访问不存在
    问题描述如上图中，查看请求时，点击“查看日志”按钮后，正常浏览器会打开一个页面，可以看到这个请求的日志，但出现问题后，浏览器会报404错误，如下图解决方法cd$EBS_ORACLE_HOME/common/scripts实际目录：(/oracle/PROD/fs1/FMW_Home/Oracle_EBS-app1/common/scripts)请根据自己的目......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99