Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, ludovic.

Asked: November 25, 2014 - 3:19 pm UTC

Last updated: November 21, 2022 - 11:50 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hello Tom,

I seen a new feature for Oracle 12c : In-Memory database cache. I seen it's a new memory area to organize data in column format for BI/DWH queries.

I would like to know how Oracle decides when use the data from In-memory area or from the buffer cache ? What are criteria for this decision ?

Plus, when a block is updated in the memory (get from disk, updated by a DML, ...) how Oracle synchronize blocks both In Memory area and Buffer cache ?

Thanks in advance.

---
Ludovic

and Tom said...

The criteria is all cost based optimizer driven.

If you execute a query that needs many of the columns from a table for a few rows - block/row oriented structures with b*tree indexes are "best"

If you execute a query that needs 2 columns out of 50 for millions or billions of rows (think "select sum(sales) from orders where order_date between :x and :y"), we'll go for a full scan - and that full scan would be able to use the in memory structures. Instead of having to pull in and parse two columns out of millions of rows on hundreds of thousands of database blocks, we'll just process the two columns in the columnar data store. This allows us to process orders of magnitude more rows per second (data we need is all stored together, all of the sales data is right next to the sales data, all of the order dates are right next to the other order dates. We don't have to pull an 8k block into the CPU cache to find just 500 bytes of useful information - everything we pull into the CPU cache is data we need.

The database knows what sections of a table are in the in memory columnar datastore and as blocks (which belong to a section of a table) are modified - we reflect those changes in the columnar data store. It (the in memory stuff) can use undo information to provide the same old consistent read you are familiar with (non-blocking, read consistent queries hold true for in memory as well as row oriented/block based queries)

see also:

https://blogs.oracle.com/In-Memory/



Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

IOTs in memory ?

Sokrates, December 11, 2014 - 9:35 pm UTC

Are there any chances that we will see the memory columnar datastore also implemented for IOTs ?
Or doesn't Oracle see any possible use-cases here ( as some Oracle-employee told me ) ?
Tom Kyte
December 12, 2014 - 10:29 am UTC

issue with IOTs is the lack of a rowid for data, data floats around in that structure - it doesn't have a "location". So, it is different from rows on blocks and mapping that to the in memory column store would be entirely different.

Also if you are using an IOT, hopefully you are only accessing via the primary key (secondary indexes on IOTs in an OLTP system where you modify the IOT frequently is not a good thing, the rowid guesses go stale and you end up having to rebuild that thing over and over). If you are accessing via a primary key - in memory won't help you (you'd have to be fast full scanning the IOT for in memory to make an impact)

so, never say never (and never say always, I always say..) - but I'm not aware of any plans in that direction


IOT structure

Shankar, December 18, 2014 - 5:12 am UTC

As you said 'issue with IOTs is the lack of a rowid for data, data floats around in that structure' can you please elaborate a bit more. Why IOTs dont have rowids?
Tom Kyte
December 18, 2014 - 8:15 pm UTC

because data in an index has a place it HAS to go and we might have to move data to make it fit.


suppose you have a leaf block in the IOT that is full. The primary keys on that leaf block are


a, b, c, d, f, g, h


so there are 7 entries on that leaf block - and remember the block is full (the other columns of the IOT are there as well, they are filling the block)

Now, someone inserts e. E has to go between D and F - it has to. But the leaf block is full.

so, we get a new empty leaf block, put it into the index right next to this one and move about half of the data from this block to the other block. Each leaf block is now about 50% full and we can insert e right after d no problem.

But half of the rows are on a new block - their rowid (if they had one) would be invalid.


Even if the rows DID NOT move, we'd have a problem. Take the same case - the same seven initial rows, but assume now the block is not full. A has slot 1 on the block, B has slot 2 ... F has slot 5 and so on.

You insert E. It has to take over slot 5, it has to be after d and before f. F, G and H would have to change their rowids - their slots changed (a rowid has file, block, and slot in it).


