|
공략 포인트
90년대 중반의 어느 봄 날, 입사 후 기술 교육을 수료하고 부서 배치 면담을 하게 되었다. 젊은이들은 자기 주장이 확실해야 보기 좋다는 신문 컬럼을 등에 업고 이름이 그럴 듯한 연구개발 파트에서 일하고 싶다는 뜻을 강력히 내비쳤으나 상사는 현장에서 뛰는 것이 배울 것이 많다는 등의 갖가지 이유를 들면서 회유하기 시작하였다.
그 때 나온 얘기 중의 하나가 일명 ‘C 부장의 튜닝 종말론’ 이다. 튜닝을 하는 부서로 가겠다는 말을 한 것도 아닌데 왜 튜닝을 공격 대상으로 삼았는지는 몇 달이 지난 후에야 알았지만(공개 불가) 아무튼 그 분은 자동 튜닝 프로그램의 발달로 튜닝 기술을 가진 엔지니어의 설 자리가 갈수록 좁아질 것이라는 대 예언을 하였다.
시간이 흐를 만큼 흐른 지금 데이터베이스 튜닝은 비교적 원인과 해결책이 분명하기 때문에 전문 프로그램이 제법 만족스러운 수준의 성능을 발휘하고 있다.
그러나 90% 이상의 비중을 차지하는 애플리케이션 튜닝은 아직까지 프로그램이 사람의 두뇌를 따라오기는 너무 벅찬 상황이다. 언젠가는 이 분야 역시 뛰어난 능력을 갖춘 프로그램에 의해서 튜너들의 영역이 위축되는 시대가 도래하겠지만 앞으로도 꽤나 오랜 시간동안 인간 튜너들의 활약이 계속될 것으로 보인다.
DBA도 데이터베이스 디자인과 SQL 튜닝에 관한 일정 수준 이상의 지식을 겸비해야 겠지만 튜닝에 관한한 주로 데이터베이스 튜닝에만 참여하므로 본 과목에서도 이런 업무 성격에 맞게 데이터베이스 튜닝을 중심으로 출제된다.
본 과목은 총 57문제 중에서 38문제(67%) 이상을 맞추어야 합격할 수 있다. OCP 시험은 난이도에 따라서 커트라인을 조정하는데 67%면 중간 정도로 오라클에서는 이 과목을 그다지 어렵게 생각하지 않는 모양이다. 문제 수는 Introduction to Oracle 과목과 함께 가장 적다.
본 과목이 어렵게 느껴진다면 이전 과목들에 대한 이해를 바탕으로 하는 심화학습이라는 점 때문이다. 선수 과목들에 대한 이해를 확실히 하지 못하고 문제만 암기했다면 여기서 한계를 절감하게 된다. 그러나 반대로 앞으로 나올 내용은 한번쯤 들어본 것이 많기 때문에 오히려 가벼운 마음으로 공부할 수 있지 않을까 생각된다.
원래는 MTS(Multithreaded Server)의 튜닝에 대해서도 다루어야 하지만 MTS에 대해서는 그 동안 설명이 부족했으므로 다음의 Network Administration 과목으로 넘기기로 한다.
개요
1-1. 보기의 튜닝 내용을 가장 먼저 할 것부터 시간 순으로 나열하시오(주관식)
a. 운영 체제의 캐시 최적화
b. 데이터 딕셔너리 캐시 최적화
c. 데이터 파일과 리두로그 파일을 디스크마다 분산 배치
d. 비트맵 인덱스의 사용
e. 어레이 프로세싱에 의한 처리
<해설>
오라클을 튜닝할 때에는 번득이는 감각을 무시할 수 없기 때문에 100% 절대적인 순서란 있을 수 없겠지만 교과서적인 방법에 따르면 가장 효과가 큰 것부터 해결해 나가도록 되어 있다. 오라클에서는 이런 기준으로 튜닝 과정을 아래와 같이 크게 10 단계로 나누고 있는데 가급적 암기해 두는 것이 좋다.
1) 비즈니스 룰
2) 데이터 디자인
3) 애플리케이션 디자인 -
4) 데이터베이스의 논리적 구조
5) 데이터베이스 오퍼레이션(e)
6) 액세스 경로(d)
7) 메모리(b)
8) I/O 및 데이터베이스의 물리적 구조(c)
9) 리소스 경합
10) 운영 체제(a)
1~6 단계는 애플리케이션 디자이너와 개발자가 주로 담당하고 마지막 10번은 시스템 관리자의 몫이므로 오라클 DBA는 7~9 단계를 집중적으로 책임지게 된다. 이와 같이 DBA가 담당한 분야는 하위 타선이므로 병목 현상과 같은 심각한 문제를 해결하는 경우가 아니라면 획기적인 효과를 기대하기는 어려운 편이다.
정답 : (e),(d),(b),(c),(a) (<- 마우스로 정답 부분을 선택하세요)
1-2. 다음 중에서 alert.log 파일에 결과가 기록되는 명령이 아닌 것은?
a. CREATE SEQUENCE
b. CREATE TABLESPACE
c. SHUTDOWN ABORT
d. CREATE ROLLBACK SEGMENT
e. ALTER SYSTEM SWITCH LOGFILE
<해설>
alert.log 파일은 데이터베이스와 관련된 굵직한 사건들이 주로 기록되는 곳이다. 데이터베이스의 이상 여부를 판단할 때만 참조하는 것이 아니라 그 곳에 들어있는 정보는 튜닝에서도 요긴하게 쓰인다.
a)와 같은 국소적인 명령을 제외한 나머지가 모두 alert.log에 흔적을 남기므로 나중에라도 어떤 명령이 실행되었는지를 알 수 있다. 단, e)의 경우 ALTER SYSTEM SWITCH LOGFILE 명령 자체가 기록되지는 않고 ‘Thread 1 advanced to log sequence 7’와 같은 메시지가 기록되어서 로그 스위치가 발생했음을 알려준다.
정답 : (a) (<- 마우스로 정답 부분을 선택하세요)
1-3. 보기 중에서 MAX_DUMP_FILE_SIZE 파라미터의 영향을 받지 않는 파일은?
a. alert.log
b. ora14127.trc
c. orclarc0.trc
d. 모두 영향을 받음
<해설>
트레이스 파일은 텍스트 포맷으로 되어 있는데 가만히 내버려두면 세상 좁은 줄 모르고 커지는 속성을 가지고 있다. 디스크 사정이 여유롭지 못할 때에는 MAX_DUMP_FILE_SIZE 파라미터로 최대 크기를 제한함으로써 그 이상으로는 확장되지 못하도록 하면 된다.
MAX_DUMP_FILE_SIZE 파라미터는 그다지 자주 이용되지는 않는데 아무런 단위를 지정하지 않으면 기본적으로 운영 체제의 블록 크기로 인식하며 디폴트 값은 무한대이다.
그러나 alert.log 파일은 다른 트레이스 파일과는 성격이 크게 다르므로 이 파라미터의 영향을 받지 않는다.
정답 : (a) (<- 마우스로 정답 부분을 선택하세요)
1-4. 보기 중에서 시스템 퍼포먼스에 가장 큰 영향을 미치는 것은?
a. ALTER SESSION SET SQL_TRACE=TRUE;
b. EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(9,2476,TRUE);
c. EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(9,2476,FALSE);
d. init.ora에서 SQL_TRACE=TRUE
<해설>
SQL_TRACE는 SQL 명령을 분석하기 위해서 트레이스 파일을 만들고자 할 때 사용되는 초기화 파라미터이다. 또한 ALTER SESSION 명령이나 DBMS_SYSTEM 패키지를 통해서도 그 효과를 변경할 수 있다.
a)는 현재의 세션의 세션이 끝날 때 까지 모든 SQL 문에 대한 트레이스를 생성하라는 명령이고 b)는 다른 사용자의 세션에 트레이스를 거는 방법인데 특히 b)의 명령에 주목할 필요가 있다.
데이터베이스를 사용하는 애플리케이션은 생각 이상으로 광범위하다. 그런데 애플리케이션 중에는 미리 완벽하게 짜여져서 사용자가 커스터마이징을 할 여지가 없고 내부적으로 어떤 SQL 명령을 사용하는지 공개되어 있지 않은 것들이 많다.
이런 폐쇄적인 애플리케이션을 사용하다가 특정한 작업에서 유난히 퍼포먼스가 떨어질 때에는 그 이유를 알아내기가 상당히 까다롭다. 애플리케이션을 직접 만들었다면 ALTER SESSION 명령으로 트레이스를 걸어 보면 되지만 사용자가 마음대로 SQL 문을 실행할 수 없도록 설계되어 있다면 인스턴스 전체에 트레이스를 걸어야 하기 때문이다.
그런데 b)처럼 DBMS_SYSTEM 패키지를 사용하면 간단하게 애플리케이션 외부에서 원하는 세션에 트레이스를 걸 수 있으므로 여간 편리한 기능이 아니다. 방법은 간단해서 SET_SQL_TRACE_IN_SESSION 프로시저에 트레이스를 걸고자 하는 세션의 SID, SERIAL#, TRUE를 파라미터로 넘겨주면 된다.
d)는 인스턴스 전체에 트레이스가 걸려서 퍼포먼스에 지대한 영향을 끼치므로 미리 사용자들에게 충분히 알려서 뒤탈이 없도록 해야 한다.
정답 : (d) (<- 마우스로 정답 부분을 선택하세요)
튜닝도구와 다이나믹 퍼포먼스 뷰
2-1. 래치에 관한 정보를 얻기 위해서 SQL 문을 작성하였다. FROM 절의 괄호 안에 들어갈 V$ 뷰로 적절한 것은?(주관식)
SELECT b.name, a.gets, a.misses, a.sleeps
FROM ( ) a, ( ) b
WHERE b.latch# = a.latch#;
<해설>
V$로 시작되는 뷰는 다이나믹 퍼포먼스 뷰라고 불리는 것들로 인스턴스 기동 이후에 시시각각 변화하는 데이터베이스 관련 정보를 제공하므로 데이터베이스가 얼마나 효율적으로 동작하고 있는지를 판단하는 바로미터로 활용할 수 있다.
그런데 인스턴스가 셧다운되면 그 동안 쌓였던 내용이 모두 사라지고 다음에 새롭게 시작되므로 인스턴스가 기동된 초기에는 V$ 뷰가 데이터베이스의 퍼포먼스를 정확하게 반영하지 못하며 어느 정도의 워밍업 시간이 지난 후에야 의미 있는 값을 갖게 된다.
V$ 뷰의 종류는 V$TABLESPACE처럼 퍼포먼스와는 무관한 것들을 비롯하여 거의 200개에 육박하는데 그 중에서 본 문제의 해답을 찾으면 v$latch와 v$latchname 이다. 래치를 포함하여 각종 V$ 뷰에 대해서는 관련된 단원에서 차차 다루게 될 것이다.
정답 : (v$latch, v$latchname) (<- 마우스로 정답 부분을 선택하세요)
2-2. utlbstat.sql과 utlestat.sql 스크립트를 사용해서 오전과 오후의 퍼포먼스를 각각 측정하기로 하였다. 그런데 오전에 만들어진 report.txt 파일이 있는 상태에서 오후에 다시 이들 스크립트를 실행했다면 결과는 어떻게 되겠는가?
a. 기존의 report.txt 파일 뒤에 오후의 측정 결과가 추가된다.
b. report.txt 파일이 이미 존재하므로 에러가 발생하고 종료된다.
c. 기존의 report.txt 파일의 내용이 삭제되고 오후에 생성된 정보만 남는다.
d. report21942.txt 와 같이 서버 프로세스의 프로세스 ID를 이용하여 파일 이름이 작성된다.
<해설>
데이터베이스 튜닝은 사실 utlbstat.sql과 utlestat.sql만 잘 이용해도 충분하다. 시중에는 현란한 그래픽으로 포장된 프로그램들이 출시되어 호기심을 자극하지만 실시간 모니터링이나 전문가 시스템 수준의 권고를 기대하지 않는다면 이 두 스크립트가 만들어낸 결과만으로도 얼마든지 데이터베이스의 상태를 체크할 수 있다.
이들 스크립트를 사용하면 원하는 기간 동안의 데이터베이스의 상태를 V$ 뷰로부터 추출하여 결과를 report.txt 파일로 생성해 준다. V$ 뷰는 인스턴스가 기동된 시점부터의 통계 정보를 가지고 있기 때문에 작업량이 많은 시간과 한가한 시간이 구분 없이 섞여 있으므로 단순히 V$ 뷰의 누적된 내용만으로는 튜닝 포인트를 찾기가 어렵다.
퍼포먼스 문제를 안고 있는 시스템이라고 하더라도 업무량이 폭증하는 몇 시간 정도가 문제가 되는 것이지 하루 종일 버벅거리지는 않는다. 피크 타임에 발생하는 문제가 점심시간이나 야간의 한가한 시간에 묻혀 상쇄되어 버리기 때문에 V$ 뷰에서 별다른 문제를 발견하지 못할 함정이 있는데 이 점을 극복하고자 utlbstat.sql과 utlestat.sql을 이용한다.
사용방법은 매우 간단해서 성능을 측정하고자 할 때 utlbstat.sql을 실행하고 끝마칠 때 utlestat.sql을 실행해 주기만 하면 된다. 그러면 utlbstat.sql가 실행될 때와 utlestat.sql가 실행될 때의 상태 변화를 계산하여 해당 기간의 통계 정보를 뽑아낸다.
그런데 utlestat.sql에서는 spool 기능을 이용해서 report.txt 파일을 생성하기 때문에 여러 차례 사용할 경우 기존의 파일 내용이 없어지고 새로운 내용으로 덮어쓰게 되므로 주의해야 한다. 기존의 report.txt 파일을 미리 다른 이름으로 변경해 놓거나 복사본을 만들어 두는 것을 잊어서는 안 된다.
정답 : (c) (<- 마우스로 정답 부분을 선택하세요)
2-3. 래치에 대한 WILLING-TO-WAIT 요청을 시도했으나 이미 다른 프로세스가 사용중이어서 할당 받지 못했다. 그래서 잠시 후에 다시 할당 시도가 있었으나 실패하였고 이후 한 번 더 실패한 후에 성공할 수 있었다. 최초의 시도를 포함해서 세 번을 실패하고 네 번째에 성공한 것인데 이 때 V$LATCH의 GETS, MISSES, SLEEPS 의 값은 어떻게 변하는가? (주관식)
<해설>
이탈리아 축구를 상징하는 빗장수비, 공포영화에서 살인범에게 쫓기던 주인공이 문고리에 걸어서 열리지 않도록 하는 쇠막대, 이들이 모두 래치라고 불리는 것이다.
오라클에서는 여러 프로세스가 하나의 SGA 영역을 동시에 사용함으로써 혼란이 발생하는 것을 방지하기 위해서 사용 전에 미리 래치를 걸어두도록 하고 있다. 만약 다른 프로세스가 먼저 래치를 차지했다면 래치의 성격에 따라서 WILLING-TO-WAIT 타입은 잠시 기다렸다가 다시 시도를 하고 IMMEDIATE 타입은 포기하고 다음으로 넘어가게 된다.
GETS는 WILLING-TO-WAIT 타입의 요청이 성공한 회수를 의미한다. 본 문제에서는 여러 번의 실패 끝에 래치를 차지했는데 실패 회수와는 상관 없이 래치를 차지한 것은 한 번 이므로 GETS는 1만큼 증가한다.
MISSES는 최초의 WILLING-TO_WAIT 타입의 요청이 실패한 회수이므로 처음에 실패했을 때 1만큼 증가하고 나중에 실패한 것은 이 값에 영향을 주지 않는다.
SLEEPS는 WILLING-TO-WAIT 타입의 요청이 실패하고 잠시 후에 다시 시도할 때까지 쉰 회수를 의미하므로 3만큼 증가한다.
정답 : (GETS는 1, MISSES는 1, SLEEPS는 3 씩 증가됨) (<- 마우스로 정답 부분을 선택하세요)
Shared pool 튜닝
3-1. Shared Pool을 구성하는 요소가 아닌 것은?
a. 라이브러리 캐시
b. 데이터 딕셔너리 캐시
c. PGA
d. UGA
<해설>
부족한 메모리를 알뜰하게 사용하고자 각종 파라미터를 지지고 볶으면서 1GB 메모리가 설치된 시스템을 부러워하던 때가 있었다. 그런데 지금은 메모리 가격이 워낙 저렴하기 때문에 메모리 튜닝에 대한 인식이 현저히 달라졌음을 실감한다.
메모리를 튜닝하기 위해서 시간과 인건비를 들이기 보다는 차라리 그 돈으로 메모리를 증설하는 편이 더 좋은 효과를 거둘 수도 있고 또한 회사의 고정 자산이 증가하는 부수입도 생긴다는 얘기다. 이렇게 메모리 튜닝의 인기가 과거만은 못한 것이 사실이지만 제아무리 메모리가 넉넉하더라도 그에 맞게 파라미터를 잡아주지 않으면 제대로 활용할 수 없는 것은 예나 지금이나 마찬가지다.
Shared Pool은 SGA의 일부분으로 라이브러리 캐시, 데이터 딕셔너리 캐시, UGA는 Shared Pool에 포함되지만 PGA는 그렇지 않다.
라이브러리 캐시는 SQL 명령이나 PL/SQL 객체의 파싱 정보가 저장되는 곳으로 캐시에 들어있는 것과 동일한 명령이 실행된다면 상당한 비용을 지불해야 하는 파싱 과정을 거치지 않고 캐시의 정보를 이용해서 바로 실행할 수 있기 때문에 빈번하게 실행되는 명령의 성능을 크게 향상시켜 준다.
UGA(User Global Area)는 세션의 정보가 기록되는 곳인데 MTS 환경이라면 Shared Pool에 생성이 되지만 MTS 환경이 아닌 경우에는 세션 정보가 PGA에 저장된다. MTS 환경에서는 동일한 세션에서도 각 명령을 처리하는 서버 프로세스가 변경될 수 있으므로 필요한 세션 정보를 공유하기 위해서 PGA가 아닌 Shared Pool을 이용하는 것이다.
정답 : (c) (<- 마우스로 정답 부분을 선택하세요)
3-2. 데이터 딕셔너리 캐시의 퍼포먼스 정보를 확인할 수 있는 것은?
a. V$ROWCACHE
b. V$LIBRARYCACHE
c. V$SGA
d. V$SQLAREA
e. V$DICTIONARYCACHE
<해설>
데이터 딕셔너리 캐시는 SQL 명령의 실행에 필요한 딕셔너리 정보, 즉 테이블과 컬럼의 존재 여부, 사용자의 권한, 객체의 의존 관계 등이 저장되는 곳이다. Oracle6 에서는 딕셔너리 캐시에 관한 파라미터가 별도로 존재했으나 Oracle7 이후부터는 Shared Pool 내에서 자동으로 적절하게 설정된다.
라이브러리 캐시의 정보는 SQL 문이 완벽하게 동일하지 않으면 재사용이 불가능하지만 그런 상황에서도 딕셔너리 캐시의 정보는 이용될 수도 있다는 장점이 있다.
데이터 딕셔너리 캐시에 관한 정보는 V$ROWCACHE에서 얻을 수 있는데 GETS 컬럼에는 데이터 객체에 대한 정보를 요청한 회수가 들어 있고 GETMISSES 컬럼에는 캐시에서 요청한 정보를 찾지 못한 캐시 미스 회수가 들어 있다.
따라서 이 컬럼의 값들을 종합해서 캐시 적중률을 계산하면 된다. 시스템의 용도에 따라서 다르겠지만 라이브러리 캐시의 적중률보다 딕셔너리 캐시의 적중률 목표를 조금 낮게 설정하는 것이 보편적이다.
정답 : (a) (<- 마우스로 정답 부분을 선택하세요)
3-3. SHARED_POOL_SIZE가 30MB 일 때 SHARED_POOL_RESERVED_SIZE의 최대 크기는?
a. 3MB
b. 10MB
c. 15MB
d. 30MB
e. 제한 없음
<해설>
SHARED_POOL_RESERVED_SIZE 파라미터를 이용하여 Shared Pool(정확히는 라이브러리 캐시) 내에 생성된 특별 공간은 비교적 자주 이용되는 큰 객체를 저장하기 위해서 주로 사용된다. 예를 들어 커다란 패키지가 Shared Pool에 로드되면 필요한 공간을 마련하고자 많은 수의 작은 객체가 밀려나게 되고 결국 전체적인 성능이 떨어지는 경우가 많았다. 그래서 아예 별도의 공간을 설정하여 다른 객체에 미치는 영향을 차단하거나 또는 다른 객체로부터의 영향을 단절함으로써 캐시 성능을 유지하자는 것이다.
SHARED_POOL_RESERVED_SIZE에 의해서 할당되는 영역은 Shared Pool의 일부분이기 때문에 Shared Pool의 크기에 제한을 받을 수 밖에 없는데 최대 SHARED_POOL_SIZE 값의 50%까지 설정할 수 있다. 디폴트는 5%이다.
정답 : (c) (<- 마우스로 정답 부분을 선택하세요)
버퍼 캐쉬 튜닝[.color]
4-1. Keep Pool, Recycle Pool, Default Pool의 캐시 적중률 관계는 어떻게 되어야 바람직한가?
a. Keep > Recycle > Default
b. Keep > Default > Recycle
c. Default > Keep > Recycle
d. Default > Recycle > Keep
e. Recycle > Keep > Default
<해설>
데이터베이스 버퍼 캐시(줄여서 버퍼 캐시)는 최근에 사용했던 테이블, 인덱스, 롤백, LOB 등 각종 세그먼트의 블록을 저장하고 있는 메모리 영역으로 SGA의 한 축을 구성한다. 흔히 캐시라고 하면 가장 쉽게 떠올릴 수 있는 형태라고 할 수 있다.
버퍼 캐시는 한정된 공유 공간이기 때문에 사용하다 보면 여러 가지 문제점이 드러나는데 어떻게 하면 실제로 자주 이용되는 블록이 캐시에서 밀려나지 않고 오랫동안 남아있도록 하느냐가 튜닝의 관건으로 자주 사용되지 않는 커다란 테이블을 읽어 들이는 바람에 빈번히 이용되는 블록들이 캐시에서 밀려나는 경우가 가장 큰 골치거리다.
그래서 Shared Pool 튜닝에서 Reserved Area를 마련하거나 Large Pool을 도입하는 것처럼 버퍼 캐시도 데이터 블록이 얼마나 자주 이용되느냐를 예상하여 Keep Pool, Recycle Pool, Default Pool의 3 개로 나눌 수 있도록 하였다. 이 기능은 옵션이기 때문에 이도 저도 귀찮다면 버퍼 캐시에 충분한 메모리를 할당할 여유가 된다면 Default Pool 하나로 쓰는 것도 나쁜 선택은 아니다.
Keep Pool에는 가장 자주 이용되는 데이터를 저장하고 Recycle Pool은 단발적으로 이용되는 것을 저장하며 그 밖의 블럭들은 Default Pool에 저장되도록 유도함으로써 서로 간의 영향을 최소화할 수 있다.
각 Pool의 성격에 따라서 생각해 보면 Keep Pool의 캐시 적중률이 가장 높아야 하고 Recycle Pool은 가장 낮으며 Default Pool은 그 중간 정도가 되어야 적정하다. 만약 이 관계가 유지되지 않는다면 각 Pool의 크기가 적절하지 않거나 각 Pool에 할당한 세그먼트의 이용 행태가 생각과는 다르기 때문이다.
정답 : (b) (<- 마우스로 정답 부분을 선택하세요)
4-2. 초기화 파라미터를 다음과 같이 설정하였을 때 Default Pool 의 크기는 어떻게 되는가?
DB_BLOCK_SIZE = 2048
DB_BLOCK_LRU_LATCHES = 10
DB_BLOCK_BUFFERS = 4096
BUFFER_POOL_KEEP = 2048
BUFFER_POOL_RECYCLE = 512
a. 512
b. 1024
c. 1536
d. 2048
e. 4096
<해설>
버퍼 캐시의 전체 크기는 DB_BLOCK_BUFFERS로 결정되며 단위는 DB_BLOCK_SIZE이다. 만약 Keep Pool과 Recycle Pool을 지정하지 않는다면 DB_BLOCK_BUFFERS가 모두 Default Pool로 사용되고 Keep Pool과 Recycle Pool을 지정하면 남은 영역이 자동으로 Default Pool로 이용된다.
현재 버퍼 캐시는 총 4096개의 오라클 블록으로 이루어져 있으며 그 중에서 Keep Pool에 2048개, Recycle Pool에 512개의 블록을 할당했으므로 남은 1536개의 블록이 Default Pool로 이용된다. V$BUFFER_POOL을 조회해 보면 각각의 크기를 확인할 수 있다.
정답 : (c) (<- 마우스로 정답 부분을 선택하세요
4-3. V$SYSSTAT 뷰를 통해서 버퍼 캐시의 적중률을 구하려고 한다. 이 때 필요한 통계 정보의 종류를 모두 선택하시오(세 가지).
a. physical reads
b. global gets
c. consistent gets
d. db block gets
e. physical writes
<해설>
버퍼 캐시의 적중률은 캐시에서 읽은 회수를 전체 읽기 회수로 나누거나 반대로 1에서 캐시가 아닌 디스크에서 읽은 비율을 빼 주어도 되는데 흔히 이용되는 공식은 다음과 같다.
1 – physical reads / (consistent gets + db block gets)
physical reads는 디스크로부터의 물리적인 읽기 회수이고 consistent gets는 롤백 세그먼트 블록으로부터 읽은 회수, 그리고 db block gets는 롤백 세그먼트가 아닌 커런트 블럭에서 읽은 회수이다. 따라서 전체 읽기 회수는 db block gets + consistent gets 이고 이 값으로 physical reads를 나누면 디스크로부터 읽은 비율이 나온다. 따라서 이를 1에서 빼면 캐시 적중률이 산출된다.
정답 : (a), (c), (d) (<- 마우스로 정답 부분을 선택하세요)
4-4. CACHE 키워드를 사용할 수 있는 경우를 모두 선택하시오(세 가지).
a. ALTER TABLE
b. ALTER INDEX
c. 힌트
d. ALTER USER
e. CREATE TABLE
<해설>
CACHE 기능은 CREATE TABLE, ALTER TABLE 명령이나 힌트를 이용해서 설정할 수 있다. CACHE 기능을 이용하지 않았다고 해서 그 테이블이 버퍼 캐시에 올라가지 않는 것은 아니며 CACHE 기능을 사용하는 이유는 Full Table Scan(FTS) 의 독특한 성격 때문이다.
FTS은 테이블 전체를 읽기 때문에 대체로 읽어들인 블록의 개수가 상당히 많은 편이기 때문에 버퍼 캐시의 LRU 리스트에서 가장 나중에 밀려날 위치를 차지한다면 다른 블록들을 밖으로 밀어내 버릴 위험이 높다. 그래서 FTS의 경우에는 LRU 리스트에서 가장 먼저 밀려날 위치에 올림으로써 리스트 내의 다른 블록에 대한 영향을 최소화하도록 되어 있다.
그런데 실제로 FTS의 대상이 되는 테이블이 자주 이용된다거나 옵티마이저에 의해서 의도하지도 않았던 FTS이 발생한 경우에는 이런 특성이 역효과를 가져올 수 있다. 그래서 FTS을 하더라도 다른 블록과 마찬가지로 정상적으로 리스트에 올라가도록 할 때에 사용되는 것이 CACHE 기능이다. 사용 예는 다음과 같다.
ALTER TABLE emp CACHE;
SELECT /*+ CACHE */ * FROM emp;
정답 : (a), (c), (e) (<- 마우스로 정답 부분을 선택하세요)
리두 로그 버퍼 튜닝
5-1. 리두 로그 버퍼에 엔트리를 기록하지 못하고 재시도한 비율은 적을수록 좋다. 이 비율을 구하기 위해서 V$SYSSTAT에서 어떠한 통계 정보를 가져와야 하는지 두 가지를 선택하시오.
a. redo buffer allocation retries
b. log buffer space
c. redo log space requests
d. redo buffers waits
e. redo entries
<해설>
리두 로그 버퍼는 지금까지 살펴본 Shared Pool과 버퍼 캐시와는 성격이 좀 다르다. Shared Pool이나 버퍼 캐시에서 우리의 관심은 캐시 적중률, 즉 읽기 작업의 효율성이었지만 리두 로그 버퍼는 쓰기 작업을 얼마나 원활하게 수행하는 가에 초점이 맞추어진다.
리두 로그 버퍼는 리두 로그 파일에 정보가 기록되기 전에 거쳐가는 곳으로 서버 프로세스는 리두 정보를 리두 로그 버퍼에 쌓고 LGWR는 리두 로그 버퍼의 엔트리를 리두 로그 파일에 기록하는 역할을 담당한다.
LGWR가 버퍼를 비우는 속도보다 리두 엔트리의 생성 속도가 빠르면 리두 로그 버퍼에 제 때 기록할 수 없기 때문에 정체가 발생한다. 이 때에는 리두 로그 버퍼의 크기를 늘려주거나 리두 로그 파일을 적절히 분산하여 기록 속도를 높여주어야 한다.
V$SYSSTAT에서 제공하는 통계 정보를 이용해서 재시도 비율을 구할 수가 있는데 redo buffer allocation retries / redo entries 와 같은 공식을 이용한다. 이 값은 거의 0에 가까운 수준을 유지해야 하며 만약 이 수치가 일정 기준을 초과하거나 증가추세를 보인다면 리두 로그에 관한 튜닝을 할 필요가 있다.
정답 : (a), (e) (<- 마우스로 정답 부분을 선택하세요)
5-2. 체크포인트가 원활하게 이루어지고 있지 않음을 알려주는 소스가 아닌 것은?
a. alert.log
b. report.txt
c. V$SGA
d. V$SYSTEM_EVENT
<해설>
체크포인트가 발생하면 데이터베이스 버퍼 캐시의 커밋된 트랜잭션과 리두 로그 버퍼의 내용이 각각 데이터 파일과 리두 로그 파일에 기록된다. 로그 스위치가 일어날 때 체크포인트도 함께 수행되는데 여기서 정체가 발생하면 퍼포먼스 저하로 이어진다.
체크포인트의 이상 유무는 여러 곳에서 확인할 수 있는데 alert.log에 Checkpoint not complete 메시지가 있으면 체크포인트가 지연됨에 따라 로그 스위치가 제대로 이루어지지 못함을 알 수 있다.
utlestat.sql에서 생성한 report.txt에도 체크포인트에 관한 통계 정보가 나와 있으며 V$SYSTEM_EVENT에서 log file switch (checkpoint incomplete) 이벤트 레코드를 보면 체크포인트의 문제를 점검할 수 있다. 체크포인트에서 적체가 발생하면 리두 로그 파일의 크기를 확장하여 로그 스위치 빈도를 줄여주는 것도 하나의 해결 방법이 된다.
정답 : (c) (<- 마우스로 정답 부분을 선택하세요)
5-3. 다음 중에서 잘못된 SQL 문은?
a. CREATE TABLE emp_backup AS SELECT * FROM emp NOLOGGING;
b. CREATE INDEX idx_ename ON emp(ename) NOLOGGING;
c. ALTER TABLE emp UNRECOVERABLE;
d. ALTER TABLE emp NOLOGGING;
e. ALTER TABLE emp CACHE LOGGING;
<해설>
리두 로그와 관련된 튜닝으로는 아예 리두 로그 정보를 생성하지 않는 것이 가장 최선이기 때문에 대량의 데이터를 다루는 작업에서는 리두 로그 정보가 만들어지지 않도록 하는 방법을 즐겨 이용한다. 이렇게 하면 나중에 복구할 때 필요한 정보가 없다는 단점은 있지만 일단 급한 작업을 빨리 수행하기 위해서는 이보다 좋은 대안은 없다.
SQL 문에서 리두 로그 정보가 만들어지지 않도록 하는 키워드로는 NOLOGGING과 UNRECOVERABLE 두 가지가 있다. 각각은 사용처가 정해져 있는데 NOLOGGING 옵션이 지원하는 명령이나 대상 객체의 범위가 넓기 때문에 오라클에서는 NOLOGGING의 사용을 권장한다.
UNRECOVERABLE 옵션은 CREATE TABLE AS SELECT 명령에서 사용되는 것으로 (b)처럼 ALTER 명령에는 적용할 수 없다.
NOLOGGING 옵션은 테이블의 속성이기 때문에 한번 지정하면 그 효과가 계속 남아있고 나중에 변경할 수도 있지만 UNRECOVERABLE은 속성이 아니기 때문에 효력이 해당 명령에 국한되는 특징이 있다.
정답 : (c) (<- 마우스로 정답 부분을 선택하세요)
데이터베이스 구성과 I/O 튜닝
6-1. Full Table Scan에서 한 번의 I/O로 보다 많은 데이터를 읽으려면 어떤 초기화 파라미터를 수정해야 하는가? (주관식)
<해설>
SQL문을 작성할 때에는 가능한 FTS(Full Table Scan)이 발생하지 않도록 해야 하는 것은 너무나도 기본적인 상식에 속하지만 지금도 인덱스를 경유해서 실행될 수 있음에도 FTS으로 처리됨으로써 퍼포먼스 손실을 보고 있는 시스템이 주위에 넘친다. 인덱스를 타느냐 못 타느냐의 차이는 메모리와 디스크 액세스의 속도 차이만큼이나 크다.
그러나 꼭 FTS를 사용해야 한다면 그 시간을 최대한 단축시키는 것이 급선무인데 가장 쉽게 컨트롤 할 수 있는 방법이 DB_FILE_MULTIBLOCK_READ_COUNT 파라미터를 조절하는 것이다. 이 파라미터는 순차적인 읽기 작업을 수행할 때 한 번의 I/O로 몇 개의 오라클 블록을 읽을 것인지를 결정하는 것으로 이 값을 높여서 FTS의 속도를 향상시킬 수 있다.
OLTP 환경에서는 디폴트 값을 변경하지 않고 그대로 사용하는 것도 괜찮은 선택이며 FTS이 지배적인 DSS(Decision Support System) 환경에서는 허용 한도까지 높이는 것이 좋다.
정답 : (DB_FILE_MULTIBLOCK_READ_COUNT) (<- 마우스로 정답 부분을 선택하세요)
6-2. 데이터베이스의 I/O 성능을 향상시키기 위한 방법이 아닌 것은?
a. RAID 디바이스를 이용한 데이터파일의 스트라이핑
b. 테이블과 인덱스의 분리
c. 리두로그 파일과 데이터파일의 분리
d. SYNCHRONOUS I/O 사용
<해설>
스트라이핑은 이제 서버 뿐 아니라 PC에서도 대중화되기 시작하였다. 스트라이핑은 하나의 파일을 여러 개의 디스크로 분산시켜 저장함으로써 파일의 각 부분에 대하여 동시 다발적으로 I/O를 처리하는 기술인데 스트라이핑 환경을 제대로 구성하면 디스크 액세스 속도가 몰라보게 빨라짐을 체감할 수 있다.
디스크가 여러 개 설치되어 있다면 운영 체제에서 제공하는 기능이나 써드 파티 소프트웨어를 이용해서 구현할 수도 있고 하드웨어 적으로는 RAID 컨트롤러를 구입하여 간단히 해결해도 된다.
소프트웨어나 RAID 컨트롤러의 지원을 받을 수 없는 열악한 상황이지만 꼭 스트라이핑을 이용하고 싶다면 테이블의 익스텐트를 강제적으로 여러 디스크에 하나씩 생성하여 인위적으로 구현할 수도 있다. 그러나 테이블의 개수가 많다면 DBA 혼자 감당하기에는 무척 번거로우므로 트랜잭션이 집중적으로 몰리는 핵심 테이블에 대해서만 작업하는 것이 바람직하다.
테이블과 인덱스의 분리, 롤백 세그먼트와 임시 세그먼트의 독립, 리두로그 파일과 데이터 파일의 분리 등은 가장 기본적인 튜닝 방법에 속한다. 기본 원칙은 동시에 이용될 소지가 있는 것은 서로 떨어뜨려 놓자는 것이다.
SYNCHRONOUS I/O 보다는 ASYNCHRONOUS I/O의 성능이 뛰어나다. SYNCHRONOUS I/O는 운영 체제로부터 이전 작업에 대한 응답을 받기 전까지는 다음 I/O 작업을 처리하지 못하지만 ASYNCHRONOUS I/O는 그런 제약을 받지 않기 때문에 ASYNCHRONOUS I/O를 사용하는 것이 속도면에서 유리하다. 그러나 간혹 ASYNCHRONOUS I/O가 원인이 되어서 오동작을 일으키는 사례가 있기 때문에 오라클에서 ASYNCHRONOUS I/O를 사용하기에 앞서 호환성 문제를 확인해 보는 것이 좋다.
정답 : (d) (<- 마우스로 정답 부분을 선택하세요)
6-3. report.txt 파일에서 각 테이블스페이스의 I/O 통계를 발췌한 내용은 다음과 같다. 튜닝이 필요할 것으로 판단되는 테이블스페이스는?
TABLESPACE READS WRITES
------------- ------------- -------------
RBS 245984 50492
SYSTEM 31432 34914
TEMP 1043 2854
TOOLS 20532 15840
USERS 502 353
a. RBS
b. SYSTEM
c. TEMP
d. TOOLS
e. USERS
<해설>
SYSTEM 테이블스페이스에는 SYSTEM 롤백 세그먼트와 데이터 딕셔너리외에의 이물질은 절대로 저장하지 않는 것이 원칙이다. 데이터 딕셔너리에 대한 액세스가 평소에도 대단히 활발하고 모든 SQL 문이 이 영역을 거치지 않고는 수행될 수 없기 때문에 SYSTEM 테이블스페이스에 대한 액세스 효율이 떨어지면 필연적으로 퍼포먼스가 저하될 수 밖에 없다.
데이터 딕셔너리는 쓰기 작업보다는 읽기 작업이 주로 발생하는 곳이므로 SYSTEM 테이블스페이스의 I/O 통계를 뽑았을 때 읽기 작업이 쓰기 작업보다 훨씬 많은 것이 정상이다. 그런데 위의 결과에서는 SYSTEM 테이블스페이스의 쓰기 작업이 오히려 읽기 작업을 초과하고 있기 때문에 RBS나 TEMP 세그먼트, 또는 빈번하게 쓰기 작업이 발생하는 사용자 테이블 등이 생성되어 있을 가능성이 높으므로 이를 확인해서 다른 곳으로 이동시켜야 한다.
정답 : (b) (<- 마우스로 정답 부분을 선택하세요)
[color]오라클 블록의 효율적 사용법
7-1. 데이터의 업데이트가 빈번하게 발생하는 테이블에 대한 블록의 PCTFREE 값은 어떻게 설정해야 하는가?
a. Higher
b. Lower
<해설>
PCTFREE와 PCTUSED는 데이터를 처리하는 속도에 상당한 영향력을 행사하는 요소이지만 최적의 값을 찾는 것은 고난도의 기술을 필요로 한다. 그래서 많은 사이트들이 본의 아니게 중간자적 입장을 취하면서 디폴트 값을 그대로 사용하고 있다.
PCTFREE는 해당 블록에 저장되어 있던 레코드가 변경되어 크기가 늘어나는 것을 대비해서 남겨두는 여유 공간의 비율로 기본값은 10%이다. 그런데 만약 DSS과 같이 일단 데이터가 입력된 다음에는 거의 변경할 일이 없는 시스템이라면 남겨둔 10% 영역이 고스란히 낭비될 가능성이 높다.
따라서 시스템의 성격이 명확하다면 PCTFREE를 낮춰서 블록 내의 공간 활용률을 높이거나 PCTFREE를 높여서 업데이트 작업의 처리 비용을 낮게 유지할 수 있다.
업데이트가 자주 일어나는 테이블이라면 공간 활용률이 떨어지는 것을 감수하더라도 PCTFREE를 키워서 앞으로 발생할 업데이트에 대비해야 한다. 물론 레코드가 변경되어도 길이는 항상 고정적이라면 얘기는 다르겠지만 말이다.
정답 : (a) (<- 마우스로 정답 부분을 선택하세요)
7-2. 블록의 크기를 작을 때 기대할 수 있는 효과가 아닌 것은?
a. 블록 사용에 대한 경합 발생이 적다.
b. 랜덤 액세스 속도가 빠르다.
c. OLTP 시스템에 적합하다.
d. 로우 체인의 발생을 줄일 수 있다.
<해설>
블록의 크기는 데이터베이스를 생성한 다음에는 변경이 불가능하기 때문에 그 값을 결정하지 못하고 망설이는 모습을 가끔 볼 수 있다. 다행히 오라클에서는 샘플 데이터를 이용하여 블록의 크기에 따른 읽기, 쓰기 작업의 속도를 테스트한 결과를 매뉴얼을 통해서 제공하고 있으므로 이를 참조하면 어느 정도 감을 잡을 수 있다. 그래도 도저히 결정할 자신이 없다면 중간 정도인 8KB로 만들어 놓고 양다리 걸치기를 시도해보는 것도 나쁜 방법은 아니다.
OLTP 시스템은 사용자 수는 많은 반면 한 번에 처리하는 데이터의 크기가 아주 작기 때문에 블록의 크기도 작게 만드는 것이 유리하다. 블록의 크기가 작으면 우선 랜덤 액세스 속도가 빠르고 또한 한 블록 안에 들어 있는 레코드 수가 적으므로 여러 트랜잭션이 하나의 블록을 사용하기 위해서 경합이 발생하는 빈도도 줄어드는 부수입이 생긴다. 그리고 꼭 필요한 만큼만 읽게 되므로 버퍼 캐시에 쓸데없는 데이터가 올라와서 아까운 메모리를 차지하는 비율도 감소한다.
그러나 로우 체인은 블록의 크기가 작으면 발생할 가능성이 더 높으며 헤더 정보가 블록의 일정 부분을 차지하기 때문에 블록의 크기가 작을 때에는 상대적으로 헤더 정보로 인한 오버헤드가 더 크다는 단점이 있다.
정답 : (d) (<- 마우스로 정답 부분을 선택하세요)
7-3. 로우 마이그레이션은 어떤 명령을 실행할 때 발생하는가?
a. INSERT
b. UPDATE
c. DELETE
d. SELECT
e. TRUNCATE
<해설>
로우 체인은 하나의 레코드가 너무 커서 한 블록에 저장될 수 없기 때문에 여러 개의 블록으로 나뉘어져 저장되어 있는 경우를 말한다. 따라서 기존의 레코드가 업데이트 되거나 새로운 레코드가 추가될 때 모두 발생할 수 있다.
로우 마이그레이션은 기존의 레코드가 업데이트 되는 바람에 크기가 증가하여 한 블록에 저장될 수 없을 때 레코드 전체를 여유가 있는 다른 블록으로 이동시키고 기존의 블록에는 새로운 블록에 대한 포인터를 남겨두는 것을 말한다.
두 경우 모두 하나의 레코드를 액세스 하기 위해서는 여러 개의 블록을 건드려야 하므로 성능 저하의 주범이라는 점에서는 유사하지만 로우 마이그레이션은 업데이트 시에만 발생한다는 차이점을 발견할 수 있다.
정답 : (b) (<- 마우스로 정답 부분을 선택하세요)
정렬작업의 최적화
8-1. 다음 중에서 내부적으로 정렬 작업을 유발하지 않는 명령은?
a. SELECT DISTINCT
b. UNION ALL
c. MINUS
d. ANALYZE
e. GROUP BY
<해설>
길고 짧은 것은 대봐야 안다는 말이 있듯이 정렬 작업을 하려면 대상 데이터를 모두 비교해야 하므로 시간이 많이 걸린다. 또한 메모리에서 정렬을 끝내지 못하고 디스크를 이용해야 한다면 치러야 할 대가는 몇 곱절 더 커질 수 밖에 없다. 그래서 정렬 작업은 퍼포먼스 튜닝에서 제거 대상 일 순위에 꼽히는 품목 가운데 하나이다.
보기에서는 UNION ALL을 제외한 모든 명령이 정렬 작업을 필요로 한다. UNION은 두 개의 쿼리 결과 중에서 중복된 것을 걸러내기 때문에 정렬을 해야 하지만 UNION ALL은 중복 여부를 신경 쓰지 않고 단순히 쿼리 결과를 합쳐주는 것으로 끝나므로 정렬을 할 필요가 없다.
정답 : (b) (<- 마우스로 정답 부분을 선택하세요)
8-2. 정렬 작업에 할당된 메모리를 정렬 작업이 끝난 후에 일정한 크기로 줄어들도록 하는 데 이용되는 초기화 파라미터는?
a. SORT_AREA_RETAINED_SIZE
b. SORT_AREA_SIZE
c. SORT_MULTIBLOCK_READ_COUNT
d. NLS_SORT
<해설>
SORT_AREA_SIZE 파라미터는 각 사용자마다 그만큼의 메모리를 할당하기 때문에 무턱대고 큰 수치로 지정해 놓으면 사용자 접속이 늘어나면서 나중에는 메모리가 쉽게 고갈될 수도 있다. 이 파라미터의 디폴트 값은 대부분의 OLTP 시스템에서 괜찮은 성능을 보여주는 값으로 설정되어 있다.
SORT_AREA_SIZE에 의해서 불필요하게 메모리가 점유되는 것을 방지하기 위해서 SORT_AREA_RETAINED_SIZE를 작은 값으로 지정해 놓으면 작업이 끝난 후에는 정렬을 위한 메모리 영역이 이 파라미터에 지정된 크기로 축소되므로 메모리를 절약할 수 있다. 롤백 세그먼트의 OPTIMAL 파라미터를 연상하면 될 것이다.
정답 : (a) (<- 마우스로 정답 부분을 선택하세요
8-3. 임시 세그먼트의 사용 상태를 확인하기 위해서 다음 명령을 실행하였다.
SELECT tablespace_name, max_sort_blocks
FROM V$SORT_SEGMENT;
TABLESPACE_NAME MAX_SORT_BLOCKS
--------------------------- ---------------------------
TEMP1 80
이 결과만을 놓고 보았을 때 SORT_AREA_SIZE 파라미터는 최소한 얼마 이상으로 설정하는 것이 좋겠는가? (단, DB_BLOCK_SIZE는 2KB)
a. 32KB
b. 64KB
c. 80KB
d. 120KB
e. 160KB
<해설>
V$SORT_SEGMENT의 MAX_SORT_BLOCKS 컬럼을 보면 최대 80개의 오라클 블록 규모의 정렬 작업이 TEMPORARY 타입의 테이블스페이스에서 처리된 적이 있음을 알 수 있다. 이 결과에 나타난 수준의 정렬 작업까지 메모리에서 처리한다면 이상적이므로 이를 SORT_AREA_SIZE의 기준으로 삼을 수 있다.
V$SORT_SEGMENT에서 보여주는 수치보다 더 큰 정렬 작업이 과거나 미래에 존재할 수도 있으므로 지금 보고있는 MAX_SORT_BLOCKS 컬럼의 값은 SORT_AREA_SIZE의 최소값이라고 생각하는 편이 좋다. 만약 이 값이 너무 크다면 그대로 적용하기는 무리이지만 본 문제의 경우 80 * 2KB = 160KB 이므로 별 탈 없이 설정해도 무방한 수준이다.
정답 : (e) (<- 마우스로 정답 부분을 선택하세요)
|
