※ 시험후기들을 참고해 복기한것으로 틀린부분이 있을 수 있음. ※ 실제 시험에는 ERD와 ROW SOURCE GENERATION 정보가 같이 나왔음.

1번

(실기1) 아래와 같이 테이블구조가 있는데 기존SQL를 개선하고, 필요시 인덱스를 설계변경 할 것
불필요하게 인덱스 변경시 감점

[테이블 정보]
* BOARD : #BRD_ID, BRD_NM, ...
* ARTICLE : #ART_SN, ART_TIL, ART_CONT, MBR_NO, BRD_ID, REG_DT,  ...
* MEMBER : #MBR_NO, MBR_NM, ...

[인덱스]
BOARD_PK : BRD_ID
ARTICLE_PK : ART_SN
MEMBER_PK : MBR_NO
ARTICLE_X01 : BRD_ID
ARTICLE_X02 : MBR_NO

[정보]
BOARD 테이블 : 10건
최근 3일 또는 5일전 게시글 : 850여 건
MEMBER 테이블 : 5000건(?)

[기존SQL]
SELECT
*
FROM
(SELECT
B.BRD_ID
, B.BRD_NM
, A.ART_SN
, A.ART_TIL
, GET_TEXT(A.ART_CONT,100) AS ART_CONT
, A.MBR_NO
, M.MBR_NM
, A.REG_DT
FROM BOARD B, ARTICLE A, MEMBER M
WHERE B.BRD_ID = :BRD_ID
AND A.BRD_ID = B.BRD_ID
AND A.MBR_NO = M.MBR_NO
AND A.REG_DT >= TRUNC(SYSDATE,-3)
ORDER BY A.REG_DT DESC
)
WHERE ROWNUM <= 5;

제출답안

· 인덱스 재구성 : ARTICLE_X01 : BRD_ID, REG_DTM

SELECT *
FROM (SELECT /*+ LEADING(B A M) USE_NL(A M) INDEX_DESC(A ARTICLE_X01) */B.BRD_ID, B.BRD_NM, A.ART_SN, A.ART_TIL, GET_TEXT(A.ART_CONT,100) AS ART_CONT, A.MBR_NO, M.MBR_NM,A.REG_DT
        FROM BOARD B, ARTICLE A, MEMBER M
       WHERE B.BRD_ID = :BRD_ID
        AND A.BRD_ID = B.BRD_ID
        AND A.MBR_NO = M.MBR_NO
        AND A.REG_DT >=TRUNC(SYSDATE,-3)
    ORDER BY A.REG_DT DESC
)
WHERE ROWNUM<=5;

해설(내생각)

• 문제점 기존 ARTICLE_X01(BRD_ID) 구성으로는 BRD_ID(게시판번호)에 들어있는 게시글을 전부 읽고 5ROW를 뽑는다.
• 튜닝포인트 1) ORDER BY 하나의 테이블에 한 컬럼으로 정렬하는 간단한 구성으로, 인덱스 생성해 충분히 sort order by를 제거 할 수 있다. 2) B.BRD_ID 가 PK이기때문에 B->A 순서로 접근해도 SORT ORDER BY 가 생략된다. (A 먼저보면 불필요하게 B를 5번 스캔함.)
• 추가 개선 여지 1) CLOB형 컬럼이 있는데 개선포인트? 딱히 찾지못했음. 어차피 5ROW 읽어야되고 sort order by 제거하면 크게 문제되지 않을것으로 생각됨. 2) SELECT-LIST에 참여하지 않거나 ERD상으로 불필요 테이블이 있는지? 불필요 테이블이 있는지 확인했는데 모두 필요한 테이블들. 3) 조건 추가 할만한게 있는지? A 먼저 봐야됐다면 A.BRD_ID = :BRD_ID를 추가해야겠지만, B.BRD_ID가 PK이기에 불필요.

2번

