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;
JoeLi's TechLife 