성능 개선을 위한 기본 지식 - 옵티마이저, 소프트 파싱,하드 파싱

 

1. 옵티마이저 

 

옵티마이저는 성능 최적화기라고 할 수 있다. SQL 실행 계획을 만드는 역할으 수행한다.

 

옵티마이저에는 두 가지 방식이 있는데, 비용 기반 옵티마이저(CBO),와 규칙 기반 옵티마이저(RBO)이다.

 

비용 기반 옵티마이저는 최소 비용으로 목표로 실행 계획을 만들고, 규칙 기반은 일정한 규칙에 따라 실행 계획을 만든다.

 

대부분의 RDMS가 비용 기반 옵티마이저이다.

 

1.2 비용이란?

 

비용 기반 옵티마이저에서 비용은 IO횟수, CPU Time, 메모리 사용량을 의미한다. 이와 같은 비용을 산출할 때 가장 중요한 것은 테이블의 통계 정보다. 

통계 정보가 제대로 수집되지 않으면, 옵티마이저는 엉뚱한 실행계획을 만든다. 

통계가 제대로 구성되었더라도 완벽한 실행 계획은 나오지 않는다. 

이런 경우 실행계획의 문제점을 찾아내고, 힌트를 사용해 경로를 고정해주는 것을 고려해 볼 수 있다. 

 

* 책에서 힌트를 많이 사용하지만, 힌트를 사용하는 것 보다 힌트가 없어도 옵티마이저가 실행계획을 잘 만들 수 있게 SQL을 잘 작성하고 인덱스를 적절하게 만들어주는 것이 좋다. 힌트는 최후의 수단이다. 

 

1.3 작동 방식 

 

파싱 단계에서 만들어진 SQL 파싱트리를 기반으로 옵티마이저는 다음 동작을 수행한다.

 

• Query TransFormer: 파싱된 SQL을 보고 같은 결과를 도출하되, 더 나은 실행계획을 갖는 SQL로 변환이 가능한지 판단
• Estimator: 시스템 통계정보를 딕셔너리로부터 수집하여 SQL 실행 시 총 비용을 계산
• Plan Generator : Estimator를 통해 계산된 값들을 토대로 후보군이 되는 실행계획 도출 

 

1.4 최적화 목표

 

1) 전체 처리속도 최적화

 

쿼리 최종 결과집합을 끝까지 읽는 것을 전제로, 비용이 가장 적은 실행계획을 선택함 

대부분 DBMS의 기본 옵티마이저 모드는 전체 처리속도 최적화에 맞춰져 있는데, 이 모드를 바꿀 수 있다.

alter system set optimizer_mode = all_rows --시스템에서 레벨 변경
alter session set optimizer_mode = all_rows --세션별 레벨 변경

select /*힌트*/ from t where -- 쿼리별 레벨 변경

 

2)최초 응답 속도 최적화

 

전체 중 일부만 필요할 때, 전체를 모두 수행하는 것이 아니라, 전체 중 일부만 처리해서 결과를 제공하는 것을 목표로 때에 따라 더 나은 성능을 낼 수 있다. (ex 전체 중 10개 정도만 필요하다면?)

 

Oracle 옵티마이저에게 최초 응답속도 최적화를 요구하려면, 옵티마이저 모드를 first_rows로 바꿔주면된다.

예를 들어 시스템,세션,쿼리 레벨에서 first_rows 10으로 지정하면, 사용자가 전체 결과집합 중 처음 10개 로우만 읽고 멈추는 것을 전제로 가장 빠른 응답 속도를 낼 수 있는 실행 계획을 선택한다.

 

select /*+ first_rows(10) */ * from t where ;

 

1.5 옵티마이저 행동에 영향을 미치는 요소 

 

