Oracle 메모리 사용에 관한 연구 기본 카테고리

Reference : http://www.dator.co.kr/enda/91375

오라클 데이터 buffer는 Oracle버젼에 따라 많은 변화를 보여왔다. 현재 10g, 11g 이전에 획기적으로 변한 버젼이 9i버젼때라고 할 수 있는데, 가장 큰 부분이 buffer block size를 다르게 구성할 수 있는 부분이라고 할 수 있다. 11g버젼에서는 또한 많은 부분이 달라져 있고 이 부분은 차후 글을 올릴 것을 약속하고 9i버젼의 메모리 사용에 대한 고찰을 해보고자 한다.

다음과 같은 SQL을 실행시킬 경우를 생각해 보자.

SELECT * FROM CAMP_20020

WHERE CUST_NAME LIKE ‘남%’; 

아주 단순하면서도 실제의 업무에서 많이 쓰이는 Pattern의 SQL이다. 그런데 이런 간단한 SQL을 실행시킬 때 동일한 환경에서 (동일 테이블, 동일서버등) 어떤 경우는 10초가 걸리고 어떤 경우는 1초가 걸릴 수 있다.  왜 그럴까?

Application 튜닝의 기본은 SQL튜닝이지만 위와 같이 튜닝조차 할 것이 없는 SQL등은 어떻게 할 것인가? 그 많은 해답 중 본 문서에서는 Oracle 9i를 기준으로 메모리를 효과적으로 사용하여 Database I/O를 줄이고 높은 Performance를 낼 수 있는 방법에 대해 살펴보고자 한다.

Oracle 9i New Feature중 가장 획기적으로 기능이 추가된 것이 Memory부분이다. 이전 버전에서는 Database를 Creation하면 db_block_size가 고정되어 한정된 메모리를 용도에 알맞게 효율적으로 나누어 사용하는데 한계가 있었다. Oracle 9i는 Data Buffer Cache를 db_block_size와 용도에 따라 나누어 사용할 수 있는 기능을 제공한다. 본 문서에서는 이에 대한 기본개념을 살펴보고 Performance 관점에서 ‘어떻게 하면 Oracle 9i의 메모리를 가장 효율적으로 사용할 수 있는가’에 대한 solution을 제공하고자 한다.

 

Oracle 9i has Eight RAM Data Buffers Architecture

  

이전 버전의 Buffer Cache는 위의 그림과 같이 세분화되어 있지 않고 하나의 저장공간으로만 되어 있었지만 9i에서는 위의 그림과 같이 세분화 되어 있다. 이것은 Data Segment를 Buffer Cache에 Load할 때 Data Segment를 Block Size와 용도에 맞게 나누어서 메모리에 Load할 수 있다는 이야기가 된다. 대부분의 DBA들은 이런 기능이 추가되었다는 사실 조차도 모르고 있으며 설사, 안다고 해도 이것의 엄청난 사용효과에 대해 모르고 있다.

KEEP Pool 

KEEP Buffer는 I/O Operation이 일어나는 것을 방지하기 위하여 Segment를 Buffer에 Retain한다.
주로 사이즈가 작고 Frequently Referenced되며 Size가 많이 늘어나지 않는 테이블들을 지정한다. 주로 code나 dimension성의 테이블을 지정하며 Transaction이나 Fact성 - 사이즈가 큰 테이블- 과 빈번하게 조인할 때 Random Access의 부하가 부담이 될 때 효과를 발휘한다. 만약, 이전버전에서와 같이 모든 Segment가 Buffer를 공유한다면 크기가 큰 테이블을 한 번 Full scan하고 다시는 안 쓰는 그런 테이블들 때문에 이런 자주 쓰이는 테이블들이 aged out될 수 있다. KEEP Buffer는 Default로 지정한 db_block size로 구성이 되는 Standard block size Buffer이다. DB_KEEP_CACHE_SIZE parameter에 의해 지정할 수 있다.

RECYCLE Pool

