Overview
lob_enable_block_cache_threshold specifies the threshold for enabling the LOB query cache for OUTROW storage.
Privilege requirements
Query parameters
Both the
systenant and all user tenants can query this parameter using theSHOW PARAMETERSstatement or theGV$OB_PARAMETERSview.Modify parameters
Both the
systenant and user tenants can modify this parameter.
Attributes
Attribute |
Description |
|---|---|
| Parameter type | CAP |
| Default Value | 256K |
| Value range | [0B, 512M] |
| Modifiable | Yes, you can use the following methods:ALTER SYSTEM SETStatement modification. |
| Whether to restart the OBServer node for the change to take effect | No, the setting takes effect immediately. |
Usage instructions
When reading LOB data stored in OUTROW format, if the length of the LOB data being read is less than or equal to lob_enable_block_cache_threshold, the microblocks containing that LOB data are cached during the query. This eliminates the need for disk I/O in subsequent queries, thereby improving the performance of hotspot data access. The performance improvement is particularly significant for JSON and spatial data types.
For example, the initial data is in table t.
obclient> create table t(pk int primary key, c1 text);
obclient> insert into t values(1, repeat('1', 9000);
When reading this LOB data, if the lob_enable_block_cache_threshold parameter is set to 8096B, the length of the LOB data to be read (9000B) exceeds this threshold, so the caching logic is not triggered. If the value is set to 9000B, the caching logic will be triggered during the next read. If the cache has not been evicted by the time of the next read, disk I/O will not be triggered. The configuration method is as follows:
obclient> ALTER SYSTEM SET lob_enable_block_cache_threshold='9000b';
Furthermore, if the next query accesses other LOB data within the same microblock, disk I/O will not be triggered, thereby improving the query performance for OUTROW-stored LOBs.
Configuration example
Set the length of the LOB query cache stored in OUTROW to 4k.
obclient> ALTER SYSTEM SET lob_enable_block_cache_threshold='4K';