[SQLP필기풀이]6장 고급SQL튜닝(3)데이터베이스 Call 최소화

✍️ 35,36번 : 데이터베이스 CALL

데이터베이스 CALL

1. DML문은 대부분 I/O가 Execute Call 단계에서 발생
2. SELECT문은 대부분 I/O가 Fetch Call 단계에서 발생
3. Parse Call단계에서 Recursive Call이 발생할 수 있다.
   • 하드 파싱 중 딕셔너리 조회 단계
4. Execute Call단계에서 Recursive Call이 발생할 수 있다.
   • DB 저장형 프로시저 수행시 그 안에 내장된 SQL을 실행할 때
5. Fetch Call단계에서 Recursive Call이 발생할 수 있다.
   • Select 문에 사용한 DB 저장형 함수에 내장된 SQL을 실행할 때
6. User Call은 네트워크를 경유하기 때문에 Recursive Call보다 성능 부하가 훨 크다.

🍋 기출 포인트

1. ⭐️ 애플리케이션 커서를 캐싱하지 않는 한 Parse Call은 바인드 변수를 사용해도 매번 일어난다.

✍️ 37번 : 데이터베이스 CALL 실행계획

데이터베이스 CALL 실행계획

1. ⭐️ 애플리케이션 커서를 캐싱하면 parse Call은 1번만 일어난다.
2. Fetch Call Count가 있으면 Select문이다.
3. ( rows / Fetch Call ) 은 Array size 이다.
4. (query / Execute Call) 은 한 번 "실행"할 때 읽는 평균 블록수이다.

✍️ 38,42번 : 데이터베이스 CALL을 줄이는 방안

데이터베이스 CALL을 줄이는 방안

1. Array Size를 늘린다.
2. Array Processing을 활용한다.
3. ( rows / Fetch Call ) 은 Array size 이다.
4. 전체 결과집합을 한 번에 출력하지 않고 페이징 기법을 활용한다.

🍋 기출 포인트

1. ⭐️ 애플리케이션 커서를 캐싱하지 않는 한 Parse Call은 바인드 변수를 사용해도 매번 일어난다.
2. 바인드 변수를 사용한다고 해서 parse Call이 줄어드는건 아니다.
3. 네트워크 패킷 크기만을 늘린다고 해서 데이터베이스 CALL이 줄어드는 건 아니다.

✍️ 41번 : Array Processing

Array Processing

1. PL/SQL 프로그램보다 Java 프로그램에서 훨씬 유용하다.(개선율이 더 높다.)
2. 연속된 일련의 과정을 모두 Array 단위로 처리해야 효과적이다.
   • 3개의 단위 작업 중 1번째 , 3번째에만 Array Processing을 적용하면 2번째에서 병목 구간이 생긴다.
3. 이를 사용하는 핵심 원리는 DB Call을 줄이기 위함이다.

🍋 기출 포인트

1. Array 단위를 늘릴수록 성능은 좋아지나 선형적으로 좋아지지는 않는다.개선율은 점차 감소한다.일정 수준을 넘어서면 개선 효과는 없고 자원만 많이 사용된다.

✍️ 43번 : 부분 범위 처리

부분 범위 처리

1. 서버 프로세스가 결과 집합을 한번에 전송하지 않고 클라이언트로부터 Fetch Call이 올때마다 나눠서 전송하는 것이다.
   • 나눠서 전송하는 과정에 중간에 기다릴 땐 CPU를 OS에 반환한 상태이다.
2. Array Size를 작게 설정할수록 부분범위 처리에 의한 응답속도는 빨라진다.
3. 부분범위 처리를 활용할 땐 읽어야할 데이터 범위에서 조건절을 만족하는 데이터가 많을수록 운반 단위를 빨리 채울 수 있어 빠른 응답 속도를 보인다.

🍋 기출 포인트

1. 부분범위 처리를 활용할 땐 읽어야할 데이터 범위에서 조건절을 만족하는 데이터가 많을수록 운반 단위를 빨리 채울 수 있어 빠른 응답 속도를 보인다.

✍️ 44번 : DB I/O와 운반단위

DB I/O와 네트워크

1. Multiblock I/O 단위를 줄일수록 Full Table scan시 I/O Call 횟수가 늘어난다.
2. Multiblock I/O는 한 익스텐트 안에서 이루어진다.때문에 한 익스텐트에 속한 마지막 블록을 읽었는데
   아직 Multiblock I/O 단위를 채우지 못했어도 다음 익스텐트를 추가로 읽지는 않는다.
