2024년 2월 2일 4교시 오전에 배운 내용 복습

 

ShP을 만든 이유: 실행계획을 공유하기 위해서

실행계획이 없는 상태에서는 절대 SQL 문이 돌아가지 않는다.

 

★ 실행 계획

 

1. 데이터 처리 결정: rowid 스캔을 할지 full table scan 을 할지 결정하는 것

- rowid scan:

       by user rowid / by index rowid

    - 18 자리 rowid 를 유저가 모두 기억하지 못하므로 rowid 를 직접 입력해서 데이터를 찾기보다는

      주로 인덱스를 통해 row id 를 찾아 데이터를 처리한다.

    - 프라이머리 키 제약조건과 유니크 키 제약조건을 걸었을 때만 유니크 인덱스가 생성되고

      그 외에는 non-unique 제약조건을 유저가 직접 생성해야 한다.

- full table scan

    - 데이터 몇 건 찾자고 테이블에 있는 모든 블록을 스캔하는 방식... 너무 비효율적이다.

    - 테이블에 데이터가 적게 들어가 있을 때는 상관이 없지만, 데이터가 많이 들어가 있을 때는 성능이 떨어진다.

 

2. 조인의 방법 결정 (= 서로 다른 테이블에 액세스하는 방법을 결정)

- hash join

- nested loop join

- sort merge join

 

3. 조인의 순서 결정

- from 절에 나열되어 있는 테이블의 개수만큼 순서를 결정해야 한다.

- 조인해야 할 테이블의 개수가 n 개이면

   n! 의 경우의 수가 나온다.

   예) from a, b, c - 조인해야 할 테이블의 개수가 3개일 때:

         조인 순서는 (a, b, c) (a, c, b) (b, a, c) (b, c, a) (c, a, b) (c, b, a) 6가지가 가능하게 되는데

         저 경우의 수를 모두 따져서 순서를 결정한다.

 

저 세 가지를 따져서 실행 계획을 만드는 것도 꽤나 비용이 큰 과정이므로

오라클 입장에서는 한번 만든 실행계획을 최대한 잘 가지고 있으려고 한다.

(그래서 ShP 안에 담아놓고 공유하는 것)

 

★ 동일한 SQL 문장일 경우에만 실행 계획을 sharing 한다.

 

그럼 동일한 SQL 문장이라는 것의 기준은?

 

1. 대소문자가 무조건 일치해야 한다.

2. 공백문자의 개수 (1칸을 띄우느냐 / 2칸을 띄우느냐) 까지도 같아야 한다.

3. 탭 키, 엔터키, 주석 문자 (-) 개수도 (-을 2개 썼는지 3개 썼는지 하는 것까지도 다) 맞아야 한다.

4. 힌트 (/*+ full (e..., 실행 계획을 제어하는 명령어) 도 맞아야 한다. 

5. 테이블의 소유자 이름이 쓰여 있느냐 안 써 있느냐까지도 일치해야 한다.

    (!!!!!!!!!!!! 현장에서는 테이블 이름 앞에 테이블 소유자를 꼭 써야 한다 !!!!!!!!!!!!)

 

(13:10)

★ 테이블 통계

 

예를 들어, 사원 테이블 안에 데이터가 1억 건 들어있는데

그 중에서 100번 사원의 데이터를 조회한다고 하자.

그러면 실행 계획을 만들기 위해서는: 1억 건 데이터 안에서 1건을 찾는 계획을 세워야 한다는 사실을 먼저 알아야 한다.

(테이블 안에 있는 전체 row 의 수가 얼마인지를 아는 것 역시 데이터 처리 방법을 결정하는 하나의 기준이 된다)

 

그런데 테이블 안에 들어있는 row 의 수를 매번 세어보게 되면 > 성능이 엄청나게 저하된다.

그래서 테이블 안에 들어있는 전체 건수를 어딘가에 담아놓는 것이 = 테이블 통계

 

(테이블 row 의 수는 select num_rows from dba_tables; 명령어나

select num_rows from user_tables; 명령어로 확인 가능)

 

 

그런데 만약 통계 정보가 없다면? 그래서 전체 row 의 수를 모른다면?

오라클은 현재 그 테이블이 사용하는 블록의 수는 알고 있으므로

샘플 블록의 row 의 수를 세어본 다음

샘플 블록 안의 row수와 전체 블록의 개수를 곱해서 테이블의 전체 row 수를 추측하고

추측을 바탕으로 실행 계획을 만든다.

(실행 계획을 명확하게 만들 수 없으므로 별로 좋은 방법은 아님)

 

통계정보가 이렇게 중요합니다.

 

하지만 낮 시간에 통계정보를 수집하지는 말자.

(통계 수집을 하는 순간 그 테이블과 관련된 실행 계획들이 모두 무효화되는데

무효화하려면 또 해당 테이블들과 관련된 실행 계획을 담고 있는 LCO 를 몽땅 다 찾아야 하고

LCO 를 찾으려면 래치를 잡아야 하고... LCO 를 찾아서 exclusive 모드로 시침핀 찍어놓아야 하고

핀을 찍는 순간 다른 유저는 실행계획을 사용할 수 없게 되므로 작업이 막힌다)

 

 

wait event 의 해결방법 : 문장 튜닝 - 절대로 메모리를 먼저 건드리는 것이 아니다.

 

 

2. bind (옵션 단계)

- SQL 문에 변수 처리가 되어있을 경우, 변수에 실제 값이 입력되는 단계

- 그렇다면 SQL 문에 변수처리가 되어 있을 때의 실행계획은 어떨까?

  select * from hr.emp where id = :b;

  parse 단계에서 실행계획을 만들 때, :b 는 모르는 값이므로 기본값으로 놓고

  emp 테이블의 사원번호에는 프라이머리 키 제약조건이 걸려 있으므로 (유일한 값만 있으므로)

  emp 테이블의 전체 row 중에서 1건만 찾는 실행 계획을 세운다.

  그래서 변수 처리가 되어있더라도 실행계획은 잘 만들어질 수 있다.

- 그런데 저 변수로 중복이 가능한 값이 들어갈 때:

  select * from hr.emp where dept_id = :b;

  dept_id 는 중복될 수 있는 값이고 결과값도 들쭉날쭉하다. (10번 부서의 인원수 다르고 20번 부서의 인원수 다르고...)

  차라리 중복이 되더라도 10번 부서 20명, 20번 부서 20명, ... 이런 식으로 균등하게 되어있으면 괜찮은데

  결과값이 들쭉날쭉한 경우에는 > 차라리 변수 말고 상수처리해서 하드 파싱을 유도하는 것이 낫다.

  (실행 계획을 따로 가져가도록 : 전체 중에 6건 찾는 실행계획과 전체 중에 20건 찾는 실행계획이 서로 같을 수 없으므로)

- 변수 처리는 경우에 따라 오히려 비효율적일 수도 있다.

 

변수처리하는 부분은 문장 튜닝할 때 다시 나온다.

 

4. fetch

- library cache lock 은 null 모드로 바뀌고, library cache pin 은 해제된다.

- active set 결과(= select 문장의 결과)를 유저 프로세스에 전달한다.