Rowids cannot be fluid like that - maintaining indexes would be a nightmare, and applications that rely on rowids would not be able to - the rowid of a row would change and change and change constantly.


Data blocks in a heap table do not have that issue. If a block is full, we stop inserting into it. Rows do not move (except in some very special cases - like updating a partition key to make the row move partitions - and the update will be internally processed as a delete+insert, very expensive in general)

12c - Inmemory - on Exadata

Rajeshwaran, Jeyabal, November 24, 2016 - 2:24 pm UTC

Team,

Could you please help me to understand why does this query with inmemory enable goes many times slower ?

demo@ORAST01> set timing on
demo@ORAST01> select count(*) from big_table;

  COUNT(*)
----------
 100000000

1 row selected.

Elapsed: 00:00:02.37
demo@ORAST01>
demo@ORAST01> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              35
Full Blocks        .....................         496,608
Total Blocks............................         499,032
Total Bytes.............................   4,088,070,144
Total MBytes............................           3,898
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              20
Last Used Ext BlockId...................         372,224
Last Used Block.........................             208

PL/SQL procedure successfully completed.

demo@ORAST01> alter table big_table inmemory;

Table altered.

demo@ORAST01> select owner,segment_name,bytes,
  2         inmemory_size,
  3         populate_status
  4  from v$im_segments ;

no rows selected

demo@ORAST01> select count(*) from big_table;

  COUNT(*)
----------
 100000000

1 row selected.

demo@ORAST01> column owner format a10
demo@ORAST01> select owner,segment_name,bytes,
  2         inmemory_size,
  3         populate_status
  4  from v$im_segments;

OWNER      SEGMENT_NAME                        BYTES INMEMORY_SIZE POPULATE_
---------- ------------------------------ ---------- ------------- ---------
DEMO       BIG_TABLE                      4068499456     837746688 COMPLETED

1 row selected.

demo@ORAST01> set timing on
demo@ORAST01> set serveroutput off
demo@ORAST01> select count(*) from big_table;

  COUNT(*)
----------
 100000000

1 row selected.

Elapsed: 00:00:27.21

demo@ORAST01> select /*+ gather_plan_statistics */ count(*) from big_table;

  COUNT(*)
----------
 100000000

1 row selected.

Elapsed: 00:00:27.60
demo@ORAST01> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  dqts640xzgw2z, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from big_table

Plan hash value: 599409829

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:26.77 |     377K|    495K|
|   1 |  SORT AGGREGATE             |           |      1 |      1 |      1 |00:00:26.77 |     377K|    495K|
|   2 |   TABLE ACCESS INMEMORY FULL| BIG_TABLE |      1 |    100M|    100M|00:00:26.76 |     377K|    495K|
------------------------------------------------------------------------------------------------------------


14 rows selected.

Elapsed: 00:00:00.99
demo@ORAST01>
demo@ORAST01>
demo@ORAST01> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

5 rows selected.

Elapsed: 00:00:00.66
demo@ORAST01>

Connor McDonald
November 26, 2016 - 3:01 am UTC

Can you repeat the experiment with something like:

select col, count(*) group by
or
select count(nullable_col)


select count(*) can often be a special case, because the row count can obtained from the block header, ie, you dont have to actually trawl the block contents.

12c - Inmemory - on Exadata

Rajeshwaran, Jeyabal, December 08, 2016 - 2:26 pm UTC

Sorry for the delay in getting back on this.

demo@ORAST01> select owner,segment_name,bytes,
  2             inmemory_size,
  3             populate_status,
  4             bytes_not_populated
  5      from v$im_segments t ;

OWNER SEGMENT_NAME                        BYTES INMEMORY_SIZE POPULATE_ BYTES_NOT_POPULATED
----- ------------------------------ ---------- ------------- --------- -------------------
DEMO  BIG_TABLE                      4076953600     836632576 COMPLETED          2965176320

1 row selected.