3. 테이블 익스텐트를 작게 설정하면 Full Table Scan 할 때 I/O Call 횟수가 늘어날 수 있다.
4. SDU, TDU 등 네트워크 패킷을 작게 설정할수록 서버와 클라이언트 간 통신 횟수가 늘어난다.
5. 한 번의 Fetch Call에서 마지막 블록을 읽다가 운반 단위가 모두 차면 바로 데이터를 전송
   한다.그리고 남은 데이터는 다음 Fetch Call에서 읽어서 전송한다.
6. Array Size를 작게 설정할수록 한 블록을 여러 번에 걸쳐서 읽게 된다.반대로, 크게 설정하면 한 블록을 여러 번에 걸쳐 읽는다.

🍋 기출 포인트

1. Full Table Scan시 I/O Call 횟수는 Multiblock I/O를 작게 설정할수록 늘고, 크게
   설정할수록 줄어든다.
2. 테이블 익스텐트를 작게 설정하면 Full Table Scan 할 때 I/O Call 횟수가 늘어날 수 있다.
3. Array Size를 작게 설정할수록 한 블록을 여러 번에 걸쳐서 읽게 된다.반대로, 크게 설정하면 한 블록을 여러 번에 걸쳐 읽는다.

✍️ 45번 : 값이 변하지 않는 이름 속성 조회 쿼리 성능 개선

코드명, 상품명, 고객명처럼 값이 변하지 않는 이름 속성 조회 쿼리 성능 개선

1. ID 속성(코드, 상품번호, 고객/ID 등)은 DB에서 읽고 이름 속성은 애플리케이션 서버 레이어에 미리 캐싱해 둔 데이터(또는 메모리 DB)를 조회하여 조인 부하를 줄인다.
2. 인덱스 및 SQL 튜닝만으로 만족스러운 성능을 내기 어려울 때, 반정규화해서 테이블에 미리 저장해 둔다.
3. 페이징 처리를 활용한다면, 화면에 출력하는 최종 데이터에 대해서만 조인하도록 구현한다.

🍋 기출 포인트

1. 코드명, 상품명, 고객명처럼 값이 변하지 않는 이름 속성을 출력할 때 조인 말고 DB 저장형 함수를 사용한다면 DB Call 부하로 인해 성능은 더 나빠진다.

✍️ 46번 : DB 저장형 사용자 정의 함수/프로시저의 특징

DB 저장형 사용자 정의 함수/프로시저의 특징

1. 가상머신(VM) 상에서 실행되는 Interpreting 언어이다.
2. 호출 시마다 SQL 실행엔진과 PL/SQL 가상머신 사이에 Context Switching이 발생한다.
3. 함수/프로시저에 내장된 SQL이 참조하는 테이블에 구조 변경, 인덱스 변경, 통계정보 재수집 등이 일어나면, 이후 최초 실행 시점에 재컴파일이 일어난다.

🍒 문제 해설

1. 함수에 SQL이 내장돼 있다면, 함수 실행 횟수만큼 Recursive Call이 발생한다.
2. SELECT-LIST에 있다면, 결과집합 건수만큼 함수가 실행된다.
3. 함수가 조건절에 있다면, 처리되는 건수만큼 함수가 실행된다.

🍋 기출 포인트

1. 함수에 SQL이 내장돼 있다면, 메인 쿼리에 Execute Call이 발생할 때마다 Recursive Call도
   한 번씩 발생하는 게 아니라 위치한 곳에 따라 발생 횟수가 달라진다.
   • Select 문 안에 있는 경우 : 결과 건 수
   • where 절 안에 있는 경우 : 실행 건 수

✍️ 47번 : DB 저장형 사용자 정의 함수/프로시저의 Recursive Call 발생 횟수

DB 저장형 사용자 정의 함수/프로시저에서의 DB Call 횟수

1. Call 횟수는 SQL 트레이스의 Call Statistics를 통해 확인할 수 있다.
2. Call 횟수는 VSSQL에서 SQL_ID를 확인한 후 쿼리를 통해서도 확인할 수 있다.

   SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS, FETCHES
   FROM VSSQL
   WHERE SQL_ID = :SQL_ID;

🍒 문제 해설

1. 메인 쿼리 결과집합이 100건이므로 Execute Call과 Fetch Call은 각각 100회 발생한다.
2. SQL 프로그램에 내장된 SQL에 대해서는 오라클이 자동으로 커서를 캐싱하므로 Parse Call은 최초 1회만 발생한다. 따라서 총 Call 횟수는 201이다.

🍋 기출 포인트

1. PL/SQL에 내장된 SQL에 대해서는 오라클이 자동으로 커서를 캐싱한다.
   • Parse Call 발생 횟수 : 1회

✍️ 48번 : DB 저장형 함수에 의한 성능 저하를 해소하는 방안

DB 저장형 함수에 의한 성능 저하를 해소하는 방안

