[SQLP필기풀이]6장 고급SQL튜닝(2)DML 튜닝(미완성)

Yu River·2022년 6월 30일

SQLP SQLP필기풀이

SQLP필기연습

목록 보기

19/35

✍️ 23번 : DML 성능에 영향을 미치는 요소

DML 성능에 영향을 미치는 요소

Lock
인덱스와 무결성 제약
Redo 및 Undo 로깅
조건절
서브쿼리
Redo 및 Undo 로깅
커밋

DML 성능에 영향을 미치지 않는 요소

DML 시에는 옵티마이저 모드가 무조건 'ALL ROWS'이다.

🍋 기출 포인트

옵티마이저 모드는 DML 성능에 미치는 요소와 거리가 멀다.

✍️ 24번 : 자바 소스 (어플리케이션) 내 쿼리 수행 성능

자바 소스 (어플리케이션) 내 쿼리 커밋 수행시 내부 동작 과정

자바 내 커밋
네트워크 경유 DB CALL 발생
서버 프로세스가 Undo와 Redo 버퍼에 로깅 후 LGWR에게 신호를 보냄
- "Redo 파일에 로깅해줘!!" 😝
LGWR은 작업을 완료한 후 서버 프로세스에게 작업 완료 신호를 보냄

🍋 기출 포인트

버퍼 블록들이 바로 데이터 파일에 기록되는 건 아니고 나중에 일괄적으로 DBWR에 의해 기록된다.

✍️ 25번 : 온라인 트랜잭션이 아닌 야간에 배치 UPDATE 성능 개선

온라인 트랜잭션이 아닌 야간에 배치 UPDATE 튜닝 기법

update 되는 컬럼을 포함한 인덱스 Unusable 작업 완료 후 재생성
PK , FK 제약 해제 후 작업 완료 후 재설정
병렬 처리 활용

🍋 기출 포인트

NO LOGGIING 옵션은 INSERT문에서만 적용이 가능하다.

✍️ 27,28번 : Direct Path Insert

[2] Insert 튜닝

Direct Path Insert가 빠른 이유

undo 로그와 Redo 로그를 생성하지 않는다.
Freelist를 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력한다.
Insert 할 블록을 버퍼캐시에서 탐색하지 않고 데이터 파일에 직접 기록한다.

Direct Path Insert가 작동하는 경우

Insert ~ Select 문에 append 힌트 사용
병렬 DML 활성화 후 INSERT 문에 parallel 힌트 사용
CTAS로 테이블 생성

🍋 기출 포인트

테이블을 NOLOGGING 모드로 전환했다고 해서 Direct Path Insert가 작동하는 건 아니다.

✍️ 27,28번 : Insert ~ Select 문 성능 개선

Direct Path Insert가 빠른 이유

NOLOGGING 모드는 힌트가 아니라 따로 모드를 지정해주는 명령어가 필요하다.
```
 ALTER TABLE AA NOLOGGING;
```

append 힌트와 NOLOGGING 모드는 보통 붙어다닌다.

 ALTER TABLE AA NOLOGGING;
 
  INSERT /*+ append */ INTO AA
  SELECT * FROM BB;

parallel 힌트와 병렬 DML 활성화는 붙어다녀야 한다.

 ALTER SESSION ENABLE PARALLEL DML;
 
  INSERT /*+ parallel(AA, 4) */ INTO AA
  SELECT * FROM BB;

Direct Path Insert가 작동하는 경우

Insert ~ Select 문에 append 힌트 사용
병렬 DML 활성화 후 INSERT 문에 parallel 힌트 사용
CTAS로 테이블 생성

🍋 기출 포인트

병렬 DML 활성화를 먼저 하지 않으면 INSERT 문에 parallel 힌트는 무용지물이다.

✍️ 29번 : INSERT 튜닝

아래 INSERT 성능을 개선하는 데 가장 효과가 큰 것을 고르시오.

(DB 로그는 Archive 모드 항목에 기술한 명령문 외에 다른 옵션이나 설정은 전혀 활성화하지 않은 상태임)

INSERT INTO TARGET_T
SELECT * FROM SOURCE_T;
COMMIT;

INSERT /*+ NOLOGGING */ INTO TARGET_T
SELECT * FROM SOURCE_T;

NOLOGGING 힌트는 없다.

