ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > oralce 压缩表与heap表存储空间与查询性能比较

oralce 压缩表与heap表存储空间与查询性能比较

原创 Linux操作系统 作者：tian1982tian 时间：2012-09-06 13:54:04 0 删除 编辑
首先来看一下test表
SQL> set autot trace exp stat
SQL> SELECT COUNT(*) FROM test;
已用时间:  00: 00: 03.42
执行计划
----------------------------------------------------------
Plan hash value: 3187697131
--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |  9479   (4)| 00:01:54 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_TIME |    15M|  9479   (4)| 00:01:54 |
--------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42926  consistent gets
      42900  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> SELECT COUNT(*) FROM test;
已用时间:  00: 00: 03.29
执行计划
----------------------------------------------------------
Plan hash value: 3187697131
--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |  9479   (4)| 00:01:54 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_TIME |    15M|  9479   (4)| 00:01:54 |
--------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42926  consistent gets
      42900  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
将buffer_cache内存中cache的数据清空
SQL> alter system flush buffer_cache;
系统已更改。
已用时间:  00: 00: 00.03
SQL> SELECT COUNT(*) FROM test;
已用时间:  00: 00: 03.39
执行计划
----------------------------------------------------------
Plan hash value: 3187697131
--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |  9479   (4)| 00:01:54 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_TIME |    15M|  9479   (4)| 00:01:54 |
--------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42926  consistent gets
      42901  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
将此表数据cache到内存后终于，查询的效率提高了
SQL> SELECT COUNT(*) FROM test;
已用时间:  00: 00: 00.60
执行计划
----------------------------------------------------------
Plan hash value: 3187697131
--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |  9479   (4)| 00:01:54 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_TIME |    15M|  9479   (4)| 00:01:54 |
--------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42926  consistent gets
          0  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
以下创建压缩表和普通表，对比存储和查询性能
SQL> create table test2
  2  as
  3  select * from test;
表已创建。
已用时间:  00: 01: 31.29
SQL> create table test3 compress
  2  as
  3  select * from test;
表已创建。
已用时间:  00: 01: 25.39
SQL> SELECT COUNT(*) FROM test2;
已用时间:  00: 00: 10.46
执行计划
----------------------------------------------------------
Plan hash value: 634289536
--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 | 33818   (2)| 00:06:46 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST2 |    15M| 33818   (2)| 00:06:46 |
--------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
        146  recursive calls
          0  db block gets
     152084  consistent gets
     152017  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> SELECT COUNT(*) FROM test3;
已用时间:  00: 00: 07.78
执行计划
----------------------------------------------------------
Plan hash value: 1832809687
--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 | 24779   (3)| 00:04:58 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST3 |    16M| 24779   (3)| 00:04:58 |
--------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
         28  recursive calls
          0  db block gets
     110776  consistent gets
     110719  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> SET AUTOT OFF
SQL> col owner format a15
SQL> col segment_name format a15
SQL> select owner,segment_name,bytes/1024/1024 m from dba_segments where segment_name='TEST2';
OWNER           SEGMENT_NAME             M
--------------- --------------- ----------
SYS             TEST2                 1216
已用时间:  00: 00: 00.06
SQL> select owner,segment_name,bytes/1024/1024 m from dba_segments where segment_name='TEST3';
OWNER           SEGMENT_NAME             M
--------------- --------------- ----------
SYS             TEST3                  872
已用时间:  00: 00: 00.06
SQL> show parameter db_block
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192
SQL>
test2表与test3表相比：
1: 存储test2表比test3多了1216-872=344M
2: 查询性能：test2表比test3多扫描344*1024/8=44032个数据库，故查询时间也多出10.46-07.78=2.68秒
但是加载压缩表数据比加载普通表时间将近多2倍

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16400082/viewspace-742794/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 记一次无备份的恢复
下一篇： Linux oracle ORA-01031: insufficient privileges
请登录后发表评论 登录
全部评论
tian1982tian

注册时间：2010-12-29

  • 博文量
    70
  • 访问量
    176790

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593