1. CASE 문이나 조인문으로 전환 👉 ⭕️
2. Deterministic 함수로 전환 👉 ⭕️
3. Result 캐시 기능을 활용 👉 ⭕️

DB 저장형 함수에 의한 성능 저하를 해소하는 방안 오답

1. DB 저장형 함수를 제거하고 C, JAVA, VB 등 애플리케이션 함수로 전환 👉 ❌

🍒 문제 해설

1. DB 저장형 함수를 C, JAVA, VB 등 애플리케이션 함수로 전환하면, Recursive Call 대신 User Call이 발생하므로 성능이 더 느려진다.
2. CASE 문이나 조인문을 활용함으로써 DB 저장형 함수를 제거한다면, "함수 호출 및
   Recursive Call이 발생하지 않으므로"성능 개선에 큰 도움이 된다.
3. Deterministic 함수로 전환하면 캐싱 효과가 나타난다.
4. 함수에서 참조하는 테이블 데이터가 자주 변하지 않는다면, Result 캐시 기능도 도움이
   된다.

✍️ 49번 : 결과집합이 평균 10만 건인 DB 저장형 함수 쿼리에 대한 튜닝 방안

SELECT 글번호, 등록일시, 작성자ID, GET_WRITER(작성자ID) 작성자명 , 
CODE_NM('418', 게시글분류코드) 게시글분류명
FROM 게시판 B
WHERE 게시판코드 = :BBS_CD
AND 등록일시 BETWEEN TRUNC(SYSDATE-2) AND SYSDATE
ORDER BY 등록일시 DESC

결과집합이 평균 10만 건인 DB 저장형 함수 쿼리에 대한 튜닝 방안

1. 페이징 처리를 활용한다. 👉 ⭕️
2. CODE_NM 함수를 스칼라 서브쿼리에 씌워서 실행한다. 👉 ⭕️

   (SELECT CODE_NM('418', 게시글분류코드) FROM DUAL) 게시글분류명

3. 작성자명과 게시글분류명 조회용 함수를 조인문으로 변경하고, NL 조인으로 유도한다. 👉 ⭕️

결과집합이 평균 10만 건인 DB 저장형 함수 쿼리에 대한 튜닝 방안 오답

1. 부분범위 처리를 활용하기 위해 ORDER BY를 제거하고, INDEX_DESC(B (게시판코드, 등록일시)) 힌트를 추가한다. 👉 ❌

🍒 문제 해설

1. 부분범위 처리를 활용하면 함수 호출 부하를 최소화할 수 있다. 부분범위 처리를 활용하
   려면 소트 연산을 생략할 수 있어야 하는데, 이를 위해 SQL에서 ORDER BY를 제거할 필요
   는 없다. 게시판_X1 인덱스를 사용하면 소트 연산이 자동으로 생략되기 때문이다.
2. 페이징 처리 활용도 함수 호출 부하를 최소화하는 데 큰 도움이 된다.
3. 함수에 스칼라 서브쿼리를 씌워서 실행하면 함수 호출 부하를 줄이는 데 큰 도움이 된
   다. 스칼라 서브쿼리에 캐싱 기능이 작동하기 때문이다. 단, 함수에 입력하는 값 종류가
   많지 않아야 한다.

🍋 기출 포인트

1. 함수를 풀어서 조인문으로 변경하는 것이 함수 호출 부하를 줄이는 근본적인 해법이다.
2. 부분범위 처리가 가장 잘 작동하기 때문에 NL 조인이 가장 효과적이다.
3. 해시 조인은 소트 연산을 생략할 수 없어 부분범위 처리에 효과적이지 않다.
4. 부분범위 처리를 활용하기 위해 SQL에서 ORDER BY를 제거할 필요는 없다.
   게시판_X1 인덱스를 사용하면 소트 연산이 자동으로 생략되기 때문이다.
   

✍️ 50번 : DB 저장형 함수에 내장된 SQL에 대한 Recursive Call 횟수를 줄이는 방안 효과가 큰 순

메인 쿼리 결과집합이 매우 클 때 DB 저장형 함수에 내장된 SQL에 대한 Recursive Call 횟수를 줄이는 방안 효과가 큰 순

1. 스칼라 서브쿼리를 이용한 함수 결과 캐싱
2. Deterministic 함수 캐싱
3. Result 캐시
4. Native 컴파일

🍒 문제 해설

