트러블슈팅 오라클 퍼포먼스 2판 (2017년)
결과 캐싱
캐싱
- 데이터 블록 ==> 버퍼 캐시
- 데이터 딕셔너리 ==> 딕셔너리 캐시
- 커서 ==> 라이브러리 캐시
- 결과 ==> 결과 캐시, 11GR1, Enterprise Edition
서버측 결과 캐시
SQL> SELECT p.prod_category
2 , c.country_id
3 , SUM(s.quantity_sold) AS quantity_sold
4 , SUM(s.amount_sold ) AS amount_sold
5 FROM sales s
6 , customers c
7 , products p
8 WHERE s.cust_id = c.cust_id
9 AND s.prod_id = p.prod_id
10 GROUP BY p.prod_category, c.country_id
11 ORDER BY p.prod_category, c.country_id
12 ;
PROD_CATEGORY COUNTRY_ID QUANTITY_SOLD AMOUNT_SOLD
-------------------------------------------------- ---------- ------------- -----------
Electronics 52769 3247 405416.65
...
Software/Other 52790 237071 8027258.63
81 개의 행이 선택되었습니다.
경 과: 00:00:01.87
Execution Plan
----------------------------------------------------------
Plan hash value: 1866882273
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68 | 3808 | | 2274 (4)| 00:00:28 | | |
| 1 | SORT GROUP BY | | 68 | 3808 | | 2274 (4)| 00:00:28 | | |
|* 2 | HASH JOIN | | 968 | 54208 | | 2273 (3)| 00:00:28 | | |
| 3 | VIEW | index$_join$_003 | 72 | 1512 | | 3 (34)| 00:00:01 | | |
|* 4 | HASH JOIN | | | | | | | | |
| 5 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | | 1 (0)| 00:00:01 | | |
| 6 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | | 1 (0)| 00:00:01 | | |
| 7 | VIEW | VW_GBC_9 | 968 | 33880 | | 2270 (3)| 00:00:28 | | |
| 8 | HASH GROUP BY | | 968 | 26136 | | 2270 (3)| 00:00:28 | | |
|* 9 | HASH JOIN | | 918K| 23M| 1200K| 2228 (2)| 00:00:27 | | |
| 10 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| | 406 (1)| 00:00:05 | | |
| 11 | PARTITION RANGE ALL| | 918K| 14M| | 494 (3)| 00:00:06 | 1 | 28 |
| 12 | TABLE ACCESS FULL | SALES | 918K| 14M| | 494 (3)| 00:00:06 | 1 | 28 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="P"."PROD_ID")
4 - access(ROWID=ROWID)
9 - access("S"."CUST_ID"="C"."CUST_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3185 consistent gets
0 physical reads
0 redo size
2973 bytes sent via SQL*Net to client
470 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
81 rows processed
SQL> SELECT /*+ result_cache */
2 p.prod_category
3 , c.country_id
4 , SUM(s.quantity_sold) AS quantity_sold
5 , SUM(s.amount_sold ) AS amount_sold
6 FROM sales s
7 , customers c
8 , products p
9 WHERE s.cust_id = c.cust_id
10 AND s.prod_id = p.prod_id
11 GROUP BY p.prod_category, c.country_id
12 ORDER BY p.prod_category, c.country_id
13 ;
PROD_CATEGORY COUNTRY_ID QUANTITY_SOLD AMOUNT_SOLD
-------------------------------------------------- ---------- ------------- -----------
Electronics 52769 3247 405416.65
...
Software/Other 52790 237071 8027258.63
81 개의 행이 선택되었습니다.
경 과: 00:00:01.76
Execution Plan
----------------------------------------------------------
Plan hash value: 1866882273
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68 | 3808 | | 2274 (4)| 00:00:28 | | |
| 1 | RESULT CACHE | 4fq786v0sq49wgux1y518akv7q | | | | | | | |
| 2 | SORT GROUP BY | | 68 | 3808 | | 2274 (4)| 00:00:28 | | |
|* 3 | HASH JOIN | | 968 | 54208 | | 2273 (3)| 00:00:28 | | |
| 4 | VIEW | index$_join$_003 | 72 | 1512 | | 3 (34)| 00:00:01 | | |
|* 5 | HASH JOIN | | | | | | | | |
| 6 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | | 1 (0)| 00:00:01 | | |
| 7 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | | 1 (0)| 00:00:01 | | |
| 8 | VIEW | VW_GBC_9 | 968 | 33880 | | 2270 (3)| 00:00:28 | | |
| 9 | HASH GROUP BY | | 968 | 26136 | | 2270 (3)| 00:00:28 | | |
|* 10 | HASH JOIN | | 918K| 23M| 1200K| 2228 (2)| 00:00:27 | | |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| | 406 (1)| 00:00:05 | | |
| 12 | PARTITION RANGE ALL| | 918K| 14M| | 494 (3)| 00:00:06 | 1 | 28 |
| 13 | TABLE ACCESS FULL | SALES | 918K| 14M| | 494 (3)| 00:00:06 | 1 | 28 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ITEM_1"="P"."PROD_ID")
5 - access(ROWID=ROWID)
10 - access("S"."CUST_ID"="C"."CUST_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=4; dependencies=(SH.SALES, SH.PRODUCTS, SH.CUSTOMERS); parameters=(nls); name="SELECT /*+ result_cache */
p.prod_category
, c.country_id
, SUM(s.quantity_sold) AS quantity_sold
, SUM(s."
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3185 consistent gets
0 physical reads
0 redo size
2973 bytes sent via SQL*Net to client
470 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
81 rows processed
SQL> /
PROD_CATEGORY COUNTRY_ID QUANTITY_SOLD AMOUNT_SOLD
-------------------------------------------------- ---------- ------------- -----------
Electronics 52769 3247 405416.65
...
Software/Other 52790 237071 8027258.63
81 개의 행이 선택되었습니다.
경 과: 00:00:00.33
Execution Plan
----------------------------------------------------------
Plan hash value: 1866882273
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68 | 3808 | | 2274 (4)| 00:00:28 | | |
| 1 | RESULT CACHE | 4fq786v0sq49wgux1y518akv7q | | | | | | | |
| 2 | SORT GROUP BY | | 68 | 3808 | | 2274 (4)| 00:00:28 | | |
|* 3 | HASH JOIN | | 968 | 54208 | | 2273 (3)| 00:00:28 | | |
| 4 | VIEW | index$_join$_003 | 72 | 1512 | | 3 (34)| 00:00:01 | | |
|* 5 | HASH JOIN | | | | | | | | |
| 6 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | | 1 (0)| 00:00:01 | | |
| 7 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | | 1 (0)| 00:00:01 | | |
| 8 | VIEW | VW_GBC_9 | 968 | 33880 | | 2270 (3)| 00:00:28 | | |
| 9 | HASH GROUP BY | | 968 | 26136 | | 2270 (3)| 00:00:28 | | |
|* 10 | HASH JOIN | | 918K| 23M| 1200K| 2228 (2)| 00:00:27 | | |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| | 406 (1)| 00:00:05 | | |
| 12 | PARTITION RANGE ALL| | 918K| 14M| | 494 (3)| 00:00:06 | 1 | 28 |
| 13 | TABLE ACCESS FULL | SALES | 918K| 14M| | 494 (3)| 00:00:06 | 1 | 28 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ITEM_1"="P"."PROD_ID")
5 - access(ROWID=ROWID)
10 - access("S"."CUST_ID"="C"."CUST_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=4; dependencies=(SH.SALES, SH.PRODUCTS, SH.CUSTOMERS); parameters=(nls); name="SELECT /*+ result_cache */
p.prod_category
, c.country_id
, SUM(s.quantity_sold) AS quantity_sold
, SUM(s."
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
2973 bytes sent via SQL*Net to client
470 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
81 rows processed
SQL> SELECT status
2 , creation_timestamp
3 , build_time
4 , row_count
5 , scan_count
6 FROM v$result_cache_objects
7 WHERE cache_id = '4fq786v0sq49wgux1y518akv7q'
8 ;
STATUS CREATION BUILD_TIME ROW_COUNT SCAN_COUNT
--------- -------- ---------- ---------- ----------
Published 18/01/19 149 81 1
11GR2 에서의 result cache
- 11GR1 : 힌트 /*\+ result_cache \*/
- 11GR2 : 테이블 result_cache(mode force)
- 한 쿼리 내 모든 테이블이 force 로 지정되어야 result cache 동작
result cache 제어 동적 파라미터
- result_cache_mode : manual(기본, 권장), force(모든 쿼리 캐시)
- result_cache_max_size : 공유풀 결과캐시 메모리 상한값
- result_cache_max_result : 단일 쿼리 결과가 result_cache_max_size 중에서 차지하는 비율, 기본값 5
- result_cache_remote_expirstion : 원격 오브젝트에 대한 쿼리 캐싱 유효시간, 기본값 0
result cache 사용 불가 쿼리
- 비결정적(nondeterministic) 함수, 시퀀스, 임시테이블 사용 쿼리
- 읽기일관성에위배되는 쿼리, DML 수행 후 쿼밋하기 전 수행 쿼리
- 데이터 딕셔너리에 대한 쿼리
DBMS_RESULT_CACHE 패키지
- bypass : 일시적으로 결과캐시를 세션/시스템 레벨에서 활성화/비활성화
- flush : 결과 캐시 제거
- invalidate : 지정된 오브젝트 관련 결과 캐시 무효화
- invalidate_object : 단일 캐시 항목 무효화
- memory_report : 메모리 사용 보고서 생성
- status : 결과 캐시 상태 조회
PL/SQL 함수 결과 캐싱
CREATE OR REPLACE FUNCTION f_rc_y(v_cust_id IN NUMBER)
RETURN NUMBER
RESULT_CACHE
IS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM customers
WHERE cust_id||'' = v_cust_id
;
RETURN v_cnt;
END;
/
CREATE OR REPLACE FUNCTION f_rc_n(v_cust_id IN NUMBER)
RETURN NUMBER
-- RESULT_CACHE
IS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM customers
WHERE cust_id||'' = v_cust_id
;
RETURN v_cnt;
END;
/
SQL> SELECT COUNT(f_rc_y(1)) FROM customers;
COUNT(F_RC_Y(1))
----------------
55500
경 과: 00:00:00.11
SQL> SELECT COUNT(f_rc_n(1)) FROM customers WHERE ROWNUM < 100;
COUNT(F_RC_N(1))
----------------
99
경 과: 00:00:02.52
PL/SQL 함수 결과 캐시 사용불가
- OUT 파라미터를 가지는 함수
- 호출자 권한으로 정의된 함수(12.1 부터 사용 가능)
- 파이프라인된 테이블 함수
- anonymos PL/SQL 블럭 내 정의된 함수
- LOB, REF CURSOR, object, record 타입을 입력/반환 하는 함수
- 예외(Exception) 는 캐시되지 않음
클라이언트 측 결과 캐시
클라이언트 캐시
- 클라이언트에서 동작하는 캐시
- OCI 라이브러리 기반 데이터베이스 드라이버 사용하는 어플리케이션에서 실행된 결과 캐시
- JDBC OCI, ODP>NET, OCII, ODBC
- 캐시의 목적과 동작은 서버 측과 동일
- 클라이언트 측 구문 캐싱을 활용하는 SQL 구문(12장 참조)에서만 캐싱 결과를 사용 할 수 있다.
- 장점 : 클라이언트,서버 간 라운드 트립을 피할 수 있다.
- 단점 : 일관성이 보장되지 않음(폴링방식의 무효화)
- 주의 : 12.1 멀티테넌트 환경에서 지원 안됨.
클라이언트 캐시 관련 파라미터
- 서버 파라미터
- CLIENT_RESULT_CACHE_SIZE
- CLIENT_RESULT_CACHE_LAG
- 클라이언트 파라미터(sqlnet.ora)
- OCI_RESULT_CACHE_MAX_SIZE
- OCI_RESULT_CACHE_MAX_RSET_SIZE
- OCI_RESULT_CACHE_MAX_RSET_ROWS
result cache 사용시기
- 동일 오퍼레이션을 끊임 없이 반복하는 어플리케이션으로 인한 성능 저하
- 모든 쿼리를 캐시해서는 안된다. 선별적 사용
result cache 함정과 착오
- 일관성 보장 안됨 주의
"데이터베이스 스터디모임" 에서 2017년에 "전문가를 위한 트러블슈팅 오라클 퍼포먼스(Second Edition)
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4404
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.