sqld 자격검정 실전문제 과목3 문제풀이 1~10

정답 4번

정답이 4번 실제 처리 건수인 이유는, 보통 실행계획은 “실행 전에 옵티마이저가 세운 예상 계획” 이라서 실제로 몇 건이 처리됐는지 는 알 수 없기 때문이다

옵티마이저란

DB가 SQL을 실행하기 전에 가장 효율적인 실행 방법(실행계획) 을 고르는 기능이다

어떤 테이블을 먼저 읽을지, 인덱스를 탈지(인덱스 스캔) 아니면 전체를 읽을지(풀 스캔), 조인을 어떤 방식으로 할지(Nested Loop/Hash Join 등)를 결정한다 이때 실제로 데이터를 전부 읽고 판단하는 게 아니라, 테이블/인덱스의 통계 정보를 바탕으로 결과 건수(Cardinality)와 비용(Cost)을 추정해서 가장 싸다고 판단되는 계획을 선택한다.그래서 통계가 부정확하면 예상 건수와 Cost가 틀어져 실행계획이 비효율적으로 나올 수도 있다

따라서  실행계획으로 알 수 있는 것들

1. 액세스 기법: TABLE ACCESS FULL, INDEX RANGE SCAN 같은 접근 방식이 그대로 찍힌다.
2. 질의 처리 예상 비용(Cost): 말 그대로 예상 비용이 실행계획에 나온다. (그래서 2번은 실행계획으로 알 수 있는 정보가 맞다)
3. 조인 순서/조인 방식: NESTED LOOPS, HASH JOIN 등이 보이고, 어떤 테이블을 먼저 읽는지도 흐름으로 파악 가능하다.

실행계획으로 알 수 없는 것

1. 실제 처리 건수: 실행계획에 보이는 Card(Cardinality)는 실제 처리 건수가 아니라 옵티마이저가 추정한 건수(예상 행 수)다 

참고로 실제 처리 건수(Actual rows) 를 보려면 DB에 따라

• Oracle: DBMS_XPLAN.DISPLAY_CURSOR + ALLSTATS LAST 같은 “실행 후 통계”
• PostgreSQL: EXPLAIN ANALYZE
• MySQL: EXPLAIN ANALYZE(버전 지원 시)

처럼 실행까지 해서 런타임 통계를 봐야 한다

따라서 4번이 정답이다 

정답 3번

오라클에서 모든 인풋과 아웃풋(I/O)의 처리의 최소단위는 블록(BLOCK)다

테이블이 아닌이유는

1. 테이블은 논리 단위다
   “행/컬럼의 집합”이라는 개념이지, 디스크가 테이블을 한 번에 통째로 읽고 쓰는 구조가 아니다.
2. 디스크 I/O는 물리 단위로 한다.
   DB는 디스크에서 데이터를 읽을 때 블록(오라클) 단위로 읽어서 메모리(Buffer Cache)에 올린다.
   그래서 “레코드 1개, 컬럼 1개만 필요해도” 그 레코드가 들어있는 블록 전체를 읽는다는 설명이 성립한다.
3. 만약 I/O 단위가 테이블이면?
   컬럼 하나 조회할 때마다 “테이블 전체를 매번 읽는다”가 되어버리는데, 현실적으로 말이 안 된다 (엄청 비효율)

정리하면, 테이블은 저장 구조의 최소 단위가 아니라서 I/O 단위가 될 수 없고, 실제로 읽고 쓰는 단위는 블록(오라클) / 페이지(DBMS마다 표현) 라고 보면 된다

정답 4번

버퍼에 캐시된 이후 변경 발생 = 메모리에서 수정됨

디스크(데이터파일)에 있는 블록을 메모리(버퍼 캐시)로 올려두면 “캐시됨” 상태다
그 상태에서 UPDATE/INSERT/DELETE 같은 변경이 일어나면 메모리 안의 블록 내용이 바뀐다

근데 이 순간에는 아직 디스크에 저장(반영)된 게 아닐 수 있다 → 메모리 내용과 디스크 내용이 달라짐

디스크와 동기화가 필요한 상태 = 더티(Dirty) 버퍼

그래서 “버퍼에 캐시된 이후 변경이 발생했지만, 아직 디스크에 기록되지 않아 동기화가 필요”한 버퍼 블록을 더티(Dirty) 버퍼라고 한다.

즉, “더러운”의 의미는 실제로 dirty하다가 아니라 ‘디스크와 내용이 달라진 상태’ 라는 뜻이다.

디스크에 기록되는 순간 = 프리(Free) 버퍼가 됨

오라클은 나중에 DBWR(데이터베이스 라이터) 같은 프로세스가 더티 블록을 디스크에 써서 동기화한다

디스크에 기록이 끝나면?

• 이제 그 블록은 더 이상 “동기화가 필요한 상태(Dirty)”가 아니다.
• 그리고 필요하면 다른 블록을 올리기 위해 재사용 가능한 후보가 된다.

문제 문장에서는 “재사용하기 위해 디스크에 기록하는 순간 이 버퍼는 ㉡ 버퍼가 된다”라고 했으니, 여기서는 프리(Free) 로 보는 게 맞다.

 그래서 선택지는 ④ (㉠ 더티, ㉡ 프리) 가 된다

정답 2번