demo@ORAST01>


Since the object is not completely populated into in-memory ( bytes_not_populated > 0 ) - the count query was slow on my part.


Connor McDonald
December 10, 2016 - 1:31 am UTC

Thanks for getting back to us.

IMCU Pruning

Rajeshwaran, Jeyabal, January 09, 2017 - 2:19 am UTC

Team,

Could you help me to understand, why IMCU pruning is not happened with this case?

why does IM-Storage index doesn't kickup with this, scenario? or why should we have to visit all the IMCU in this case for the filter (owner ='SCOTT')
rather than applying IM Storage index to eliminate (owner <> 'SCOTT') values

demo@ORA12C> select count(*) from big_table;

  COUNT(*)
----------
  10000000

1 row selected.

demo@ORA12C>
demo@ORA12C> column segment_name format a12
demo@ORA12C> column inmemory_size format 999999999
demo@ORA12C> select segment_name,
  2    inmemory_size,bytes,
  3    bytes_not_populated,
  4    populate_status,
  5    round(bytes/inmemory_size,2) compress_factor
  6  from v$im_segments ;

SEGMENT_NAME INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED POPULATE_ COMPRESS_FACTOR
------------ ------------- ---------- ------------------- --------- ---------------
BIG_TABLE        292814848 1295450112                   0 COMPLETED            4.42

1 row selected.

demo@ORA12C>
demo@ORA12C> select t1.name, t2.value
  2  from v$sysstat t1,
  3       v$mystat t2
  4  where t1.name like 'IM%'
  5  and t1.statistic# = t2.statistic#
  6  and t1.name in ('IM scan CUs columns accessed',
  7      'IM scan CUs pruned',
  8      'IM scan segments minmax eligible');

NAME                                                    VALUE
-------------------------------------------------- ----------
IM scan CUs columns accessed                              361
IM scan CUs pruned                                          0
IM scan segments minmax eligible                            0

3 rows selected.

demo@ORA12C> set serveroutput off
demo@ORA12C> select max(object_id) from big_table
  2  where owner ='SCOTT';

MAX(OBJECT_ID)
--------------
         93319

1 row selected.

demo@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  7jt3nm66vk1bp, child number 0
-------------------------------------
select max(object_id) from big_table where owner ='SCOTT'

Plan hash value: 599409829

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |    17 (100)|          |
|   1 |  SORT AGGREGATE             |           |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS INMEMORY FULL| BIG_TABLE |  2818 | 30998 |    17   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - inmemory("OWNER"='SCOTT')
       filter("OWNER"='SCOTT')


20 rows selected.

demo@ORA12C> select t1.name, t2.value
  2  from v$sysstat t1,
  3       v$mystat t2
  4  where t1.name like 'IM%'
  5  and t1.statistic# = t2.statistic#
  6  and t1.name in ('IM scan CUs columns accessed',
  7      'IM scan CUs pruned',
  8      'IM scan segments minmax eligible');

NAME                                                    VALUE
-------------------------------------------------- ----------
IM scan CUs columns accessed                              399
IM scan CUs pruned                                          0
IM scan segments minmax eligible                           19

3 rows selected.

demo@ORA12C>
demo@ORA12C>

Maria Colgan
January 24, 2017 - 7:24 pm UTC

Even if the value SCOTT only occurs 1% of the time in your table, it's still possible that Storage Index pruning won't take place, as the value SCOTT may fall between the the MIX, MAX range for every IMCU, even if the value doesn't exist in that IMCU.

Take for example a MIX, MAX range of [APEX, XDB] for the owner column on each IMCU. The value SCOTT falls within that range for every IMCU. Therefore no pruning will occur.

The only way to improve the chances of Storage Index pruning occurring would be to sort the data within the Big_Table on the owner column. It won't guarantee pruning but it will increase the chances.

Here is a quick example showing the difference sorting can make on my version of BIG_TABLE.

--- Unsorted Big_Table

