Skip to main content

Search

Welcome to Tibero GTS!

Performance Improvement Measures Through Data Buffer Cache Area

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;

 

 

Related to