6-2 Performance Management - Use Result Cache

When using a separate memory pool the result of SQL query and PL/SQL function can be cached in the memory which leads a huge performace gain, because the query is not executed again and the blocks are read from the cache. The result is cached in the new area called result cache which is the part of shared pool in the SGA. A new initialization parameter RESULT_CACHE_MAX_SIZE is used to set the size fo this memory pool. If it is set to 0, then the result cache feature is disabled. To enable caching all SQL statements RESULT_CACHE_MODE parameter must be set to FORCE. This parameter can be enabled at database as well as the session level.

 

/* 자동적으로 테이블에 관한 모든 쿼리를 result_cache로 저장하는 방법 */

CREATE TABLE table1 (id NUMBER)
RESULT_CACHE (MODE FORCE)

 

 

 

 

 

난이도: ★★☆☆☆

문제 1: Result Cache를 활성화하고, status를 체크하여라. (2분)

 

SELECT DBMS_RESULT_CACHE.STATUS() FROM dual;

show parameter result

 

 

 

난이도: ★★☆☆☆

문제 2: 테이블을 생성하고, session level에서 result cache를 활성화하고, 테이블을 쿼리한 후 메모리에 결과를 집어넣어라. Cached Objects와 Attribute를 확인하라. 그다음에 result cache를 사용하지 않고 같은 쿼리를 실행하여라. (10분)

 

이번에는 메모리에 캐싱된 result 값을 가져오는 것이 아닌, 파싱부터 처음부터 진행하도록 한다. buffer_cache와 shared_pool를 flush하고, 다시 쿼리했더니, resursive call이 4에서 32로 늘어났고, elapsed time도 근소하지만 0.01초에서 0.11초로 늘어났다.

/* 세션 레벨로 result_cache_mode를 FORCE 모드로 설정: 자세한 것은 Oracle Docs(Performance Management)에서. */
ALTER SYSTEM SET result_cache_mode=FORCE;

SET TIMING ON
SET AUTOTRACE TRACEONLY EXP STAT

CREATE TABLE table01
AS
SELECT	object_type, object_id
FROM	dba_objects
WHERE	rownum <= 100;

SELECT	*
FROM	table01;

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT	*
FROM	table01;

 

 

 

 

 

난이도: ★★☆☆☆

문제 3: result cache option을 활성화하고 쿼리한 후, 결과값이 memory에서 왔는지 확인하기 위해 result cache performance view를 체크하여라. (10분)

 

/* DBMS_RESULT_CACHE 패키지를 이용하여 result cache의 상태를 확인하거나(status), result cache를 활성화하거나(memory_report), result cache 내역을 삭제하거나(flush), 일부 result cache를 비활성화할 수 (novalidate) 있다. */

SELECT	dbms_result_cache.status() FROM dual;

EXEC dbms_result_cache.memory_report(detailed=> true);

EXEC dbms_result_cache.flush;

EXEC dbms_result_cache.novalidate('OWNER_NAME', 'SCHEMA_NAME');


/* v$fixed_table 테이블을 쿼리하여 result cache 관련 통계 내용을 담고 있는 뷰 리스트를 확인할 수 있다. */
SELECT	name
FROM	v$fixed_table
WHERE	name LIKE 'V$RESULT%';

 

 

 

 

난이도: ★★★★☆

문제 4: RESULT_CACHE 옵션을 붙여서 function을 생성하여라. 그리고 이것이 정상적으로 작동하는지 확인하여라. (15분)

 

/* The result of PL/SQL can also be kept in the result cache. To put the result of the PL/SQL object to the memory, RESULT_CACHE option is used. */

 

/* The result of PL/SQL can also be kept in the result cache. To put the result of the PL/SQL object to the memory, RESULT_CACHE option is used. */

CREATE TABLE tbl_function_result_cache 
AS
SELECT	*
FROM	dba_objects
WHERE	rownum <= 3;

INSERT INTO tbl_function_result_cache
SELECT * FROM tbl_function_result_cache;

CREATE SEQUENCE myseq;




CREATE OR REPLACE FUNCTION fn_result_cache (p_name IN VARCHAR2)
	RETURN VARCHAR2
	RESULT_CACHE
IS
	id	NUMBER := 0;
BEGIN
	SELECT myseq.NEXTVAL INTO id FROM dual;
	
	id := id + 1;
	RETURN INITCAP (LOWER(p_name));
END;
/

CREATE OR REPLACE FUNCTION fn_without_result_cache (p_name IN VARCHAR2)
	RETURN VARCHAR2
IS
	id	NUMBER := 0;
BEGIN
	SELECT myseq.NEXTVAL INTO id FROM dual;
	
	id := id + 1;
	RETURN INITCAP (LOWER(p_name));
END;
/



SELECT	fn_without_result_cache(object_name)
FROM	tbl_function_result_cache;
SELECT	myseq.CURRVAL FROM dual;

SELECT	fn_result_cache(object_name)
FROM	tbl_function_result_cache;
SELECT	myseq.CURRVAL FROM dual;

SELECT	id, name, cache_id, row_count, block_count, type, status
FROM	v$result_cache_objects;