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!

Leave a Reply