SQL> select count(*) from big_table;

  COUNT(*)
----------
  26226176

SQL> select count(*) from big_table where owner='SCOTT';

  COUNT(*)
----------
      1024


SQL> select t1.name, t2.value
  2  from v$sysstat t1,
  3     v$mystat t2
  4  where t1.name like 'IM%'
  5  and t1.statistic# = t2.statistic#
  6  and t1.name in ('IM scan CUs columns accessed', 'IM scan CUs pruned', 'IM scan segments minmax eligible');

NAME              VALUE
---------------------------------------------------------------- ----------
IM scan CUs columns accessed        0
IM scan CUs pruned         0
IM scan segments minmax eligible       0

SQL> 
SQL> select max(object_id) from big_table where owner ='SCOTT';

MAX(OBJECT_ID)
--------------
  91774

SQL> 
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID g9r6qxh4dczd4, child number 0
-------------------------------------
select max(object_id) from big_table where owner ='SCOTT'

Plan hash value: 599409829

-------------------------------------------------------------------------------
| Id  | Operation      | Name | Rows | Bytes | Cost (%CPU)| 
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |  | | | 10730 (100)|  |
|   1 |  SORT AGGREGATE      |  |     1 |    11 |      |  |
|*  2 |   TABLE ACCESS INMEMORY FULL| BIG_TABLE |  1024 | 11264 | 10730   (3)| 
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - inmemory("OWNER"='SCOTT')
       filter("OWNER"='SCOTT')


20 rows selected.

SQL> 
SQL> select t1.name, t2.value
  2  from v$sysstat t1,
  3     v$mystat t2
  4  where t1.name like 'IM%'
  5  and t1.statistic# = t2.statistic#
  6  and t1.name in ('IM scan CUs columns accessed', 'IM scan CUs pruned', 'IM scan segments minmax eligible');

NAME              VALUE
---------------------------------------------------------------- ----------
IM scan CUs columns accessed       74
IM scan CUs pruned         2
IM scan segments minmax eligible      39


-- Same table sorted by Owner
SQL> select t1.name, t2.value
  2  from v$sysstat t1,
  3     v$mystat t2
  4  where t1.name like 'IM%'
  5  and t1.statistic# = t2.statistic#
  6  and t1.name in ('IM scan CUs columns accessed', 'IM scan CUs pruned', 'IM scan segments minmax eligible');

NAME              VALUE
---------------------------------------------------------------- ----------
IM scan CUs columns accessed        0
IM scan CUs pruned         0
IM scan segments minmax eligible       0

SQL> 
SQL> select max(object_id) from big_table_sorted where owner ='SCOTT';

MAX(OBJECT_ID)
--------------
  91774

SQL> 
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID f4a2z8wsqjdm0, child number 0
-------------------------------------
select max(object_id) from big_table_sorted where owner ='SCOTT'

Plan hash value: 3625205436

-------------------------------------------------------------------------------
| Id  | Operation      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |       |       |  4184|        |
|   1 |  SORT AGGREGATE      |         |     1 |    11 |     |        |
|*  2 |   TABLE ACCESS INMEMORY FULL| BIG_TABLE_SORTED |   904K|  9714K|  4184  
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - inmemory("OWNER"='SCOTT')
       filter("OWNER"='SCOTT')


20 rows selected.

SQL> 
SQL> select t1.name, t2.value
  2  from v$sysstat t1,
  3     v$mystat t2
  4  where t1.name like 'IM%'
  5  and t1.statistic# = t2.statistic#
  6  and t1.name in ('IM scan CUs columns accessed', 'IM scan CUs pruned', 'IM scan segments minmax eligible');

NAME              VALUE
---------------------------------------------------------------- ----------
IM scan CUs columns accessed        2
IM scan CUs pruned        44
IM scan segments minmax eligible      45

ALTER TABLE statement on In-memory objects

Rajeshwaran, Jeyabal, January 09, 2017 - 2:37 am UTC