INSERT /*+ APPEND */ INTO TARGET_T NOLOGGING
SELECT * FROM SOURCE_T;

TARGET_T 뒤쪽에 지정한 NOLOGGING은 테이블 별칭 (Alias)일 뿐 NOLOGGING 기능과는 무관하다.
Redo 로깅은 생략하지 못하지만, APPEND 힌트를 통해 Direct Path Insert 기능은
작동하므로 성능 개선에 도움이 된다.

⭕️

ALTER TABLE TARGET_T NOLOGGING;
INSERT /*+ APPEND */ INTO TARGET_T
SELECT * FROM SOURCE_T;

테이블을 NOLOGGING 모드로 전환했으므로 Redo 로깅을 생략하면서 Direct Path Insert 기능까지 작동하므로 성능 개선에 큰 도움이 된다.

ALTER TABLE TARGET_T NOLOGGING;
INSERT /*+ PARALLEL(T, 4) */ INTO TARGET_T T
SELECT * FROM SOURCE_T;

병렬로 INSERT 할 때는 APPEND 힌트를 지정하지 않아도 자동으로 Direct Path Insert 기능이 작동한다.
병렬로 INSERT 할 때는 사전에 'ALTER SESSION ENABLE PARALLEL DML' 명령을 통해 병렬 DML을 활성화해야 한다.안 하면 Direct Path Insert가 작동하지 않는다.
만약 병렬 DML을 활성화한 후에 INSERT 했다면 3번보다 성능이 더 좋을 것이다.

✅ INSERT 튜닝 순서
(1) NOLOGGING 모드 :: 로그를 안 남기고 INSERT
ALTER TABLE AA NOLOGGING;
nologging 모드는 Redo 로그를 생성하지 않게 하는 옵션이므로 Direct Path Insert와 직접적인 관련은 없다.

다만, 이 기능은 Direct Path Insert 할 때만 테이블을 nolopping 모드로 전환한 상태에서 작동할 뿐이다.
(2) Direct Path Insert :: HWM 바깥 영역에 데이터를 순차적으로 입력(버퍼캐시 탐색X)
INSERT /*+ append */ INTO AA
  SELECT * FROM BB;
로깅을 할 수도 안 할 수도 있다.

이 기능의 핵심은 버퍼캐시 탐색(LRU 알고리즘)을 안 하고 HWM 바깥 영역에 데이터를 순차적으로 입력한다는 데에 있다.
(3) 인덱스 , 제약 조건 비활성화
(4) 병렬 INSERT
 ALTER SESSION ENABLE PARALLEL DML;
  INSERT /*+ parallel(AA, 4) */ INTO AA
  SELECT * FROM BB;
병렬로 INSERT 할 때는 APPEND 힌트를 지정하지 않아도 자동으로 Direct Path Insert 기능이 작동한다.

✍️ 30번 : INSERT 튜닝

온라인 트랜잭션이 없는 야간에 대량 데이터를 일괄 INSERT 하는 아래 배치(Batch) 프로그램 성능을 개선하기 위한 방안으로 가장 부적절한 것을 고르시오.

INSERT INTO TARGET_T
SELECT * FROM SOURCE_T;

COMMIT;

테이블을 nologging 모드로 전환한 상태에서 INSERT 문에 append 힌트를 사용한다. 👉 ⭕️
병렬 DML을 활성화한 상태에서 INSERT 문에 parallel 힌트를 사용한다. 👉 ⭕️
인덱스 및 제약를 해제한 상태에서 INSERT를 수행한다. 👉 ⭕️
Array Processing을 활용해 10만 건 단위로 INSERT 하도록 프로그램을 수정한다. 👉 ❌

🍋 기출 포인트

Array Processing 기능은 성능을 개선하는 데 큰 도움을 주지만, INSERT INTO SELECT 문으로 INSERT 할 때보다는 느리다.

✍️ 31번 : 대량 데이터 INSERT 튜닝

온라인 트랜잭션이 없는 야간에 대량 데이터를 일괄 INSERT 하는 아래 배치(Batch) 프로그램 성능을 개선하시오.

(단, TARGET_T 테이블에 PK 인덱스(TARGET_T_PK)만 존재하며, 다른 인덱스는 없는 상태임)

