Note
- For OceanBase Database V4.3.x, this parameter was introduced in OceanBase Database V4.3.2.
- For OceanBase Database V4.2.x, this parameter was introduced in OceanBase Database V4.2.3.
Description
lob_enable_block_cache_threshold specifies the threshold for caching the LOB data stored in OUTROW mode that is read during a query.
Limitations
Query the parameter
In the
systenant or a user tenant, you can execute theSHOW PARAMETERSstatement to query the parameter or query theGV$OB_PARAMETERSview for the parameter.Modify the parameter
You can modify the parameter in the
systenant or a user tenant.
Attributes
Attribute |
Description |
|---|---|
| Type | Capacity |
| Default value | 256K |
| Value range | [0B, 512M] |
| Modifiable | Yes. You can use the ALTER SYSTEM SET statement to modify the parameter. |
| Effective upon OBServer node restart | No |
Considerations
If the size of the LOB data stored in OUTROW mode that is read in the current query is smaller than or equal to the value of the lob_enable_block_cache_threshold parameter, the system caches the microblock that stores the LOB data. This way, the LOB data can be directly read from the cache in subsequent queries without disk I/O operations, thus improving query performance for frequently accessed data, especially JSON and GIS data.
Take the table named t as an example.
obclient> create table t(pk int primary key, c1 text);
obclient> insert into t values(1, repeat('1', 9000);
Assume that the lob_enable_block_cache_threshold parameter is set to 8096 B. If the size of the LOB data to read in the current query from this table is 9000 B, which exceeds the threshold specified by the parameter, the caching logic is not triggered. If you set the parameter to 9000 B, the next query for the same data will trigger the caching logic. In this case, disk I/O operations will not be triggered in subsequent queries if the cached microblock is not evicted. Here is an example to set the parameter:
obclient> ALTER SYSTEM SET lob_enable_block_cache_threshold='9000b';
Additionally, subsequent queries for other LOB data in the same microblock will not trigger disk I/O operations either. This improves query performance for LOB data stored in OUTROW mode.
Examples
Set the threshold for caching the LOB data stored in OUTROW mode that is read during a query to 4 KB.
obclient> ALTER SYSTEM SET lob_enable_block_cache_threshold='4K';