Oracle 12.1.0.2的In Memory组件测试问题

linghe301

环境 是一台物理笔记本  IBM  W500

安装了 Linux6.4 和Oracle12.1.0.2

我在测试In Memory组件发现

[oracle@oracle12c Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 30 05:05:42 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                    2932632 bytes
Variable Size                  436207720 bytes
Database Buffers          536870912 bytes
Redo Buffers                   13844480 bytes
In-Memory Area                 3305111552 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdborcl open;

Pluggable database altered.

SQL> conn sde/sde@pdborcl
Connected.
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
  13631488

SQL> select * from v$inmemory_area;

POOL                           ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
    CON_ID
----------
1MB POOL                    2632974336         33554432 DONE
         3

64KB POOL                     654311424         34996224 DONE
         3


SQL> set timing on
SQL> set autot trace
SQL> select * from t1;

91710 rows selected.

Elapsed: 00:00:01.65

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time         |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 91710 |    10M|   429   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1         | 91710 |    10M|   429   (1)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
       7553  consistent gets
       1538  physical reads
          0  redo size
   12218706  bytes sent via SQL*Net to client
      67795  bytes received via SQL*Net from client
       6115  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      91710  rows processed

=======================================================================

将该表进行memory测试

SQL> alter table t1 inmemory;

Table altered.

Elapsed: 00:00:00.07
SQL> set autot off
SQL> select * from v$inmemory_area;

POOL                           ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
    CON_ID
----------
1MB POOL                    2632974336         33554432 DONE
         3

64KB POOL                     654311424         34996224 DONE
         3


Elapsed: 00:00:00.00
SQL> select count(*) from t1;

  COUNT(*)
----------
     91710

Elapsed: 00:00:00.02
SQL> select * from v$inmemory_area;

POOL                           ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
    CON_ID
----------
1MB POOL                    2632974336         37748736 DONE
         3

64KB POOL                     654311424         35127296 DONE
         3


Elapsed: 00:00:00.01
SQL> set autot trace
SQL> select * from t1;

91710 rows selected.

Elapsed: 00:00:02.98

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------------
---

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time
  |

--------------------------------------------------------------------------------
---

|   0 | SELECT STATEMENT           |          | 91710 |    10M|    32  (16)| 00:00:0
1 |

|   1 |  TABLE ACCESS INMEMORY FULL| T1   | 91710 |    10M|    32  (16)| 00:00:0
1 |

--------------------------------------------------------------------------------
---



Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
    4987819  bytes sent via SQL*Net to client
      67795  bytes received via SQL*Net from client
       6115  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      91710  rows processed

SQL>



测试结论：为什么在memory测试的指标都比一般情况要好，但是查询时间反而低了，而且我是在本机测试，不考虑网络的问题，这个我很不解，希望各位高手给予指点！

zcl3264

inmemory是列式存储，数据经过压缩的。它的优势是针对某些列的分析型操作。你如果只是把数据拿出来，数据库需要把列数据拼成行数据，相对于普通的行式存储还要干额外的工作，当然要慢了。

linghe301

zcl3264 发表于 2014-7-30 13:19
inmemory是列式存储，数据经过压缩的。它的优势是针对某些列的分析型操作。你如果只是把数据拿出来，数据库 ...

非常感谢你的回答