转载

Oracle 12c In-Memory 初探


Oracle 在12c版本中引入了
内存与列式压缩选件In-Memory,In-Memory可以极大提升查询的性能。

这里通过一个对比可以看到同样的查询中能提高多少性能。

1.先给In-Memory分配内存
SQL> show parameter inmemory;
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string                            DEFAULT
inmemory_max_populate_servers        integer                           0
inmemory_query                       string                            ENABLE
inmemory_size                        big integer                       0
inmemory_trickle_repopulate_servers_ integer                           1
percent
optimizer_inmemory_aware             boolean                           TRUE

2.INMEMORY_SIZE定义了in-memory的大小
SQL> alter system set inmemory_size=1000m scope=spfile;
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

修改完需要重启一下
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.


Total System Global Area 5016387584 bytes
Fixed Size                  3721128 bytes
Variable Size            1056966744 bytes
Database Buffers         3724541952 bytes
Redo Buffers               13053952 bytes
In-Memory Area            218103808 bytes
Database mounted.
Database opened.



3.建立一张测试表
SQL> create table bmw.t as select * from dba_objects;


Table created.


SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from dba_tables where table_name='T';


TABLE_NAME INMEMORY_PRIORITY        INMEMORY_DISTRIBUTE                           INMEMORY_COMPRESSION
---------- ------------------------ --------------------------------------------- ---------------------------------------------------
T

可以看到并未真正的分配USED_BYTES
SQL> l
  1* SELECT * FROM V$INMEMORY_AREA
SQL> /


POOL                 ALLOC_BYTES USED_BYTES POPULATE_STATUS          CON_ID
-------------------- ----------- ---------- -------------------- ----------
1MB POOL               837812224          0 DONE                          3
64KB POOL              201326592          0 DONE                          3



4.看一下未使用In-Memory时的查询
SQL> set autot trace
SQL> SELECT * FROM bmw.t;
90927 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 90927 |     9M|   416   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 90927 |     9M|   416   (1)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       7487  consistent gets
       1525  physical reads
          0  redo size
   12128303  bytes sent via SQL*Net to client
      67223  bytes received via SQL*Net from client
       6063  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      90927  rows processed

执行计划中也可以看到使用TABLE ACCESS FULL扫描,consistent gets也达到了7487。


5.将表放到In-Memory中
SQL> set autot off
SQL> alter table bmw.t inmemory;


Table altered.
      
SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from dba_tables where table_name='T';

TABLE_NAME INMEMORY_PRIORITY        INMEMORY_DISTRIBUTE                           INMEMORY_COMPRESSION
---------- ------------------------ --------------------------------------------- ---------------------------------------------------
T          NONE                     AUTO                                          FOR QUERY LOW

6.没有对表查询前还是未真正放到In-Memory内存中
SQL> SELECT * FROM V$INMEMORY_AREA;
POOL                 ALLOC_BYTES USED_BYTES POPULATE_STATUS          CON_ID
-------------------- ----------- ---------- -------------------- ----------
1MB POOL               837812224          0 DONE                          3
64KB POOL              201326592          0 DONE                          3

SQL> select count(*) from bmw.t;

  COUNT(*)
----------
     90927

7.查询一次后可以看到USED_BYTES已经分配了内存
SQL> SELECT * FROM V$INMEMORY_AREA;


POOL                 ALLOC_BYTES USED_BYTES POPULATE_STATUS          CON_ID
-------------------- ----------- ---------- -------------------- ----------
1MB POOL               837812224    4194304 DONE                          3
64KB POOL              201326592     131072 DONE                          3

8.查看一下执行计划
SQL> set autot trace
SQL>  SELECT * FROM bmw.t;
90927 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873


-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      | 90927 |     9M|    20  (20)| 00:00:01 |
|   1 |  TABLE ACCESS INMEMORY FULL| T    | 90927 |     9M|    20  (20)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
    4944325  bytes sent via SQL*Net to client
      67223  bytes received via SQL*Net from client
       6063  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      90927  rows processed
这里可以看到使用了TABLE ACCESS INMEMORY FULL扫描,而且consistent gets为5.可以看到INMEMORY会给查询带来非常高的性能提升。
正文到此结束
Loading...