오라클 SQL PATCH와 SQL PROFILE의 차이점

 

Oracle 데이터베이스의 SQL Tuning 도구 중 SQL Patch와 SQL Profile은 자주 혼동되는 개념입니다. 이 두 가지는 성능 문제를 해결하거나 SQL 실행 계획을 최적화하는 데 사용되지만, 작동 원리와 사용 목적에서 차이가 있습니다. 이 글에서는 두 개념의 차이를 상세히 설명하고, 각각의 활용 사례를 예시와 함께 살펴보겠습니다.

---

1. SQL PATCH란?

SQL Patch는 특정 SQL ID에 대해 실행 계획을 강제로 수정하거나 고정된 힌트를 적용할 수 있도록 Oracle이 제공하는 기능입니다. SQL Plan Management(SPM) 기능과 함께 사용되며, 문제가 되는 SQL 문장이 재컴파일되거나 재실행될 때에도 수정된 실행 계획이 유지됩니다.

특징

• 목적: 특정 SQL 문에 대해 힌트를 강제 적용하거나, 특정 계획을 무효화합니다.
• 적용 범위: 특정 SQL ID에만 적용됩니다.
• 주요 사용 시점:
  • 성능 문제를 일으키는 SQL 문이 있을 때 힌트를 추가하거나 실행 계획을 강제 조정.
  • SQL 코드 자체를 수정할 수 없는 경우.

구성 방법

SQL Patch는 DBMS_SQLDIAG 패키지를 사용해 생성합니다.

BEGIN
  DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_id => '123abcxyz',
    name => 'fix_index_hint',
    description => 'Force index hint for performance improvement'
  );
END;

---

2. SQL PROFILE란?

SQL Profile은 SQL 문장을 실행하기 전에 **옵티마이저(Optimizer)**가 더 나은 실행 계획을 선택하도록 도와주는 통계 정보를 제공합니다. 이는 옵티마이저의 결정을 돕는 데이터 기반 접근 방식으로, 자동 SQL 튜닝 기능에서 주로 생성됩니다.

특징

• 목적: 옵티마이저의 실행 계획 선택 과정에서 추가 정보를 제공하여 더 나은 계획을 선택하도록 유도.
• 적용 범위: 비슷한 구조의 여러 SQL 문에도 적용 가능.
• 주요 사용 시점:
  • 옵티마이저가 잘못된 실행 계획을 선택할 때.
  • SQL 코드를 수정하지 않고 성능을 개선해야 할 때.

구성 방법

SQL Profile은 SQL Tuning Advisor를 사용하여 자동으로 생성하거나, DBMS_SQLTUNE 패키지를 사용해 수동으로 생성합니다.

BEGIN
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name => 'Tuning_Task',
    name => 'profile_example'
  );
END;

---

3. SQL PATCH와 SQL PROFILE의 주요 차이점

구분 SQL PATCH SQL PROFILE

작동 방식	힌트를 강제로 적용하거나 실행 계획을 수정.	옵티마이저에게 추가 정보를 제공하여 실행 계획 선택을 개선.
적용 범위	특정 SQL ID에만 제한적으로 적용.	SQL 구조에 따라 여러 문장에도 적용 가능.
생성 방법	DBMS_SQLDIAG 패키지를 통해 수동으로 생성.	SQL Tuning Advisor를 통해 자동 또는 수동 생성.
사용 목적	SQL 실행 계획 강제 수정 및 문제 해결.	잘못된 실행 계획 개선 및 통계 보완.
기반 기술	고정 힌트 기반.	옵티마이저 통계 기반.
SQL 변경 필요 여부	SQL 코드를 수정하지 않아도 됨.	SQL 코드를 수정하지 않아도 됨.

---

4. 실제 활용 예시

SQL PATCH 활용 예

문제: 특정 쿼리가 옵티마이저에 의해 잘못된 실행 계획을 선택하여 성능이 저하됨.
해결: SQL Patch를 사용해 인덱스를 강제 적용.

BEGIN
  DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_id => '45abcde67f',
    hint_text => 'INDEX(orders idx_order_date)',
    name => 'fix_execution_plan'
  );
END;

결과: 실행 계획에서 옵티마이저가 항상 idx_order_date 인덱스를 사용하도록 강제되며 성능이 개선됨.

---

SQL PROFILE 활용 예

문제: 복잡한 쿼리에서 옵티마이저가 잘못된 조인 순서를 선택함.
해결: SQL Profile을 생성하여 옵티마이저가 더 나은 조인 순서를 선택하도록 유도.

BEGIN
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name => 'Tuning_Task',
    name => 'improve_join_order'
  );
END;

결과: 조인 순서가 개선되면서 실행 시간이 대폭 단축됨.

---

SQL PATCH와 SQL PROFILE의 결합 활용

둘 다 SQL 코드 수정 없이 문제를 해결할 수 있지만, SQL Patch는 특정 힌트 강제 적용에 적합하며, SQL Profile은 옵티마이저의 결정 보완에 적합합니다. 상황에 따라 이 두 가지를 병행해 사용할 수도 있습니다.

---

5. 결론

SQL Patch와 SQL Profile은 SQL 성능 문제를 해결하기 위한 강력한 도구입니다.

• SQL 문이 잘못된 실행 계획을 선택하고, 힌트를 강제로 적용해야 하는 경우 SQL Patch를 활용하세요.
• 옵티마이저의 통계 부족이나 잘못된 계획 선택이 문제라면 SQL Profile을 사용하세요.

이 두 가지를 적절히 활용하면 튜닝 효과를 극대화할 수 있습니다. 여러분의 환경에 맞는 도구를 선택하여 데이터베이스 성능을 최적화해보세요!