(실기2) 아래와 같이 테이블 구조가 있는데, 기존SQL를 개선하고, 필요시 인덱스를 생성할 것
(생성시 인덱스 및 파티셔닝이 필요하면 local partition 또는 global partition 등 작성, PK는 변경할 수 없음)

[테이블 정보]
* COMPANY : #CMP_ID, CMP_NM, ...
* ORDER : #ORD_NO, ORD_DT, CMP_ID, STAT_CD
* ORD_MATERIAL : #ORD_NO, #MTR_ID, ORD_DT, NUM_QTY, AMT_QTY, DC_QTY
* MATERIAL : #MTR_ID, MTR_NM, ...

[인덱스]
COMPANY_PK : CMP_ID
ORDER_PK : ORD_NO
ORDER_X01 : STAT_CD, ORD_DT
ORD_MATERIAL_PK : ORD_NO, MTR_ID
MATERIAL_PK : MTR_ID

[정보]
ORDER : 약 150여 만건 → STAT_CD 01(5%), 02(5%), 03(40%), 04(50%) 이외의 값은 존재하지 않음
ORD_MATERIAL : 연간 1500여 만건 / ORD_DT가 ORDER 테이블을 반정규화 하여 사용하고 있음 / 연단위로 ORD_DT로 파티셔닝 되어 있음
COMPANY : 5천건
MATERIAL : 1만건
주로 최근 1년을 자주 조회함

[기존SQL]
SELECT
A.ORD_NO
, C.CMP_ID
, C.CMP_NM
, SUM(B.NUM_QTY) AS NUM_QTY
, SUM(B.AMT_QTY) AS AMT_QTY
, SUM(B.DC_QTY) AS DC_QTY
FROM ORDER A, ORD_MATERIAL B, COMPANY C, MATERIAL D
WHERE
A.STAT_CD NOT IN ('03','04')
AND A.ORD_DT BETWEEN TO_DATE(:START_DT,'YYYY-MM-DD') AND TO_DATE(:END_DT,'YYYY-MM-DD')
AND A.ORD_NO = B.ORD_NO
AND A.CMP_ID = C.CMP_ID
AND A.MTR_ID = D.MTR_ID
GROUP BY A.ORD_NO, C.CMP_ID, C.CMP_NM;

제출답안

· 신규 인덱스 생성 : ORD_MATERIAL_X01 : ORD_NO, ORD_DT (LOCAL NON-PREFIXED)

SELECT /*+ LEADING(A B C) USE_NL(B C) INDEX(B ORD_MATERIAL_X01) INDEX(A ORDER_X01) */ A.ORD_NO, C.CMP_ID, C.CMP_NM, SUM(B.NUM_QTY) AS NUM_QTY, SUM(B.AMT_QTY) AS AMT_QTY, SUM(B.DC_QTY) AS DC_QTY
FROM ORDER A, ORD_MATERIAL B, COMPANY C
WHERE A.STAT_CD IN ('01','02')
AND A.ORD_DT BETWEEN TO_DATE(:START_DT,'YYYY-MM-DD') AND TO_DATE(:END_DT,'YYYY-MM-DD')
AND A.ORD_NO = B.ORD_NO
AND A.CMP_ID = C.CMP_ID
AND A.ORD_DT = B.ORD_DT
GROUP BY A.ORD_NO, C.CMP_ID, C.CMP_NM;

해설(내생각)

• 문제점 1) STAT_CD 에 '01', '02' 는 전체 중 10% 뿐이나 NOT IN 으로 인해 풀스캔중임. -> 너무 IN으로 바꾸고 싶음. 2) NL JOIN(ORD_NO + ORD_DT) VS HASH JOIN(ORD_DT) STAT_CD 10% 라는 부분과 최근 1년 조회가 많다는 걸 보고 전자를 택함 IF) ORD_DT를 선두로한다면 다른 모든 주문을 불필요하게 읽어야함.