1. SQL과 연산자 형태 : SQL을 어떤 형태로 작성 혹은 어떤 연산자를 사용했는지에 따라 다른 선택을 할 수 있다.
2. 옵티마이징 팩터: 쿼리가 똑같아도 인덱스,클러스터링,파티셔닝 구성에 따라 실행계획과 성능이 달라진다.
3. DBMS 제약 조건: 여러 제약조건은 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공한다. ex) 인덱스 칼럼에 not null 제약이 설정되어 있으면, 옵티마이저는 전체 개수를 구하는 count 쿼리에 이 인덱스를 활용할 수 있다. 
4. 옵티마이저 힌트 : 옵티마이저의 판단보다 사용자가 지정한 옵티마이저 힌트를 우선시한다.
5. 통계정보 : 옵티마이저는 통계정보를 사용해서 비용을 계산한다.
6. 옵티마이저 관련 파라미터, DBMS 버전과 종류 : DBMS 버전에 따라 성능 차이가 있다.

1.4 통계정보를 이용한 비용 계산 원리

 

실행계획을 수립할 때 CBO는 SQL 문장에서 엑세스할 데이터 특성을 고려하기 위해 통계정보를 이용한다.

최적의 실행계획을 위해 통계 정보가 항상 데이터를 정확하게 반영하고 있어야하는 이유다.

 

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=364

 

---

출처: https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=364

---

2. 소프트 파싱, 하드 파싱 

 

• SQL을 실행하면 오라클은 구문 분석을 가장 먼저 한다. (문법, 테이블,칼럼,뷰 등 검사)
• 이후, SQL의 실행 계획이 메모리에 있는지 검색 
  • 소프트 파싱 : 구문 분석만 하고, 실행계획은 재사용하는 것
  • 하드 파싱: 구문 분석 + 실행 계획 만다는 과정 
• 안정적인 성능을 위해선 소프트 파싱이 가능하도록 SQL을 작성해야함
• 하드 파싱을 막으려면, 바인드 변수를 사용해 SQL을 작성하도록 해야한다.
• 바인드 변수 값에 따라 성능 저하 발생하기도 함 
  • 바인드 변수를 사용하면, 처음 실행한 SQL만 하드 파싱 
  • 만약 고객 A는 주문 한 건, B는 십만 건의 주문이 있다 가정할 때 
    • A고객은 고객 ID 인덱스를 사용하는 것이 성능에 유리
    • B고객은 고객 ID 인덱스를 사용하면 성능에 좋지 않다. (데이터 많으면 인덱스 비효율)
  • 다만 위와 같은 문제에도 불구하고 소프트 파싱으로 개발해야함 
  • 하드 파싱은 생각보다 CPU 자원을 많이 사용하기 때문임 

---

3.  IO

 

• SQL 성능 개선 핵심은 IO다 DB에서 가장 기본적이면서도 핵심적인 역할을 수행함 
• SQL 성능 개선을 위해서는 불필요한 IO가 발생하지 않는지 살펴봐야한다. 
  • SQL에 불필요한 테이블을 사용하고 있는지 살펴보기 
  • 불필요한 데이터를 조회하지는 않는지 살펴보기 
  • 최소 IO로 처리하려면 인덱스가 필수이다. 

---

4. 블록

 

• 오라클에서 IO를 처리하는 최소 단위다. MS-SQL, MySQL에서는 페이지라고 부름 
• 데이터 한 건을 조회하거나 저장하기 위해서는 하나의 블록을 읽거나 써야함 
• 블록의 크기는 다양하게 설정할 수 있지만, 대부분 8KB가 기본이다. 
  • 대량 데이터를 분석하는 것이 목적인 시스템이라면 더 큰 크기의 블록을 고민해 볼 수 있다. 
• 한 블록에 여러 건의 데이터 들어갈 수도 혹은 한 건의 데이터도 들어가지 못할 수도 있다. 
  • 아이템 ID와 아이템 명으로 구성된 테이블이 있고, 각 칼럼이 40Byte라면, 한 건에 80Byte이며 한 블럭에 대략 100건을 저장하고, 한 블록을 읽을 때 마다 100건의 데이터를 읽는다.
• 최소 크기로 데이터가 저장되도록 테이블을 설계해야한다. 불필요하게 큰 데이터가 저장되면, 블록 하나에 저장할수 있는 데이터 건수가 작아지고, IO 횟수가 늘어난다. 
• SQL 실행계획을 보면 블록 IO 수치를 볼 수 있다. 

