12c In-Memory in PDB
In preparation for our upcoming 12c In-Memory Webcast @CaryMillsap, @TanelPoder, and I solicited questions from members of the universe at large on the interweb. We got a question about how In-Memory works with the 12c multi-tentant option and it got me thinking so I gave it a quick try. As it turns out, it works about as you would expect. The basic idea is to turn it on for the container DB (which is where the memory is actually allocated (ala the other main shared memory regions) and then decide which PDBs to allow to use it (and if so how much of it to use) or not. First, here are the steps necessary to allocate the memory in the container DB.
-bash-3.2$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 12 16:07:31 2014
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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
INSTANCE_NAME STARTUP_TIME CURRENT_TIME DAYS SECONDS
---------------- -------------------------- -------------------------- ------- ----------
democ1 12-SEP-2014 09:49 12-SEP-2014 16:07 .26 22675
SYS@democ1> select cdb from v$database;
CDB
---
YES
SYS@democ1> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
PDB_ID PDB_NAME STATUS
---------- ------------------------------ ---------
2 PDB$SEED NORMAL
3 DEMOPDB NORMAL
SYS@democ1> @parms
Enter value for parameter: inmem
Enter value for isset:
Enter value for show_hidden:
NAME VALUE ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
inmemory_clause_default TRUE FALSE FALSE
inmemory_force DEFAULT TRUE FALSE FALSE
inmemory_max_populate_servers 12 TRUE FALSE FALSE
inmemory_query ENABLE TRUE FALSE FALSE
inmemory_size 0 FALSE FALSE TRUE
inmemory_trickle_repopulate_servers_percent 1 TRUE FALSE FALSE
optimizer_inmemory_aware TRUE TRUE FALSE FALSE
7 rows selected.
SYS@democ1> alter system set inmemory_size=500M;
alter system set inmemory_size=499M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified
SYS@democ1> alter system set inmemory_size=500M scope=spfile;
System altered.
SYS@democ1> startup force
ORACLE instance started.
Total System Global Area 8589934592 bytes
Fixed Size 6877112 bytes
Variable Size 1644167240 bytes
Database Buffers 6257901568 bytes
Redo Buffers 144117760 bytes
In-Memory Area 536870912 bytes
Database mounted.
Database opened.
SYS@democ1> @parms
Enter value for parameter: inmem
Enter value for isset:
Enter value for show_hidden:
NAME VALUE ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
inmemory_clause_default TRUE FALSE FALSE
inmemory_force DEFAULT TRUE FALSE FALSE
inmemory_max_populate_servers 12 TRUE FALSE FALSE
inmemory_query ENABLE TRUE FALSE FALSE
inmemory_size 536870912 FALSE FALSE TRUE
inmemory_trickle_repopulate_servers_percent 1 TRUE FALSE FALSE
optimizer_inmemory_aware TRUE TRUE FALSE FALSE
7 rows selected.
So I logged into a container database that has one PDB (DEMOPDB). I checked to see if there was memory assigned to the column store, and there wasn’t. (inmemory_size was set to the default value of 0). Then I attempted to change the value, which as you can see is not a dynamic component. I had to make the change in the spfile and then restart the entire database including any PDBs.
So we’ve enabled inmemory at the container level. Now let’s check the PDB.
Note: The CDB/PDB is still a little weird for me. I use this little script to set SQL*Plus prompt to keep me straight. connect_pdb.sql
Note 2: Someone else asked about the very simple inmem_segs.sql script so here’s a link to it too: inmem_segs.sql
SYS@democ1> @connect_pdb
Enter value for pdb_name: DEMOPDB
Session altered.
SYS@democ1:DEMOPDB> alter system set inmemory_size=0;
alter system set inmemory_size=0
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
SYS@democ1> select pdb_id con_id, pdb_name, status, open_mode, restricted from dba_pdbs a, v$pdbs b where a.con_id = b.con_id order by pdb_id;
CON_ID PDB_NAME STATUS OPEN_MODE RES
---------- ------------------------------ --------- ---------- ---
2 PDB$SEED NORMAL READ ONLY NO
3 DEMOPDB NORMAL MOUNTED
SYS@democ1:DEMOPDB> startup
Pluggable Database opened.
SYS@democ1:DEMOPDB> @parms
Enter value for parameter: inmem
Enter value for isset:
Enter value for show_hidden:
NAME VALUE ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
inmemory_clause_default TRUE FALSE FALSE
inmemory_force DEFAULT TRUE FALSE FALSE
inmemory_max_populate_servers 12 TRUE FALSE FALSE
inmemory_query ENABLE TRUE FALSE FALSE
inmemory_size 536870912 FALSE FALSE TRUE
inmemory_trickle_repopulate_servers_percent 1 TRUE FALSE FALSE
optimizer_inmemory_aware TRUE TRUE FALSE FALSE
7 rows selected.
SYS@democ1:DEMOPDB> -- inmemory_size value is inherited from CDB as documented.
SYS@democ1:DEMOPDB> -- let's check which inmem parameters can be set at PDB level
SYS@democ1:DEMOPDB>
SYS@democ1:DEMOPDB> select name from v$system_parameter where ispdb_modifiable = 'TRUE' and name like nvl('&name',name);
Enter value for name: inmem%
NAME
--------------------------------------------------------------------------------
inmemory_size
inmemory_clause_default
inmemory_force
inmemory_query
SYS@democ1:DEMOPDB> -- So inmemory_size can be set
SYS@democ1:DEMOPDB> alter system set inmemory_size = 0;
System altered.
SYS@democ1:DEMOPDB> alter system set inmemory_size = 10M;
System altered.
SYS@democ1:DEMOPDB> -- no bounce required, that's cool
SYS@democ1:DEMOPDB> -- let's make sure it works
SYS@democ1:DEMOPDB>
SYS@democ1:DEMOPDB> create table kso.junk as select * from dba_objects;
Table created.
SYS@democ1:DEMOPDB> alter table kso.junk inmemory;
Table altered.
SYS@democ1:DEMOPDB> select count(*) from kso.junk;
COUNT(*)
----------
91025
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner:
Enter value for segment_name:
OWNER SEGMENT_NAME ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
KSO JUNK 13.0 4.1 3.2 .0
----------------
sum 4.1
SYS@democ1:DEMOPDB> -- so it works
SYS@democ1:DEMOPDB> alter system set inmemory_size = 0;
System altered.
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner:
Enter value for segment_name:
OWNER SEGMENT_NAME ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
KSO JUNK 13.0 4.1 3.2 .0
----------------
sum 4.1
SYS@democ1:DEMOPDB> -- hmmm - doesn't seem to release memory already allocated, not too surprising
SYS@democ1:DEMOPDB> -- let's make sure nothing can be loaded after setting to 0 though
SYS@democ1:DEMOPDB>
SYS@democ1:DEMOPDB> alter table kso.junk inmemory;
Table altered.
SYS@democ1:DEMOPDB> -- another quirk worth mentioning - "alter table X inmemory" flushes object from column store if it happens to already be there
SYS@democ1:DEMOPDB>
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner:
Enter value for segment_name:
OWNER SEGMENT_NAME ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
----------------
sum
no rows selected
SYS@democ1:DEMOPDB> select count(*) from kso.junk;
COUNT(*)
----------
91025
SYS@democ1:DEMOPDB> -- should repopulate is memory is available
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner:
Enter value for segment_name:
OWNER SEGMENT_NAME ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
----------------
sum
no rows selected
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner:
Enter value for segment_name:
OWNER SEGMENT_NAME ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
----------------
sum
no rows selected
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner:
Enter value for segment_name:
OWNER SEGMENT_NAME ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
----------------
sum
no rows selected
SYS@democ1:DEMOPDB> alter system set inmemory_size = 10M;
System altered.
SYS@democ1:DEMOPDB> select count(*) from kso.junk;
COUNT(*)
----------
91025
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner:
Enter value for segment_name:
OWNER SEGMENT_NAME ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
KSO JUNK 13.0 4.1 3.2 .0
----------------
sum 4.1
SYS@democ1:DEMOPDB> -- let's try to make it bigger than the 500M we allocated at the container level
SYS@democ1:DEMOPDB> alter system set inmemory_size = 1G;
alter system set inmemory_size = 1G
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified
SYS@democ1:DEMOPDB> -- No can do
So as documented, the PDB specific value of inmemory_size is inherited from the CDB if it is not explicitly set at the PDB level. But we can also modify it (without restarting anything) as long as the value doesn’t exceed the overall value allocated at the container level. Note that this can be used to control how much of the column store memory any one PDB can use and that you can under or over allocate the space as you see fit. However, a single PDB can not specify a value that exceeds the actual value set (and allocated) for the entire container DB as was shown by the last attempted change to set it to 1G when only 500M was allocated by the container DB.
Th-th-th-that’s all folks!