• 튜닝포인트 1) STAT_CD NOT_IN을 IN으로 변경해 INDEX SCAN 유도 ORD_NO이 150만건 테이블이고 최대기간 검색 시 10%인 15만건 정도면 INDEX 스캔해도 괜찮아보임. 2) ORD_MATERIAL 테이블 인덱스 신규생성 및 A.ORD_DT = B.ORD_DT 조건추가 ORD_NO + ORD_DT LOCAL로 구성하면 ORD_NO으로 접근 후 파티션 PRNUING가능 자주 들어오는 연단위조건이 들어올 시 한개의 파티션만 보면됨. 3) 불필요 테이블 제거하기 : MATERIAL 조인 제거 SELECT-LIST에 참조하지도 않고, ERD상 ORD_MATERIAL은 반드시 하나의 MATERIAL을 포함하기 때문에 제거가능(EXISTS도 불필요.)

• 추가 개선여지 1) A,B 조인 후 GROUP BY 하고나서 COMPANY C와 조인

참고했던 책

1. 오라클 성능고도화 I,II
2. SQLP 핵심노트 1,2권
3. 노랭이
4. 기출3개년(1,2과목대비)

과목별 후기

1과목

몇 문제의 ERD,지문이 반페이지 차지 할 정도로 길어서 당황했습니다😐 10문제중 처음보는 문제 4개 정도(ERD해석)였고, 나머지 6개는 노랭이/최근기출과 유사했습니다.

2과목

SQLD 시험유형, 노랭이/최근기출과 거의 유사했지만 생각보다 시간 많이 잡아먹었습니다. SQLP준비하면서 1,2과목은 거의 안봤는데 준비해놓아야 충분한 시간 확보가 가능해보입니다.

3과목

오라클성능고도화 1권과 핵심노트1,2 와 유사한 문제가 많았습니다.

핵심노트1,2에 있는 문제가 문제은행처럼 그대로 출제되는건 아니기에 오라클성능고도화 정독 후 핵심노트1,2를 이론 정리하는 방향으로 접근하는게 좋아보입니다.

기억나는 헷갈렸던 문제

1) alter system set optimizer_mode=all_rows 2) alter database set optimizer_mode=all_rows -> 틀림 3) alter session set optimizer_mode=all_rows 4) select /+ all_rows / * from ~

실기

지문이 왜이렇게 긴지 문제 보자마자 숨막혔습니다. 튜닝 할만한 부분을 체크하면서 해석했는데 시간이 오래걸리고 무엇보다 손쿼리 작성하는게 힘듭니다ㅠ(볼펜으로만 작성가능)

여러가지 아이디어들이 막 생각나는데 실전처럼 해볼수가 없으니 머릿속이 정리가 안되서 혼란스러웠습니다.

그리고 힌트 작성하라는 말은 없었는데, 혹시 몰라 작성해주었습니다.

실1) 최근 등록일자 5 rows 를 뽑는 쿼리 튜닝하기 체크포인트

1. select-list에 clob 형이 있음.(맨 끝 SELECT로 빼야될지? -> 어차피 SORT 생략으로 굳이 필요없었음.)
2. SORT ORDER BY 생략하기 -> 인덱스 생성

실2) 대용량 파티셔닝 테이블 조인 쿼리 튜닝하기 체크포인트

1. not in 절을 in 절로 바꿔서 INDEX RANGE SCAN 유도
2. 불필요 조인 테이블 있으면 제거 (1:M 에서 1쪽이고, SELECT-LIST에서 참조안해 제거가능)
3. 파티션 인덱스 설계(LOCAL/GLOBAL, PREFIXED/NON-PREFIXED, NON-PARTITION)

단답식

단1] 쪼갤수 없는 의미 ? 속성

단2] 컬럼정보 주고 주식별자로 적절한 속성쓰시오 직원번호

