Oracle 성능애 영향을 미치는 것들-SHARED_POOL_SIZE

 

SHARED_POOL_SIZE는 모든 버젼의 오라클에서 사용되는 파라미터로, 단위는 byte 단위이며,

이 영역은 data dictionary나, stored procedure, 그리고 각종 SQL statement가 저장됩니다.

 

SGA 영역가운데 많은 비중을 차지하는 shared_pool_size는 다시 dictionary cache

및 library cache 영역으로 나뉘어 지며, db_block_buffers와 마찬가지로 너무 크거나, 작게 잡지 않도록 하여야 합니다.

 

SHARED_POOL_SIZE 값이 적절한지 여부는 data dictionary cache 및 library cache 의 hitratio로 측정할 수 있습니다.

 

SQL 처리에는 data dictionary가 여러 차례 참조되므로, data dictionary 조회 시 디스크 I/O가 적게 발생하도록 하면, 성능 향상에 도움이 됩니다.

 

Data dictionary cache hit ratio는 다음 SQL에 의해 측정 가능합니다.

 

SELECT (1-(SUM(getmisses)/SUM(gets))) * 100 "Hit Ratio" FROM V$ROWCACHE;

 

결과는 다음과 같이 생성됩니다.

 

Hit Ratio ---------- 93.4581591

 

Data dictionary cache hit ratio는 90% 이상을 유지하는 것이 바람직 하지만, 인스턴스 구동 직후에는 캐쉬 영역에 데이터가 저장되지 않으므로 대략 85% 가량을 유지 하도록 하는 것이 바람직합니다.

 

Library cache 영역은 공유 SQL 영역 및 PL/SQL 영역으로 나뉘어 집니다. SQL이 실행될 경우, 문장은 먼저 parsing 되어야 하는데, library cache는 SQL 및 PL/SQL을 미리 저장해 두어, 실제 parsing이 발생하는 빈도를 줄이는 역할을 합니다.

OLTP 업무의 경우, 동일한 SQL이 여러 차례 수행되므로 적절한 cache 영역을 확보함으로써 성능 향상을 기대할 수 있습니다. 물론 bind variable을 사용하여야만 공유 가능한 SQL이 생성됩니다.

 

SHARED_POOL_SIZE 값이 적을 경우는 물론이거니와, 너무 이 값을 크게 지정해도 문제가 된다. SHARED_POOL_SIZE가 너무 클 경우, 새로운 SQL 수행 시 가용한 메모리 영역을 찾아 내기 위한 latch contention 의 가능성이 높아지게 됩니다.

 

V$SGASTAT을 조회하여 free memory를 조사할 수 있으며, 메모리가 낭비되고 있는지 여부도 확인 가능합니다.

 

SELECT name, bytes/1024/1024 "Size in MB" FROM V$SGASTAT WHERE name='free memory';

 

실행 결과는 다음과 같습니다.

 

NAME                                                 Size in MB ---------------------------------------------------- ---------- free memory                                          11.4772873 free memory                                                  24

 

이 결과는 shared pool에 11M 공간이 사용되지 않고 있으며, 만약 shared pool의 크기를 24M 로 지정하였다면, 절반 이상의 메모리 공간이 사용되지 않고 낭비되고 있음을 의미합니다.