X

Enabling Real-Time Analytics With Database In-Memory

  • April 7, 2021

Memoptimized Rowstore - Fast Lookup

Andy Rivenes
Product Manager

Released as a feature in Oracle Database 18c, the Memoptimized Rowstore Fast Lookup feature is intended to provide very fast retrieval of key-value style queries. The feature makes use of an in-memory hash index instead of a traditional B-tree index for very fast key lookups.

The Memoptimized Rowstore Fast Lookup feature is not part of Database In-Memory. It is however, one of Oracle Database's In-Memory technologies and since it was developed by the same group as Database In-Memory I thought it would be appropriate to post this in the Database In-Memory blog. As we try to point out, Database In-Memory targets analytic workloads by accessing inmemory columnar formatted data and providing orders of magnitude better performance for analytic queries. The Memoptimized Rowstore Fast Lookup feature is a bit different, its goal is speeding up row based queries.

The Fast Lookup feature only supports SELECT queries on primary key values. This can result in even faster access than fully cached index and database block lookups. Therefore the Fast Lookup feature can be very useful for applications that require very high-frequency reads. The database blocks associated with a table enabled for fast lookup are pinned in a new memoptimize pool that is part of the System Global Area (SGA). This ensures a pure in-memory access of both the key and the data values. The following diagram illustrates the feature:

The following example shows the table definition for the FAST_LOOKUP table that we will use in this blog post:

create table FAST_LOOKUP (
  id number(5),
  test_col varchar2(15))
segment creation immediate;

alter table FAST_LOOKUP add constraint fast_lookup_pk primary key (id);

Note that I specified segment creation immediate. In order to enable a table for fast lookup the table segment must exist. This is really only an issue if you decide to try and enable the table for fast lookup at creation or before any data is inserted into the table. Tables enabled for fast lookup must still have a primary key index defined, but when a fast lookup is used access is in-memory and as we will see, uses less resources than a traditional primary key lookup.

A new initialization parameter has been added to support fast lookup. The parameter is MEMOPTIMIZE_POOL_SIZE and it controls the size of this new pool, which is allocated out of the SGA as mentioned earlier. The parameter is not dynamically modifiable and is not controlled by Automatic Shared Memory Management. The memoptimize pool contains two parts, a memoptimize buffer area which uses the same structure as buffers in the database buffer cache, and a hash index that maps to the memoptimize pool buffers. The memoptimize buffers are pinned for the objects that are enabled for fast lookup similar to the idea of the KEEP buffer pool. More details are documented in the Oracle Database Concepts Guide.

It is possible to see the memory allocation of the memoptimize pool. I set the MEMOPTIMIZE_POOL_SIZE to 500 MB:

SQL> show parameters memoptimize

NAME			         TYPE        VALUE
------------------------ ----------- ------------------------------
memoptimize_pool_size    big integer 500M

We can then query the dynamic performance view V$SGA_DYNAMIC_COMPONENTS to see the size of the component name "memoptimize buffer cache":

SQL> select COMPONENT, CURRENT_SIZE, USER_SPECIFIED_SIZE, GRANULE_SIZE 
  2  from v$sga_dynamic_components where COMPONENT like 'memopt%';

COMPONENT                              CURRENT_SIZE  USER_SPECIFIED_SIZE         GRANULE_SIZE
------------------------------ -------------------- -------------------- --------------------
memoptimize buffer cache                  536870912            536870912             16777216

SQL>

Tables have to be specifically enabled for fast lookup. The following example shows my test table FAST_LOOKUP being enabled for fast lookup. Note that a new attribute has been added to the USER_TABLES view so you can determine if a table has been enabled for fast lookup:

SQL> select table_name, MEMOPTIMIZE_READ from user_tables;

TABLE_NAME		       MEMOPTIM
------------------------ --------
FAST_LOOKUP              DISABLED

SQL> alter table TEST_FAST_LOOKUP memoptimize for read;

Table altered.

SQL> select table_name, MEMOPTIMIZE_READ from user_tables;

TABLE_NAME		       MEMOPTIM
------------------------ --------
FAST_LOOKUP              ENABLED

SQL>

Even though the FAST_LOOKUP table has been enabled for fast lookup we still have to add it to the memoptimize pool. To add, or populate, a table into the memoptimize pool the DBMS_MEMOPTIMIZE package is used. The following shows how to populate the FAST_LOOKUP table:

SQL> exec dbms_memoptimize.populate('MEMOPT','FAST_LOOKUP');

PL/SQL procedure successfully completed.

SQL>

Once populated we can now make use of fast lookup. Let's try an example:

SQL> select test_col from fast_lookup where id = 10;

