Shared Pool 진단 및 튜닝
SGA에서 Shared Pool만큼 사이즈를 설정하기 어려운 parameter가 없다. 그리고 이 Shared Pool의 크기는 너무 크거나 작아도 문제가 된다.
즉,얼마나 적절히 설정하느냐에 따라 DB의 성능이 좌우된다. Parameter만 잘 설정해도 성능이 좋아진다니 이렇게 편하고 좋은 튜닝방법이 있을까 하는 생각이 든다. 하지만 Shared Pool의 크기를 아무 지식과 진단없이 적절히 설정하기란 여간 까다롭지 않다.
특히 Row Cache와 Library Cache는 크기를 각각 설정하는 것이 불가능하기 때문에 초보자에겐 이런 것들이 어렵게 느껴질 수 있다.
Wait Event의 중요성
Wait Event발생을 줄이는 것은 중요한 일이고 이를 잘 분석하여 튜닝하는 것은 매우 효과적인 방법이다. 그럼 이제 Shared Pool을 Wait Event의 관점에서 진단하고 분석해 보자.
Shared Pool의 목적
Shared Pool의 목적은 실행된 Cursor를 공유하여 CPU나 memory를 효율적으로 사용하는 데 있다.
Cursor란 SQL의 경우 실행할 때 필요한 실행계획 같은 실행정보를 담고 있는 SGA상에 할당된 Heap Memory를 말한다. 물론 공유할 수 있는 것들은 다양하다. 공유할 수 있는 정보들을 나열하자면 SQL구문,실행계획,PL/SQL소스와 그것의 실행정보,Table,View같은 Object 등이 있다.
이것들을 공유한다면 동일한 PL/SQL이나 SQL을 실행함에 있어 매번 실행계획을 만들며 hard parsing이 일어나는 부하를 예방할 수 있다.
Shared Pool의 구성요소
Process목록, Session목록, Enqueue목록, Transaction목록 등이 할당된 Permament Area와 SQL문을 수행하는데 필요한 모든 객체 정보를 관리하는 Library cache,dictionary정보를 관리하는 Row cache,그리고 마지막으로 동적메모리 할당을 위한 공간인 Reserved Area로 나눌수 있다.
Heap Manager를 통한 메모리 관리
메모리에 대한 할당 및 해제작업은 Heap Manager를 통해 동적으로 관리가 된다. 이 Heap Manager에 대해 간락히 알아보면 Top-level의 Heap과 그 하위에 여러개의 Sub-Heap을 포함하는 구조를 이루고 있다.
이 Heap은 또한 linked list구조의 Extent들로 구성이 되어 있으며 Extent는 여러개의 chunk로 구성되어 있다. 실제적으로 chunk의 사용현황에 대해 알고 싶다면 X$KSMSP라는 view를 통해 관찰할 수 있을것이다.
Chunk의 관리
Chunk는 4가지 상태로 관리가 된다. Free,Recreatable,Freeable,Permanent다. 이러한 chunk들의 상태에 따라 linked list가 구성된다.
Free는 즉시 사용 가능한 상태를 말한다. 이 Free상태의 chunk등로 묶여 있는 linked list가 free list인 것이다. 구체적으로 설명하자면 이것은 255개의 bucket이 있고 각 bucket당 free chunk들이 linked list구조로 연결되어 있다.
이때 bucket은 각각의 정해진 기준의 크기 이하의 chunk들로만 구성되어 있다. 이러한 이유로 bucket이 아래로 갈수록 chunk들의 크기가 크다.
Recreatable은 재생성 가능한 상태이다. 이것은 사용이 되었지만 다시 사용될 확률이 낮아져서 재사용이 가능한 상태가 된 것이며, 현재 사용중이 아니라면 chunk를 재사용할 수 있도록 이러한 상태의 chunk를 묶어 LRU list로 관리한다.
Freeable은 session이나 call동안에만 필요한 객체를 저장하고 있는 상태이며 이는 session등이 금방 끊길 수도 있기 때문에 chunk가 필요할 때 할당의 대상이 되지는 못한다.
Permanent는 영구적인 객체를 저장하고 있는 상태이며 이것 역시 사용할 수 없는 chunk다.
Shared Pool의 관리
Chunk를 할당하는 과정에서 반드시 필요한 것이 Shared Pool Latch의 획득이다. 하지만 이 Latch는 아쉽게도 Shared Pool당 단 1개뿐이다. 이것은 즉, chunk를 동시에 할당 받아야 할 상황이라면 획득하는 과정에서 결합을 벌이게 된다는 의미이다.
무엇 때문에 이렇게 Shared Pool Latch수를 적게 만들어 놓았는지 정확히 알 수는 없지만 여러개를 만들어 놓았다면 역시나 동기화 문제를 관리하는데 있어 어려움이 있기 때문이 아닐까 싶다.
Chunk의 할당과정
Shared Pool Latch를 획득하게 되면 우선 free chunk를 찾기위해 free list를 탐색한다. 그리고 적절한 free chunk가 있다면 할당을 받지만 없다면 LRU list를 탐색하게 된다. 이것마저도 찾지 못한다면 Reserved Free List를 탐색하고 이것역시 실패하면 Spare Free Memory를 탐색하게 된다. 이 모든 과정이 실패가 되었을 때 ORA-4031 에러가 발생하게 되는 것이다. 이 과정에서 할당을 받게 된다면 딱 필요한 크기만 할당을 받고 나머지는 다시 free list에 등록이 되기 때문에 free list가 할당이 된다고 해서 반드시 짧아지는 것은 아니다. 그리고 적절한 chunk를 찾기 위해 위에서와 같이 여러 과정은 거치지만 이 과정은 생각보다 매우 빠른시간 안에 이루어진다.
하지만 이것들이 다수의 작업이 된다면 경합에 대한 wait event은 피부로 느껴질 것이다.
여기서 이제껏 언급이 없었던 Spare Free Memory에 대해 궁금해 하는 분이 많을 꺼 같아 간단하게 설명하자면(Steve Adams의 Oracle Internal에 수록) instance가 startup되었을때 Shared Pool Size에 정해진 크기의 절반만이 Shared Pool에 할당된다.
이것은 성능을 극대화 하는데에도 연관이 있으리란 생각이 든다. Chunk의 수가 줄면 그 만큼 free list가 짧아지기 때문에 그에 대한 탐색시간도 짧아지고 Shared Pool Latch의 소유시간 역시 짧아지기 때문에 메모리를 숨겨놓지 않을까 생각된다.
위의 과정들을 미우어 짐작해볼 때 Shared Pool Latch의 소유시간은 free list의 길이와 얼마나 빨리 적절한 chunk를 찾느냐에 따라서 결정된 다는 것을 알 수 있을 것이다. 그럼 free list의 길이가 길어지는 것은 어떠한 경우 일까?
그것은 바로 chunk split가 다량으로 발생하여 단편화 되었을 때이다. 이러한 경우 free list의 길이가 길어지게 되는 것이다. 단편화는 hard parsing에서 일어나는 것이다. 이런 과도한 단편화로 인해 Shared Pool Latch의 경합만 가중 시키는 것이 아니다. 큰 chunk 할당이 요구되는 hard parsing이 이루어 질때 적절한 free chunk를 찾지 못하여 ORA-4031에러를 유발하게 된다.
Shared Pool Size설정
1. memory가 무조건 크다는 생각으로 Shared Pool을 늘리면 안 된다고 볼 수 있다. 이것은 오히려 free list의 길이만 늘어나게 되기 때문이다. 그리고 V$SGASTAT를 통해 확인한 Shared Pool의 free memory가 작다고 해서 SHARED_POOL_SIZE를 늘려서는 안 된다.
Free memory는 단지 free chunk의 합이기 때문이다. 이는 즉, LRU list, reserved list, spare memory도 있기 때문에 크게 문제가 되는 것은 아니라는 말이다.
OEM Shared pool advice 이용. peak time이후에 이용하는 것을 권장
Shared Pool Size를 적절히 줄이게 되면 free list 탐색시간의 감소로 인해 hard parsing에 의한 Shared Pool Latch의 경합을 줄이는 효과를 볼 수 있지만 ORA-4031에러의 위험이나 상주할 수 있는 공유 객체의 수가 줄어들어 LRU list를 자주 이용하기 때문에 오히려 부가적인 hard parsing을 발생시킬 수 있음에 유의해야 한다. 이때 오르내리는 객체가 프로시져나 패키지라면 그 부하는 상당할 것이다. 이에 대비햐여 DBMS_SHARED_POOL.KEEP을 이용하여 메모리에 고정시키는 방법도 유용하다. Shared Pool을 flush해도 내려가지 않기 때문이다.
Shared Pool Latch가 발생하는 것을 가장 줄일 수 있는 방법은 bind변수의 사용이나 CURSOR_SHARING parameter를 설정하는 것이다.
CURSOR_SHARING parameter는 SQL문장을 자동으로 bind변수치환을 해주는 변수이다.
Flush Shared Pool은 단편화된 free chunk에 대해 coalesce작업이 이루어 지기 때문에 유용하지만 NOCACHE옵션이 없는 sequence가 있다면 예상치 못한 gap이 생길 수도 있기 때문에 유의해야 한다.
Library Cache Latch와 Shared Pool Latch의 관계
Shared Pool을 튜닝 하는데 있어 반드시 Shared Pool Latch획득만이 문제가 되는 것은 아니다. 바로 Shared Pool Latch획득 이전에 Library Cache Latch의 획득이 먼저 있기 때문이다.
hard parsing이 soft parsing보단 부하가 큰 작업임을 알 수 있다. 또한 Library Cache Latch와 Shared Pool Latch획득시점을 미루어 보아 동시에 많은 세션이 Library Cache Latch를 획득하려고 하게 되면 이것에 대한 병목 현상으로 Shared Pool Latch에 대한 경합은 상대적으로 줄어들 수 있을 것이란 예상도 가능하다.
.
.
.
글이 좀 더 있는데 이해하기 좀 어려운 내용들..
출처:OKM 2007 spring