Document Type | Technical Information
Category | Administration
Applicable Product Version | Tibero7.2.3
Document Number | TADTI194
Overview
This document summarizes methods to improve performance by utilizing the data buffer cache area.
Method
1. Expanding the Data Buffer Cache Area
If space is insufficient, data cannot be loaded at once and must be loaded multiple times in parts, which can degrade performance. (Sufficient memory allocation is necessary)
2. Utilizing the KEEP Data Buffer Cache Area
In a DB environment, frequently used table data can be resident in the Keep pool area to reduce DISK IO during query execution. To use this feature, you must set the DB_KEEP_CACHE_SIZE parameter value. (Cannot be applied dynamically, requires restart)
As of Tibero 7.2.4, DB_KEEP_CACHE_SIZE parameter information
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value | 0 |
| Class | Optional, Adjustable, Static, System |
| Range |
When setting DB_KEEP_CACHE_SIZE, the general Buffer value should be set as DB_CACHE_SIZE - DB_KEEP_CACHE_SIZE.
DB_KEEP_CACHE_SIZE can be adjusted within the DB_CACHE_SIZE limit.
If the OS memory is expanded and the DB instance's TOTAL_SHM_SIZE and DB_CACHE_SIZE increase compared to before, DB_KEEP_CACHE_SIZE can also be adjusted within the increased DB_CACHE_SIZE.
Example using only the DEFAULT POOL without separate designation (TOTAL_SHM_SIZE=2G, MEMORY_TARGET=4G)
Example after setting DB_CACHE_SIZE=100M (TOTAL_SHM_SIZE=2G, MEMORY_TARGET=4G)
3. Utilizing the RECYCLE Data Buffer Cache Area
Assign the RECYCLE data buffer cache area when querying tables that are rarely used and occasionally accessed but have very large sizes.
To use the RECYCLE data buffer cache area, you must set the DB_KEEP_CACHE_SIZE parameter value. (Cannot be applied dynamically, requires restart)
As of Tibero 7.2.4, DB_RECYCLE_CACHE_SIZE parameter information
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value | 0 |
| Class | Optional, Adjustable, Static, System |
| Range |
Example query result after setting DB_RECYCLE_CACHE_SIZE=100M (TOTAL_SHM_SIZE=2G, MEMORY_TARGET=4G)
4. Caching Tables
Perform warmup queries or jobs to keep frequently used objects resident in the buffer pool continuously.
Full scan warmup query method
select /*+ FULL(COLUMN_NMAE)*/ COUNT(*) FROM TARGET_TABLE_NAME;
Method to cache using JOB
CREATE OR REPLACE PROCEDURE TARGET_TABLE_NAME_WARMUP
IS
my_variable NUMBER;
BEGIN
select /*+ FULL(COLUMN_NMAE)*/ COUNT(*) FROM TARGET_TABLE_NAME;
DBMS_OUTPUT.PUT_LINE('Number of TARGET: ' || my_variable);
END TARGET_TABLE_NAME_WARMUP;
DECLARE job_no number;
begin
dbms_job.submit (job_no,'TARGET_TABLE_NAME_WARMUP;',sysdate,'SYSDATE + 30/60/24');
end;
/
5. Flushing the Data Buffer Cache Area
Remove unnecessary data by flushing the data buffer cache area to free up space.
alter system FLUSH BUFFER_CACHE;