Buffer Cache Usage for a lob segment if cached

Oracle has added JSON support from 12.1.0.2. At various locations, e.g. Oracle XML DB Developer’s Guide, JSON Developer’s Guide, Oracle recommends “whenever you store JSON data in a BLOB or CLOB column you turn on the LOB cache option for that column. This option is turned off by default.

So if a column of a table is defined as a blob data type and is used to store JSON data, you may want to monitor the usage of Oracle buffer cache for this lob column since it can potentially age other non-LOB pages out of the buffer cache prematurely, especially lob data usually is quite big.

Here I am giving an example to check it:

  • Checking SGA and Buffer cache allocated. As you can see, this database has 90G SGA assigned. Buffer Cache is about 62G sized automatically at the moment.
10:42:05 SQL> show parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 90G
sga_target                           big integer 90G
unified_audit_sga_queue_size         integer     1048576

10:44:48 SQL> select name, value/1024/1024 MB from v$sga order by 2;

NAME                         MB
-------------------- ----------
Fixed Size           4.30154419
Redo Buffers         251.695313
Variable Size        28160.0031
Database Buffers          63744


10:44:18 SQL> select name, bytes/1024/1024 MB, resizeable from v$sgainfo order by 2;

NAME                                     MB RES
-------------------------------- ---------- ---
Data Transfer Cache Size                  0 Yes
Free SGA Memory Available                 0
In-Memory Area Size                       0 No
Fixed SGA Size                   4.30154419 No
Redo Buffers                     251.695313 No
Granule Size                            256 No
Streams Pool Size                       512 Yes
Shared IO Pool Size                     512 Yes
Large Pool Size                         512 Yes
Java Pool Size                          768 Yes
Startup overhead in Shared Pool  1201.24039 No
Shared Pool Size                      26368 Yes
Buffer Cache Size                     63744 Yes
Maximum SGA Size                      92160 No

14 rows selected.
  • Getting the object ID of the lob(blob) column. Using the table name and join dba_objects and dba_lobs, we will get the object ID of the lob segment.
10:50:38 SQL> SELECT data_object_id, object_type FROM DBA_OBJECTS do, dba_lobs dl
WHERE do.object_name = dl.segment_name and dl.table_name = 'JOE_TEST_TABLE';

DATA_OBJECT_ID OBJECT_TYPE
-------------- -----------------------
         15288 LOB
  • Getting the blocks used by this lob segment. v$bh has information for every buffer in the SGA. Each row from this view represents a block. Then we can do a count based on the object ID. Here because we have 8K db block size, we can get the actual size of the buffer cache used by the lob segment. So for the moment, 2481 blocks which is 19.38MB are used.
10:52:23 SQL> select count(*) buffers, count(*) * 8/1024 MB from v$bh where objd = 15288;

   BUFFERS         MB
---------- ----------
      2481 19.3828125
  • Getting the total blobcks of Buffer cache. In this database, there is Keep Buffer Cache defined. The lob segment will use the buffer from DEFAULT.
10:52:42 SQL> SELECT name, block_size, SUM(buffers) FROM V$BUFFER_POOL GROUP BY name, block_size HAVING SUM(buffers) > 0;

NAME                 BLOCK_SIZE SUM(BUFFERS)
-------------------- ---------- ------------
DEFAULT                    8192      7278348
KEEP                       8192       504128
  • Percentage of Buffer cache used by this lob.

Percentage = 2481/7278348 * 100% ~= 0.034%

Taking snapshots periodically throughout a day and you will have a good idea how your JSON data as a lob type used in buffer cache. Below is the query you can wrap into a script to grab the data automatically.

With buffer_count as (
        SELECT count(bh.block#) buffers, count(bh.block#) * 8/1024 sizeMB 
        FROM DBA_OBJECTS do, dba_lobs dl, v$bh bh
        WHERE do.object_name = dl.segment_name and bh.objd = do.data_object_id and 
            dl.table_name = 'TEST_TABLE'),
    total_buffer_count as (
        SELECT SUM(buffers) Total FROM V$BUFFER_POOL where name='DEFAULT')
select bc.sizeMB, bc.buffers, tbc.total, round(bc.buffers/tbc.total*100, 3)||'%' perentage
from buffer_count bc, total_buffer_count tbc;

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 )

Connecting to %s