Oracle INMEMORY option for heap table

Oracle inmemory options allows users to store their data in memory. This is how we can enable inmemory option which provides faster access to table data.

SQL> show parameter inmemory_size

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 inmemory_size                        big integer 0

Here in my database inmemory  size is set to zero. We can change it by editing this parameter, restart the database after changing this parameter.

SQL> alter system set inmemory_size=100M scope=spfile;

System altered.

SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup open;
 ORACLE instance started.

Total System Global Area 2432696320 bytes
 Fixed Size 2927288 bytes
 Variable Size 637535560 bytes
 Database Buffers 1660944384 bytes
 Redo Buffers 13848576 bytes
 In-Memory Area 117440512 bytes
 Database mounted.
 Database opened.
 SQL> alter pluggable database all open;

Pluggable database altered.

Check the value of inmemory_size again for the database.

SQL> show parameter inmemory_size

NAME                                  TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 inmemory_size                        big integer 112M
 SQL>

Now we can see the parameter is set to 112MB and this can be used to store our table data. Now inmemory store doesn’t contain any table and this can be verified by the below command.

SQL> select count(*) from v$im_segments;

COUNT(*)
 ----------
 0

Now, pin the table into inmemory store.

SQL> alter table my_tab inmemory;

Table altered.

SQL> select count(*) from v$im_segments;

COUNT(*)
 ----------
 0

Table is stored in INMEMORY store only after we query it first time.

SQL> select count(*) from v$im_segments;

COUNT(*)
 ----------
 0

SQL> select count(*) from my_tab;

COUNT(*)
 ----------
 10000

SQL> select count(*) from v$im_segments;

COUNT(*)
 ----------
 1

Execution plan clearly show if the table is accessed from memory. So, lets get the execution plan for the above count statement.

 SQL> set autotrace traceonly explain;
SQL> select count(*) from my_tab;

Execution Plan
----------------------------------------------------------
Plan hash value: 2076930067

------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |       1 |       1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE         |         |       1 |          |         |
|   2 |   TABLE ACCESS INMEMORY FULL| MY_TAB |       3 |       1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s