출처: SQL BOOSTER

• 실행계획을 보면 Buffers와 Reads 항목이 있다. Buffers는 논리적 IO고, Reads는 물리적 IO다 
• 논리적 IO는 버퍼 캐시에서 데이터 읽었음을 뜻하고, 물리적 IO는 물리적 디스크에서 읽었음을 뜻한다.
• 이는 누적 값임으로, 15번을 디스크에서, 37번을 블록에서 읽었음을 알 수 있다. 

---

5. 논리적 IO와 물리적 IO

 

• 오라클에서는 버퍼캐시라는 메모리 영여깅 있다. 
• 버퍼캐시에서 필요한 데이터를 가져오는 과정이 논리적 IO다 
• 버퍼캐시에 없는 데이터는 디스크에 가서 데이터 블록을 가져와야 한다. 물리적 IO 수행 후 사용한 블록은 다시 사용할 가능성이 크므로, 버퍼 캐시에 저장해둔다. 
• 버퍼캐시는 스크보다 제한적인 자원이다. 버퍼 캐시가 꽉 차 있는 상태에서 새로운 데이터가 버퍼캐시로 들어가야 한다면, 버퍼캐시에서 가장 오래전에 사용된 데이터를 제거한다. 
• 실행계획에서 논리적 IO 항목을 주의 싶게 살펴보자. 성능이 나빠진 증거를 찾을 수 있을 것이다. 

---

6. 부분 범위처리 

 

• SQL을 실행하면, 조건에 맞는 데이터를 필요한 만큼 찾아서 먼저 내보내는 처리 방식이다. 
• SQL 집합의 총 건수가 500건이고, 화면에 우선 보여줄 건수가 50건이라면, 50건을 우선 전송하는 것이 부분 범위 처리다. 
• 이는 빠른 응답시간을 보장해준다. 

출처: SQL BOOSTER

 

• A-Rows는 50건이 나왔다. 요청 건수 단위가 200으로 설정되어 있다면 A-Rows는 200이 나올 것이다. 
• 이제 모든 결과가 나올 때 까지 스크롤 한 뒤 실행계획을 확인해보자

출처: SQL BOOSTER

• A-Rows가 2606임을 확인할 수 있다. 스크롤 바를 이용해 다음 데이터 요청해야 추가 데이터를 전송한다는 사실을 알 수 있다.  
•  

출처: SQL BOOSTER

•  SQL 조건에 맞는 데이터를 요청 건수만큼 찾을 때 까지 읽는다. 
• 요청 건수만큼 데이터를 찾으면 결과를 사용자에게 보낸다.
• 사용자가 다음 데이터를 요청하면, 1단계에서 읽다가 멈춘 지점부터 읽으면서 조건에 맞는 데이터를 요청 건수만큼씩 채워서 내보낸다. 
• 성능이 최적화된 부분 범위 처리는 결과를 내보내는 과정뿐 아니라 데이터를 읽는 과정도 부분 범위 처리되어야한다.
• GROUP BY나 SUM,AVG같은 집계함수 혹은 분석함수는 데이터를 찾는 과정에서 부분 범위 처리하기 쉽지 않다. 

출처: SQL BOOSTER

• 위 실행계획은 GROUP BY한 SQL에 실행계획이다. 
• A-Rows를 살펴보면 2606인 것을 확인할 수 있다. (모든 데이터를 읽어야만 그룹을 지을 수 있기 때문)
• HASH GROUP BY는 A-Rows가 50건임을 확인 할 수 있다. 
• 이는 2606건의 데이터를 찾아내 GROUP BY 처리하고, 클라이언트에서 우선 요청한 50건만 결과로 내보냄을 알 수 있다. 
• 최종 결과는 필요한 만큼만 내보냈지만, 읽는 과정은 그렇지 못했다. 
• ORDER BY도 마찬가지지만, SQL과 인덱스에 따라 데이터 찾는 과정을 부분 범위 처리할 수 있다.