SQLD 실전 문제 : 고급 SQL 튜닝
문제 1
Q. 아래 SQL은 v_주식선물구분 바인드 변수 입력에 따라 선택적으로 주식월별시세 또는 선물월별시세 테이블에 데이터를 입력한다. 실행 정보와 같이 바인드 변수를 입력하고 쿼리를 100번(SID=100)과 200번(SID=200) 세션에서 순차적으로 수행하였을 때 200번 세션의 상태로 올바른 것은?
[SQL]
INSERT /*+ APPEND */ ALL WHEN :v_주식선물구분 = '주식' THEN INTO 주식월별시세(종목코드, 거래일자, 종가) WHEN :v_주식선물구분 = '선물' THEN INTO 선물월별시세(종목코드, 거래일자, 종가) SELECT a.종목코드, :v_기준일자 AS 거래일자, AVG(a.종가) AS 종가 FROM 주식일별시세 a WHERE :v_주식선물구분 = '주식' AND a.거래일자 BETWEEN ADD_MONTHS(:v_기준일자, -1) AND :v_기준일자 GROUP BY a.종목코드 UNION ALL SELECT a.종목코드, :v_기준일자 AS 거래일자, AVG(a.종가) AS 종가 FROM 선물일별시세 a WHERE :v_주식선물구분 = '선물' AND a.거래일자 BETWEEN ADD_MONTHS(:v_기준일자, -1) AND :v_기준일자 GROUP BY a.종목코드;
[실행]-- 세션 100 EXEC :v_주식선물구분 := '주식'; / -- 세션 200 EXEC :v_주식선물구분 := '선물'; /
① TM 락을 Row-X(SX) 모드로 요청하고 대기한다.
② TX 락을 Exclusive 모드로 요청하고 대기한다.
③ TM 락을 Exclusive 모드로 요청하고 대기한다.
④ 정상적으로 수행된다.
정답 : ③
해설 : INSERT ALL 구문에 APPEND 힌트를 사용하면 모든 테이블에 Exclusive 모드의 TM 락이 설정된다.
※ Oracle에서 Direct Path Insert 방식으로 데이터를 입력하는 방법
- Insert Select 문장에 /*+ append */ 힌트 사용
- 병렬 모드로 Insert
- Direct 옵션을 지정하고 SQL *Loader(sqlldr)로 데이터를 로드
- CTAS(Create Table ... As Select) 문장을 수행
문제 2
Q. 다음 중 오라클에서 아래와 같이 nologging 옵션과 append 힌트를 사용하여 대용량 데이터를 INSERT할 때 극적인 성능 개선 효과를 가져오는 원리를 설명한 것으로 가장 부적절한 것은?
alter table 수납 nologging; insert /*+ append */ into 수납 select * from 수납_임시 where 수납일시 between '20131204120000' and '20131204235959'
① Lock을 사용하지 않고 빠르게 입력한다.
② Redo와 Undo 발생량을 최소화한다.
③ 데이터 입력이 가능한 빈 블록을 찾기 위해 Freelist를 조회하지 않아도 된다.
④ 버퍼 캐시를 거치지 않고 데이터를 세그먼트 HWM 뒤쪽에 순차적으로 입력한다.
정답 : ①
해설 : Append 모드로 INSERT 하면 Exclusive 모드 테이블 Lock이 걸린다.
문제 3
Q. 다음 중 DML 튜닝 방안으로 가장 부적절한 것은?
① 대량 데이터에 대해 작업할 경우, 인덱스를 Unusable 상태로 변경하고 작업한 후에 인덱스를 재성성하는 것이 빠를 수 있다.
② 대량의 데이터를 빠르게 UPDATE하기 위해 테이블을 nologging 모드로 변경하고 작업을 시작한다.
③ 오라클의 수정 가능 조인 뷰는 키-보존 테이블에만 입력, 수정, 삭제가 허용된다.
④ 대량 데이터에 대한 UPDATE나 DELETE문의 경우 기존 데이터를 임시 테이블에 저장하고 테이블을 TRUNCATE 한 후 임시 테이블을 이용해 다시 입력하는 것이 빠르다.
정답 : ②
해설 : nologging 모드는 INSERT 문일 때만 기능이 작동한다.
※ 대량의 데이터를 일반 UPDATE 문으로 갱신하면 상당히 오랜 시간이 소요될 수 있다. 다음과 같은 이유 때문이며, DELETE 문일 때도 마찬가지이다.
- 테이블 데이터를 갱신하는 본연의 작업
- 인덱스 데이터까지 갱신
- 버퍼 캐시에 없는 블록을 디스크에서 읽어 버퍼 캐시에 적재한 후에 갱신
- 내부적으로 Redo와 Undo 정보 생성
- 블록에 빈 공간이 없으면 새 블록 할당 → Row Migration 발생
문제 4
Q. 다음 중 (A), (B) 두 SQL에 대한 설명으로 가장 부적절한 것은?
(A)
UPDATE 급여지급 T SET T.월급여 = ( SELECT S.월급여 FROM 사원 S WHERE S.사원번호 = T.사원번호 ) WHERE T.급여월 = '201101' AND EXISTS ( SELECT 1 FROM 사원 S WHERE S.사원번호 = T.사원번호 AND S.부서코드 = '30' )
(B)MERGE INTO 급여지급 T USING ( SELECT S.사원번호, S.월급여 FROM 사원 S WHERE S.부서코드 = '30' ) S ON (T.급여월 = '201101' AND T.사원번호 = S.사원번호) WHEN MATCHED THEN UPDATE SET T.월급여 = S.월급여
① (A)와 (B)의 수정되는 데이터 건수는 같다.
② 둘 다 NL 방식으로 조인한다면, (A)가 (B)보다 사원 테이블의 반복 액세스가 많아 발생하는 I/O도 많다.
③ 둘 다 해시 방식으로 조인한다면, 아무리 처리량이 많아도 성능에 차이가 없다.
④ (B)는 INSERT 처리도 같이 하도록 문장을 재구성할 수 있다.
정답 : ③
해설 : (A)의 서브쿼리를 세미조인 방식으로 변경해도 조인에 참여하는 테이블이 더 많으므로 (B)보다 I/O가 더 많이 발생한다.
문제 5
Q. 야간에 단독으로 DML을 수행하는 대용량 배치 프로그램 속도를 향상시키려고 한다. 다음 중 고려할 만한 튜닝 방안으로 가장 부적절한 것은?
① 인덱스를 제거했다가 작업 완료 후 다시 생성한다.
② 수정 가능 조인 뷰(Updatable Join Wew)나 Merge문을 활용한다.
③ Oracle이라면 Update 문을 수행하기 전에 테이블을 nologging 모드로 변경한다.
④ SQL Server라면 최소 로깅(Minimal Logging) 모드 Insert 기능을 활용한다.
정답 : ③
해설 : Oracle에서 nologging 기능은 Insert 문장에서만 효과가 있다.
문제 6
Q. 다음 중 테이블 또는 인덱스를 파티셔닝하는 이유로 가장 부적절한 것은?
① 가용성 향상
② 저장 효율 개선
③ 조회 성능 개선
④ 경합 분산
정답 : ②
해설 : 테이블이나 인덱스를 파티셔닝하면 저장 효율은 오히려 나빠질 수 있다.
※ 파티셔닝이 필요한 이유
- 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경
- 성능적 측면 : 파티션 단위 조회 및 DML 수행, 경합 및 부하 분산
문제 7
Q. 다음 중 파티션에 대한 설명으로 가장 부적절한 것은?
① 파티션은 애플리케이션에 투명하다. 즉, 파티션되지 않은 테이블을 새롭게 파티셔닝하더라도 쿼리는 수정하지 않아도 된다.
② 기존에 사용되던 인덱스를 파티셔닝하면 그 인덱스를 액세스하던 일부 SQL 성능이 오히려 느려질 수 있다.
③ 파티션 칼럼에 대한 검색조건을 (변수가 아닌) 상수 값으로 제공해야 Partition Pruning이 작동한다.
④ 파티션 단위로 인덱스를 재생성할 수 있다.
정답 : ③
해설 : 파티션 칼럼에 대한 검색 조건을 바인드 변수로 제공하더라도 Partition Pruning은 작동한다.
문제 8
Q. 다음 중 아래와 같이 주문 테이블을 생성하고 주문 데이터가 월평균 100만 건이라고 가정할 때, 보기 중 블록 I/O 측면에서 비효율이 없는 SQL을 2개 고르시오. (단, ④번 항목 일자 테이블에 대한 블록 I/O는 소량이므로 무시하기로 함.)
create table ( 고객번호 varchar2(12), 주문일자 varchar2(8), 주문시각 varchar2(6), ..., ... ) partition by range(주문일자) { partition m201101 values less than('20110201'), partition m201102 values less than('20110301'), partition m201103 values less than('20110401'), partition m201104 values less than('20110501'), partition m201105 values less than('20110601'), partition m201106 values less than('20110701'), partition mmaxval values less than(maxvalue) );
①select * from 주문 where 주문일자 between '20110101' and '20110331'
②select * from 주문 where 주문일자 between '20110101' and '20110131' union all select * from 주문 where 주문일자 between '20110201' and '20110228' union all select * from 주문 where 주문일자 between '20110301' and '20110331'
300x250
③select * from 주문 where substr(주문일자, 1, 6) in ('201101', '201102', '201103')
④select /*+ ordered use_nl(b) */ b.* from 일자 a, 주문 b where a.일자 between '20110101' and '20110331' and b.주문일자 = a.일자
정답 : ①, ②
해설 : ③은 파티션 키 칼럼을 가공했으므로 모든 파티션을 Full Scan하게 된다. ④는 주문 테이블이 월 단위로 파티션되어 있는데, 일 단위로 조회 조건을 제공하므로 각 월에 속한 일자 개수만큼 파티션 Full Scan을 반복하게 된다.
문제 9
Q. 거래 테이블이 아래와 같을 때, 다음 중 LOCAL PREFIXED 파티션 인덱스로 가장 적절한 것은?
create table 거래 ( 고객번호 varchar2(l0), 종목코드 varchar2(20), 거래일시 date, ... ) partition by range (거래일시) ( partition p2010 values less than(to_date('20110101', 'yyyymmdd')), partition p2011 values less than(to_date('20120101', 'yyyymmdd')), partition p2012 values less than(to_date('20130101', 'yyyymmdd')), partition p2013 values less than(to_date('20140101', 'yyyymmdd')), partition pmax values less than(maxvalue) );
① create index 거래_N1 on 거래(거래일시) local;
② create index 거래_N2 on 거래(고객번호) locall;
③ create index 거래_N3 on 거래(종목코드) local;
④ create index 거래_N4 on 거래(종목코드, 거래일시) local;
정답 : ①
해설 : Local Prefixed 파티션 인덱스는 인덱스 선두 칼럼이 파티션 키인 경우를 말한다. 거래일시 기준으로 파티셔닝된 테이블에 인덱스를 Local 파티셔닝 한다면 인덱스 파티션 키도 거래일시가 된다.
※ 파티션 인덱스
- Local Prefixed 파티션 인덱스
- Local NonPrefixed 파티션 인덱스
- Global Prefixed 파티션 인덱스
- Global NonPrefixed 파티션 인덱스 (→ Oracle Not Support)
- NonPartitioned(비파티션) 인덱스
문제 10
Q. 다음 중 아래 DCL 스크립트를 보고, 보기 중 거래_IDX1과 거래_IDX2 인덱스 각각에 해당하는 가장 적절한 인덱스 유형을 2개 고르시오.
create table 거래 ( 계좌번호 number, 상품번호 varchar2(6), 거래일자 varchar2(8), 거래량 number, 거래금액 number ) partition by range (거래일자) ( partition p1 values less than('20110101'), partition p2 values less than('20120101'), partition px values less than(maxvalue) ); create index 거래_idx1 on 거래(거래일자, 상품번호) GLOBAL partition by range(거래일자) ( partition p1 values less than('20120101'), partition px values less than(maxvalue) ); create index 거래_idx2 on 거래(계좌번호, 거래일자) LOCAL;
① Global Prefixed
② Global NonPrefixed
③ Local Prefixed
④ Local NonPrefixed
정답 : ①, ④
해설 : Global 파티션 인덱스인 거래_IDX1의 파티션 키는 거래일자이고, 파티션 키가 인덱스 선두 칼럼이므로 Prefixed 파티션이다. 거래_IDX2는 Local 인덱스이므로 파티션 키는 거래일자가 된다. 파티션 키가 인덱스 선두 칼럼이 아니므로 Nonprefixed 파티션이다.
문제 11
Q. 다음 중 배치(Batch) 프로그램 튜닝 방안으로 가장 부적절한 것은?
① 시스템 사용자의 업무시간이 종료되자마자, 동시에 수행 가능한 모든 배치 프로그램을 집중적으로 수행함으로써 총 소요시간을 단축한다. 그래야 문제가 생겼을 때 대처할 수 있는 시간을 확보할 수 있다.
② 개별 프로그램 측면에서도 최초 응답속도보다는 전체 처리속도 최적화를 목표로 설정해야 한다.
③ 파티션과 병렬 처리를 적절히 활용한다.
④ 여러 배치 프로그램에서 공통적으로 사용하는 집합을 정의해 이를 임시 테이블로 생성한다.
정답 : ①
해설 : 같은 시간대에 수많은 프로그램이 집중적으로 수행되면 총 수행 시간이 더 늘어난다. 자원(CPU, Memory, Disk 등)과 Lock(Latch와 같은 내부 Lock까지 포함)에 대한 경합이 발생하면서 프로세스가 실제 일한 시간보다 대기하는 시간이 더 많아지기 때문이다.
※ 배치 프로그램의 특징
- 사용자와 상호 작용 없이
- 대량의 데이터를 처리하는
- 일련의 작업들을 묶어
- 정기적으로 반복 수행하거나
- 정해진 규칙에 따라 자동으로 수행
문제 12
Q. 다음 중 오라클 병렬 프로세싱에 대한 설명으로 가장 부적절한 것은?
① 일련의 처리 과정이 모두 병렬로 진행되도록 해야 병렬 효과를 극대화 할 수 있다.
② 옵티마이저는 병렬 프로세스 간 통신랑올 최소화하려고 노력한다.
③ 병렬 DML 시, Exclusive 모드 TM Lock이 걸려 해당 테이블을 갱신하는 다른 트랜잭션이 모두 블록킹되므로 주의해야 한다.
④ 병렬 Update는 Redo Log를 생성하지 않는다.
정답 : ④
해설 : 테이블을 nologging 모드로 바꾸면 Redo Log가 생성되지 않도록 할 수 있지만, 이 기능은 append 또는 parallel 힌트를 사용해 Direct Load Insert 할 때만 작동한다.
문제 13
Q. 주문 테이블은 주문일자 기준으로 Range 파티셔닝, 고객번호 기준으로 Hash 서브 파티셔닝 되어 있는 상태이다. 고객 테이블은 비파티션(Non-Partitioned) 테이블이다. 등록된 고객 수는 100만 명이고, 월 평균 주문 레코드는 2,000만 건이다. 이 상태에서 두 테이블을 조인하는 병렬 쿼리를 수행했더니 평소보다 3배 이상 오래 걸렸고, 실행 계획을 확인해 보니 아래와 같았다. Oracle에서 이 병렬 쿼리의 속도를 향상시키기 위해 추가해야 할 힌트로 가장 적절한 것은?
select /*+ ordered use hash(o) parallel(c 2) parallel(o 2) */ o.고객번호, sum(o.주문금액), min(c.등급코드) from 고객 c, 주문 o where o.고객번호 = c.고객번호 and o.주문일자 between '20100901' and '20100930' group by o.고객번호---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Pstart | Pstop | TQ | IN-OUT | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ100003 | | | Q1,03 | P->S | | 3 | SORT GROUP BY | | | | Q1,03 | PCWP | | 4 | PX RECEIVE | | | | Q1,03 | PCWP | | 5 | PX SEND HASH | :TQ100002 | | | Q1,02 | P->P | | 6 | SORT GROUP BY | | | | Q1,02 | PCWP | | 7 | HASH JOIN | | | | Q1,02 | PCWP | | 8 | PX RECEIVE | | | | Q1,02 | PCWP | | 9 | PX SEND HASH | :TQ100000 | | | Q1,00 | P->P | | 10 | PX BLOCK ITERATOR | | | | Q1,00 | PCWC | | 11 | TABLE ACCESS FULL | 고객 | | | Q1,00 | PCWP | | 12 | PX RECEIVE | | | | Q1,02 | PCWP | | 13 | PX SEND HASH | :TQ100001 | | | Q1,01 | P->P | | 14 | PX BLOCK ITERATOR | | 1 | 8 | Q1,01 | PCWC | | 15 | TABLE ACCESS FULL | 주문 | 1 | 8 | Q1,01 | PCWP | ----------------------------------------------------------------------------------------------------
① pq_distribute(o none none)
② pq_distribute(o none broadcast)
③ pq_distribute(o partition none)
④ pq_distribute(o hash hash)
정답 : ③
해설 : 주문 테이블이 고객번호 기준으로 Hash 서브 파티셔닝 되어 있으므로 Partial Partition Wise Join을 활용하는 것이 유리하다.
※ pq_distribute 힌트를 사용함으로써 옵티마이저의 선택을 무시하고 사용자가 직접 조인을 위한 데이터 분배 방식을 결정할 수 있다.
- 옵티마이저가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할 때
- 기존 파티션 키를 무시하고 다른 키 값으로 동적 재분할하고 싶을 때
- 통계 정보가 부정확하거나 통계 정보를 제공하기 어려운 상황(→옵티마이저가 잘못된 판단을 하기 쉬운 상황)에서 실행 계획을 고정시키고자 할 때
- 기타 여러 가지 이유로 데이터 분배 방식을 변경하고자 할 때
'Certificate > SQLD' 카테고리의 다른 글
| [SQLD] SQL개발자 단원별 내용 정리 & 실전 문제 (0) | 2022.08.28 |
|---|---|
| [SQLD] 실전 문제 : 인덱스와 조인 (0) | 2022.07.06 |
| [SQLD] 실전 문제 : 옵티마이저 원리 (0) | 2022.07.05 |
| [SQLD] 실전 문제 : Lock과 트랜잭션 동시성 제어 (0) | 2022.07.05 |
| [SQLD] 실전 문제 : 아키텍처 기반 튜닝 원리 (0) | 2022.07.02 |
| [SQLD] 실전 문제 : SQL 최적화 기본 원리 (0) | 2022.07.02 |
| [SQLD] 실전 문제 : SQL 활용 ② (0) | 2022.07.02 |
| [SQLD] 실전 문제 : SQL 활용 ① (0) | 2022.06.30 |