Team,

the documentation has this

http://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN14231

....
For a database object with a priority level other than NONE, an ALTER TABLE or ALTER MATERIALIZED VIEW DDL statement
involving the database object does not return until the DDL changes are recorded in the IM column store.
....


So what kind of ALTER TABLE statments fall under this category? tried a couple of ALTER TABLE statments, but they come up pretty quickly for me.

demo@ORA12C> alter table big_table inmemory priority high;

Table altered.

demo@ORA12C> select count(*) from big_table;

  COUNT(*)
----------
  10000000

1 row selected.

demo@ORA12C> column partition_name format a10
demo@ORA12C> select segment_name,
  2    partition_name,
  3    inmemory_size,bytes,
  4    bytes_not_populated,
  5    populate_status,
  6    round(bytes/inmemory_size,2) compress_factor
  7  from v$im_segments ;

SEGMENT_NAME   PARTITION_ INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED POPULATE_ COMPRESS_FACTOR
-------------- ---------- ------------- ---------- ------------------- --------- ---------------
BIG_TABLE                     292814848 1295450112                   0 COMPLETED         4.42

1 row selected.

demo@ORA12C>
demo@ORA12C> set timing on
demo@ORA12C> alter table big_table add  x char(100);

Table altered.

Elapsed: 00:00:00.04
demo@ORA12C> alter table big_table nologging;

Table altered.

Elapsed: 00:00:00.00

any info missing for the above questions

Rajeshwaran, Jeyabal, January 17, 2017 - 6:34 am UTC

Team - are you looking for any additional info to answer the above two followups? did i miss something here?
Connor McDonald
January 18, 2017 - 2:14 am UTC

For example

SQL> create table big_table as
  2  select d.* from dba_objects d,
  3    ( select 1 from dual connect by level <= 20 );

Table created.

SQL>
SQL> alter table big_table inmemory priority high;

Table altered.

SQL> alter table big_table drop column owner;

Table altered.


Whilst the last statement was running I did:

--
-- initial result
--
SQL> select segment_name,
  2        inmemory_size,bytes,
  3        bytes_not_populated,
  4        populate_status,
  5        round(bytes/inmemory_size,2) compress_factor
  6  from v$im_segments ;


SEGMENT_NAME                   INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED POPULATE_ COMPRESS_FACTOR
------------------------------ ------------- ---------- ------------------- --------- ---------------
BIG_TABLE                           41222144  293601280                   0 COMPLETED            7.12

--
-- repeated executions during 'drop' command execution
--

SEGMENT_NAME                   INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED POPULATE_ COMPRESS_FACTOR
------------------------------ ------------- ---------- ------------------- --------- ---------------
BIG_TABLE                              65536  293601280           285573120 COMPLETED            4480

SEGMENT_NAME                   INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED POPULATE_ COMPRESS_FACTOR
------------------------------ ------------- ---------- ------------------- --------- ---------------
BIG_TABLE                           40173568  293601280                   0 COMPLETED            7.31



IMCU Pruning

Rajeshwaran, Jeyabal, January 18, 2017 - 1:02 pm UTC

Thanks for the above response.

Please help me with understanding the IMCU pruning.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8731715200346709157#9532856300346596772