KEEP Pool과는 다르게 segment를 Buffer에 retain하고 있지 않는다. 따라서 크기가 아주 크고 주로 Full scan이 일어나며 가끔씩 referenced되는 테이블들을 지정한다. 주로 Batch성의 테이블들을 지정한다. 만약 Online Transaction과 Batch Processing을 동시에 돌리는 서버를 사용할 경우, Full scan이 많이 일어나고 그 양이 굉장히 많을 경우에는 어차피 전체의 데이터를 메모리에 올릴 수 없을 때 RECYCLE Pool에 지정을 하는 것이 좋다. 이 것은 다른 frequently referenced 되는 테이블들을 위해서이다. RECYCLE Pool역시 Default로 지정한 db_block size로 구성이 되는 Standard block size Buffer이다. DB_RECYCLE_CACHE_SIZE parameter에 의해 지정할 수 있다.

DB_CACHE_SIZE

Default로 지정한 db_block size로 구성이 되는 Standard block size Buffer로 특별한 setting을 하지 않으면 대부분의 Buffer Cache가 이 Standard Buffer로 채워지게 된다. Table Creation시나 Tablespace Creation시에 Buffer_Pool이나 block size를 지정하지 않으면 모든 segment는 이 Standard Buffer에 Load되게 된다. System Tablespace, Temporary Tablespace, Undo Tablespace등이 Standard block size로 만들어지므로 이런 Tablespace에서 일어나는 Operation (Sorting, Rollback등)들은 이 Standard Buffer를 이용하게 된다.

DB_Nk_CACHE_SIZE

위의 그림과 같이 지정한 block size로 구성이 되는 Non-Standard block size Buffer로 Default Size는 0 이므로 환경에 따라 setting을 해야만 한다. Non-Standard block size로 Creation된 Tablespace에 존재하는 Segment는 해당하는 Buffer로 Load되게 된다.

우리는 Oracle 9i의 Buffer Cache Architecture와 각 Buffer들이 어떤 segment들을 Loading하여 사용하는가에 대해 기본적인 것을 알아보았다. 그렇다면 segment들은 어떤 기준에 의해 Standard Buffer외에 다른 Non-Standard Buffer를 지정하여 사용해야 하는가?  즉,  block size에 따른 segment들의 이용을 Performance관점에서 접근을 해보도록 한다.

Performance에 영향을 미치는 것들은 많이 있겠지만, 그 중에서 가장 Critical한 것은 Database 의 I/O이다. 어떻게 하면 동일한 결과를 얻는데 I/O를 적게 일으킬 수 있는가?

이것이 Application 튜닝의 가장 핵심인 것이다. 이 것은 SQL 튜닝이 가장 기본적인 것이 될 것이고 물리적인 튜닝이 뒷받침이 되어야 한다.

Database의 Block은 I/O의 단위이다. 데이터가 물리적인 디스크와 메모리 Buffer사이를 이동하는 단위인 것이다. 만약, 메모리의 Buffer Size가 무한정 크다면 우리는 Block의 크기를 크게 할수록 유리할 것이다. 한 번의 Single I/O에 드는 비용은 Block의 크기에 거의 상관없이 동일하기 때문에 – 다음 그림에서 설명-  Block의 크기를 크게 하면 할수록 많은 데이터가 메모리상에 Load될 것이다. 따라서 Memory Hit율은 그만큼 높아지게 될 것이기 때문이다.

1) Seek Time : Read-Write Head를 움직이는 시간. 적당한 Cylinder로 Head를 위치시키는 시간으로 가장 많은 시간이 소요된다.

2) Access Time : Rotation Delay이가 발생되는 곳으로 Data를 회전하는 Disk를 통해 액세스한다.

3) Transmission Time : 읽은 데이터를 SGA DB Cache로 전송한다.

 

위의 그림에서 알 수 있듯이 요청한 데이터를 액세스하기 위해서 Latency(Seek Time + Access Time)가 대부분 소요되기(99%) 때문에Single I/O로 2K Block을 읽는 것과 32K Block을 읽는 비용은 거의 같다.

