转载

Oracle 12c InMemory in Tablespaces

上篇文章讲了在表级别可以开启INMEMORY，其实在Tablespaces也可指定开启Tablespaces ，在Tablespaces 级别指定后，存储在这个Tablespaces 上的表也都开启了INMEMORY。下面我们来测试一下。

1.建立一个表空间，并指定INMEMORY字句
SQL> CREATE TABLESPACE tbs1 datafile 'tbs1' SIZE 40M DEFAULT INMEMORY;

2.在新建立的表空间上建立一张表，且表不指定INMEMORY
SQL> create table bmw.tt tablespace tbs1 as select * from dba_objects ;
Table created.

3.可以看到新建立的表也都开启了INMEMORY，这是因为我们在表空间级别开启了INMEMORY。
SQL> l
1* select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from dba_tables where table_name='TT'
SQL> /

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

SQL> l
1 SELECT * FROM V$INMEMORY_AREA
2*
SQL> /
POOL ALLOC_BYTES USED_BYTES POPULATE_S CON_ID
---------- ----------- ---------- ---------- ----------
1MB POOL 837812224 4194304 DONE 3
64KB POOL 201326592 131072 DONE 3

当表没有查询时，我们可以看到并未分配内存

4.查询表
SQL> set autot trace
SQL> SELECT * FROM bmw.tt;
90935 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90935 | 9M| 32 (16)| 00:00:01 |
| 1 | TABLE ACCESS INMEMORY FULL| TT | 90935 | 9M| 32 (16)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
7485 consistent gets
1527 physical reads
0 redo size
4945264 bytes sent via SQL*Net to client
67234 bytes received via SQL*Net from client
6064 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90935 rows processed

以上虽然使用了TABLE ACCESS INMEMORY FULL，但由于表未在内存中，还是产生了大量的 consistent gets

5.再查询测试一下，可以看到consistent gets降到了3
SQL> /
90935 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90935 | 9M| 32 (16)| 00:00:01 |
| 1 | TABLE ACCESS INMEMORY FULL| TT | 90935 | 9M| 32 (16)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
4945264 bytes sent via SQL*Net to client
67234 bytes received via SQL*Net from client
6064 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90935 rows processed
SQL> set autot off
SQL> SELECT * FROM V$INMEMORY_AREA;
POOL ALLOC_BYTES USED_BYTES POPULATE_S CON_ID
---------- ----------- ---------- ---------- ----------
1MB POOL 837812224 8388608 DONE 3
64KB POOL 201326592 262144 DONE 3

在V$INMEMORY_AREA视图中也可以看到已经分配了多少内存。

如果取消表空间级别的INMEMORY，可以使用 ALTER TABLESPACE tbsname NO INMEMORY 子句.

正文到此结束