oracle 12c 性能,12c新特性-感受ORACLE 12C IN_MEMORY性能威力

感受ORACLE 12C IN_MEMORY性能威力

oracle 12C in_memory(简称IM)对数据库性能的提升是巨大的，最重要的一点是这一切

对应用程序是透明的。首先我们来看看实验中IM的相关参数设置

使用imemory特性的前提，要设置这两个参数

SQL>alter system set  inmemory_max_populate_servers=2 scope=spfile;

SQL>alter system set inmemory_size=100M scope=spfile;(这个参数设置的大小最小为100M)

SQL> show parameter inmemory;

NAME                                 TYPE        VALUE

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

inmemory_clause_default              string

inmemory_force                       string      DEFAULT

inmemory_max_populate_servers        integer     2

inmemory_query                       string      ENABLE

inmemory_size                        big integer 100M

inmemory_trickle_repopulate_servers_ integer     1

percent

optimizer_inmemory_aware             boolean     TRUE

创建一个普通用户，创建一个表数据来自dba_objects;

SQL> create user c##frank identified by frank;

User created.

SQL> grant connect,resource,unlimited tablespace to c##frank;

Grant succeeded.

SQL> create table c##frank.t1 as select * from dba_objects;

Table created.

把用户表放到IM中.

SQL> alter table t1 c##frank.inmemory;

Table altered.

收集一下统计息.

SQL> exec dbms_stats.gather_table_stats('C##FRANK','T1');

PL/SQL procedure successfully completed.

SQL> set autotrace on;

SQL> select count(object_id) from c##frank.t1;

COUNT(OBJECT_ID)

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

90905

Execution Plan

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

Plan hash value: 3724264953

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

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

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

|   0 | SELECT STATEMENT            |      |     1 |     5 |    27   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE             |      |     1 |     5 |            |          |

|   2 |   TABLE ACCESS INMEMORY FULL| T1   | 90905 |   443K|    27   (0)| 00:00:01 |

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

Statistics

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

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

552  bytes sent via SQL*Net to client

552  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

做一个简单的查询，可以看出有以下几点:

1全表扫描多了INMEMORY关健字;

2consistent gets急剧减少,少得有点可怜了，说明对内存访问极少;

3,cost是27相比传统的查询，看看in-memory快了多少，使用NO_INMEMORY hint让optimizer不走in-memory,而走传统的buffer cache

SQL> select /*+ NO_INMEMORY */ count(object_id) from t1;

COUNT(OBJECT_ID)

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

90905

Execution Plan

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

Plan hash value: 3724264953

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |     5 |   416   (1)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |

|   2 |   TABLE ACCESS FULL| T1   | 90905 |   443K|   416   (1)| 00:00:01 |

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

Statistics

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

33  recursive calls

0  db block gets

1557  consistent gets

0  physical reads

0  redo size

552  bytes sent via SQL*Net to client

552  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

3  sorts (memory)

0  sorts (disk)

1  rows processed

传统的成本是416,consistent gets是1557,相比in-memory，访问数据大量增加.这是由于是传统是存储的是行式，而in-memory存放的是列式.速度快了非常多.有人可能会问，这里选择是单列，如果是count(*)了，性能还有这么明显吗?好，我们来看例子

SQL> select /*+ NO_INMEMORY */ count(*) from c##frank.t1;

COUNT(*)

----------

90905

Execution Plan

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

Plan hash value: 3724264953

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |   426   (1)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| T1   | 90905 |   426   (1)| 00:00:01 |

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

Statistics

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

5  recursive calls

0  db block gets

1536  consistent gets

1527  physical reads

0  redo size

544  bytes sent via SQL*Net to client

552  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

在实验中还产生了物理读.再来看看in-memory的count(*)

SQL> select /*+ INMEMORY */ count(*) from c##frank.t1;

COUNT(*)

----------

90905

Execution Plan

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

Plan hash value: 3724264953

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

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

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

|   0 | SELECT STATEMENT            |      |     1 |    27   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE             |      |     1 |            |          |

|   2 |   TABLE ACCESS INMEMORY FULL| T1   | 90905 |    27   (0)| 00:00:01 |

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

Statistics

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

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

544  bytes sent via SQL*Net to client

552  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

即使count(*),in-memory也和原来一样，快上很多倍.估计看到这样一对比很多人都已经坐不住了.

比如手动把表放到in-memory area中

SQL> create table t1(a int,b int);

Table created.

SQL> alter table t1 inmemory;

Table altered.

取消表在inmemory中存放

SQL> alter table t1noinmemory;

Table altered.

还可以在栏位级定义哪一栏放到 in-memory area中

SQL> alter table t1 inmemory;

Table altered.

SQL>alter table t1 inmemory (a) no inmemory (b);

Table altered.

先定义整个表存放在in memory中,才能再定义某个栏位存放在in memory中.