Database Oracle Administrator_SGA의 주요구성요소(Shared Pool 및 기타 요소)

Step 1. Oracle Server 전체 구조 살펴보기

- Sub : SGA의 주요구성요소(Shared Pool 및 기타 요소)

 

바로 이어쓰는 Shared Pool!! 필 충만합니당 ㅎㅎ

Shared Pool에는 많은 구성요소가 들어있었습니다. 전체적인 그림을 볼까요?

 

 

 

이런 구성요소들이 있었습니다! Shared Pool의 뜻부터 천천히 살표보도록 하겠습니다.

Shared Pool을 한글말로? 공유풀이죵. 말그대로 다른 사용자들과 공유해서 사용하는 곳이라는 겁니다.

위 그림에서는 Server Result Cache가 있는데 이것은 11g부터 추가가 된것임을 알려드립니다.

 

가장 큰 조각인 Library Cache는 무었일까요? 무엇이기에 Shared SQL Area와 Private SQL Area를 품고 있을까요?

이 부분은 Soft Parse할 때 사용되는 공간으로 이미 수행 되었던 SQL 문장이나 PL/SQL 문자의 Parse Code와 해당 SQL, PL/SQL 문장, 실행계획 등이 저장되어있고 저번에 말했듯이 Shared Pool도 LRU 알고리즘이라고 했죠? 이부분도 LRU로 관리가 되어집니다.

Soft Parse가 뭐지? 하실텐데요. 간단히 SQL이 빠르게 동작하는 과정이라고 생각하시면됩니다. 좀 어려운 부분이라 Oracle Architecture를 살펴본 후 보도록 하겠습니다.

 

다음으로 Data Dictionary Cache는? 구문분석이나 옵티마이져가 실행계획을 세울 때 사용되는 주요 Dictionary들이 Row단위로 Cache되어있습니다. 다~ 모르는 용어들이죠?ㅎㅎ 구문분석이 Parse고 SQL 실행원리중 Optimization이 있고 Dictionary는 사전이 줄단위로 되어 있다고 생각하면 되는데요..ㅎ 이렇게해도 잘 모르겠죵ㅎㅎ 간단히 말해보겠습니다.

Data Dictionary Cache는 사전입니다. Oracle Database의 모든것에 대한 사전! 사전을 찾을때 abcd 순 혹은 가나다라 순으로 정렬을 시키고 찾고 하죠? 그래도 찾는데 시간이 오래걸리기도 하구요..ㅎ 그래서 Data Dictionary를 이용하면 시간도 많이 걸립니다. SQL을 실행 시킬때 키워드를 분석하는 문법 분석 및 테이블, 컬럼검사를 하는데 이것을 구문분석에 포함시키고 이 결과가 앞에서 말한 Library Cache에 없으면 Data Dictionary를 이용하여 실행계획을 만들고 하는 것입니다. 쉽게 설명하려고 해도 잘 안되네요..ㅎ 뒤에 SQL 실행 원리에서 좀더 자세히 예를 들어서 설명해 볼께요..ㅎ 지금은 딱히 생각나는 예가 없어서 ;;

 

Data Dictionary 다음에 있는 것이 Server Result Cache 입니다. 11g부터 추가가 됐다고 말을 했었죠?ㅎ

Server Result Cache는 말그대로 역할은 Cache를 저장 하는 것입니다. 성능상의 문제 때문에 이런 것들이 생기게 되었는데요

11g이전에는 그럼 어떻게 했을까요? Select를 실행시키고 나서 Data Buffer Cache에서 데이터를 찾아와야 합니다. 하지만 서버를 자신만 쓰는 것이 아닌 수많은 사용자들이 있습니다. 그럼 저번에 말했듯이 순서를 기다리고 대기표를 뽑는 Latch라는 개념이 들어가죠. 자신의 차례가 올때까지 기다리는 성능적 문제가 있었습니다. 하지만 11g이후에는 Server Result Cache가 결과물을 저장하여 Data Buffer Cache까지 가지 않아도 된다는 것입니다. 딱보기에는 엄청난 성능 향상이 있을 것 같지만 그렇지 않다는거..ㅎㅎ

서진수쌤 책대로 저도 한번 테스트를 해보겠습니당 ㅎㅎ

 

 

 

 

MANUAL이 기본값입니다. 그럼 사용은 어떻게 해야 사용이 될까욤? 자동으로 하게 하는 것은 VALUE가 FORCE면 자동입니다.

