Oracle 환경에서 DBA_IAS_PREGEN_STMTS 뷰는 상대적으로 잘 알려지지 않았지만, 애플리케이션 서버(IAS: Internet Application Server) 및 미리 생성된 SQL 문장을 관리·점검할 때 꼭 짚고 넘어가야 하는 데이터 딕셔너리 뷰입니다. 많은 DBA가 DBA_TAB_COLUMNS, DBA_INDEXES, V$SQL처럼 눈에 잘 띄는 뷰는 자주 활용하지만, DBA_IAS_PREGEN_STMTS는 “언젠가 한 번 보긴 했는데…” 정도로 지나치는 경우가 많습니다. 이 글에서는 단순 컬럼 설명을 넘어, 다음과 같은 관점에서 DBA_IAS_PREGEN_STMTS를 깊게 파고듭니다.
- 이 뷰가 왜 존재하는지, 어떤 구조를 갖는지
- 실제 운영 환경에서 어떤 문제를 진단할 때 유용한지
- 다른 관련 뷰와 어떻게 엮어서 보는 것이 좋은지
- SQL 튜닝, 애플리케이션 서버와의 연계, 모니터링 자동화 아이디어
1. DBA_IAS_PREGEN_STMTS란 무엇인가?
DBA_IAS_PREGEN_STMTS는 말 그대로 IAS(Internet Application Server)에서 미리 생성(Pregenerated)된 문장(Statements)에 대한 정보를 저장하는 DBA 레벨의 데이터 딕셔너리 뷰입니다. 여기서 말하는 “문장”은 대부분 SQL 또는 PL/SQL 블록, 그리고 일부 환경에서는 웹 요청을 처리하는 데 사용되는 관련 메타데이터까지 포함할 수 있습니다.
과거 Oracle Application Server(예: Oracle9iAS, 10g AS 등) 환경에서, 웹/애플리케이션 계층은 데이터베이스와 긴밀하게 연동되며, 성능 향상을 위해 쿼리를 미리 파싱하거나 실행 계획을 미리 준비해두는 전략을 사용했습니다. 이때 미리 준비된 SQL/PLSQL에 대한 정보가 데이터베이스 내부에 저장되고, 이를 DBA가 조회할 수 있도록 만들어놓은 창구 중 하나가 바로 DBA_IAS_PREGEN_STMTS입니다. 즉, 요약하면 다음과 같이 정의할 수 있습니다.
정의: DBA_IAS_PREGEN_STMTS는 Oracle IAS와 연동되는 환경에서 미리 생성·등록된 SQL/PLSQL 등의 실행 문장에 대한 메타데이터를 제공하는 DBA 전용 데이터 딕셔너리 뷰이다.
2. 기본 구조 및 주요 컬럼 이해하기
Oracle 버전 및 IAS 구성 방식에 따라 세부 컬럼 구성은 다소 차이가 있을 수 있으나, 일반적으로 다음과 같은 유형의 컬럼들을 포함합니다(컬럼 이름은 예시이며, 실제 환경에서 DESC DBA_IAS_PREGEN_STMTS로 확인하는 것을 권장합니다).
| 컬럼 예시 | 데이터 타입 | 설명 |
|---|---|---|
| STMT_ID | NUMBER | 미리 생성된 문장을 구분하는 고유 식별자 |
| STMT_TEXT | CLOB / VARCHAR2 | 실제 SQL 또는 PL/SQL 텍스트 |
| MODULE_NAME | VARCHAR2 | 해당 문장을 사용하는 애플리케이션 모듈 이름 |
| CREATED_BY | VARCHAR2 | 문장을 생성한 계정 또는 컴포넌트 |
| CREATED_DATE | DATE | 문장이 미리 생성되어 등록된 시각 |
| LAST_EXECUTED | DATE | 문장이 마지막으로 사용(실행)된 시각 |
| EXEC_COUNT | NUMBER | 해당 문장의 누적 실행 횟수(환경에 따라 존재) |
| STATUS | VARCHAR2 | 문장 사용 가능 여부(VALID/INVALID 등) |
실제 컬럼 이름과 타입은 Oracle 버전 및 IAS 구성에 따라 변경될 수 있지만, 대부분 “누가, 언제, 어떤 문장을, 얼마나 사용했는지”에 초점을 맞추고 있다는 점이 공통적입니다.
3. DBA_IAS_PREGEN_STMTS가 중요한 이유
이 뷰는 단순히 “과거 제품군의 흔적” 정도로 치부하기에는 아까운 요소들이 많습니다. 특히 다음과 같은 상황에서는 상당히 유용한 도구가 됩니다.
3.1 성능 저하의 원인이 되는 정적 SQL 파악
IAS 및 관련 애플리케이션은 특정 화면이나 기능에서 동일한 SQL을 반복적으로 호출하는 경우가 많습니다. 이때 문제가 되는 SQL이 “미리 생성된 문장”에 포함되어 있다면, DBA_IAS_PREGEN_STMTS는 해당 SQL의 원문과 사용 여부를 추적하는 데 큰 도움을 줍니다.
- 실행 횟수 대비 성능이 안 나오는 SQL 찾기
- 더 이상 사용하지 않는 문장이 계속 유지되고 있는지 확인
- 튜닝이 필요한 후보 SQL을 빠르게 리스트업
3.2 애플리케이션 버전 변경에 따른 SQL 정리
애플리케이션 버전 업그레이드나 모듈 리팩토링 과정에서 더 이상 사용되지 않는 SQL이 남아 있는 경우가 많습니다. LAST_EXECUTED나 EXEC_COUNT와 같은 컬럼이 존재한다면, 최근 일정 기간 동안 전혀 실행되지 않은 문장을 찾아내어 정리(삭제 또는 비활성화)할 수 있습니다.
3.3 감사 및 변경 이력 분석
누가(계정 또는 모듈) 어떤 문장을 언제 등록했는지와 같은 정보는 보안 감사, 변경 이력 관리에 매우 중요합니다. 특정 모듈에서 예기치 않은 데이터 변경이 발생했다면, 관련 미리 생성 문장을 추적함으로써 원인을 좁혀갈 수 있습니다.
4. 관련 뷰와의 비교: 언제 무엇을 쓸 것인가?
실제 운영에서는 DBA_IAS_PREGEN_STMTS만 단독으로 보기보다는 다른 뷰와 함께 활용하는 경우가 많습니다. 아래 표는 유사하게 “SQL/문장 관련 메타데이터”를 제공하는 대표적인 뷰와 DBA_IAS_PREGEN_STMTS를 비교한 것입니다.
| 뷰 이름 | 주요 목적 | 특징 | 사용 시점 |
|---|---|---|---|
| DBA_IAS_PREGEN_STMTS | IAS에서 미리 생성된 문장 관리 | 정적/사전 등록 SQL, 모듈 및 작성자 정보 포함 | 애플리케이션 서버 연계 및 미사용 SQL 정리 |
| V$SQL | 현재/최근 공유 풀에 존재하는 SQL | 실제 실행 계획, 버퍼 읽기, 수행 시간 등 상세 성능 정보 | 실시간/최근 성능 분석, 핫 스팟 튜닝 |
| DBA_HIST_SQLSTAT | AWR 기반 SQL 히스토리 | 장기적인, 스냅샷 단위의 SQL 성능 통계 | 장기간 추세 분석, 특정 기간 성능 회귀 분석 |
| DBA_SOURCE / USER_SOURCE | PL/SQL 소스 코드 | 패키지/프로시저/함수 단위의 전체 코드 확인 | 코드 리뷰, 로직 점검, 배포 전/후 비교 |
핵심은 이렇습니다. DBA_IAS_PREGEN_STMTS는 “어떤 문장을 미리 준비해두었는가?”를 보여주는 메타데이터 뷰이고, V$SQL이나 DBA_HIST_SQLSTAT는 “실제로 어떻게 실행되고 있는가?”를 보여주는 성능 관점의 뷰입니다. 이 둘을 함께 보면, 미리 만들어 두었지만 거의 쓰이지 않는 SQL과 반대로 의도와 달리 과도하게 사용되는 SQL을 동시에 파악할 수 있습니다.
5. 실무에서 바로 쓸 수 있는 SQL 예제
5.1 최근 30일 이내 한 번도 실행되지 않은 문장 찾기
-- 최근 30일 동안 한 번도 실행되지 않은 미리 생성 문장
SELECT stmt_id,
SUBSTR(stmt_text, 1, 80) AS short_text,
module_name,
created_date,
last_executed,
exec_count
FROM dba_ias_pregen_stmts
WHERE (last_executed IS NULL
OR last_executed < SYSDATE - 30)
ORDER BY created_date;
이 결과는 다음과 같은 업무에 바로 활용할 수 있습니다.
- 더 이상 사용하지 않는 문장 후보 리스트업
- 애플리케이션 버전 업그레이드 후 잔존 리소스 정리
- 보안/감사 측면에서 “죽어 있는 코드” 파악
5.2 실행 횟수가 많은 상위 미리 생성 문장 확인
-- 실행 횟수가 많은 상위 20개 문장 조회
SELECT *
FROM (
SELECT stmt_id,
SUBSTR(stmt_text, 1, 100) AS short_text,
module_name,
exec_count,
last_executed
FROM dba_ias_pregen_stmts
WHERE exec_count IS NOT NULL
ORDER BY exec_count DESC
)
WHERE ROWNUM <= 20;
이 결과를 바탕으로 다음과 같은 작업을 진행할 수 있습니다.
- 자주 사용되는 정적 SQL의 실행 계획 점검
- 해당 SQL이 사용하는 인덱스, 파티션 전략 검토
- 애플리케이션 측 파라미터 바인딩 방식 검토
5.3 특정 모듈에서 사용하는 미리 생성 문장 목록
-- 특정 모듈(예: ORDER_API)을 사용하는 미리 생성 문장 조회
SELECT stmt_id,
SUBSTR(stmt_text, 1, 80) AS short_text,
created_by,
created_date,
exec_count
FROM dba_ias_pregen_stmts
WHERE module_name = 'ORDER_API'
ORDER BY created_date DESC;
이 결과는 모듈 단위 영향도 분석, 기능별 SQL 검토, 버그 대응 시 관련 SQL 리스트업 등 다양한 상황에서 유용합니다.
6. 성능 튜닝 관점에서의 활용 전략
6.1 “예상보다 느린 화면”의 숨은 원인 찾기
사용자들이 “특정 화면이 느려졌다”고 느낄 때, DBA는 보통 V$ACTIVE_SESSION_HISTORY나 V$SQL을 먼저 확인합니다. 하지만 화면에서 사용하는 SQL이 대부분 미리 생성된 정적 문장이라면, 해당 SQL이 어떤 모듈에 묶여 있는지, 언제부터 사용되었는지는 DBA_IAS_PREGEN_STMTS가 더 잘 보여줄 수 있습니다. 예를 들어 다음과 같은 흐름이 가능합니다.
- 웹 서버/애플리케이션 로그에서 느린 요청의 모듈명 또는 기능 코드 확인
DBA_IAS_PREGEN_STMTS에서 해당 모듈에 연계된 SQL 목록 조회- 각 SQL을 해시값 또는 텍스트 일부를 기준으로
V$SQL과 조인하여 실제 실행 계획과 통계를 확인 - 튜닝 필요 SQL 선별 후 인덱스·힌트·파라미터 조정 등 수행
6.2 미사용 문장 정리를 통한 관리 비용 절감
오래된 시스템일수록 “설계는 했지만 실제로는 거의 안 쓰는 기능”이 많습니다. 이 기능들이 사용하는 SQL은 대부분 DBA_IAS_PREGEN_STMTS에 그대로 남아 있습니다. 미사용·저사용 SQL을 정리하면 다음과 같은 효과를 기대할 수 있습니다.
- 문장 관리 스크립트, 배치 작업, 배포 파이프라인 단순화
- 코드 리뷰 범위 축소 → QA 및 검증 시간 단축
- 보안 감사 시 점검해야 할 대상 수 감소
6.3 SQL 텍스트 기반 패턴 분석
STMT_TEXT 컬럼이 충분한 길이로 제공된다면, 패턴 분석을 통해 다음과 같은 인사이트도 얻을 수 있습니다.
- 특정 테이블을 과도하게 사용하는 모듈 확인
- 하드 코딩된 리터럴 사용 패턴 파악 → 바인드 변수 전환 후보 선정
- LIKE, NOT IN, 함수 기반 조건 등 비용이 큰 패턴이 많은 모듈 파악
-- LIKE 조건을 많이 사용하는 미리 생성 문장 찾기
SELECT stmt_id,
SUBSTR(stmt_text, 1, 100) AS short_text,
module_name
FROM dba_ias_pregen_stmts
WHERE UPPER(stmt_text) LIKE '% LIKE %';
7. 권한과 보안 측면에서의 고려사항
DBA_IAS_PREGEN_STMTS는 DBA_ 접두사가 붙어 있는 만큼, 일반 계정보다는 시스템을 관리하는 DBA 계정에서 접근하는 것이 일반적입니다. 다음과 같은 보안 관점을 고려하면 좋습니다.
- 애플리케이션 로직에 민감한 SQL이 포함되어 있을 수 있으므로, 불필요한 계정에 SELECT 권한을 부여하지 않는다.
- 감사 로그와 연계할 경우, 누가 어떤 시점에 어떤 SQL을 새로 등록했는지 추적 가능하므로 권한 관리를 명확히 해둔다.
- 개발/테스트 환경에서는 조회 범위를 제한하거나, 마스킹된 형태로 SQL 텍스트를 제공하는 정책도 고려할 수 있다.
8. DBA_IAS_PREGEN_STMTS 활용 베스트 프랙티스
실제 프로젝트·운영 환경에서 쓸 수 있는 활용 팁을 정리하면 다음과 같습니다.
| 활용 시나리오 | 주요 작업 | 기대 효과 |
|---|---|---|
| 미사용 SQL 정리 | LAST_EXECUTED·EXEC_COUNT 기반으로 미사용 문장 선별 | 코드베이스와 메타데이터 단순화, 유지보수 부담 감소 |
| 성능 문제 분석 | 자주 실행되는 문장 파악 후 V$SQL 등과 연동하여 튜닝 | 핫 스팟 SQL 튜닝으로 응답 속도 향상 |
| 모듈별 영향도 분석 | MODULE_NAME 기준으로 SQL 목록 추출 | 버전 업그레이드 시 영향 범위 명확화 |
| 보안·감사 대응 | CREATED_BY·CREATED_DATE 활용 변경 이력 추적 | 예상치 못한 데이터 변경 원인 규명에 도움 |
| 리팩토링 사전 분석 | 테이블·패턴 기반으로 SQL 사용 현황 조사 | DB 스키마 변경 시 리스크 감소 |
9. 오늘 시점에서 이 뷰를 바라보는 현실적인 관점
최근에는 Oracle WebLogic, 클라우드 네이티브 아키텍처, 마이크로서비스 등 다양한 기술 스택이 등장하면서, 과거 IAS 기반 애플리케이션 서버를 직접 운용하는 사례는 줄어들고 있습니다. 그렇다고 해서 DBA_IAS_PREGEN_STMTS와 같은 뷰가 완전히 쓸모없어졌다고 보기는 어렵습니다. 특히 다음과 같은 상황에서는 여전히 중요한 역할을 할 수 있습니다.
- 레거시 Oracle Application Server를 아직 운영 중인 엔터프라이즈 환경
- 과거 IAS 기반 애플리케이션에서 클라우드/신규 프레임워크로 마이그레이션 중인 프로젝트
- 레거시 시스템의 영향 범위를 분석해야 하는 컨설팅/감리 업무
이런 환경에서는 DBA_IAS_PREGEN_STMTS 같은 뷰를 이해하고 있느냐가 “옛날 시스템은 잘 모른다”와 “레거시까지 깊게 보는 전문가”를 가르는 기준이 되기도 합니다.
10. 정리 및 마무리
DBA_IAS_PREGEN_STMTS는 현재 기준으로 봤을 때 다소 마이너한 뷰처럼 보일 수 있지만, 애플리케이션 서버와 데이터베이스가 강하게 결합된 레거시 환경에서는 다음과 같은 가치를 제공합니다.
- 미리 생성된 SQL/PLSQL 문장에 대한 메타데이터 제공
- 모듈 단위 영향도 분석 및 성능 튜닝 후보 발굴
- 미사용/저사용 문장 정리를 통한 관리 비용 감소
- 보안·감사·감리 업무에서의 변경 이력 추적
지금 당장 모든 프로젝트에서 활용되는 뷰는 아니지만, 레거시 시스템을 깊이 이해하고 장기적인 마이그레이션 전략을 설계하려는 DBA·아키텍트라면 반드시 한 번은 짚고 넘어가야 할 주제입니다. 이 글에서 제시한 SQL 예제와 활용 시나리오를 기반으로, 자신의 환경에서 DBA_IAS_PREGEN_STMTS가 어떤 역할을 할 수 있을지 직접 점검해 보길 추천합니다.
참고 자료(출처)
- Oracle Database Reference, Data Dictionary Views (DBA_*/USER_*) 섹션
- Oracle Application Server(Oracle9iAS, Oracle Application Server 10g) Administrator's Guide
- Oracle Database Performance Tuning Guide – SQL Tuning, Shared Pool, AWR 관련 장