하지만 우리가 사용하는 서버의 메모리 size는 관리하는 segment의 size에 비해 턱없이 작다. 그리고 테이블의 데이터 발생은 RDB의 개념상 무작위, 무순서이기 때문에 Clustering Factor (데이터가 물리적으로 모여있는 정도)가 낮아 Single I/O에 대해 Block Hit율이 높을 수가 없다. 만약 100개의 row를 읽어야 한다고 가정하면 100개의 row가 전부 다른 Block안에 존재할 수도 있다는 얘기이다. 이런 상황에서 읽어야 하는 row를 가지고 있는 block이 메모리에 존재하는 %가 높을수록 Memory Hit는 높아지게 되고 Performance는 따라서 증가하게 된다. 이 얘기는 동일한 size의 메모리상에 많은 수의 Block을 가지고 있을수록 동일 Operation에 대해 I/O의 횟수가 적다. 즉, Memory Hit가 증가한다는 결론이 된다. 하지만, Clustering Factor가 좋은 테이블의 경우, 다시 말해 원하는 데이터가 물리적으로 모여 있어 하나의 Block에 원하는 데이터가 많이 들어 있는 경우는 그 반대의 현상이 벌어진다. 

만약 100개의 row를 읽어야 한다고 가정했을 때 그 row들이 물리적으로 모여있어 Block Size가 클 경우에 하나의 Block안에 전부 들어가 있을 수도 있다. 이런 경우는 Block Size가 클수록 I/O 횟수가 적게 되어 Memory Hit가 증가한다.

(Clustering Factor를 높이려면 조회 컬럼으로 order by를 하여 Table을 Re-Creation해주면 된다.)

Memory Hit와 Block Size의 절대치를 표시하지 않는 이유는 상황에 따라 (메모리의 크기, Segment의 크기, 서버의 성능등) 그 수치가 다르기 때문이며 우리가 알아야 하는 것은 Clustering Factor가 좋을 때와 좋지 않을 때의 Block Size와 Memory Hit의 상대적인 관계인 것이다. 실제로 동일한 테이블을 가지고 random access를 할 때 Clustering Factor와 Block Size에 따라 일어날 수 있는 Performance의 차이는 크게는 약 10배 이상 차이가 날 수 있다.

여기서 우리는 한가지 고려해야 할 사항이 있다. Database에서는 I/O의 단위가 Block인 것처럼 물리적인 저장 단위도 Block인 것이다. 따라서 테이블과 같은 segment를 저장할 때는 테이블의 Width와 Block Size를 고려를 해야만 한다. 어짜피 테이블의 모든 row를 한 Block에는 담을 수 없지만, 테이블의 한 row는 반드시 한 Block안에 들어갈 수 있도록 Block size를 지정해야만 한다. 만약 그렇지 않으면 하나의 row를 읽기 위하여 여러 개의 Block을 읽어야 하는 경우가 생길 수 있다. 이런 경우는 Performance에 치명적인 결과를 가져올 수 있다.  Block의 구조는 아래 그림과 같이 내부적으로 여러 부분으로 나누어져 있으며 실제로 데이터가 들어가는 부분은 “Row Data”부분이 된다. 따라서 테이블의 Table의 Width에 따라 다음과 같이 Block Size를 지정하는 것이 좋을 것이다.

인덱스의 경우는 Block Size가 클 수록 Index의 Depth도 짧아 질 것이고 Index의 구조상 인덱스 컬럼 + Rowid순으로 정렬이 되어 있어 Clustering Factor가 100% 이므로 하나의 Leaf Block안에 많은 Sorting된 데이터가 물리적으로 모여 있게 될 것이다. 따라서 Index 검색 시간은 놀라울 정도로 향상되게 될 것이다.

DB SEGMENT의 BLOCK SIZE 지정

앞에서 살펴본 바와 같이 block size에 따라 Memory Hit 및 Performance가 많은 차이가 날 수 있기 때문에 우리는 정확한 개념을 바탕으로 Oracle 9i의 새로운 기능을 제대로 활용할 필요가 있다. 아무리 좋은 기능이 있더라도 제대로 활용하지 못하면 새로운 버전을 Upgrade할 필요가 무엇이 있겠는가?

Performance관점에서 Disk I/O를 감소시키려면 Index의 Block은 가능한 크게 잡는 것이 좋다. 즉, Index Tablespace를 따로 구성해서 그 Tablespace의 Block Size는 가능한 최대로 잡는 것이다.

