전문가칼럼

DBMS, DB 구축 절차, 빅데이터 기술 칼럼, 사례연구 및 세미나 자료를 소개합니다.

물탱크 구조로 알아본 오라클의 블록 옵션 ‘PCTFREE와 PCTUSED’

전문가칼럼
DBMS별 분류
Oracle
작성자
dataonair
작성일
2014-07-22 00:00
조회
28327




◎ 연재기사 ◎


물탱크 구조로 알아본 오라클의 블록 옵션 ‘PCTFREE와 PCTUSED’


이산가족 찾기 생방송을 통해 배우는 DB 원리


개발자에게 맞는 DB 공부방법 찾기: 물리적 분류와 논리적 분류 그리고 인덱스


데이터베이스 인덱스의 오해와 진실


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (상)


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (하)


누구도 알려주지 않았던 ‘오라클 인덱스 생성도’의 비밀


누구도 알려주지 않았던 ‘오라클 쿼리 작성의 비법’


퀴리 최적화 및 튜닝을 위한 오라클 공정쿼리 작성법


만능 쿼리와 한 방 쿼리


오라클 옵티마이저 ‘CBO와 RBO’ 이해하기


재미있는 DB 이야기 ‘60갑자와 쿼리’


그림으로 배우는 ‘오라클 조인의 방식’ 이야기


반드시 알아야 하는 오라클 힌트절 7가지


오라클 플랜을 보는 법


개발자들의 영원한 숙제 ‘NULL 이야기’


알면 유용한 오라클 기능 ‘GATHER_PLAN_STATISTICS’


알면 유용한 오라클 기능들


오라클 DICTIONARY를 활용한 DB툴 프로그램 ‘FreeSQL’


이제는 말할 수 있다: 주식 자동매매 프로그램(상)


이제는 말할 수 있다: 주식 자동매매 프로그램(하)


개발자들이 자주 접하는 오라클 에러 메세지


재미있는 DB 이야기 ‘사라진 날짜를 찾아라’


오라클 랜덤 함수와 사용자 정의 함수


그림으로 배우는 ‘공정쿼리와 인덱스 생성도’


이병국의 개발자를 위한 DB 이야기: 디폴트 세팅의 함정과 오라클 파라미터


재미있는 DB 이야기 ‘놀라운 마방진의 세계’


오라클 운반 최소 단위 BLOCK


이병국의 개발자를 위한 DB 이야기: 이세돌과 알파고의 세기의 대결


이병국의 개발자를 위한 DB 이야기(30회) : DB 엔지니어의 가볍게 읽는 세상 이야기


이병국의 개발자를 위한 DB 이야기: 튜닝(31회) : 개발자를 위한 DB 튜닝 실전(1편)


이병국의 개발자를 위한 DB 이야기: 튜닝(32회) : 개발자를 위한 튜닝 실전(2편)


이병국의 개발자를 위한 DB 이야기: 튜닝(33회) : 개발자를 위한 튜닝 실전(3편)


이병국의 개발자를 위한 DB 이야기: 튜닝(34회) : 개발자를 위한 DB 튜닝 실전(4편)


이병국의 개발자를 위한 DB 이야기: 튜닝(35회) : 개발자를 위한 튜닝 실전(5편)


이병국의 개발자를 위한 DB 이야기: 페이징 처리에 대한 이해 (36회)


보기 좋은 떡이 먹기도 좋다 - 좋은 쿼리 좋은 성능


테이블의 수직분할과 수평분할에 대한 이해


DB 성능 제고를 위한 채번의 이해와 방식별 장단점 비교


이병국의 개발자를 위한 DB 이야기: 마지막회 : ‘개발자를 위한 DB 이야기’ 연재를 마치며



이병국의 개발자를 위한 DB 이야기: 워밍업 (1회)

물탱크 구조로 알아본 오라클의 블록 옵션 ‘PCTFREE와 PCTUSED’



