[ORACLE] 오라클 SQL 튜닝

SQL 튜닝은 SQL의 수행 시간을 단축시키는 것을 목적으로 한다. 다양한 방법을 통해서 옵티마이저가 더욱더 효율적으로 실행 계획을 생성할 수 있도록 도와주는 것이다.

 

1. SQL 변경

SQL의 작성 방법에 따라 optimizer가 다른 경로로 실행 계획을 수립할 수 있다. 같은 결과를 내더라도 쿼리를 짜는 방법은 다양하기 때문에 원래의 SQL 문을 옵티마이저가 더 효과적인 실행 계획을 생성할 수 있는 SQL 문으로 바꾸는 방법이다.

 

2. HINT의 사용과 경로의 제어

  - ORACLE의 힌트 기능은 ORACLE로 하여금 사용자가 원하는 경로의 실행 계획을 만들어 내도록 하는 것
  - SQL의 변경이나 재작성 없이 실행 경로를 제어 할 수 있는 방법

  - CBO를 사용하는 경우에는 반드시 지정한 경로로 실행되지는 않는다.
  - 통계 정보의 유무 등에 의해 사용자의 Hint가 무시되는 경우도 있으므로 반드시 실행 계획을 확인
  - 힌트 외에 인덱스를 사용하지 못하도록 인덱스 컬럼을 가공하는 등 실행 경로를 제한하는 방법도 있다.
  - 힌트를 사용하거나 인덱스 컬럼을 가공하여 작성된 SQL은 향후에도 지속적으로 영향을 미치므로 주의

 

3. 새로운 인덱스 추가
SQL 문의 효율적인 처리를 위해서는 특정 테이블의 특정 칼럼 값을 이용해서 해당 데이터를 빨리 찾아야 하는데, 인덱스가 없기 때문에 옵티마이저가 어떤 실행 계획을 선택하더라도 느릴 수밖에 없는 경우가 있다. 이와 같은 상황에서는 새로운 인덱스 생성을 통해서 옵티마이저가 해당 인덱스를 이용하는 새로운 실행 계획을 선택하도록 할 수 있다. 

4. 통계 정보의 추가 및 갱신

Optimizer의 비용 산정 모듈에서는 테이블, 칼럼, 인덱스 등에 대한 통계정보를 이용해서 선택도(selectivity), 카디널리티(cardinality) 등을 구하고 이를 통해서 궁극적으로 실행 계획의 비용을 계산한다. 그런데, 만일 특정 테이블/칼럼에 대한 통계 정보가 없거나, 오래 전에 만들어진 경우는 비용 계산이 부정확하게 되고, 따라서 옵티마이저가 선택하는 실행 계획이 실제로는 안 좋은 실행 계획일 수가 있다. 이를 해결하기 위해서는 특정 통계정보를 추가하거나 새로 갱신해 주어서 옵티마이저가 정확한 비용 산정을 통해서 더 나은 실행 계획을 선택하도록 해주는 방법이다.

 

테이블에 접근하는 방법

1) TABLE ACCESS FULL
Full table scan은 테이블의 각 ROW를 순차적으로 읽는다. ORACLE은 full table scan의 성능을 향상시키기 위해 동시에 여러 개의 Block을 읽는다. 특히 where절이 없는 query를 수행할 때에는 반드시 full table scan을 하므로 조심하여야 한다.

2) ROWID를 통한 TABLE ACCESS
  - 테이블에 대한 접근 성능을 향상시키기 위해서 RowID라고 하는 임의의 컬럼 값을 사용하여 각 Row에 접근

  - RowID는 row가 저장되어 있는 물리적인 위치에 대한 정보를 담김

  - ORACLE은 데이터의 물리적 위치 정보를 담고 있는 RowID와 관련된 index를 사용

  - 인덱스를 통해 ORACLE은 RowID에 빨리 접근할 수 있으며 index에 사용된 컬럼을 찾는 query의 수행 속도를 향상
  - 인덱스를 통한 Table 접근은 query의 형태에 따라 unique와 range로 나눌 수 있다.

 