단3] GROUP BY로 풀어쓴 쿼리 주고 ROLLUP으로 변환 ROLLUP(A.상품번호, A.상품금액)

단4] 계층형 쿼리 결과쓰기

단5] 버퍼캐시는 ___알고리즘 LRU

단6] 인덱스 구성 변경 시 A_ROWS 맞추기

단7] 파티셔닝 인덱스 생성하기 문법 CREATE INDEX TMP_X01 ON TAB(COL1,COL2) __; LOCAL

단8] 테이블, 결과주고 빈칸채우기 (ROWS vs. RANGE) SELECT 상품코드, sum(금액) over(order by 금액 ___ between 0 preceding and 50 following) from A; rows

단9] alter table tab1 ____ ; nologging

단10] 쿼리레벨 옵티마이저 모드 설정 /*+ FIRST_ROWS */

44회차 시험은 결과발표 1주일전 조기발표 했습니다. 결과는.. 합격!👍

실기 풀이는 다음 포스팅에 있음.

데이터베이스 아키텍처

1. 데이터베이스와 인스턴스의 정의

오라클에서는 디스크에 저장된 데이터 집합(DATAFILES, REDO LOG FILES, CONTROLFILES)를 데이터베이스, SGA 공유 메모리 영역과 이를 엑세스하는 프로세스 집합을 인스턴스라 칭함.

2. 오라클 백그라운드 프로세스 설명

• SMON : System Monitor, 장애 발생 시스템 재기동 시 인스턴스 복구 수행/임시 세그먼트,익스텐트 모니터링
• PMON : Process Monitor, 이상이 생긴 프로세스가 사용하던 리소스 복구
• DBWn : Databse Writer, 버퍼캐시에 있는 Dirty 버퍼를 데이터파일(디스크)에 기록
• LRWR : Log Writer, 로그 버퍼 엔트리를 Redo 로그 파일(디스크)에 기록
• ARCn : Archiver, 꽉찬 REDO 로그 덮어쓰여지기 전에 아카이브 로그 디렉토리로 백업
• CKPT : Checkpoint,
• RECO : Recoverer, 분산 트랜잭션 문제 발생 시 해결

3. 데이터 저장 구조에 대한 설명

• 블록(페이지) : 데이터를 읽고 쓰는 단위
• 익스텐트 : 공간을 확장하는 단위
• 세그먼트 : 익스텐트 집합 (연속할 필요는 없음, 두개 다른 데이터파일에 있는 익스턴트가 하나의 세그먼트로 구성 될 수 있음)
• 테이블스페이스 : 세그먼트를 담는 컨테이너로, 여러 데이터파일로 구성

4. 테이블스페이스 - 세그먼트 - 익스텐트 - 블록

5. 오라클 UNDO 사용하는 목적

• Transaction Rollback
• Transaction Recovery
• Read Consistency

6. 오라클 REDO 로그 설명

• Online Redo 로그는 최소 두개 이상 파일로 구성되어야함.(번갈아 사용해서 차면 archive에 떨굼)
  • 로그 스위치 주기는 빠를 수록 좋다? (X) 스위치가 많이 일어나 백업되지 못한 ONLINE REDO에 스위칭 일어나면 DB HANG 발생 할 수 있음.
• FAST COMMIT : COMMIT 후 실시간으로 디스크에 반영되면 부하 유발함. 따라서 REDO LOG 파일에만 APPEND로 정확하게 기록 후 커밋을 완료함(실제 데이터파일에 변경은 배치로 일괄 작업됨)
• Database Recovery(=Media Recovery)/Cache Recovery/Fast Commit

7. REDO 메커니즘

• FAST COMMIT
• WRITE AHEAD LOGGING : 버퍼캐시 블록 갱신 전에 먼저 REDO 엔트리를 로그버퍼에 기록, DBWR가 일하기 전에 LGWR가 REDO 로그버퍼를 디스크에 기록했음이 보장되어야 함.
• LOG FORCE AT COMMIT : 메모리상에 로그 버퍼는 유실 될 수있으니, 최소한 트랜잭션종료(커밋) 시 로그를 데이터파일에 기록해야함.