이병국 andongcn@dreamwiz.com 프리랜서 DB 엔지니어로서 동아제약 전산실에서 SW 개발 업무를 시작으로 프리랜서 개발자로 독립해 활동하던 중 우연한 기회에 DB와 인연을 맺게 됐다. 현재 삼성생명 전산 운영팀에서 쿼리 성능을 개선하는 DB 튜닝과 IOA 업무를 맡고 있다. 개발자 출신의 DB 엔지니어로 활동하면서 개발자에게 DB 관련 지식이 꼭 필요함을 절감했다. ‘정보의 불균형이 시장 왜곡을 가져온다’는 ‘레몬시장이론’은 중고차 거래를 통해 알아볼 수 있다. 좋은 차와 나쁜 차를 아는 중개인과 모르는 구매자 사이에는 정보의 비대칭성 때문에 구매자가 손해를 볼 수 있다. 구매자도 차에 대해 기본적인 지식을 알고 있어야, 정보의 균형이 맞으므로 서로 손해를 보지 않고 합리적인 가격에 차를 거래할 수 있다. 마찬가지로 개발자들도 DB에 대해 기본적인 지식을 습득하여 정보의 균형을 맞추면, DB 엔지니어와 협업이 더 쉬워지고 한 단계 더 발전한 자신의 모습을 발견할 것이다.



“성공과 실패의 경험을 나누자, 용기와 희망을 나누자“

개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB 엔지니어로서 활동하면서 얻은 경험과 지식을 나누고자 한다. DB를 자주 접하는 SW 개발자뿐 아니라, DB 전문가를 꿈꾸는 대학생에서 DB 분야에 입문한지 1~2년 된 기 입문자가 쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다. 물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.

전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등 개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다. DB 기술서적이나 번역서보다는 조금 더 부드럽게 접근할 계획이다. 그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다. 이 연재는 시리즈로 나갈 것이다. 연재를 끝까지 읽는 독자라면, 준전문가만큼 DB의 원리를 아는 것을 목표로 시작한다.

첫 회에 소개할 내용은 DB 테이블을 생성할 때 만나는 PCTFREE와 PCTUSED 파라미터다. 가장 기본적인 내용인데도 그 의미를 모르고 쉽게 지나칠 수도 있어서, 본격적인 DB 연재를 시작하기에 앞서 앞으로 자주 언급될 내용이라는 측면에서 이를 연재의 첫 주제로 정했다.



약방의 감초: PCTFREE와 PCTUSED 파라미터

PCTFREE는 데이터 블록에 저장된 Row Data가 변경 작업에 따라 행 크기가 증가할 상황에 대비한 여유 공간이다. 기본값으로 10%가 설정된다. PCTUSED는 블록 재사용 여부를 결정하는 요소다. 데이터가 사용하는 공간이 설정 값 이하일 경우 해당 블록을 재사용할 수 있다. 기본값으로 40%가 설정된다.



