SQL Result Cache, PL/SQL Function Cache, Client Side Caches, Database Resident Connection Pooling 등을 이용하여 성능을 개선하는 방법을 배워 보십시오.
SQL Result Cache
메모리는 하드 드라이브에 비해 액세스 속도가 훨씬 빠릅니다. 또 하드 드라이브 아키텍처에 엄청난 혁신이 이루어지지 않는 이상 향후 수 년 간은 이러한 판도가 계속될 공산이 큽니다. 캐싱(caching)의 중요성이 강조되는 것도 바로 이 때문입니다. 캐싱이란 디스크가 아닌 메모리에 데이터를 저장하는 프로세스를 의미합니다. 캐싱은 오라클 데이터베이스 아키텍처의 가장 기초가 되는 원리입니다. 사용자들은 데이터베이스 서버의 디스크가 아닌 버퍼 캐시를 통해 데이터를 전달받습니다.
캐싱은 정적인 데이터를 가진 상대적으로 작은 테이블(예: STATES, PRODUCT_CODES 등)에서 특히 효과가 있습니다. 하지만 기업의 고객 정보를 저장한 CUSTOMER 테이블과 같은 커다란 테이블의 경우를 생각해 봅시다. 이러한 테이블은 정적 데이터의 비중이 높은 편이지만 수시로 고객의 정보가 추가, 삭제된다는 특성이 있습니다.
이러한 테이블에서도 캐싱은 어느 정도 효과를 가질 것입니다. 하지만 캐시를 이용하면서, 변경된 데이터의 올바른 정보를 확인하고 있다는 사실을 어떻게 보장할 수 있을까요? Oracle Database 11g가 SQL Result Cache를 통해 그 해답을 제공합니다. 아래와 같은 쿼리를 고려해 봅시다. 이 쿼리를 실행하여 통계 정보와 응답 시간을 확인해 봅니다:
여기서 몇 가지 참고할 점이 아래와 같습니다:
실행 계획을 통해 풀 테이블 스캔이 수행되었음을 알 수 있습니다.
5,136회의 get(논리적 I/O)이 발생했습니다.
실행 시간은 2.57초가 걸렸습니다.
테이블의 내용이 자주 변경되지 않으므로, 힌트를 사용하여 쿼리 결과를 캐시 메모리에 저장하는 방법을 선택할 수 있습니다.
이 쿼리는 처음 실행된 것과 동일하지만 힌트가 사용되었다는 차이가 있습니다. 두 번째 쿼리를 실행한 결과가 아래와 같습니다
첫 번째 경우와 몇 가지 차이점을 확인할 수 있습니다.
응답 시간이 3초에서 0.01초로 줄어 들었습니다.
Consistent gets가 0으로 줄었습니다. 다시 말해 논리적 I/O가 전혀 발생하지 않았습니다. (힌트가 포함된 쿼리를 처음 실행한 경우라면 캐시에 데이터를 저장하기 위해 논리적 I/O가 발생할 것입니다. 하지만 후속 실행되는 쿼리에서는 데이터를 캐시로부터 가져오므로 논리적 I/O가 전혀 발생하지 않습니다.)
실행 계획을 통해 RESULT CACHE가 사용되었음을 확인할 수 있습니다.
실행 계획 아래의 'note'에서 어떤 유형의 캐시가 어떤 결과값에 대해 사용되었는지 알 수 있습니다.
쿼리 시간의 단축 효과는 놀라운 수준입니다. 두 번째 쿼리에서는 캐시를 사용하였고 데이터베이스 메모리(결과 캐시)로부터 바로 쿼리 결과가 전달된 때문입니다.
SQL Result Cache는 버퍼 캐시, 프로그램 글로벌 영역과 마찬가지로 SGA에 존재하는 캐시의 한 유형입니다. 'result_cache' 힌트를 사용하여 쿼리를 수행하면 오라클은 다른 경우와 마찬가지 방법으로 작업을 실행하는 한편으로 결과를 SQL Result Cache에 저장합니다. 따라서 동일한 쿼리를 재차 수행하면 테이블을 직접 조회하는 대신 캐시로부터 결과를 가져오게 됩니다. 캐시의 크기를 설정하기 위한 초기화 매개변수는 아래와 같습니다:
그렇다면 테이블 로우가 변경된 경우에는 어떻게 될까요? 쿼리를 통해 새로운 값을 확인할 수 있을까요, 아니면 이전 값이 조회될까요? 직접 확인해 봅시다. SQL*Plus 세션을 따로 하나 열어 테이블의 로우를 업데이트합니다
이제 업데이트를 수행한 세션에서 커밋을 실행하고 쿼리를 다시 실행합니다.
FL의 데이터가 자동으로 4로 업데이트 되었음을 주목하시기 바랍니다. 하부 테이블의 데이터가 변경되어 캐시가 무효화되었고, 후속 쿼리가 실행되면서 다이내믹 리프레시 작업이 수행되었습니다. 결국 SQL Result Cache를 사용하더라도 항상 정확한 결과를 보장할 수 있음을 알 수 있습니다.
Materialized View와의 차이z
Materialized View(MV)에 친숙한 사용자라면 이 기능이 MV와 어떻게 다른지 궁금할 수도 있을 것입니다. 차이는 아주 많습니다. 표면적으로 두 가지는 유사해 보일 수 있습니다. 두 가지 모두 결과를 미리 저장해 놓고 있다가 전달하는 방식을 사용합니다. 하지만 그 뿐입니다. MV는 데이터를 데이터베이스 스토리지에 저장하는 반면 SQL Result Cache는 메모리에 저장합니다. SQL Result Cache는 디스크 공간을 전혀 사용하지 않으며 데이터베이스 인스턴스가 셧다운 되거나 result_cache 공간이 부족해지는 경우 삭제 처리됩니다.
또 MV는 정적인 특성을 가지며, 하부 테이블의 데이터가 변경되더라도 MV는 이 사실을 알지 못합니다. query_rewrite_integrity를 stale_tolerated로 설정한 상태에서 MV를 수동으로 리프레시 하지 않으면 사용자는 최신 정보를 반영하지 않은 오래된 데이터를 확인할 수 있습니다. 또는 하부 테이블에 대해 베이스 쿼리(base query)를 재실행해야 합니다(이 작업은 훨씬 오랜 시간이 걸립니다). SQL Result Cache를 이용하면 캐시를 수동으로 리프레시할 필요가 없습니다. 캐시는 쿼리가 실행되는 시점에 자동으로 리프레시 됩니다.
MV는 한층 정교한 rewrite 알고리즘을 제공합니다. 반면 SQL Result Cache는 동일한 쿼리가 다시 실행된 경우, 그리고 하부 데이터가 변경되지 않은 경우에만 사용됩니다. MV의 query rewrite 기능은 MV에 대한 롤업 데이터, 테이블 또는 다른 MV와의 조인, 추가적인 조건부 적용 등 데이터 웨어하우스 환경의 쿼리에서 매우 유용합니다.
다시 말해, MV와 SQL Result Cache는 서로 비교될만한 대상이 아니며 장단점이 제각각 다르고 그 용도 또한 전혀 다릅니다.
서브쿼리
서브쿼리에서도 SQL Result Cache를 사용할 수 있습니다. 아래와 같은 쿼리를 고려해 봅시다:
위의 쿼리에서 캐싱은 인라인 뷰의 서브쿼리에 대해 실행됩니다. 'inner' 쿼리가 변경되지 않는 한 'outer' 쿼리가 변경되더라도 캐시는 여전히 유효합니다.
데이터베이스에서 SQL Result Cache를 위해 사용되는 메모리의 크기를 확인하려면 dbms_result_cache 패키지를 사용합니다:
캐시(결과 캐시와 함수 캐시 모두)를 삭제(flush) 처리하는 방법은 아래와 같습니다:
begin
dbms_result_cache.flush;
end;
위 명령을 실행한 뒤에는, result_cache 힌트를 사용하여 CUSTOMERS 테이블에 기존 쿼리를 실행했을 때 논리적 I/O가 발생함을 확인할 수 있습니다.
물론 쿼리가 한 번 실행되고 나면 다시 결과가 캐시에 저장되고, 후속 실행되는 쿼리는 한층 빠르게 수행될 것입니다. 전체 캐시가 아닌 특정 테이블의 캐시만을 무효화(invalidate) 처리하고자 하는 경우 아래 명령을 사용합니다:
begin
dbms_result_cache.invalidate('ARUP','CUSTOMERS');
end;
SQL Result Cache의 통계를 확인할 때 사용되는 데이터 딕셔너리 뷰는 아래와 같습니다:
SQL Result Cache를 이용하면 많은 수의 쿼리 결과를 캐시에 저장하고 활용할 수 있습니다. 또 사용자가 수동으로 개입하거나 별도의 코드를 작성하지 않아도 하부 테이블이 변경되면 캐시가 자동으로 무효화됩니다.
PL/SQL Function Result Cache
결과값을 반환하기 위해 SQL 쿼리 대신 PL/SQL 함수를 사용하는 경우를 생각해 봅시다. 모듈화된 코드를 구현하기 위해 쿼리 대신 함수를 사용하는 것은 매우 일반화된 관례입니다. 두 개의 테이블이 있다고 가정해 보겠습니다. CUSTOMERS 테이블은 state_code를 포함하는 고객 정보를 저장하고 있습니다. TAX_RATE 테이블은 각 주(state)의 세율(tax rate) 정보를 저장합니다. 고객에 적용되는 세율을 확인하려면 두 테이블의 조인을 수행해야 합니다. 고객 ID를 매개변수로 취하여 state_code를 기준으로 세율 정보를 반환하는 함수가 아래와 같이 작성되었습니다:
아래와 같이 함수를 몇 차례 반복 실행합니다. 함수를 실행할 때마다 경과 시간을 측정하는 것을 잊지 마십시오.
함수는 매번 실행될 때마다 거의 동일한 응답 시간을 보입니다. (함수 내부의 처리를 지원시키기 위해 일부러 sleep 구문을 삽입했습니다. 이렇게 하지 않으면 결과가 너무 빨리 반환될 것입니다.) 코드를 살펴보면 대부분의 경우 함수가 반복 실행되더라도 동일한 결과가 반환될 것임을 알 수 있습니다. 고객의 state 정보와 state에 대한 tax rate 정보는 자주 변경되지 않습니다. 따라서 고객의 tax rate는 거의 항상 동일한 상태를 유지할 것입니다. 그렇다면 이 함수의 실행 결과를 캐시에 저장해 두면 좋지 않을까요?
Oracle Database 11g가 바로 이에 필요한 기능을 제공합니다. 'result_cache'를 이용하면 함수 결과도 캐시에 저장할 수 있습니다. 캐시를 사용 중인 상황에서 state 또는 tax_rate 정보가 변경되면 어떻게 될까요? 함수의 실행 결과 캐시가 하부 테이블에 대해 갖는 종속성을 설정할 수 있습니다. 따라서 테이블의 데이터가 변경되는 경우 캐시를 무효화 처리하고 함수를 재실행하여 캐시를 리빌드하게 됩니다. 'result_cache' 구문(굵은 글꼴로 표시)을 삽입한 함수가 아래와 같습니다:
이제 함수를 생성하여 앞에서와 같은 방법으로 실행합니다:
캐시를 사용하지 않은 경우와 마찬가지로 실행에 1.21초가 걸렸습니다. 하지만 함수를 다시 한 번 더 실행하면 차이를 확인할 수 있습니다:
경과 시간이 0.01초에 불과합니다! 어떻게 된 것일까요? 분명 함수가 처음 실행되었을 때에는 1.21초가 걸렸습니다. 하지만 두 번째 실행될 때는 캐시에 저장된 결과가 사용된 것입니다. 두 번째 실행 과정에서는 함수가 실제로 실행되지 않고 캐시로부터 바로 결과를 가져왔습니다. 따라서 함수 코드에 포함된 1초 간의 sleep 구문이 실행되지 않았습니다.
이 캐시는 customer_id=1에 해당하는 데이터만을 저장하고 있습니다. 다른 고객에 대해 함수를 실행하면 그 결과가 어떠할까요?
위에서 확인할 수 있듯, 함수는 새로운 매개변수가 입력될 때마다 그 결과를 캐시에 저장합니다. 이제 동일한 매개변수를 다시 한 번 사용하면 캐시로부터 결과를 가져올 수 있을 것입니다. 따라서 여러 고객에 대해 함수를 실행함에 따라 캐시에 저장되는 데이터의 크기 또한 증가합니다.
함수 코드에 포함된 "relies on" 구문에 주목하시기 바랍니다. 이 구문은 캐시가 두 개의 테이블, 즉 CUSTOMERS와 TAX_RATE에 종속됨을 정의하고 있습니다. 테이블의 데이터가 변경되면 캐시 또한 리프레시 되어야 합니다. 리프레시 작업은 자동으로 수행됩니다. 데이터가 변경되지 않으면 캐시는 캐시된 결과를 그대로 전달하는 작업만을 수행합니다.
캐시를 사용하지 않도록 설정하고자 하는 경우라면 DBMS_RESULT_CACHE 패키지를 이용할 수 있습니다:
실행 시간을 통해 캐시가 사용되지 않았음을 확인할 수 있습니다.
캐시 vs. 패키지 변수
SQL Result Cache와 PL/SQL Function Cache를 사용하는 대신 패키지 변수(scalar 데이터타입 또는 PL/SQL 컬렉션)을 이용해서도 인-메모리 캐싱의 효과를 얻을 수 있습니다. 이 경우 애플리케이션은 테이블 로우 또는 함수가 아닌 변수에 접근하게 됩니다. 패키지 변수는 메모리를 기반으로 구현되므로 캐시와 같은 효과를 제공합니다. 그렇다면 SQL Result Cache는 어떤 이점을 갖는 것일까요?
여러 가지 차이가 있습니다. 다른 세션을 열어 cust_id = 5인 고객에 대해 함수를 실행한 후, 다시 같은 고객에 대해 함수를 재실행해 봅시다:
실행 시간을 주목하시기 바랍니다. 함수가 실제로 실행되지 않고 캐시로부터 결과를 바로 가져왔음을 알 수 있습니다. 이처럼 함수가 다른 세션을 통해 캐시 되었더라도, 여전히 사용이 가능합니다.
캐시는 세션 단위가 아닌 데이터베이스 인스턴스 단위로 관리됩니다. 이러한 특징은 동일 세션 내에서만 사용되는 패키지 변수와 분명한 차이를 갖습니다.
또 패키지 변수는 변경되는 하부 테이블에 대해 아무런 지식을 갖고 있지 않습니다. 데이터가 변경되는 경우 수동으로 리프레시 작업을 수행해야 하며, 그렇지 않은 경우 변경 이전의 데이터가 조회될 수 있습니다. 반면 SQL Result Cache와 PL/SQL Function Cache는 하부 테이블이 변경될 때 자동으로 리프레시 됩니다.
Client Query Result Cache
클라이언트가 대역폭이 제한된 네트워크 링크를 통해 데이터를 조회해야 하는 경우를 생각해 봅시다. 데이터베이스는 캐시로부터 결과를 바로 전달하지만, 네트워크 링크를 통해 클라이언트에 전달되기까지 오랜 시간이 걸릴 수 있습니다. 물론 Oracle Coherence와 같은 특수한 미들웨어 프레임워크를 이용하면 Java, PHP, Ruby 환경의 데이터를 캐시 처리하는 것이 가능합니다. 하지만 캐시를 클라이언트 레벨에서 관리할 수 있는 보다 보편적인 방법은 없을까요?
Oracle Database 11g에 새로 추가된 Client Query Result Cache가 바로 이러한 기능을 제공합니다. OCI8 드라이버를 이용하는 데이터베이스 클라이언트 스택(C, C++, JDBC-OCI 등)이라면 이 기능의 활용이 가능합니다. Client Query Result Cache는 SQL 쿼리 결과를 서버가 아닌 로컬에 저장할 수 있게 합니다. Client Query Result Cache가 제공하는 혜택이 아래와 같습니다:
- 애플리케이션 개발자들이 SQL Result Cache를 일관성 있게 구현해야 할 필요성을 제거합니다
-비용 면에서 보다 저렴한 클라이언트 메모리를 활용하여 애플리케이션 워킹 셋을 로컬에 저장함으로써, 서버-사이드 쿼리 캐싱을 클라이언트 메모리로 확장합니다.
-서버와 클라이언트 간의 라운드 트립 요소를 제거함으로써 성능을 개선합니다
-서버 리소스 절감을 통해 서버 확장성을 개선합니다.
-메모리 관리, 결과 셋의 동시 접근 등 투명한 캐시 관리 기능을 제공합니다.
-서버의 데이터가 변경되는 경우에도 투명하고 일관성 있는 방식으로 캐시를 유지합니다.
-RAC 환경에서의 일관성을 보장합니다.
-이 기능을 사용하려면 초기화 매개변수를 아래와 같이 수정해 주기만 하면 됩니다:
CLIENT_RESULT_CACHE_SIZE = 1G
위 구문은 클라이언트 캐시를 1GB로 설정하고 있습니다. 이 값은 모든 클라이언트의 캐시 사이즈를 합산한 결과로 정의됩니다. (이 매개변수는 정적으로 정의되므로 데이터베이스를 재시작해 주어야 합니다.) 클라이언트 쪽에서는 SQLNET.ORA 파일의 매개변수를 수정하여 캐시를 설정합니다.
이제 실제 사용 방법에 대해 알아 봅시다. OCI8 드라이버를 이용하여 데이터베이스에 접근하여 SQL 구문을 실행하는 간단한 Java 코드가 아래와 같습니다. SQL 구문은 "select /*+ result_cache */ * from customers"로 정의됩니다. 구문 실행 결과의 캐시를 위해 힌트가 사용되고 있습니다.
위의 코드를 CacheTest.java 파일에 저장하고 컴파일을 수행합니다:
$ORACLE_HOME/jdk/bin/javac CacheTest.java 이제 컴파일된 클래스를 실행합니다:
실행 작업을 몇 차례 반복합니다. 실행이 여러 차례 반복되면서, 아래의 다이내믹 뷰를 통해 클라이언트 캐시에 결과 값이 저장되었음을 확인할 수 있습니다.
select * from client_result_cache_stats$
/
select * from v$client_result_cache_stats
/
Client-Side Query Result Cache는 일반적으로 자주 변경되지 않는 정적 테이블에서 유용하게 활용됩니다. (물론 데이터가 변경되더라도 캐시는 리프레시 됩니다.) Client-side Query Result Cache는 캐시가 서버가 아닌 클라이언트에 저장된다는 점에서 SQL Result Cache와 차이를 갖습니다. 따라서 클라이언트가 데이터를 얻기 위해 서버에 직접 접촉할 필요가 없으며, 그 결과로 네트워크 대역폭과 서버 CPU를 동시에 절감할 수 있습니다. 보다 자세한 정보는 <Oracle Call Interface Programmers Guide>를 참고하시기 바랍니다.
Database Resident Connection Pooling
고전적인 클라이언트/서버 아키텍처에서는 사용자 세션과 데이터베이스 연결 간에 1대1의 관계가 존재합니다. 하지만 웹 기반 시스템에서는 상황이 전혀 다릅니다.
웹 기반 시스템은 기본적으로 "stateless" 속성을 갖습니다. 다시 말해 사용자가 페이지에 방문하면 데이터베이스 연결이 생성되고 페이지 로딩이 완료되면 데이터베이스 연결이 해제됩니다. 나중에 사용자가 다른 페이지 링크를 클릭하더라도 앞에서 사용된 것과는 전혀 다른 데이터베이스 연결이 새로 생성되어 활용됩니다. 이러한 아키텍처는 많은 수의 동시 연결을 관리할 필요가 없다는 장점을 제공합니다.
하지만 연결을 새로 생성하는 데에는 많은 오버헤드가 수반됩니다. 그래서 사용되는 것이 커넥션 풀링(connection pooling)입니다. 커넥션 풀링 환경에서는 페이지로부터 데이터베이스 접근이 요청되면, 기존에 생성된 연결 중 하나를 풀에서 가져와 전달하게 됩니다. 작업이 완료되면 웹 세션은 연결을 다시 풀로 반환합니다.
하지만 고전적인 클라이언트-사이드/미들-티어 커넥션 풀링은 다음과 같은 문제점을 갖고 있습니다:
각각의 풀은 단일 미들-티어 노드로 제한됩니다.
풀의 크기가 증가하는 경우 데이터베이스 서버의 메모리 리소스를 소진시킬 수 있습니다.
워크로드의 분산이 효율적으로 수행되기 어렵습니다.
이러한 문제를 해결하기 위해, Oracle Database 11g에는 Database Resident Connection Pool(DRCP)이라는 이름의 서버-사이드 풀이 새로 추가되었습니다. DRCP는 C, C++, PHP 등 OCI 드라이버를 사용하는 모든 데이터베이스 클라이언트에서 사용 가능합니다.
Oracle Database 11g에는 디폴트 커넥션 풀이 기본적으로 구현되어 있으나, 디폴트 환경에서는 셧다운 된 상태입니다. 커넥션 풀을 시작하려면 아래 명령을 사용합니다:
이제 데이터베이스 연결을 위한 connect 문자열을 변경합니다. Client-Side Result Cache 섹션의 예제 코드를 다시 활용해 보겠습니다:
private String jdbcURL = "jdbc:oracle:oci8:@PRONE3_POOL";
이것으로 모든 작업이 완료되었습니다. 이제 애플리케이션은 서버가 아닌 풀로 연결됩니다. 씬 클라이언트와 표준 JDBC 연결 문자열을 사용하는 경우라면 POOLED 구문을 사용할 수 있습니다:
prolin3. proligence.com:1521/PRONE3:POOLED
위의 구문을 통해 오라클에 기본 설정된 디폴트 풀이 시작됩니다. DBMS_CONNECTION_POOL 패키지에 포함된 CONFIGURE_POOL 프로시저를 사용하여 풀의 설정을 변경할 수 있습니다.
DRCP 기능을 이용하면 단일 풀을 통해 수만 명이 넘는 동시 사용자를 원활하게 지원할 수 있습니다. 또 여러 클라이언트 또는 미들-티어 노드들이 하나의 풀을 공유할 수 있으며, RAC 또는 Data Guard 환경에서는 DRCP와 FAN의 조합을 통해 Fast Connection Failover를 구현할 수 있다는 이점이 있습니다.
제공 : DB포탈사이트 DBguide.net
댓글