BTW: we need to have one more "review" button at the bottom of the page. (for some very old pages having many follow up's we still have to scroll up to find this "review" button :) )
Maria Colgan
January 24, 2017 - 7:26 pm UTC

Please see my response above.

Inmemory size at PDB level

Rajeshwaran, Jeyabal, March 11, 2019 - 2:12 pm UTC

Team,

Was reading this and tried to increase the inmemory_size parameter at PDB level but not possible, but works at CDB level.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/enabling-the-im-column-store.html#GUID-48581A2D-1650-410D-8E02-1D8250CCDBED
demo@PDB1> show parameter inmemory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 512M
demo@PDB1> conn sys/Password-1@pdb1 as sysdba
Connected.
sys@PDB1> alter system set inmemory_size = 514M scope=both ;
alter system set inmemory_size = 514M scope=both
                                                *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

sys@PDB1> alter system set inmemory_size = 640M scope=spfile;
alter system set inmemory_size = 640M scope=spfile
                                                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

sys@PDB1> conn sys/Password-1@orcl as sysdba
Connected.
sys@ORCL> alter system set inmemory_size = 640M scope=spfile;

System altered.

sys@ORCL>

also checked in the Reference guide, no such restriction mentioned.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/INMEMORY_SIZE.html#GUID-B5BEB6BF-5308-485F-920D-CBB584DDDE8F

Is that not possible to increase INMEMORY_SIZE parameter at PDB level? Kindly advice.
Connor McDonald
March 18, 2019 - 10:14 am UTC

I didnt see that on my system - version ?

--
-- cdb
--
SQL> startup force
ORACLE instance started.

Total System Global Area 1795159784 bytes
Fixed Size      9135848 bytes
Variable Size    503316480 bytes
Database Buffers  1006632960 bytes
Redo Buffers      7639040 bytes
In-Memory Area    268435456 bytes
Database mounted.
Database opened.
SQL> alter system set inmemory_size = 384m;

System altered.

--
-- pdb
--
SQL> select sys_context('userenv','con_id') from dual;

SYS_CONTEXT('USERENV','CON_ID')
------------------------------------------------------
3

SQL> alter system set inmemory_size = 128m;

System altered.

SQL> alter system set inmemory_size = 256m;

System altered.


on 18c DB, inmemory_size unable to modify at PDB level

Rajeshwaran, Jeyabal, March 18, 2019 - 11:44 am UTC

Team,

I am on Oracle 18c and here is the full testcase, unable to modify INMEMORY_SIZE setting at PDB level.

sys@PDB1> conn sys/Password-1@pdb1 as sysdba
Connected.
sys@PDB1> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
sys@PDB1> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO
sys@PDB1>
sys@PDB1> show parameter inmemory_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 512M
sys@PDB1> alter system set inmemory_size = 532M scope=both;
alter system set inmemory_size = 532M scope=both
                                               *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option


sys@PDB1> alter system set inmemory_size = 532M scope=spfile;
alter system set inmemory_size = 532M scope=spfile
                                                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option


sys@PDB1> conn sys/Password-1@orcl as sysdba
Connected.
sys@ORCL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

sys@ORCL> show parameter inmemory_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 512M
sys@ORCL> alter system set inmemory_size = 532M scope=both;
alter system set inmemory_size = 532M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified


sys@ORCL> alter system set inmemory_size = 532M scope=spfile;

System altered.

sys@ORCL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
sys@ORCL>

Connor McDonald
March 20, 2019 - 2:56 am UTC

Notice the difference between your demo and mine - mine is altering in system, and you are trying to alter the spfile.

It would not be wise for us to allow spfile modification at pdb level because an obvious scenario could then play out:

1) CDB admin says "I'm allowing 20G in total for inmemory, and allowing 5G of that for PDB1" and sets spfile entries accordingly.

2) PDB1 admin says "I'll just alter my PDB1 setting up to 18G" and bounce my database :-)

It should always be the CDB admin that sets the ceiling for inmemory both globally and for each PDB. PDB admins can then control their inmemory up to that amount.

on 18c DB, inmemory_size unable to modify at PDB level

Rajeshwaran, Jeyabal, March 21, 2019 - 9:56 am UTC

Thanks that helps.

In-memory on RAC database.

Rajeshwaran Jeyabal, July 31, 2021 - 4:02 am UTC

Team,

We are on 18c (18.10) on Exacc, two node RAC database with one node got in-memory size set.
however when we enable inmemory on few partitions, its not getting populated to IMCU.
please do help us to understand why it is not getting populated

