Poor Man’s In-Memory Caching

Introduction

It is common knowledge, or at least it should be, that Oracle has
released version 12.1.0.2 in June 2014. which has a feature called
“Oracle In-Memory”. The name is a tad confusing, since many
features were called “in-memory” before, from “Very Large
Memory” to Times 10 product.

This “In Memory” option is a licensed product featuring
columnar storage, in addition to the normal buffer storage, almost
identical to IBM’s “Blu acceleration” product, released for DB2
in 2013. Essentially, it’s like creating an in-memory bitmap index on
the low selectivity column, but without adverse effects when it comes
to locking. Obviously, as In Memory option is a licensed product, it
cannot be what the title is referring to.

There is another caching new feature built into 12.1.0.2, which is
much less spectacular but still useful, if properly understood. It is
not unlike the KEEP buffer pool, known from the previous versions,
although it is a bit less granular. This feature is controlled by the
init parameter named DB_BIG_TABLE_CACHE_PERCENT_TARGET, with very
suggestive and self-explanatory name and can be monitored by the two
V$ tables, namely V$BT_SCAN_CACHE and V$BT_SCAN_OBJ_TEMPS. Both the
parameter and the V$ tables are described in the reference manual.

Essentially, by setting this parameter on a single instance
database, the instance allocates a percent of the buffer cache for
caching large tables. What are large tables? Apparently, the same
criteria as for direct reads is applied: if the table is larger than
2% of SGA, it is considered a large table. This is explained in
detail by Tanel Poder in his excellent article.
The same criteria is used to decide whether the table is big or not.

Details

It is not possible to control this feature on the table basis, by
specifying something like BUFFER_POOL KEEP in STORAGE clause for the
table. All large tables are cached, when a scan is executed. It
doesn’t necessarily need to be a full table scan

SQL>
startup

ORACLE
instance started.

Total
System Global Area 838860800 bytes

Fixed
Size 2929936 bytes

Variable
Size 335547120 bytes

Database
Buffers 494927872 bytes

Redo
Buffers 5455872 bytes

Database
mounted.

Database
opened.

SQL>
alter session set container=pdbtest;

Session
altered.

SQL>
select object_count from V$BT_SCAN_CACHE;

OBJECT_COUNT

————


0

SQL>
set autotrace on

SQL>
select count(*) from scott.t1;

COUNT(*)

———-

2000000

Execution
Plan

———————————————————-

Plan
hash value: 227768458

——————————————————————————

|
Id | Operation | Name | Rows | Cost (%CPU)| Time |

——————————————————————————

|
0 | SELECT STATEMENT | | 1 | 211 (6)| 00:00:01 |

|
1 | SORT AGGREGATE | | 1 | | |

|
2 | INDEX FAST FULL SCAN| SYS_C0011355 | 2565K| 211 (6)|
00:00:01 |

——————————————————————————

Note

—–


dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-

947
recursive calls


0 db block gets

11489
consistent gets

4191
physical reads


0 redo size

542
bytes sent via SQL*Net to client

552
bytes received via SQL*Net from client


2 SQL*Net roundtrips to/from client

698
sorts (memory)


0 sorts (disk)


1 rows processed

SQL>
select object_count from V$BT_SCAN_CACHE;

OBJECT_COUNT

————


1

So, the cache was populated by the full index scan. The same
caching goes into effect if a range scan is used. Caching takes
place when the big table is accessed, not only by the full table
scan. Also, optimizer is unaware that the table is cached. This claim
will be proven later. First, let’s check that the object is the right
one.

SQL>
select object_count from v$bt_scan_cache;

OBJECT_COUNT

————


1

SQL>
select ts#,dataobj#,policy from V$BT_SCAN_OBJ_TEMPS;

TS#
DATAOBJ# POLICY

———-
———- ———-

196612 96464
MEM_ONLY

SQL>
select owner,object_name,object_type

2
from dba_objects

3
where data_object_id=96464;

OWNER

——————————————————————————–

OBJECT_NAME

——————————————————————————–

OBJECT_TYPE

———————–

SCOTT

T1

TABLE

Sure enough, it’s our table SCOTT.T1, which has 2M generated rows
and takes around 64M of disk space. The TS# column is supposed to be
the number of the tablespace, but the number is obviously wrong since
I have only 7 tablespaces in PDBTEST and 5 in CDB$ROOT. Connection ID
in the V$BT_SCAN_OBJ_TEMPS is also consistently 0, despite the fact
that the object resides in CON_ID 4.. Now, it is time to prove that
optimizer doesn’t know about caching. The first thing to execute is
counting without any hints:

SQL>
set timing on

SQL>
select ts#,dataobj#,con_id from V$BT_SCAN_OBJ_TEMPS;

TS#
DATAOBJ# CON_ID

———-
———- ———-

