효과적인 테이블 결과 캐시 모드 (Table Result Cache Mode) 표시

오라클 데이터베이스에서 테이블 결과 캐시(Table Result Cache)는 SQL 쿼리의 결과를 메모리에 캐싱하여 이후 동일한 쿼리가 실행될 때 디스크 I/O를 최소화하고 응답 시간을 단축시키는 강력한 기능입니다. 테이블 결과 캐시 모드를 효과적으로 활용하면 데이터베이스 성능을 크게 향상시킬 수 있습니다.

테이블 결과 캐시란?

테이블 결과 캐시는 서버 결과 캐시의 한 종류로, 쿼리 결과 전체를 메모리에 저장하여 재사용합니다. 이는 특히 자주 실행되는 읽기 중심의 쿼리에서 효과적이며, 디스크 I/O를 줄여 전반적인 데이터베이스 성능을 향상시킵니다.

테이블 결과 캐시 모드 설정

테이블 결과 캐시 모드는 다음과 같이 3가지 레벨로 설정할 수 있습니다.

• 쿼리 레벨 (SQL Hint): RESULT_CACHE 힌트를 사용하여 특정 쿼리에 대해서만 테이블 결과 캐시를 활성화하거나 비활성화합니다.
• 테이블 레벨 (Table Annotation): 테이블에 어노테이션을 추가하여 해당 테이블에 대한 모든 쿼리에 테이블 결과 캐시를 적용할지 여부를 설정합니다.
• 세션 레벨 (Session Parameter): RESULT_CACHE_MODE 세션 파라미터를 설정하여 세션 내의 모든 쿼리에 테이블 결과 캐시를 적용할지 여부를 설정합니다.

각 모드별 사용법 및 예제

쿼리 레벨 (SQL Hint)

특정 쿼리에 대해서만 테이블 결과 캐시를 사용하거나 사용하지 않도록 설정하려면 SQL 힌트를 사용합니다.

-- 테이블 결과 캐시 활성화 힌트
SELECT /*+ RESULT_CACHE */ deptno, dname FROM dept WHERE deptno = 10;

-- 테이블 결과 캐시 비활성화 힌트
SELECT /*+ NO_RESULT_CACHE */ deptno, dname FROM dept WHERE deptno = 10;

테이블 레벨 (Table Annotation)

테이블에 어노테이션을 추가하여 테이블 전체에 대한 테이블 결과 캐시 동작을 설정합니다.

-- 테이블 어노테이션 추가
ALTER TABLE dept RESULT_CACHE (MODE DEFAULT);

-- 테이블 어노테이션을 FORCE로 변경
ALTER TABLE dept RESULT_CACHE (MODE FORCE);

-- 테이블 어노테이션 제거
ALTER TABLE dept RESULT_CACHE (MODE MANUAL);

• DEFAULT: 쿼리 힌트가 없으면 서버가 캐싱을 고려합니다.
• FORCE: 힌트와 관계없이 캐싱을 강제합니다.
• MANUAL: 테이블 수준에서 캐싱이 결정되지 않고, 힌트가 필요합니다.

세션 레벨 (Session Parameter)

세션 파라미터를 사용하여 세션 전체에 대한 테이블 결과 캐시 동작을 설정합니다.

-- 세션 파라미터 설정
ALTER SESSION SET RESULT_CACHE_MODE = MANUAL;

-- 테이블 결과 캐시 모드 확인
SHOW PARAMETER RESULT_CACHE_MODE;

• MANUAL: 쿼리 힌트 또는 테이블 어노테이션을 따릅니다.
• FORCE: 모든 쿼리 결과에 대해 캐싱을 시도합니다.

테이블 어노테이션과 세션 파라미터 조합

테이블 어노테이션과 세션 파라미터를 함께 사용하면 더욱 세밀한 제어가 가능합니다. 다음 표는 다양한 조합에 따른 효과적인 테이블 결과 캐시 모드를 보여줍니다.

실제 사용 예시

다음은 테이블 결과 캐시를 사용하여 성능을 개선하는 예시입니다.

-- 1. 테이블 생성 및 데이터 삽입
CREATE TABLE sales (
    prod_id NUMBER PRIMARY KEY,
    prod_name VARCHAR2(50),
    quantity NUMBER,
    sales_date DATE
);

INSERT INTO sales (prod_id, prod_name, quantity, sales_date) VALUES (1, 'Product A', 100, SYSDATE);
INSERT INTO sales (prod_id, prod_name, quantity, sales_date) VALUES (2, 'Product B', 50, SYSDATE);
INSERT INTO sales (prod_id, prod_name, quantity, sales_date) VALUES (3, 'Product C', 75, SYSDATE);
COMMIT;

-- 2. 테이블에 RESULT_CACHE 어노테이션 추가
ALTER TABLE sales RESULT_CACHE (MODE FORCE);

-- 3. 쿼리 실행 (최초 실행 시 캐싱)
SELECT prod_id, prod_name, quantity FROM sales WHERE quantity > 60;

-- 4. 쿼리 재실행 (캐시된 결과 사용)
SELECT prod_id, prod_name, quantity FROM sales WHERE quantity > 60;

테이블에 RESULT_CACHE (MODE FORCE) 어노테이션이 추가되었으므로, 쿼리가 처음 실행될 때 결과가 캐싱되고 이후 실행에서는 캐시된 결과를 사용하여 응답 시간이 단축됩니다.

주의사항 및 팁

• 테이블의 데이터가 자주 변경되면 테이블 결과 캐시의 효과가 줄어들 수 있습니다.
• 서버 리소스 사용량을 고려하여 캐시 크기를 적절하게 설정해야 합니다.
• DBMS_RESULT_CACHE 패키지를 사용하여 캐시 메모리 사용량을 모니터링하고, 필요에 따라 캐시를 플러시할 수 있습니다.
• 테이블 결과 캐시 모드는 서버 레벨에서만 동작하고, 클라이언트 레벨에서는 동작하지 않습니다.

결론

테이블 결과 캐시 모드는 데이터베이스 성능을 향상시키는 강력한 도구입니다. 쿼리 레벨, 테이블 레벨, 세션 레벨에서 모드를 적절히 구성하고 관리함으로써, 응답 시간 단축 및 디스크 I/O 감소를 통해 전반적인 데이터베이스 성능을 최적화할 수 있습니다.