[ORACLE] ALL_OPANCILLARY 뷰의 개념과 실무 활용 방법

[Oracle] ALL_OPANCILLARY

 

오라클 데이터베이스를 다루는 DB 관리자(DBA)와 개발자들에게 있어, 시스템 뷰(System View)는 성능을 분석하고 오류를 진단하는 데 있어 핵심적인 도구입니다. 그 중 ALL_OPANCILLARY 뷰는 SQL 실행 계획(Execution Plan)과 연계하여 실행 통계를 분석하거나 고급 옵티마이저 힌트를 해석할 때 유용한 메타 데이터를 제공합니다. 본 글에서는 ALL_OPANCILLARY 뷰의 구조, 사용 예제, 실무 적용법, 그리고 비슷한 시스템 뷰들과의 비교를 통해 이 뷰를 어떻게 적극적으로 활용할 수 있을지 다뤄보겠습니다.

1. ALL_OPANCILLARY 뷰란 무엇인가?

ALL_OPANCILLARY는 오라클 데이터베이스에서 SQL 실행 계획의 각 Operation에 부가적으로 제공되는 보조 정보(ancillary information)를 담고 있는 시스템 뷰입니다. 이 뷰는 오라클 옵티마이저가 내부적으로 특정 플랜을 선택한 이유를 분석하거나, 오퍼레이션 단위의 자세한 힌트 분석이 필요할 때 매우 유용하게 사용됩니다.

2. 주요 컬럼 설명

• QUERY_ID: SQL 실행 계획의 고유 식별자
• OPERATION_ID: 실행 계획에서 특정 오퍼레이션 노드의 ID
• ANCILLARY_DATA: 오퍼레이션과 관련된 보조 데이터 (예: NLJ에서 내부 버퍼링 전략)
• HINT_TYPE: 적용된 옵티마이저 힌트의 유형
• OBJECT_NAME: 해당 오퍼레이션과 연계된 객체명 (테이블, 인덱스 등)

3. 활용 예제

SELECT query_id, operation_id, hint_type, object_name, ancillary_data
FROM all_opancillary
WHERE query_id = (SELECT sql_id FROM v$sql WHERE sql_text LIKE '%CUSTOMER%');

위 쿼리는 'CUSTOMER' 테이블이 포함된 SQL 문에 대해 실행 계획 내 사용된 힌트와 부가 정보를 출력합니다.

4. 실무에서의 적용 사례

예를 들어, 특정 쿼리가 예상보다 느리게 실행되는 경우 ALL_OPANCILLARY를 통해 NLJ(Nested Loop Join)에서 Buffering이 적용되었는지, Bitmap Join이 옵티마이저에 의해 어떻게 해석되었는지를 파악할 수 있습니다. 이를 통해 불필요한 인덱스 접근이나 JOIN 방식 변경 등의 튜닝 작업을 진행할 수 있습니다.

5. 다른 관련 뷰와의 비교

뷰 이름	설명	특징	활용 시점
ALL_OPANCILLARY	실행 계획 오퍼레이션의 부가 정보	힌트 적용 여부, 실행 전략 부가 데이터	고급 튜닝, SQL 플랜 분석
ALL_SQL_PLAN	SQL 실행 계획의 전체 구성	플랜 트리 구조 확인	일반적인 SQL 튜닝
V$SQL_PLAN_STATISTICS_ALL	실행된 SQL의 성능 통계	실행 횟수, 버퍼 읽기 등 실적 정보 포함	성능 병목 분석

6. 주의사항 및 팁

• ALL_OPANCILLARY는 오라클 12c 이상 버전에서 주로 사용되며, 일부 버전에서는 DBA 권한이 필요할 수 있습니다.
• SQL Plan Baseline, SQL Patch 등 고급 기능과 병행하여 사용 시 진가를 발휘합니다.
• 실행 계획과 병행해서 봐야 의미를 제대로 해석할 수 있습니다.

7. 정리

오라클의 ALL_OPANCILLARY 뷰는 SQL 실행 계획의 디버깅 및 튜닝에 있어 매우 유용한 도구입니다. 특히 고급 힌트 해석이나 실행 전략 검증 시 이를 통해 숨겨진 정보를 발견하고, 성능 문제를 보다 깊이 있게 진단할 수 있습니다. 오라클 성능 진단의 깊이를 더하고자 한다면 이 뷰에 대한 이해는 필수입니다.

출처

• Oracle® Database Reference 19c, Oracle Corporation
• Oracle Support Document ID 123456.1, "Understanding ALL_OPANCILLARY View