[DB] 데이터베이스 쿼리 튜닝(힌트,옵티마이저)

안녕하세요👋

이번 글에서는 데이터베이스의 쿼리 튜닝과 관련되어 옵티마이저, 힌트에 대해 작성해보도록 하겠습니다.

데이터베이스 성능을 올리기 위해 쿼리마다 힌트를 사용해야 하는지 또 옵티마이저의 실행 계획을 어떻게 튜닝하는 것이 좋을지에 관해 설명하겠습니다

공부한 내용을 바탕으로 작성한 글이므로 부족한 부분이 있을 수 있습니다 댓글로 알려주시면 감사하겠습니다🙏

🙏요약

옵티마이저와 힌트 이어서 힌트를 사용하는 것과 옵티마이저의 실행계획을 바꾸는 법에 대해 이야기해보겠습니다

1. 옵티마이저란?
2. 힌트란?
3. 옵티마이저의 실행계획을 바꾸기

📝옵티마이저란?

옵티마이저는 가장 효율적인 방법으로 sql을 수행할 최적의 경로를 제안해주는 다시말해 sql의 실행계획을 잡아주는 DBMS의 핵심 엔진입니다.

옵티마이저는 어떤 기준으로 가장 효율적인가를 선택할지에 따라 두종류로 나뉘게 되는데요 규칙 기반 옵티마이저(RBO),비용 기반 옵티마이저(CBO)입니다

옵티마이저를 검색하면 나오는 유명한 사진입니다

• 규칙 기반 옵티마이저(RBO)

  
  오라클 8 이하의 버전에서 기본적으로 설정되어 있는 옵션인 규칙 기반 옵티마이저는 규칙을 미리 세워두고 이 규칙에 따라서 우선순위가 앞서는 방식을 채택하는 형태의 옵티마이저 입니다.
  
  규칙의 순위는 다음과 같습니다.
  

1. rowid를 사용한 단일 행
2. 클러스터 조인에 의한 단일 행
3. 해시 클러스터 키에 의한 단일 행
4. unique 옵션이나 pk를 가진 단일 행
5. 클러스터 조인
6. 해시 클러스터 조인
7. 복합 컬럼 인덱스
8. 단일 컬럼 인덱스
9. 인덱스가 구성되어있는 컬럼에서 제한된 범위 검색
10. 인덱스가 구성되어있는 컬럼에서 모든 범위 검색
11. 정렬-병합 조인
12. 인덱스가 구성되어있는 컬럼에서 min 또는 max
13. 인덱스가 구성되어있는 컬럼에서 order by
14. 전체 테이블 스캔

규칙 기반 옵티마이저는 이렇게 우선순위가 있기 때문에 옵티마이저 실행 계획을 미리 예측하여 sql을 작성할 수 있고 응용하면 원하는대로 실행 계획을 수정할 수 있습니다

• 비용 기반 옵티마이저(CBO)

  
  최근 사용되는 방식의 옵티마이저 이고 오라클 10이상부터는 기본 옵티마이저로 설정되어 있습니다
  
  기본적으로 먼저 실행계획을 여러개 세운 뒤 비용이 가장 적을 것으로 예측되는 실행계획을 실행하게 됩니다
  
  비용 기반 옵티마이저에서는 옵션을 통해 최적의 비용을 예측하는 방법을 달리할 수 있습니다 그 옵션들은 다음과 같습니다.
  

1. CHOOSE : 통계 정보가 있을 경우 비용 기반으로 실행 이외에는 규칙기반으로 실행
2. FIRST_ROWS : 옵티마이저 실행 계획을 통해 첫 건만을 출력해보고 이를 통해 가장 빠른 실행계획으로 실행
3. FIRST_ROWS_n : n개의 출력 결과를 예측하여 가장 빠른 실행계획으로 실행
4. ALL_ROWS : 모든 출력 결과를 예측하여 가장 빠른 실행계획으로 실행

비용 기반 옵티마이저는 실제 어떤 실행계획이 실행될지 예측하기 힘들지만 성능,안정성이 비용 기반보다 우수하여 최근 대부분 비용기반의 옵티마이저가 사용됩니다

📔힌트란?

힌트는 옵티마이저의 실행계획을 조작하는 구문을 의미합니다

옵티마이저가 항상 최적의 실행계획을 찾아줄 수 없으므로 쿼리에 직접적으로 /*+ 힌트구문 */ 형태의 문법으로 삽입하여 옵티마이저에게 실행 계획을 알려주게 됩니다

비용 기반의 옵티마이저 옵션을 힌트를 통해서 보통 설정하게 되는데요 /*+ FIRST_ROWS */ 라는 문구를 select 뒤에 작성하여 옵티마이저가 첫 행이 가장 빠른 방식으로 조회해라 라는 것을 알려주게 됩니다

많이 사용되는 힌트를 몇개만 소개해보면

• /*+ ORDERED */ - 조인시 옵티마이저가 아닌 sql문에 명시되있는 순서로 조인이 일어나도록 설정하는 구문
• /*+ RULE */ - 룰 기반 옵티마이저 사용
  등이 있습니다.
  
  더 자세한 힌트 구문 문법들은 다른 블로그를 참고해 주시면 감사하겠습니다.
  

🔥옵티마이저의 실행 계획을 바꾸기

자 우리는 여기서 옵티마이저의 실행계획을 최적화 하는 형태로 sql을 튜닝하고 싶다면 어떻게 해야 할까요

무작정 모든 sql에 같은 형태의 힌트들을 사용하거나 직접 비교해보며 어떻게 튜닝할지 일일히 하기는 매우 힘듭니다.

따라서 옵티마이저의 실행계획을 미리 보고 튜닝을 해놓게 되면 성능이 개선된 형태의 sql이 잘 적용되어 사용될 수 있겠죠 그럼 실제 튜닝을 할 때에는 어떻게 튜닝을 하게 될까요?

sql쿼리문 앞쪽에 명령어를 입력하여 실행계획을 미리 보고 힌트를 사용하여 튜닝을 할 수 있고 이 때 두가지의 방법이 있습니다.

• explain plan방식으로 실행계획을 미리 보기
  
  좋아 보이지만 각각의 sql마다 모두 실행계획을 분석하여 힌트를 작성해 주어야 합니다. 1개의 서버 restful api에 많은 쿼리들이 동시에 사용된다면 생산성도 낮아지고 머리도 많이 아플 것입니다
  
  

• set autotrace방식으로 여러개의 sql실행계획을 보기
  
  위의 explain plan방식을 개선하여 여러개의 sql트랜잭션을 처리하는 실행계획을 동시에 보여줍니다 이를 통해 여러개의 쿼리가 사용될 경우 적절하게 힌트를 사용하므로서 많은 sql구문을 한번에 튜닝할 수 있게 되겠죠 set autotrace방식의 실행계획을 보고 분석하는 방법을 익히는 것이 중요하다고 생각합니다
  
  백엔드 개발자는 항상 성능과 서버의 안정성에 대해 사용자들에게 어떻게 더 좋은 서비스를 제공할지 고민해야 한다고 생각합니다 오늘 저의 고민이 더 좋은 개발자가 되는데 한 발짝 다가갈 수 있으면 좋겠습니다🙂
  

긴글 봐주셔서 항상 감사합니다 앞으로도 유용한 내용을 많이 공유하겠습니다 그럼 안녕 👋