[SKALA] DB Query 작성 및 튜닝 기초 실습(1)

그동안은 주로 MySQL(InnoDB)을 사용해서 데이터베이스를 다뤄왔는데, 이번에는 기업 환경에서 많이 활용된다는 Oracle DB를 처음으로 사용해보게 되었다.

낯선 환경에서 시작한 만큼, 오늘은 오라클에서 기본적인 DB를 구축하고, 직접 쿼리를 작성해보는 실습을 진행했다. 특히 작성한 쿼리에 대해 실행계획을 확인하고, 성능 최적화를 위해 인덱스를 어떻게 활용할 수 있는지도 함께 살펴보았다.

이번 실습에서 핵심적으로 느낀 개념은 인덱스와 실행계획, 옵티마이저였다.

 

인덱스(Index)

인덱스는 테이블 내에서 특정 데이터를 더 빠르게 검색할 수 있도록 도와주는 자료 구조다.
Oracle에서도 기본적으로 B-tree 기반 인덱스가 사용된다.

• 자주 조회되는 컬럼이나 WHERE, JOIN, ORDER BY 절에 자주 등장하는 컬럼에 인덱스를 생성하면, 쿼리 성능이 크게 향상될 수 있다.
• 하지만 모든 컬럼에 인덱스를 생성하는 것은 오히려 성능 저하를 유발할 수 있다.
  인덱스를 유지하고 관리하는 데에도 비용이 들기 때문에, 사용 목적과 데이터 특성에 맞게 신중하게 적용하는 것이 중요하다.

 

실행계획(Execution Plan)

실행계획은 쿼리가 실행될 때 DB 엔진이 어떤 순서와 방식으로 데이터를 처리하는지를 보여주는 실행 시나리오다.

Oracle에서는 EXPLAIN PLAN 문과 함께 DBMS_XPLAN.DISPLAY() 함수를 사용해 쿼리의 실행 흐름을 시각적으로 확인할 수 있다.
실행계획을 통해 아래와 같은 정보를 확인할 수 있다:

• Full Table Scan인지 Index Scan인지
• 어떤 조인 방식(Nested Loop, Hash Join 등)이 사용되었는지
• 각 단계의 실행 순서와 예상 비용(COST)

이런 정보를 바탕으로 병목 지점을 파악하고, 필요한 경우 쿼리 구조를 개선하거나 인덱스를 추가하여 성능을 향상시킬 수 있다.

 

옵티마이저(Optimizer)

옵티마이저는 실행계획을 결정하는 데 있어 핵심적인 역할을 하는 구성 요소다.
여러 실행 전략 중에서 가장 효율적인 계획을 자동으로 선택하며, 그 판단 기준은 테이블에 수집된 통계 정보에 기반한다.

통계 갱신은 테이블 갱신, 인덱스 갱신, 스키마 갱신 등이 있다.

EXEC DBMS_STATS.GATHER_TABLE_STATS('SHOP', 'ORDER_INFO'); -- 테이블 갱신
EXEC DBMS_STATS.GATHER_INDEX_STATS('SHOP', 'IDX_ORDER_DATE'); -- 인덱스 갱신
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SHOP'); -- 스키마 갱신

 

또한, 다음과 같은 정보를 활용해 실행 방식을 결정한다:

• 테이블의 행 수
• 데이터 분포
• 인덱스 유무
• 컬럼 값의 다양성

 

실습

이번 실습은 기본적인 쇼핑몰 DB를 기반으로,
주어진 정보를 조회하는 쿼리를 작성하고, 해당 쿼리에 인덱스를 적용해 실행계획의 변화를 비교해보는 과정으로 진행되었다.

 

https://bittersweet-snowman-a04.notion.site/Oracle-1d794f06d6d7800e935df067a3d986f3?pvs=74

Oracle 기반 조인/인덱스 실습 | Notion

 

알게 된 점

• 인덱스를 생성해도 통계 정보를 갱신하지 않으면, 옵티마이저는 해당 인덱스를 제대로 인식하지 못해 사용하지 않을 수 있다.
• 옵티마이저는 단순히 인덱스 유무만이 아니라, 데이터 양, 통계 정보, 조인 방식, 예상 비용 등을 종합적으로 고려해 실행 계획을 결정한다.
• 이 중에서도 데이터 양은 실행 방식 결정에 큰 영향을 미치는 요소로, 테이블 전체 행 수나 조건절에 의해 필터링된 후 남는 예상 건수 등을 기준으로 인덱스 스캔 또는 풀스캔 여부를 판단하게 된다.
• 인덱스를 만든다고 해서 무조건 성능이 향상되는 것은 아니며, Full Table Scan이 더 효율적인 경우도 충분히 존재할 수 있다.
• 실행계획에 표시되는 COST는 예상 리소스 사용량일 뿐이며, 실제 실행 시간과 반드시 일치하지는 않는다.
• 따라서 SQL 튜닝 시에는 단순히 COST나 인덱스 유무만으로 판단하지 말고, 전체 실행계획을 해석하고 다양한 요소를 함께 고려하는 접근이 필요하다.