SQL 연산자 및 함수에 대한 메타데이터 표시

SQL 연산자 및 함수 메타데이터 개요

Oracle 데이터베이스에서 SQL 연산자 및 함수에 대한 메타데이터는 데이터 사전 뷰를 통해 접근할 수 있습니다. 이 메타데이터를 활용하면 쿼리 최적화, 데이터 검증, 사용자 인터페이스 개발 등 다양한 작업을 효율적으로 수행할 수 있습니다. 이 문서에서는 주요 데이터 사전 뷰와 함께 메타데이터를 조회하고 활용하는 구체적인 방법을 예제와 함께 설명합니다.

주요 데이터 사전 뷰

  • ALL_ARGUMENTS: 함수 및 프로시저의 인자에 대한 정보
  • ALL_PROCEDURES: 저장된 프로시저 및 함수에 대한 정보
  • ALL_SOURCE: PL/SQL 코드 소스 정보
  • DBA_HIST_SQL_COMMAND: SQL 명령어에 대한 내역 정보
  • V$SQL: 공유 풀에 캐시된 SQL 명령어 정보
  • V$SQLFN_METADATA: SQL 함수의 메타데이터

SQL 함수 메타데이터 조회

ALL_ARGUMENTS 뷰를 사용하여 SQL 함수의 인자, 데이터 타입, 이름 등의 정보를 조회할 수 있습니다. 다음은 예제입니다.


SELECT
    OWNER,
    OBJECT_NAME,
    ARGUMENT_NAME,
    DATA_TYPE,
    POSITION
FROM
    ALL_ARGUMENTS
WHERE
    OBJECT_NAME = 'UPPER'  -- 예시: UPPER 함수 메타데이터 조회
ORDER BY
    POSITION;

실행 결과 예시:

OWNER OBJECT_NAME ARGUMENT_NAME DATA_TYPE POSITION
----- ------------- --------------- --------- --------
SYS   UPPER         VARCHAR2      1
SYS   UPPER         RETURN          VARCHAR2      0

위 결과에서 UPPER 함수의 인자로 VARCHAR2 타입의 인자가 하나 있고, 반환 타입 역시 VARCHAR2임을 확인할 수 있습니다. POSITION은 인자의 순서를 나타냅니다. ‘RETURN’은 반환 값을 나타냅니다.

SQL 명령어 내역 조회

DBA_HIST_SQL_COMMAND 뷰는 과거 실행되었던 SQL 명령어에 대한 정보를 제공합니다. 명령어 텍스트, 실행 횟수, 사용자 정보 등을 확인할 수 있습니다.


SELECT
    COMMAND_NAME,
    COUNT(*)
FROM
    DBA_HIST_SQL_COMMAND
GROUP BY
    COMMAND_NAME
ORDER BY
    COUNT(*) DESC;

실행 결과 예시:

COMMAND_NAME COUNT(*)
------------- --------
SELECT          1234
INSERT           567
UPDATE          890

이 쿼리는 각 명령어 이름별 실행 횟수를 보여줍니다. 이를 통해 어떤 SQL 명령어들이 데이터베이스에서 가장 빈번하게 사용되는지 파악할 수 있습니다.

공유 풀 SQL 정보 조회

V$SQL 뷰는 공유 풀에 캐시된 SQL 명령어에 대한 정보를 제공합니다. CPU 시간, 디스크 읽기 횟수, 실행 계획 해시 값 등 쿼리 성능 분석에 유용한 정보가 포함되어 있습니다.


SELECT
    SQL_ID,
    SQL_TEXT,
    CPU_TIME,
    DISK_READS,
    EXECUTIONS
FROM
    V$SQL
WHERE
    SQL_TEXT LIKE '%EMPLOYEES%'
ORDER BY
    CPU_TIME DESC;

실행 결과 예시:

SQL_ID        SQL_TEXT                                CPU_TIME  DISK_READS EXECUTIONS
------------- -------------------------------------- -------- ----------- ----------
b123abc456def SELECT * FROM EMPLOYEES WHERE ...      1234567       890        1000
c789ghi012jklm SELECT COUNT(*) FROM EMPLOYEES ...     456789        123        2000

위 결과에서 SQL_ID는 쿼리를 식별하는 고유 ID이며, SQL_TEXT는 SQL 명령어 텍스트입니다. CPU_TIME은 쿼리 실행에 소요된 CPU 시간, DISK_READS는 쿼리 실행 중 발생한 디스크 읽기 횟수, EXECUTIONS는 쿼리 실행 횟수를 나타냅니다. CPU 시간이 높은 쿼리를 우선적으로 튜닝하는 것이 좋습니다.

V$SQLFN_METADATA 뷰를 사용한 SQL 함수의 메타데이터 정보 확인

Oracle 23ai 이상에서는 함수 시맨틱 정보는 V$SQLFN_METADATA 뷰를 통해서 확인할 수 있습니다.


SELECT
    function_name, returns_null_on_null_input
FROM
    V$SQLFN_METADATA;

V$SQLFN_METADATA 뷰에는 다음과 같은 정보가 포함됩니다.

  • function_name: 함수의 이름
  • function_type: 함수의 유형 (예: ‘BUILTIN’, ‘USER_DEFINED’)
  • implementation_type: 구현 유형 (예: ‘SQL’, ‘JAVA’)
  • invoking_rights: 호출 권한 (예: ‘DEFINER’, ‘INVOKER’)
  • is_deterministic: 함수가 결정적인지 여부 (예: ‘Y’, ‘N’)
  • parallel_enable: 병렬 처리가 가능한지 여부 (예: ‘Y’, ‘N’)
  • aggregate_enable: 집계 함수인지 여부 (예: ‘Y’, ‘N’)

실무 활용 예시

다음은 데이터베이스에서 가장 많은 디스크 I/O를 발생시키는 SQL 명령어를 찾는 예시입니다.


SELECT
    SQL_ID,
    SQL_TEXT,
    DISK_READS
FROM
    V$SQL
ORDER BY
    DISK_READS DESC
FETCH FIRST 10 ROWS ONLY;

이 쿼리를 통해 디스크 I/O가 많은 SQL 명령어를 찾은 후 해당 쿼리의 실행 계획을 분석하고, 인덱스 추가 또는 쿼리 재작성 등의 튜닝 작업을 수행할 수 있습니다.

주의사항

  • 데이터 사전 뷰는 시스템 권한이 필요할 수 있습니다. SELECT ANY DICTIONARY 또는 SELECT_CATALOG_ROLE 등의 롤을 통해 권한을 부여받아야 합니다.
  • V$ 뷰는 동적인 정보를 제공하므로, 데이터베이스 활동에 따라 결과가 달라질 수 있습니다.
  • 히스토리 뷰는 AWR (Automatic Workload Repository) 설정을 통해 보관 기간 및 스냅샷 간격을 설정할 수 있습니다.

결론

SQL 연산자 및 함수에 대한 메타데이터를 활용하면 데이터베이스 성능을 개선하고, 응용 프로그램을 효과적으로 개발할 수 있습니다. 이 문서에서 제시된 데이터 사전 뷰와 예제를 통해 Oracle 데이터베이스 전문가로서 역량을 강화하고, 실무에 적용해 보세요.

위로 스크롤