196612 96464
0

Elapsed:
00:00:00.00

SQL>
set autotrace on

SQL>
select count(*) from scott.t1;

COUNT(*)

———-

2000000

Elapsed:
00:00:00.07

Execution
Plan

———————————————————-

Plan
hash value: 227768458

——————————————————————————

|
Id | Operation | Name | Rows | Cost (%CPU)| Time |

——————————————————————————

|
0 | SELECT STATEMENT | | 1 | 211 (6)| 00:00:01 |

|
1 | SORT AGGREGATE | | 1 | | |

|
2 | INDEX FAST FULL SCAN| SYS_C0011355 | 2565K| 211 (6)|
00:00:01 |

——————————————————————————

Note

—–


dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-


0 recursive calls


0 db block gets

3911
consistent gets


0 physical reads


0 redo size

542
bytes sent via SQL*Net to client

552
bytes received via SQL*Net from client


2 SQL*Net roundtrips to/from client


0 sorts (memory)


0 sorts (disk)


1 rows processed

Elapsed:
00:00:00.08

The plan still reflects the normal situation, without any caching.
The path selected is the fast full index scan of the primary key
index. Now, let’s try forcing the full scan, using a hint:

SQL>
select /*+ full(t1) */ count(*) from scott.t1;

COUNT(*)

———-

2000000

Elapsed:
00:00:00.03

Execution
Plan

———————————————————-

Plan
hash value: 3724264953

——————————————————————-

|
Id | Operation | Name | Rows | Cost (%CPU)| Time |

——————————————————————-

|
0 | SELECT STATEMENT | | 1 | 596 (33)| 00:00:01 |

|
1 | SORT AGGREGATE | | 1 | | |

|
2 | TABLE ACCESS FULL| T1 | 2565K| 596 (33)| 00:00:01 |

——————————————————————-

Note

—–


dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-


0 recursive calls


0 db block gets

7883
consistent gets


0 physical reads


0 redo size

542
bytes sent via SQL*Net to client

552
bytes received via SQL*Net from client


2 SQL*Net roundtrips to/from client


0 sorts (memory)


0 sorts (disk)


1 rows processed

From the statistics, it is visible that the cache was used, since
there were no physical reads. Full table scan used the cache, it
should have been selected by the optimizer. The index scan will also
be selected even if the table cache is populated by the full table
scan and index blocks are not cached, which means that optimizer
doesn’t take into account the big table caching

Now, the next interesting thing happens when the cache is
cleaned. Flushing the buffer cache cleans the big table cache, but
the V$ tables do not recognize that:

SQL>
alter system flush buffer_pool all;

System
altered.

Elapsed:
00:00:00.01

SQL>
select ts#,dataobj#,con_id from V$BT_SCAN_OBJ_TEMPS;

TS#
DATAOBJ# CON_ID

———-
———- ———-

196612 96464
0

Elapsed:
00:00:00.00

SQL>
set autotrace on

SQL>
select /*+ full(t1) */ count(*) from scott.t1;

COUNT(*)

———-

2000000

Elapsed:
00:00:01.97

Execution
Plan

———————————————————-

Plan
hash value: 3724264953

——————————————————————-

|
Id | Operation | Name | Rows | Cost (%CPU)| Time |

——————————————————————-

|
0 | SELECT STATEMENT | | 1 | 596 (33)| 00:00:01 |

|
1 | SORT AGGREGATE | | 1 | | |

|
2 | TABLE ACCESS FULL| T1 | 2565K| 596 (33)| 00:00:01 |

——————————————————————-

Note

—–


dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-


79 recursive calls


0 db block gets

8058
consistent gets

8194
physical reads


0 redo size

542
bytes sent via SQL*Net to client

552
bytes received via SQL*Net from client


2 SQL*Net roundtrips to/from client


6 sorts (memory)


0 sorts (disk)


1 rows processed

SQL>

The V$BT_SCAN_OBJ_TEMPS reports that the SCOTT.T1 table is
cached, but the query causes a bunch of physical reads, which
couldn’t have happened if the table was really cached.

Conclusion

Big table caching is less granular than using the KEEP buffer
pool, which would be possible on the per-table basis and on read
block basis so it would be less efficient than using the KEEP pool.
On the other hand, it is much simpler to use, just allocate percent
of memory and forget it. The thing that bothers me most is that this
feature is also activated by a range scan and that the decision is
made solely on the basis of the table size. This guarantees bringing
into the cache some tables that I wouldn’t normally want to bring
into the cache and thereby making the cache less useful. Other than
that, this looks like an interesting new feature.

Advertisements

About mgogala

I am a consultant with an extensive Oracle DBA experience. I have worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

One Response to Poor Man’s In-Memory Caching

  1. Pingback: Oracle 12c, big table caching | dbwhisperer

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