8. 오라클 SGA 구성요소

• SGA? SYSTEM GLOBAL AREA의 약자로 DB BUFFER CACHE / REDO LOG BUFFER / SHARED POOL(Library Cache, Dictionary Cache) 로 구성

9. 오라클의 메모리 캐시에 대한 설명

• DB버퍼캐시 : 테이블/인덱스/UNDO 블록 캐싱
• 라이브러리캐시 : SQL, PL/SQL, FUNCTION, 트리거 등 캐싱
• 딕셔너리 캐시 : 테이블/인덱스/데이터파일 정보, 시퀀스 캐싱
• RESULT 캐시 : SQL 결과집합 캐싱

10. 버퍼캐시 블록과 데이터파일 블록간 동기화가 필요한 블록?

• Dirty 버퍼 : 버퍼 캐시에서 변경이 발생했지만, 아직 데이터파일에 기록되지 않아 동기화가 필요한 버퍼블록
• Free 버퍼 : 비어있거나 재사용가능한 버퍼
• Pinned 버퍼 : 읽기/쓰기 작업을 위해 액세스 되고있는 버퍼 블록

SQL 처리 과정

11. SQL의 정의

• SQL : Structured Query Language, Set-based, declarative

12. SQL 처리 과정 중 문법, 의미상 오류 체크하는 단계

SQL 파싱

13. SQL 처리 과정 중 데이터 딕셔너리에 수집해둔 토계정보 활용하는 단계

SQL 최적화

14. SQL 최적화 단계에서 옵티마이저가 최종 하나를 선택하는 궁극의 기준

예상 비용 COST

15. 오라클 옵티마이저 힌트에 대한 설명

옵티마이저 힌트는 명령어(Directives)이므로 특별한 이유 없는 한 그대로 실행됨.

16. 옵티마이저 힌트가 무시되는 경우

• 문법적으로 맞지 않게 힌트 기술하거나 잘못된 참조
• 논리적으로 불가능하거나 의미적으로 맞지 않게 힌트 기술
• 옵티마이저에 의해 내부적으로 쿼리가 변형된 경우 참고로 INDEX(A) 하면 A에 어떤 인덱스 쓸지 옵티마이저가 선택함?????

  17. 아래 옵티마이저는 힌트 잘 동작함

• /*+ INDEX(E, EMP_X01) INDEX(D DEPT_X03) */

  18. 옵티마이저 힌트 간략 정리

데이터베이스 I/O 메커니즘

19. 블록 단위 I/O 하지 않는 오퍼레이션

• 테이블/컬럼 정보를 딕셔너리 캐시에 적재할때 (ROW CACHE로 ROW 단위 I/O)

  20. 흠..

22. 버퍼캐시 히트율

• BCHR : (논 / 전체) * 100 = (전체 - 물리 / 전체) * 100 = (1- (물/전체)) *100

  27. 데이터베이스 I/O원리에 대한 설명

• 한쿼리가 같은 블록을 반복해서 액세스 하면 버퍼캐시 히트율은 높아짐
• Multiblock I/O는 한번에 I/O 콜로 여러 데이터 블록을 읽어 메모리에 적재하는 방식
• 테이블 FULL SCAN 시, 작은 익스텐트로 구성되어있다면 I/O CALL이 늘음 → Multiblock I/O라도 익스텐트 하나에 대해서만 I/O함. 익스텐트 경계를 넘지못함.

  28. 병렬쿼리 자주 사용하면?

  CPU 메모리 자원을 많이 사용하고, 잦은 체크포인트 수행으로 LGWR 작업량이 증가해 커밋 성능 지연되어 온라인 트랜잭션 처리에 나쁜 영향 줄 수 있음.