Oracle 12c In-Memory Table Practice－DanBrother的部落格

Oracle 12c In-Memory Table Practice

[oracle@testsvr admin]$ . oraenv

ORACLE_SID = [my12c] ?

The Oracle base remains unchanged with value /oracle/orabase

[oracle@testsvr admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 13 17:22:21 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

CDB$ROOT@MY12C> ALTER SYSTEM SET MEMORY_TARGET=12G scope=spfile;
CDB$ROOT@MY12C> ALTER SYSTEM SET INMEMORY_SIZE = 8G scope=spfile;

-- Bounce DB

CDB$ROOT@MY12C> startup force

ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size 3725224 bytes
Variable Size 4194306136 bytes
Database Buffers 67108864 bytes
Redo Buffers 29827072 bytes
In-Memory Area 8589934592 bytes
Database mounted.
Database opened.

CDB$ROOT@MY12C>
CDB$ROOT@MY12C> sho parameter inmemory
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
inmemory_clause_default string
inmemory_force    string DEFAULT
inmemory_max_populate_servers   integer   4
inmemory_query     string   ENABLE
inmemory_size big integer 8G
inmemory_trickle_repopulate_servers_ integer   1percent
optimizer_inmemory_aware   boolean TRUE

CDB$ROOT@MY12C> conn testuser@mypdb
Enter password:
Connected.
MYPDB@MY12C> sho user
USER is "TESTUSER"

MYPDB@MY12C> create table inmemory_test
as select rownum as num from dual
connect by level <=1000000;

MYPDB@MY12C> col TABLE_NAME format a20
MYPDB@MY12C> col INMEMORY format a10
MYPDB@MY12C> col INMEMORY_PRIORITY format a20
MYPDB@MY12C> col INMEMORY_DISTRIBUTE format a20
MYPDB@MY12C> col INMEMORY_COMPRESSION format a20
MYPDB@MY12C> col INMEMORY_DUPLICATE format a20
MYPDB@MY12C> select TABLE_NAME,INMEMORY,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION,INMEMORY_DUPLICATE
from user_tables
where table_name = 'INMEMORY_TEST';

TABLE_NAME INMEMORY INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION INMEMORY_DUPLICATE
-------------------- ---------- -------------------- --------------------------------------- ------------------------------------- ----------------------------------
INMEMORY_TEST DISABLED

MYPDB@MY12C> EXPLAIN PLAN FOR (SELECT * FROM INMEMORY_TEST);
MYPDB@MY12C> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 850049865
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 4882K| 385 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| INMEMORY_TEST | 1000K| 4882K| 385 (2)| 00:00:01 |
-----------------------------------------------------------------------------------
8 rows selected.

-- Enable In-Memory
alter table inmemory_test INMEMORY;
col TABLE_NAME format a20
col INMEMORY format a10
col INMEMORY_PRIORITY format a20
col INMEMORY_DISTRIBUTE format a20
col INMEMORY_COMPRESSION format a20
col INMEMORY_DUPLICATE format a20
select TABLE_NAME,INMEMORY,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION,INMEMORY_DUPLICATE
from user_tables
where table_name = 'INMEMORY_TEST';

TABLE_NAME INMEMORY INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION INMEMORY_DUPLICATE
------------------------ --------------- ------------------------------ ---------------------------------- -------------------------------------- --------------------
INMEMORY_TEST ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE

MYPDB@MY12C> EXPLAIN PLAN FOR (SELECT * FROM INMEMORY_TEST);
MYPDB@MY12C> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 850049865
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 4882K| 17 (12)| 00:00:01 |
| 1 | TABLE ACCESS INMEMORY FULL| INMEMORY_TEST | 1000K| 4882K| 17 (12)| 00:00:01 |
--------------------------------------------------------------------------------------------
8 rows selected.