SQL문의 공유

ORACLE은 SQL문을 parsing한 후 동일한 SQL문이 재실행될 때 다시 parsing하는 부하를 줄이기 위해 SQL문과 parsing된 정보를 메모리에 저장하여 활용한다. 이 정보는 System Global Area(SGA)의 일부분인 shared buffer pool내에 single shared context area에 저장되며 모든 유저가 공유한다. 이렇게 저장된 정보는 DB 내의 어느 사용자든 동일한 SQL문을 실행하게 때 다시 parsing하여 최적화된 실행경로를 생성해 내야 하는 부하를 줄일 수 있어 더 빠르게 결과값을 얻을 수 있다.

DBA는 효율적인 context area 사용을 위해서는 cache로 할당할 메모리의 양에 대한 적절한 값을 init.ora 내의 parameter에 지정하여야 한다. Context area는 클수록 많은 정보를 저장하고 활용할 수 있지만 시스템의 성능과 용도에 따라 반드시 그런 것은 아니므로 숙련된 엔지니어에
게 의뢰하는 것이 좋다. SQL문이 실행될 때마다 ORACLE은 먼저 context area에 동일한 문장에 대한 parsing 정보가 있는지 확인한다. 불행히도 ORACLE은 carriage return, space 그리고 대소문자를 구분하므로 이러한 문자들까지 일치하도록 하여야 다시 parsing하는 부하를 줄일 수 있다. 이러한 비교 조건을 만족시켜 shared area내의 parsing정보를 사용하기 위해서는 아래의 세가지 규칙을 따라야 한다.

 

1. 저장된 SQL문과 실행되는 SQL문의 모든 문자가 일치하여야 한다.

  -> 대문자로 통일하든 소문자로 통일하든 팀내에서 규칙을 정해서 사용해야한다. 

SELECT * from TABLE; --1
Select * From Table; --2
SELECT * FROM TABLE; --3

Select col from table
where col1 = 'a'; --1

Select col from table where col1 = 'a'; --2

위에 쿼리들은 같은 결과가 나오지만 오라클에서는 서로 다른 SQL로 인식한다. 소,대문자, 줄바꿈의 규칙을 팀내에서 정해서 사용하면 보다 빠른 결과를 얻을 수 있다.

 

2. 새로 실행되는 SQL문 내에서 참조하는 object가 기존 SQL문 내의 object와 동일하여야한다.

각 유저별로 가지고 있는 object가 있고 종류가 다르다. 두 USER 모두 접근할 수 있는 public synonym에 경우에는 동일한 SQL문이다.

각각의 유저가 private synonym으로 가지고 있을때는 서로 다른 object로 인식한다.

 

3) Bind variable을 사용할 때 variable명은 모두 동일하여야 한다.

  -> 변수를 할당할 때 변수명은 통일시킨다
다음의 두 문장은 run-time 시 동일한 값이 부여된다 하더라도 다른 문장으로 인식한다.

select ename, empno from emp where empno = :pEmpNo;
select ename, empno from emp where empno = :v_Emp_No;

 

FROM절에서의 테이블 순서 (RBO만 해당)

ORACLE parser는 항상 오른쪽 테이블부터 왼쪽 테이블로 실행경로를 생성한다. 만약 from절에 여러 개의 테이블 이름이 나열된 경우 적은 수의 row를 가진 테이블을 오른쪽에 배치하여 driving 테이블로 만들어야 한다. ORACLE이 여러 개의 테이블에 대한 처리를 할 때, 내부적으로 sort나 merge procedure를 통해 테이블들이 join 된다. 먼저 첫번째 테이블을 scan하여 sort한 후 다음 테이블을 scan한다. 그 후 이 테이블에서 추출된 데이터를 첫번째 테이블에서 추출된 데이터와 함께 조합하여 결과값을 돌려준다.

-> 적은 ROW의 테이블과 상위 테이블을 오른쪽에 배치

 

※ Driving Table : Query 수행시 처음 읽게 되는 테이블, 사용하고 있는 optimizer의 모드나 통계정보의 유무 등에 따라 결정