CREATE TABLE USER. “고객”
(
“고객번호” CHAR (10) DEFAULT ' ' NOT NULL,
“성명“ VARCHAR2 (30) DEFAULT ' ' NOT NULL,
“나이" NUMBER (3) DEFAULT 0 NOT NULL
)
TABLESPACE USER_FILE_01
PCTFREE 10 ----------------------------------------→ Default 10%로 설정
PCTUSED 40 -----------------------------------------→ Default 40%로 설정
INITRANS 1
MAXTRANS 255
STORAGE
(
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
LOGGING
MONITORING;

읽어봐도 무슨 내용인지 솔직히 모를 수도 있다. 군대 이야기를 하면 싫어할 사람도 있겠지만, 좀 더 이해가 쉽도록 군대 환경의 예로서 알아보자. 필자가 군대 생활을 할 때만 해도 수도 시설이 역학했던 부대가 적지 않았다. 필자가 근무했던 부대는 지하수를 퍼 올려서 물탱크에 저장해두고 사용했다. 대략적인 구조는 <그림 1>과 같다.



column_img_1289.jpg

대부분의 부대는 산을 등지고 기슭에 있는 경우가 많다. 물탱크는 산 중턱에 있다. 그 이유는 가압 펌프가 없더라도 낙차를 이용해 막사에서 물이 잘 나오도록 하기 위해서다. 물탱크에는 센서가 설치된다. 이 센서는 두 가지 기능을 해야 한다. 하나는 수위가 내려가면 모터를 가동시켜 물을 채우는 것이고, 다른 하나는 물이 정해진 수위를 넘으면 모터를 중지시켜 물탱크에 물이 넘치지 않도록 하는 것이다.

빨간선은 물이 찼으므로 모터를 멈추는 센서 역할을 하며, 파란선은 물이 떨어졌을 때 모터를 가동시키는 센서 역할을 한다. 이것이 바로 전극봉이다. 대개 녹이 안 스는 스테인리스 자재를 사용해야 한다. 요즘은 달라졌겠지만, 20~30년 전만 해도 ‘삽과 괭이만 있으면 건물도 짓는다’는 말이 있을 정도였으니, 스테인리스 재질의 전극봉은 말 그대로 ‘그림의 떡’이었다.

필자가 근무했던 부대에서는 접지 선에 철근 쪼가리를 매달아 사용해야 했다. 문제는 철근에 녹이 슬거나 이끼라도 끼는 날이면, 센서 오작동 현상으로 연결된다는 데 있었다. 밤새도록 모터가 돌아가면서 물난리가 난적도 있었고, 바닥이 드러났는데도 모터가 작동되지 않아 점심 무렵에야 세면을 한적도 있다. 여기다 센서 오작동에 따라 전원이 자주 ON/OFF되면서 모터 오버히트로 불이 붙은 적도 있었다(그러나 제대할 때까지 스테인리스 전극봉으로 교체되지는 못했다^^).

요즘은 물의 전도를 이용하는 대신, 물고기 부레의 원리를 이용해서 전기적으로 검출하는 장치를 사용하기도 하는데 조금 비싸다. 결론적으로 물탱크에서 모터를 정지시키는 역할을 하는 빨간색 전극봉이 PCTFREE이며, 모터를 가동시키는 파란색 전극봉이 PCTUSED다.



column_img_1290.jpg

어쩜 이렇게 오라클의 블록 관리 알고리즘이 우리나라 물탱크의 원리와 같단 말인가! 오라클 연구원 중에 한국인이 다수 있다는 이야기를 들었는데, 아마 80년대에 군대 다녀온 사람일 수 있겠다고 생각해 본다^^



“PCTFREE 10%와 PCTUSED 40% 설정값 바꿔본 사람 있나요”

물탱크 수위가 빨간색 전극봉에 도달하면 모터를 중지시켜 물 넘침을 방지하는 것처럼, 블록에서도 PCTFREE 설정값이 10%에 이르면, 즉 해당 블록에 데이터가 90% 쌓이면 더 이상 데이터를 쌓지 않는다. 다시 말하여, 블록 사용을 차단한다. 그러면 왜 10%를 남겼을까 만약 update 발생시, varchar 컬럼 타입은 길이가 늘어날 수도 있으므로 동일 블록에 저장하기 위하여 예비 공간을 마련한 것이다. 만약 다른 블록에 저장하게 된다면, 두 블록을 액세스해야 하므로 속도에 영향을 미친다.

물탱크 수위가 파란색 전극봉 아래로 떨어지면 모터를 가동하여 물을 보충해야 한다. 블록에서도 PCTUSED 설정값이 40%가 되면, 즉 해당 블록에 데이터가 삭제되어 40% 이하가 되면 블록 사용을 승인한다. 그러면 왜 40%가 디폴트일까 40%보다 낮으면 블록 사용 효율성에 문제가 있을 수 있고, 40%보다 높다면 빈번하게 블록 사용 금지 및 승인을 해야 하므로 블록 관리에 부하가 발생할 수 있다. 그래서 기본값 40%가 적절하지 않나 싶다.

둘의 합이 100에 가까워서는 안 된다 대부분의 DBA들은 PCTFREE(10%), PCTUSED(40%)의 기본 설정값을 그대로 사용한다. 테이블의 성격에 따라 기본값을 조정할 수도 있지만, 설정값을 조정해서 사용하는 DBA를 한 번도 본적은 없다.

만약 빨간색 전극봉과 파란색 전극봉 간격이 가깝다면 어떻게 될까 수위의 작은 변화에도 수시로 모터가 작동(ON/OFF)하므로 결국 모터의 오버히트 발생 가능성이 올라간다. 결국 이 말은 PCTFREE 값과 PCTUSED 값의 합이 100에 가깝다는 말인데, 이 경우 블록 관리에 엄청난 부하를 줄 것이다. 결코 그 둘의 합은 100에 가까워서는 안 된다.



순 일병의 순발력이 문제를 해결하다

다시 군대 모드다. 우리나라 군대는 참 흥미로운 조직이다. 다양한 배경을 가진 사람들이 모이다 보니 순발력을 발휘하는 ‘만능 엔지니어 순돌이 아빠’가 으레 있게 마련이다. 우리 부대에도 순돌 일병이 있었으니, 순 일병은 순간적으로 전극봉 오작동에 따른 물탱크 물 넘침 방지방법과 빨리 인지하는 방법을 떠올렸다. 순 일병이 내놓은 방법은 다음과 같다.

물탱크에서 물이 넘쳐 흐르기 전, 빨간색 파이프를 통하여 도랑으로 미리 흘려 보냄과 동시에 파이프 끝에 소리를 내는 피리 비슷한 것을 설치하는 것이었다. 피리 소리가 나면, 물이 넘치는 것이므로 바로 조치를 할 수 있는, 꽤 그럴듯한 방법이었다.

column_img_1291.jpg

예전엔 PCTFREE와 PCTUSED 두 파라미터 모두에 대해 설정하였지만, 요즘은 PCTFREE만 설정하여 사용한다. PCTUSED는 오라클 9i부터 ASSM(Automatic Segment Space Management, 자동 세그먼트 공간 관리)을 통해 자동으로 관리해 준다. 그렇다고 PCTUSED 파라미터가 없어졌다는 말은 아니다. 오라클에서 더 세분화해 내부적으로 자동으로 관리하므로 DBA가 그만큼 신경 쓸 부분은 줄어들었다. 이때 설정값을 지정하더라도 무시된다. ‘자동으로 관리된다’는 의미는 관리 부담이 줄어 들었다는 뜻이지, 최상의 설정 값으로 관리된다는 말은 아니다.

블록에 대해 더 자세히 설명한다면, 블록은 데이터 I/O의 가장 작은 단위이자 할당될 수 있는 공간의 가장 작은 단위다. 결국 블록이란 저장의 기본 단위이고, 운반(조회)의 최소 단위라 할 수 있다.



고정관념에서 벗어나기를 바라며

참고로 오라클 DB뿐 아니라 대부분의 DB 구성 알고리즘은 어느 날 ‘하늘에서 뚝 떨어져 새로 만들어진 것’이 아니라 실생활에서 이용되는 혹은 이미 상식 수준에서 인지되고 있는 그런 보편적인 원리를 바탕으로 만들어졌으므로 ‘순 일병의 물 넘침 방지기법’처럼 쉽게 접근하고 이해할 수 있다. 서두에서 말했듯이 ‘레몬시장이론’을 상기하고 DB를 지레짐작으로 어려워하지 말고 용기를 내고 하나씩 터득해 나가기를 바란다.

이 글은 DB 전문가 수준의 이해를 요구하지는 않는다. 단지 DB에 대해서 더 친숙하고 더 쉽게 이해하고 접근하길 바랄 뿐이다. “개발자가 알아야 할 DB 이야기” 1회를 마치면서 다음 회에는 더 재미있고 쉬운 이야기로 찾아 뵐 것을 약속 드린다. 2회 연재에서는 1983년, 전 국민을 눈물 바다로 만든 ‘이산가족 찾기 생방송’을 통하여 DB 원리를 배운다.

이 글을 읽으면서 궁금하거나 의문 나는 점이 있으면, 댓글을 달아주실 것을 적극 바란다. 아무리 어렵고 힘든 일이더라도 ‘관계’와 ‘소통’으로 풀어나갈 수 있음을 믿으며…. (다음 회에 계속)