하지만 자동으로 할 일은 없으니까 수동으로 하고 SQL에서 /*+result_cache*/라는 힌트 구문을 이용하면 됩니다.

 

 

 

 

우선 테스트하기 위해서 scott 계정에 rtest라는 table을 만들고 거기에 PL/SQL을 이용하여 데이터 20000개를 무작위로 넣겠습니다.

그리고 개수를 세어보니 20000개가 맞네요 ㅎ 이제 한번 테스트를 해볼까욤 ㅎ

 

 

 

 

우선 먼저 result_cache를 쓰지 않은 모습입니다. 3초가 걸린것을 알 수 있죵ㅎ 결과값보는 것은 set timing on 으로 하시면됩니다.

 

 

 

 

이번엔 힌트를 /*+result_cache*/ 로 주고 실행! 했더니... 이런..ㅎ 시간이 3초 04가 걸렸네요.. 오히려 성능저하인가..

이것만 보고 판단하기는 좀 그렇지만 성능에는 크게 변화가 없음을 알 수 있습니다. 허허..ㅎ

 

한번 force로 바꾸는 것을 보여드릴께요.

 

 

 

지금 현재 timing이 실행되어 있으니까 off해주시고 alter를 이용하여 alter system set result_cache_mode=force로 해주시면 설정 끝!

다시한번 show parameter result_cache_mode해서 보면 force로 된 것을 알 수 있습니다. 자동으로 result_cache를 하면 어떻게 될까요?

먼저 result_cache를 찾기 때문에 데이터가 cache에 저장이 된 경우에는 좋지만 cache에 없는 새로운 조회일 경우는?

생각해보면 result_cache를 먼저 찾고 없을 경우 다시 data buffer cache로 가는 불가피한 경우가 생깁니다. 그럼 또다시 성능저하..ㅎ

참 성능이라는 아이를 조절하기가 어렵네요 ㅎ 그래서 저는 manual로 해놓고 힌트를 주는 것으로 하는게 낫다고 생각합니다.

상황에 따라 SQL을 조절하여 쓰는 것이 편하니까요 ㅎㅎ

SERVER RESULT CACHE는 다시 두가지로 나누어 지는 것을 볼 수 있는데요. SQL의 결과물과 PL/SQL의 결과물을 따로 저장을 합니다.

이걸로 Server Result Cache 설명을 끝마칠께요!! 신기능이라 조심조심 써야할것 같은 이아이..ㅎㅎ

 

그 다음 구성요소인 Reserved Pool이 있죠? 이건 Shared Pool에 5KB가 넘는 오브젝트가 적재되어야 할 경우에 사용하기 위해 예약 한 공간입니다. 어떠한 때에 쓰이느냐가 중요하죠? SQL이나 PL/SQL 객체 중에 대용량인 객체가 있을경우, Shared Pool의 공간이 부족한 경우에 쓰입니다. 수동으로 용량 조절은 SHARED_POOL_RESERVED_SIZE 파라미터로 용량을 설정합니다.

 

 

 

조회는 이런식으로 SHOW PARAMETER SHARED_POOL_RESERVED_SIZE 하면 조회완료!ㅎ VALUE로 값이 나오는 것을 볼 수 있죵ㅎ

이 공간의 값은 인스턴스가 시작 될때 SHARED_POOL의 5% 공간을 줍니다. 확인해볼까요?

 

 

 

현재 SGA의 공간을 본 결과 Variable Size가 310381464 Bytes 약 294MB를 할당 했음을 알 수 있습니다. 이중 5%는 14.8M정도인데요 ㅎ

현재 7.4MB 정도로 할당이 되어 있네요..ㅎ 2.5%로 할당이 되어 있군요.. 라고 생각했습니다.

Variable Size에는 Shared Pool 말고도 여러가지의 다른 메모리 사이즈가 들어있기 때문에 다른 것으로 조회를 해보았습니다.

 

 

 

select sum(bytes) from v$sgastat where pool='shared pool'; 로 pool의 이름이 shared pool인 것들의 용량 합을 구했습니다.

그랬더니 152MB정도가 나오네요..ㅎ 그중에 5%하면 대략 7.6MB정도가 나오는 것을 알 수 있죠? 조회하나하는 데도 힘드네요..ㅎ

DB내가 아닌 서버에서도 용량 할당에 대한 것들을 볼 수가 있습니다. 바로 파라미터파일인 PFILE을 볼 수 있는데요.

 

 