규칙 기반 옵티마이저(RBO)는 “비용 계산”이 아니라 미리 정해진 규칙 우선순위로 실행계획을 선택한다 이때 ROWID로 특정 행의 위치를 바로 찾아가는 row by rowid 접근은 가장 빠른 축에 속해 우선순위가 매우 높다고 본다

비용 기반 옵티마이저(CBO)는 테이블/인덱스/컬럼의 통계정보(행 수, 분포, 선택도 등)를 바탕으로 결과 건수(Cardinality)와 비용(Cost)을 추정해 실행계획을 만들기 때문에, 통계가 갱신되면 같은 SQL이라도 실행계획이 달라질 수 있다

오라클 실행계획에서 대표적인 조인 방식은 NL Join, Hash Join, Sort Merge Join이 있으며, 대량 데이터 집계가 많은 DW 환경에서는 보통 NL Join보다 Hash Join이나 Sort Merge Join이 더 자주 유리하다는 점을 함께 기억하면 된다

✔️인풋/아웃풋(I/O) 효율화 원리

I/O 효율화의 핵심은 “디스크에서 읽는 블록 수를 줄이고, 같은 데이터를 반복해서 읽지 않게 만드는 것”이다

그래서 같은 데이터에 여러 번 접근하지 않도록 쿼리/조인 순서를 정리하고, 옵티마이저가 올바른 계획을 고르도록 통계정보를 정확히 유지하는 것이 기본이다 또 특정 상황에서 실행계획이 계속 잘못 나오는 경우에는 힌트를 통해 접근 경로를 유도해 I/O를 줄이는 방식도 보조적으로 사용한다

✔️데이터베이스 연결 개념

1. 실제 OLTP 환경에서는 커넥션을 매번 열고 닫으면 로그인/인증, 세션 생성, 네트워크 왕복, 프로세스/스레드 할당 같은 비용이 계속 발생해서 오히려 느려지기 쉽다. 그래서 현실적으로는 커넥션 풀(Connection Pool) 을 써서, 커넥션을 “완전히 종료”하는 게 아니라 반납해서 재사용하는 방식이 일반적이다
2. 프로세스 기반이 스레드 기반보다 커넥션 요청 부하가 더 크다는 뜻인데, 프로세스는 스레드보다 생성/유지 비용이 크기 때문에 일반적으로 맞는 방향이다
3. Dedicated는 “클라이언트 1명(세션)당 서버 프로세스(또는 스레드)가 붙는 구조”라서, 새로 접속(새 커넥션) 할 때마다 전용 서버 쪽 자원이 하나 더 필요해진다
4. Shared는 클라이언트가 서버 프로세스와 바로 붙는 게 아니라, 먼저 Dispatcher를 통해 요청을 전달하고, 공유된 서버 프로세스들이 일을 처리하는 구조다

정답 3번

익스텐트 내 블록들은 서로 인접하지만, 익스텐트끼리 서로 인접하지는 않는다

여기서 익스텐트란 여러 블록을 묶어 한번에 할당하는 행위로 계층으로 정리하자면 아래와 같다

• 블록(Block): 디스크에서 읽고 쓰는 최소 단위(페이지라고도 함)
• 익스텐트(Extent): 여러 블록을 묶어 “한 번에 할당”하는 단위
• 세그먼트(Segment): 하나의 객체(테이블/인덱스)가 쓰는 익스텐트들의 모음
• 테이블스페이스(Tablespace): 세그먼트들이 들어가는 논리적 그릇(여러 데이터파일로 구성될 수 있음)

정답1번

 

• 2번 Log Force at commit: “커밋 시점에 로그를 반드시 디스크에 플러시한다” 성격(커밋 규칙)이고, 지문은 더 넓게 “데이터 블록 쓰기 전에 로그 먼저”를 말함.
• 3번 Fast Commit: 커밋을 빠르게 하기 위한 개념(데이터파일 쓰기 기다리지 않음)이라 포인트가 다름.
• 4번 Delayed Block Cleanout: 커밋 후 블록의 ITL/트랜잭션 흔적 정리를 나중에 하는 개념이라 로그 선기록 규칙과 다름.\

따라서 지문에서 얘기하는 방식은 1번 이다

✔️메모리구조

DB 버퍼 캐시는 디스크(데이터파일)에서 읽어온 데이터 블록을 메모리에 저장해 재사용하는 영역이다

같은 데이터를 다시 읽을 때 디스크 I/O를 줄여 성능을 높이는 역할을 한다 대량 INSERT에서 사용하는 /*+ APPEND */ 힌트는 일반적인 버퍼 캐시 경로를 최소화하고 Direct Path Insert를 유도해 대량 적재에 유리한 방향으로 동작한다

또한 인덱스의 클러스터링 팩터가 좋으면 인덱스 순서대로 테이블 데이터가 물리적으로 가까워 연속 블록 접근이 늘어나고, 그 결과 같은 버퍼 블록을 계속 잡아두는 Buffer Pinning 효과로 I/O를 줄일 수 있다

반면 “LRU 정책이므로 Table Full Scan으로 읽은 블록이 Index Range Scan보다 캐시에 더 오래 남는다”라는 설명은 일반화가 어렵다. Full Scan은 많은 블록을 한꺼번에 읽어 캐시 오염을 유발할 수 있어 오히려 오래 남기지 않도록 처리되는 경우가 많고, 캐시에 오래 남는지는 Full/Range 여부보다 접근 패턴(반복 조회 여부)과 캐시 정책에 의해 결정된다고 정리하면 된다.