DELETE FROM TARGET_T;
COMMIT;

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ APPEND */ INTO TARGET_T T1
SELECT /*+ FULL(T2) PARALLEL(T2 4) */ *
FROM SOURCE_T T2;

ALTER SESSION DISABLE PARALLEL DML;

[내 풀이 정리]

전체 데이터 삭제를 DELETE 후 COMMIT 으로 처리하였다.
- truncate로 로그 안 남기고 성능 개선 가능
병렬 모드로 바꿨다.=> DIRECT PATH INSERT 자동 작동
- NO LOGGING 모드로 걸어주면 더 효과적임
데이터 조회를 프로세스 4개로 나눠 동시에 가져오지만 INSERT는 병렬 처리를 안해주었다.
- INSERT 처리도 4개의 프로세스로 병렬 INSERT 실시한다.

[답안]

TRUNCATE TABLE TARGET_T;

ALTER SESSION ENABLE PARALLEL DML;
ALTER TABLE TARGET_T NOLOGGING;
ALTER TABLE T1 MODIFY CONSTRAINT T1_PK DISABLE DROP INDEX;

INSERT /*+ PARALLEL(T1 4)*/ INTO TARGET_T T1
SELECT /*+ FULL(T2) PARALLEL(T2 4) */ *
FROM SOURCE_T T2;

COMMIT;

ALTER TABLE T1 MODIFY CONSTARINT T1_PK ENABLE NOVALIDATE;
ALTER TABLE TARGET_T LOGGING;
ALTER SESSION DISABLE PARALLEL DML;

🍋 기출 포인트

첫번째 , DELETE 문을 사용하면 Redo 로그와 Undo를 생성하면서 레코드 단위로 삭제하므로 데이터가 많을 때 상당히 오랜 시간이 소요된다. 반면, TRUNCATE 문은 딕셔너리 상에서 익스텐트만 반환하는 방식을 사용하므로 대량 데이터도 상당히 빠르게 삭제할 수 있다.
두번째 , DML 중에 제약 조건을 체크하고 인덱스를 관리하는 부하가 매우 크므로 작업 전에 제약을 해제하고 인덱스를 Unusable 상태로 변경하면 성능을 높이는 데 큰 도움이 된다.

🍒 대량 데이터 INSERT 튜닝 관련 꼭 알아두어야 할 것

✅ NO_LOGGING 모드 설정법

해도 되고 안 해도 되지만 DIRECT PATH INSERT 시에만 작동한다.
ALTER TABLE T1 NOLOGGING;
ALTER TABLE T1 LOGGING;
✅ 제약을 해제하고 인덱스를 Unusable 상태로 변경법
ALTER TABLE T1 MODIFY CONSTRAINT T1_PK DISABLE DROP INDEX;
ALTER TABLE T1 MODIFY CONSTARINT T1_PK ENABLE NOVALIDATE;
✅ 인덱스 UNABLE 설정법
ALTER TABLE T1 NOLOGGING;
ALTER TABLE T1 LOGGING;
✅ 병렬 모드 설정법
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL DML;

✍️ 32번 : 대량 데이터 INSERT 튜닝

새벽 시간에는 MYTAB 테이블에 온라인 트랜잭션이 거의 발생하지 않는다. 새벽에 원격 RDS 시스템으로부터 1,000만 건 정도의 데이터를 읽어서 INSERT 하는 아래 배치(Batch) 프로그램 성능을 개선하시오.

• UPDATE문에서 C1 조건절을 만족하는 데이터는 90% 이상이다.
• MYTAB 테이블의 PK는 DT + ID이다.
• 병렬 처리는 활용할 수 없다.

[sql]

CREATE TABLE MYTAB_TEMP
AS
SELECT CO AS ID, C1, C2, C3, C4
FROM YOURTAB@RDS
WHERE 1 = 2;

ALTER TABLE MYTAB_TEMP ADD CONSTRAINT MYTAB_TEMP_PK PRIMARY KEY ( ID );

DECLARE 
	V_CNT NUMBER;