PFILE의 일부입니다. 제 DB의 이름인 testdb의 정보인데요. 여기에 용량이 나와있습니다. shared pool size를 150994944로 준다.

그럼 위 값과 약간의 오차가 또다시 생기는 것을 볼 수 있습니다. 144MB가 나옵니다. 5%하면 7.2MB 핳.. 또 다르네요 어쩔수 없나봐요ㅎ

이러한 오차범위는 좀더 공부를 해야 할 것같습니다..ㅠ 이렇게 ORACLE은 여러가지의 방면으로 조회가 가능 하다는것.

메모리 기준이 되는 것은 PARAMETER 파일들이라는 것을 꼭 기억해 주시기 바랍니다.

 

그리고 다시 돌아와서 reserved_size는 shared pool size의 50%가 최대이며 부족한지는 어떻게 아는가~ 봅시당ㅎ

 

 

 

V$SHARED_POOL_RESERVED 뷰를 통해서 볼 수 있습니다. 그중 컬럼중에 REQUEST_MISSES와 REQUEST_FAILURES를 보시기 바랍니다. 이 값들이 척도인데요. REQUEST_FAILURES 값이 증가하면 이 공간이 부족하다는 뜻이고 REQUEST_MISSES 값이 0이거나 증가되지 않는다면 공간이 부족하지 않다는 뜻입니다. 현재 MISSES는 0이고 FAILURES는 값이 없는 것이 보이시나요?ㅎㅎ 현재 부족하지 않다는 뜻이 되는 겁니다.

 

추가적으로 한가지 더 말하자면 SHARED POOL SIZE는 9i부터 동적 파라미터로 분류가 되므로 DB를 끄지 않고도 파라미터 값이 조정 가능합니다. alter system set shared_pool_size = ??M 이런식으로요. 단 이때 단위는 M로 보이지만 그래뉼이라는 다른 단위가 적용이 됩니다. 이것은 뒤에서 설명 하도록 하겠습니다 ㅎ 여기까지가 Shared Pool입니다. Dictionary와 Library를 제대로 설명하지 않았는데도 양이 많네요..ㅎ SQL과 관련이 있는 부분은 SQL 동작원리에서 자세히 다룰 겁니다 ㅎ

 

이제 SHARED POOL을 넘어서서 기타 POOL들을 확인 해 보도록 하겠습니다.

Large Pool은 필수 구성요소가 아니고 옵션입니다. 사용 경우은 세가지인데요.

- Shared Server mode로 Oracle Server를 운영 할 경우 UGA를 이곳에 생성

- Parallel Execution(병렬처리) 작업을 할 경우 각 프로세스들간의 Message Buffer가 이곳에 생성

- RMAN으로 백업이나 복구를 할 경우 RMAN이 사용하는 I/O용 Buffer가 이곳에 생성

보시다 싶이 경우은 세가지의 경우인데요. 거의 서버를 운영하고 복구를 하려면 써야 한다는 것을 알 수 있습니다. 그만큼 중요하기도 하죠. Self로 자신만 테스트용 DB라면 상관은 없겠지만 다용도로 쓰려면 Large Pool은 있어야 합니다. 단 LRU로 관리 되지 않습니다.

 

다음은 Java Pool인데요. 이 공간도 SGA의 필수공간이 아닙니다. JAVA관련 code나 JVM 관련 데이터를 저장하기 위한 공간입니다.

 

Streams Pool은 10g이상부터 생긴 공간입니다. Streams 기능을 사용할 경우에만 사용됩니다. 관리자가 설정 하지 않으면 기본값 0이고 Streams 기능을 사용하게 되면 동적으로 Oracle Streams가 그 크기를 증가시키게 됩니다.

 

마지막으로 Fixed SGA는 Oracle이 내부적으로 사용하기 위해 생성시키는 공간입니다. 주로 Background Process들이 필요한 Database의 전반적인 공유 정보나 Process들끼리 공유해야만 하는 Lock 같은 정보들이 저장되는 곳입니다.

이 공간의 크기는 Oracle이 시작될때 자동으로 할당 부여되는 곳으로 변경이 불가합니다.

 

이렇게 길고긴 SGA의 주요 구성요소가 끝이 났네요..ㅎ 다음에는 Dynamic SGA 기능을 설명하도록 하겠슴다 ㅎ