테이블의 경우는 몇 가지를 복합적으로 고려를 해보아야 한다.

현재 관리하는 segment들의 size와 메모리 size, 테이블이 Random access위주의 테이블인지 또는 Full scan위주의 테이블인지, 자주 access되는 테이블인지 가끔씩 access되는 테이블인지, 주기적으로 Clustering Factor를 좋게 Re-Creation 작업이 가능한 테이블인지 등등을 살펴 보아야 한다.

만약, 현재 서버의 메모리 size가 굉장히 크다면 Block Size를 최대로 키울수록 Performance는 향상되므로 Tablespace를 Creation할 때 Block Size를 최대로 지정해 준다.

대부분의 경우 현재 관리하는 segment들의 size 보다 서버의 메모리 size가 매우 작을 것이다. 이런 경우 업무가 On-Line transaction위주의 Random access가 주를 이루고 Table의 Re-Creation작업이 주기적으로 불가능한 Table들의 경우에는 Block Size를 최대로 작게 하여 Memory Hit를 높여야 한다. 만약, Clustering Factor를 좋게 해주기 위한 Table Re-Creation작업이 주기적으로 가능한 테이블들의 경우에는 Block Size를 가능한 크게 잡는 것이 좋다. 매우 자주 access되고 그 크기가 작은 테이블들의 경우에는 KEEP Pool에 지정을 하고 대부분 Full scan위주이거나 Batch Processing에 이용되는 테이블들의 경우에는 RECYCLE Pool에 지정을 하여 사용하도록 한다. 이 모든 경우에 있어 Table Row Length에 따른 Block Size를 고려해야만 Row Chaining이 생기지 않게 될 것이다.

DDL Script

DEFAULT BLOCK SIZE의 STANDARD BUFFER를 이용하는 TABLESPACE

CREATE TABLESPACE TBS_8K DATAFILE ‘/data3/ORA920/tbs_8k.dbf’ SIZE 1024M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 320K

SEGMENT SPACE MANAGEMENT AUTO;

NON-STANDARD BUFFER를 이용하는 TABLESPACE

Init.ora Parameter의 db_nk_cache_size parameter를 세팅하여 메모리를 할당한 후 Tablespace를 Creation해야만 한다. (alter system도 가능- SGA_MAX_SIZE Setting에 주의)

CREATE TABLESPACE TBS_2K DATAFILE ‘/data3/ORA920/tbs_2k.dbf’ SIZE 1024M

EXETENT MANAGEMENT LOCAL UNIFORM SIZE 320K BLOCKSIZE 2K

SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE TBS_2K DATAFILE ‘/data3/ORA920/tbs_2k.dbf’ SIZE 1024M

EXETENT MANAGEMENT LOCAL UNIFORM SIZE 320K BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE TBS_2K DATAFILE ‘/data3/ORA920/tbs_2k.dbf’ SIZE 1024M

EXETENT MANAGEMENT LOCAL UNIFORM SIZE 320K BLOCKSIZE 32K

SEGMENT SPACE MANAGEMENT AUTO;

 위와 같이 만들어진 테이블 스페이스에 생성된 테이블 또는 인덱스는 테이블 스페이스에 설정된 BLOCK SIZE를 가진 Non Standard Buffer Cache를 사용한다.

KEEP Pool

CREATE TABLE TB_KEEP (~~~)

STORAGE (INITIAL 1024K MEXT 1024K PCTINCREASE 0 BUFFER_POOL KEEP);

CREATE INDEX INDEX_KEEP ~~~

STORAGE (INITIAL 1024K MEXT 1024K PCTINCREASE 0 BUFFER_POOL KEEP);

RECYVLE Pool

CREATE TABLE TB_RECYCLE ( ~~~)

STORAGE (INITIAL 1024K MEXT 1024K PCTINCREASE 0 BUFFER_POOL RECYCLE);

CREATE INDEX INDEX_RECYCLE ~~~

STORAGE (INITIAL 1024K MEXT 1024K PCTINCREASE 0 BUFFER_POOL RECYCLE);