demo@PDB1> select inst_id,name,display_value
  2  from gv$parameter
  3  where name ='inmemory_size'
  4  order by inst_id;

   INST_ID NAME                 DISPLAY_VALUE
---------- -------------------- --------------------
         1 inmemory_size        0
         2 inmemory_size        10G

demo@PDB1> select sys_context('userenv','instance') from dual;

SYS_CONTEXT('USERENV','INSTANCE')
----------------------------------------------------------------------
2

demo@PDB1> select partition_name,blocks,inmemory,inmemory_priority,inmemory_distribute,
  2      inmemory_compression,inmemory_duplicate
  3  from dba_tab_partitions
  4  where table_name ='YYY'
  5  and table_owner ='XXX';

PARTITION_     BLOCKS INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
---------- ---------- -------- -------- --------------- ----------------- -------------
PN                  0 ENABLED  HIGH     AUTO            FOR QUERY LOW     NO DUPLICATE
PY            2372890 ENABLED  HIGH     AUTO            FOR QUERY LOW     NO DUPLICATE

demo@PDB1> select /*+ full(t) */ count(*) from XXX.YYY partition(PY);

  COUNT(*)
----------
  40052830

demo@PDB1> select * from gv$im_segments;

no rows selected

demo@PDB1> show parameter inmemory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_adg_enabled                 boolean     TRUE
inmemory_automatic_level             string      OFF
inmemory_clause_default              string
inmemory_expressions_usage           string      ENABLE
inmemory_force                       string      OFF
inmemory_max_populate_servers        integer     46
inmemory_optimized_arithmetic        string      DISABLE
inmemory_prefer_xmem_memcompress     string
inmemory_prefer_xmem_priority        string
inmemory_query                       string      ENABLE
inmemory_size                        big integer 10G
inmemory_trickle_repopulate_servers_ integer     1
percent
inmemory_virtual_columns             string      MANUAL
inmemory_xmem_size                   big integer 0
optimizer_inmemory_aware             boolean     TRUE
demo@PDB1>


Chris Saxon
August 02, 2021 - 10:41 am UTC

You need to be using a service which only runs on the nodes that have IM enabled and ensure parallel queries only run on these nodes.

Andy Rivenes discusses this in detail at:

https://blogs.oracle.com/in-memory/oracle-database-in-memory-on-rac-part-2

inmemory in ATP

Rajeshwaran, Jeyabal, November 14, 2022 - 5:31 am UTC

Team,

how do i set inmemory_size in ATP - getting this error

admin@ATP21C> alter system set inmemory_size = 2G ;
alter system set inmemory_size = 2G
*
ERROR at line 1:
ORA-01031: insufficient privileges


admin@ATP21C> alter system set inmemory_size = 2G scope=spfile;
alter system set inmemory_size = 2G scope=spfile
                                               *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

admin@ATP21C> select json_value( cloud_identity, '$.SERVICE' returning varchar2(20) ) service
  2  from v$pdbs;

SERVICE
--------------------
ATP

admin@ATP21C>

Chris Saxon
November 14, 2022 - 1:39 pm UTC

inmemory in ATP

Rajeshwaran, Jeyabal, November 15, 2022 - 2:44 am UTC

So then i can't work with In-memory feature on ATP/ADW databases?
Connor McDonald
November 15, 2022 - 7:16 am UTC

Database In-Memory on Flash is used as an accelerator, but due to the "autonomous" nature of the service there is currently no Database In-Memory column store allocated.

(This may be revisited in future)

inmemory in ATP

Rajeshwaran, Jeyabal, November 15, 2022 - 7:50 am UTC

when you say "Database In-Memory on Flash is used as an accelerator" what does it means? are you referring to CELLMEMORY - something like this ?
demo@PDB1> alter table t cellmemory;

Table T altered.

Connor McDonald
November 21, 2022 - 11:50 am UTC

To get access to the flash on the exadatas, we need to allocate a small amount of the in-memory store.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.