TEST_COL
---------------
fast lookup

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  80dxw74r5nayk, child number 0
-------------------------------------
select test_col from test_fast_lookup where id = 10

Plan hash value: 1705947214

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID READ OPTIM| FAST_LOOKUP      |     1 |    22 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN READ OPTIM         | FAST_LOOKUP_PK   |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   2 - access("ID"=10)


19 rows selected.

SQL>

Notice that there are two new access methods associated with fast lookup:

TABLE ACCESS BY INDEX ROWID READ OPTIM
INDEX UNIQUE SCAN READ OPTIM

This is how you can tell that you used fast lookup, or more accurately that the optimizer thinks it can use fast lookup. More on that in a bit. You can only use fast lookup with a single equality predicate on the primary key. This is a key point since the whole idea of fast lookup is to bypass Oracle's normal access methods and make use of the hash index lookup in the memoptimize pool. This provides the fastest possible access to a specific row's attributes.

Here's an example where we have an IN list instead of a single equality predicate. Even though each id in the IN list could be a primary key lookup the optimizer chooses to do a unique scan and not use fast lookup.

SQL> select test_col from fast_lookup where id in (10,50,51);

TEST_COL
---------------
lookup: 10
lookup: 50
lookup: 51

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  24naxkh411v89, child number 0
-------------------------------------
select test_col from fast_lookup where id in (10,50,51)

Plan hash value: 880261190

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |     4 (100)|          |
|   1 |  INLIST ITERATOR             |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| FAST_LOOKUP    |     3 |    51 |     4   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | FAST_LOOKUP_PK |     3 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   3 - access(("ID"=10 OR "ID"=50 OR "ID"=51))

There are also new session statistics that can be used to observe fast lookup usage. There were 47 "memopt r" statistics when I queried the V$STATNAME view. Of these 47 only a handful are important for our purposes. For example, to see how many rows were populated from a populate command you might query "memopt r rows populated":

SQL> exec dbms_memoptimize.populate('MEMOPT','FAST_LOOKUP');

PL/SQL procedure successfully completed.

 SQL> SELECT n.name, s.value
  2  FROM v$sysstat s JOIN v$statname n
  3  ON n.statistic# = s.statistic# WHERE n.name like 'memopt r rows populated';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
memopt r rows populated                                               10000

SQL>

The other statistics of interest are:

memopt r lookups
memopt r hits
memopt r misses

These statistics will confirm whether the fast lookup was attempted (i.e. memopt r lookups), successful (i.e. memopt r hits) or unsuccessful (i.e. memopt r misses). The fast lookup statistic meanings are documented in the Oracle Database Reference.

Let's try our example again, but this time with autotrace enabled so that we can see statistics:

SQL> set autotrace on
SQL> select * from fast_lookup where id = 10;

        ID TEST_COL
---------- ---------------
        10 fast lookup


Execution Plan
----------------------------------------------------------
Plan hash value: 3956077887

---------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |     1 |    15 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID READ OPTIM| FAST_LOOKUP    |     1 |    15 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN READ OPTIM         | FAST_LOOKUP_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   2 - access("ID"=10)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        652  bytes sent via SQL*Net to client
         52  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Notice that we had no I/O of any kind, this was a pure in-memory lookup. Since autotrace doesn't list all statistics I reconnected and re-ran the query to show the memopt r statistics:

SQL> connect memopt/memopt
Connected.
SQL> select * from fast_lookup where id = 10;

        ID TEST_COL
---------- ---------------
        10 fast lookup

SQL> select t1.name, t2.value
  2  FROM v$sysstat t1, v$mystat t2
  3  WHERE t1.statistic# = t2.statistic# AND t2.value != 0
  4  and t1.name like 'memopt%' ORDER BY t1.name;

NAME                                                              VALUE
-------------------------------------------------- --------------------
memopt r hits                                                         1
memopt r lookups                                                      1

SQL>

We can see that we had a lookup and a hit so this verifies that we did indeed do a fast lookup. These statistics will also show up in an AWR report so it is possible to see how much use is being made of fast lookup for the entire database.

There are additional limitations on tables that are eligible for fast lookup. These are documented in the Database Performance Tuning Guide. Tables enabled for fast lookup cannot be compressed and must have a primary key constraint enabled. In addition the SQL statement cannot be inside a PL/SQL block, and currently partitioned and sub-partitioned tables are not supported.

There are even further limitations as this is not a one size fits all feature. It has a very specialized purpose and there are additional restrictions on column types within tables enabled for fast lookup.

To summarize, the Memoptimize Rowstore fast lookup feature enables very fast key value type lookup queries. This can enable applications to employ high frequency key value type queries with the least impact on database workload.

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.