1. 스칼라 서브쿼리에 대한 캐싱은 SQL 단위이다.
2. Deterministic 함수에 대한 캐싱은 Fetch Call 단위이다.따라서 Fetch Call이 많으면 같은 입력 값에 대한 Recursive Call 횟수도 많아진다.
3. 스칼라 서브쿼리 및 Deterministic 함수 결과는 PGA에 캐싱된다.
4. Result 캐시는 SGA 영역에 캐싱된다. 따라서 데이터를 읽고 쓸 때마다 래치를 획득해야 하는 부담이 있다.
5. Native 컴파일 기능을 활용하면 코드를 인터프리팅 하는 부하를 줄여준다.
6. Native 컴파일 기능을 활용해도 Context Switching과 Recursive Call에 대한 부하는 줄여주지 못한다.

🍋 기출 포인트

1. 스칼라 서브쿼리(SQL단위,PGA) > Deterministic 함수에 대한 캐싱(Fetch
   Call 단위,PGA) > Result 캐시(SGA) > Native 컴파일 순으로 효과적이다.

✍️ 51번 : 쿼리를 내장한 DB 저장형 함수의 읽기 일관성(Read Consistency) 이슈

쿼리를 내장한 DB 저장형 함수의 읽기 일관성(Read Consistency) 이슈

1. 코드명, 상품명 등을 조회할 때 함수를 사용하지 않고 스칼라 서브쿼리로 조인하면 일관성이
   보장된다. 👉 ⭕️

   select empno, ename, sal, hiredate
        , (select d.dname              -> 출력값 : d.dname
             from dept d
        where d.deptno = e.empno   -> 입력값 : e.empno
          )
     from emp e
    where sal >= 2000;

쿼리를 내장한 DB 저장형 함수의 읽기 일관성(Read Consistency) 이슈 오답

1. 함수 결과의 일관성은 기본적으로 보장되지 않지만, Deterministic으로 선언하면 보장된다. 👉 ❌

2. 함수를 스칼라 서브쿼리에 씌워서 실행하면 캐싱된 데이터를 반환하므로 일관성이 보장된다. 👉 ❌

🍒 문제 해설

1. Deterministic 함수는 Fetch Call 단위로 캐싱하므로 함수 결과의 일관성을 보장하지 않는다.
2. 함수를 스칼라 서브쿼리에 씌워서 실행할 때의 캐싱 효과는 SQL 수행을 마칠 때까지 유효하므로 일관성을 보장할 거 같지만, 그렇지 않다.
3. 함수를 스칼라 서브쿼리에 씌워서 실행할 때 각 입력값에 대한 첫 번째 함수 호출이 일어날 때까지의 시간차 때문에 보장하기 어렵다.
4. 함수를 스칼라 서브쿼리에 씌워서 실행할 때 모든 값을 다 캐싱할 수 없다는 점 때문에 보장하기 어렵다.
5. 스칼라 서브쿼리 "조인"은 일관성을 보장한다. 즉, 중간에 값이 변하더라도 쿼리 시작 시점
   기준으로 일관된 데이터를 출력한다.
   

✍️ 52번 : DB 저장형 함수 호출횟수를 최소화하고자 할 때 가장 효과적인 인덱스 고르기

DB 저장형 함수 호출횟수를 최소화하고자 할 때 가장 효과적인 인덱스 고르기

  create index 회원_XB1 on 회원(생년월일);
  create index 회원_X82 on 회원(생년월일, 전화번호);
  create index 회원_X83 on 회원(생년월일, 회원명, 전화번호);
  create index 회원_XB4 on 회원(전화번호);
  
  select *
  from 회원
  where 생년월일 like '19798'
  and 전화번호 = encryption( :phone );

🍒 문제 해설

1. ⭐️DB 저장형 함수에 대한 조건절이 필터 조건이면 필터링 횟수만큼 수행된다.⭐️
2. ⭐️DB 저장형 함수에 대한 조건절이 인덱스 액세스 조건이면 단 1회 수행된다.⭐️
3. 회원_X01 인덱스를 사용하면 생년월일 LIKE 조건을 만족하는 건수만큼 테이블 액세스 단계에서 필터링하므로 함수를 5만 번 호출한다.
4. 회원_X02 또는 회원_X03 인덱스를 사용하면, 생년월일 LIKE 조건은 인덱스 액세스 조건이지만 전화번호는 "인덱스 필터" 조건이므로 인덱스 스캔 단계에서 함수를 5만 번 호출한다.
5. 회원_X04 인덱스를 사용하면 전화번호가 액세스 조건이므로 함수는 단 1회 호출한다.

🍋 기출 포인트

1. DB 저장형 함수에 대한 조건절이 필터 조건이면 필터링 횟수만큼 수행된다.
2. DB 저장형 함수에 대한 조건절이 인덱스 액세스 조건이면 단 1회 수행된다.
3. 인덱스 컬럼 중 첫 번째 나타나는 범위검색 조건(부등호, BETWEEN, LIKE 등)까지가 인덱스 액세스 조건이고 나머지는 필터 조건이다.