BEGIN
    INSERT INTO MYTAB_TEMP
    SELECT C0, C1, C2, C3, 4
    FROM YOURTAB@RDS
    WHERE CO IS NOT NULL
    AND C5 > 0;
    
    UPDATE MYTAB_TEMP SET C4 = C4 + 1 WHERE C1 < TRUNC(SYSDATE);
    
    -배치 프로그램을 재실행할 경우를 대비하기 위한 DELETE (보통 0건 삭제)
	DELETE FROM WYTAB WHERE DT = TO_CHAR(SYSDATE, 'YYYYMMDD');
    
    INSERT INTO MYTAB (DT, ID, C1, C2, C3, C4)
	SELECT TO_CHAR(SYSDATE, 'YYYYMMDD'), A.* FRON MYTAB_TEMP A;
    
    V_CNT := SQL&ROWCOUNT;
    INSERT_LOG ( SYSDATE, 'INSERT MYTAB_TEMP', 'SUCCESS', V_CONT || 'ROWS' );
    
    COMMIT;
    
	EXCEPTION
    	WHEN dup_val_on_index THEN
			INSERT_LOG ( SYSDATE, "INSERT WYTAB_TEMP", "FAIL', '중복 데이터' );
END;


DROP TABLE MYTAB_TEMP;

[풀이 정리]

MYTAB_TEMP 테이블을 YOURTAB@RDS 스키마로 본떠 만듬
- 애초에 본떠서 만들때 아래 조건들을 다 적용하여 조회한 데이터를 넣어 만든다.
MYTAB_TEMP 테이블에 제약 조건을 냅다 설정함..
- 제약 조건을 설정하지 않고 DML을 다 마치면 후에 다시 검사한다.
MYTAB_TEMP에 대량 데이터 INSERT
- NO_LOGGING 모드와 함께 DIRECT_PATH_INSERT를 사용한다.
- CTAS를 쓰면 알아서 DIRECT_PATH_INSERT가 적용되므로 NOLOGGING만 추가로 설정해준다.
  - ```
  CREATE TABLE T1 NOLOGGING
  AS SELECT ~
```

MYTAB_TEMP의 C4를 일괄 업데이트

조건이 적용된 C4를 애초에 INSERT할 때 넣는다.

 SELECT C0, C1, C2, C3, (CASE WHEN C1 < TRUNC(SYSDATE) THEN C4 +1 ELSE C4 END ) C4 
		FROM YOURTAB@RDS
		WHERE CO IS NOT NULL
		AND C5 > 0;

MYTAB 테이블은 보통 0건 삭제되므로 따로 손 볼 필요는 없다.
C0의 컬럼이 중복되는 게 있는지 체크하고 있으면 로그에 넣는다.(롤백은 안 함)

[답안]

CREATE TABLE MYTAB_TEMP NOLOGGING
AS
SELECT C0 AS ID, C1, C2, C3, (CASE WHEN C1 < TRUNC(SYSDATE) THEN C4 +1 ELSE C4 END ) C4 
FROM YOURTAB@RDS
WHERE CO IS NOT NULL
AND C5 > 0;

DECLARE 
	V_CNT NUMBER;

BEGIN
	
    SELECT COUNT(*) INTO V_CNT FROM (
      SELECT ID
      FROM MYTAB_TEMP
      GROUP BY ID
      HAVING COUNT(*) > 1
     );
     
     IF V_CNT > 0 THEN
     	INSERT_LOG ( SYSDATE, "INSERT WYTAB_TEMP", 'FAIL', '중복 데이터' );
    ELSE 
    	-배치 프로그램을 재실행할 경우를 대비하기 위한 DELETE (보통 0건 삭제)
		DELETE FROM MYTAB WHERE DT = TO_CHAR(SYSDATE, 'YYYYMMDD');
    
        INSERT INTO MYTAB (DT, ID, C1, C2, C3, C4)
        SELECT TO_CHAR(SYSDATE, 'YYYYMMDD'), A.* FRON MYTAB_TEMP A;
    
        V_CNT := SQL&ROWCOUNT;
        INSERT_LOG ( SYSDATE, 'INSERT MYTAB_TEMP', 'SUCCESS', V_CONT || 'ROWS' );
     ENDIF;
    
    	COMMIT;=
END;

DROP TABLE MYTAB_TEMP;

🍋 기출 포인트

대량 테이블에서 90% 이상 데이터를 Redo와 Undo를 생성하면서 건건이 UPDATE 하려면 상당히 오랜 시간이 소요된다.
UPDATE를 따로 할 필요 없이 MYTAB_TEMP 테이블을 생성할 때 아예 변경된 값을 입력하면 된다.
CTAS 문으로 데이터를 입력하므로 Direct Path Insert 기능이 작동하며, NOLOGGING 옵션을 지정하였으므로 Redo 로그를 생성하지 않는다.
UPDATE를 따로 하지 않으므로 Undo 생성량도 줄일 수 있다.
최종적으로 MYTAB 테이블에 데이터를 입력할 때는 온라인 트랜잭션이 발생할 수 있으므로 Direct Path Insert 기능을 활용할 수 없다.(CTAS는 상관없다.)
MYTAB_TENP 테이블에 PK 제약을 생성한 상태에서 대량 데이터를 INSERT 하면 성능이 느리다.
ID에 중복 값이 존재하는지 확인한 후에 WYTAB 테이블에 입력하도록 프로그램을 수정한다면 굳이 PK 제약을 생성하지 않아도 된다.

✍️ 21번 : SQL Server 클러스터형 인덱스

ss

SQL Server 클러스터형 인덱스

**** 👉 ⭕️
**** 👉 ⭕️
**** 👉 ❌
**** 👉 ❌

🍋 기출 포인트

🍒 문제 해설

Yu River

도광양회(韜光養晦) ‘빛을 감추고 어둠속에서 힘을 기른다’

이전 포스트

[SQLP필기풀이]6장 고급SQL튜닝(2)DML 튜닝(미완성)

SQLP필기연습

✍️ 23번 : DML 성능에 영향을 미치는 요소

DML 성능에 영향을 미치는 요소

DML 성능에 영향을 미치지 않는 요소

🍋 기출 포인트

✍️ 24번 : 자바 소스 (어플리케이션) 내 쿼리 수행 성능

자바 소스 (어플리케이션) 내 쿼리 커밋 수행시 내부 동작 과정

🍋 기출 포인트

✍️ 25번 : 온라인 트랜잭션이 아닌 야간에 배치 UPDATE 성능 개선

온라인 트랜잭션이 아닌 야간에 배치 UPDATE 튜닝 기법

🍋 기출 포인트

✍️ 27,28번 : Direct Path Insert

Direct Path Insert가 빠른 이유

Direct Path Insert가 작동하는 경우

🍋 기출 포인트

✍️ 27,28번 : Insert ~ Select 문 성능 개선

Direct Path Insert가 빠른 이유

Direct Path Insert가 작동하는 경우

🍋 기출 포인트

✍️ 29번 : INSERT 튜닝

아래 INSERT 성능을 개선하는 데 가장 효과가 큰 것을 고르시오.

✅ INSERT 튜닝 순서

✍️ 30번 : INSERT 튜닝

온라인 트랜잭션이 없는 야간에 대량 데이터를 일괄 INSERT 하는 아래 배치(Batch) 프로그램 성능을 개선하기 위한 방안으로 가장 부적절한 것을 고르시오.

🍋 기출 포인트

✍️ 31번 : 대량 데이터 INSERT 튜닝

온라인 트랜잭션이 없는 야간에 대량 데이터를 일괄 INSERT 하는 아래 배치(Batch) 프로그램 성능을 개선하시오.

[내 풀이 정리]

[답안]

🍋 기출 포인트

🍒 대량 데이터 INSERT 튜닝 관련 꼭 알아두어야 할 것

✅ NO_LOGGING 모드 설정법

✅ 제약을 해제하고 인덱스를 Unusable 상태로 변경법

✅ 인덱스 UNABLE 설정법

✅ 병렬 모드 설정법

✍️ 32번 : 대량 데이터 INSERT 튜닝

새벽 시간에는 MYTAB 테이블에 온라인 트랜잭션이 거의 발생하지 않는다. 새벽에 원격 RDS 시스템으로부터 1,000만 건 정도의 데이터를 읽어서 INSERT 하는 아래 배치(Batch) 프로그램 성능을 개선하시오.

[sql]

[풀이 정리]

[답안]

🍋 기출 포인트

✍️ 21번 : SQL Server 클러스터형 인덱스

SQL Server 클러스터형 인덱스

🍋 기출 포인트

🍒 문제 해설

[SQLP필기문제]6장 고급SQL튜닝(2)DML 튜닝

0개의 댓글