V$SQL 뷰의 program_id, program_line# 칼럼

2023. 4. 8.·Oracle/Performance

V$SQL 뷰의 program_id 칼럼은 SQL 문이 포함된 PL/SQL 오브젝트의 오브젝트 ID, program_line# 칼럼은 PL/SQL 오브젝트에서 SQL 문이 위치한 라인 번호를 표시합니다. SQL 문이 포함된 PL/SQL 오브젝트를 식별할 수 있는 유용한 정보지만 PL/SQL 오브젝트가 재생성되는 경우 부정확한 정보가 표시될 수 있습니다.

 

테스트를 위해 아래와 같이 p1 프로시저를 생성하고 프로시저를 실행하겠습니다.

-- 1-1
CREATE OR REPLACE PROCEDURE p1
IS
    v1 NUMBER;
BEGIN
    SELECT /*+ TEST */ 1 INTO v1 FROM DUAL;
END;
/

-- 1-2
EXEC p1;

 

아래와 같이 V$SQL 뷰의 program_id 값으로 SQL 문이 포함된 PL/SQL 오브젝트를 식별할 수 있습니다.

-- 2-1
SELECT sql_id, program_id, program_line#, loads, invalidations, executions
  FROM v$sql
 WHERE sql_text = 'SELECT /*+ TEST */ 1 FROM DUAL';

SQL_ID        PROGRAM_ID PROGRAM_LINE# LOADS INVALIDATIONS EXECUTIONS
------------- ---------- ------------- ----- ------------- ----------
fb7abh8zr43xc      89111             5     1             0          1

1개의 행이 선택되었습니다.

-- 2-2
SELECT owner, object_name, object_type
  FROM dba_objects
 WHERE object_id = 89111;

OWNER OBJECT_NAME OBJECT_TYPE
----- ----------- -----------
TUNA  P1          PROCEDURE

1개의 행이 선택되었습니다.

 

p1 프로시저를 삭제한 후 p2 프로시저를 생성하고 프로시저를 실행하겠습니다.

-- 3-1
DROP PROCEDURE p1;

-- 3-2
CREATE OR REPLACE PROCEDURE p2
IS
    v1 NUMBER;
BEGIN
    SELECT /*+ TEST */ 1 INTO v1 FROM DUAL;
END;
/

-- 3-3
EXEC p2;

 

프로시저가 삭제되더라도 프로시저에 포함된 SQL 문에 대한 커서는 무효화되지 않습니다. V$SQL 뷰를 조회하면 커서가 무효화되지 않았고 program_id 값이 동일한 것을 확인할 수 있습니다. 현재 상태에서 program_id 값으로 *_OBJECTS 뷰를 조회하면 결과가 반환되지 않습니다. V$DB_OBJECT_CACHE 뷰에서 LCO를 조회해보면 p2 프로시저와 SQL 커서에 NULL 락이 설정되어 있습니다.

-- 4-1
SELECT sql_id, program_id, program_line#, loads, invalidations, executions
  FROM v$sql
 WHERE sql_text = 'SELECT /*+ TEST */ 1 FROM DUAL';

SQL_ID        PROGRAM_ID PROGRAM_LINE# LOADS INVALIDATIONS EXECUTIONS
------------- ---------- ------------- ----- ------------- ----------
fb7abh8zr43xc      89111             5     1             0          2

1개의 행이 선택되었습니다.

-- 4-2
SELECT owner, object_name, object_type
  FROM dba_objects
 WHERE object_id = 89111;

선택된 레코드가 없습니다.

-- 4-3
SELECT owner, name, type, lock_mode, pin_mode, status
  FROM v$db_object_cache
 WHERE name IN ('P1', 'P2', 'SELECT /*+ TEST */ 1 FROM DUAL');

OWNER NAME                           TYPE         LOCK_MODE PIN_MODE  STATUS
----- ------------------------------ ------------ --------- --------- ------
TUNA  P1                             NON-EXISTENT NONE      NONE      UNKOWN
TUNA  P2                             PROCEDURE    NULL      NONE      VALID
      SELECT /*+ TEST */ 1 FROM DUAL CURSOR       NULL      NONE      VALID  -- parent cursor
      SELECT /*+ TEST */ 1 FROM DUAL CURSOR       NULL      NONE      VALID  -- child cursor

4 행이 선택되었습니다.

 

NULL 락이 설정된 LCO는 공유 풀을 flush해도 해제되지 않습니다. p2 프로시저를 다시 수행하면 커서가 다시 로드되었지만 program_id 값이 변경되지 않을 것을 확인할 수 있습니다.

-- 5-1
ALTER SYSTEM FLUSH SHARED_POOL;

-- 5-2
EXEC p2;

-- 5-3
SELECT sql_id, program_id, program_line#, loads, invalidations, executions
  FROM v$sql
 WHERE sql_text = 'SELECT /*+ TEST */ 1 FROM DUAL';

SQL_ID        PROGRAM_ID PROGRAM_LINE# LOADS INVALIDATIONS EXECUTIONS
------------- ---------- ------------- ----- ------------- ----------
fb7abh8zr43xc      89111             5     2             1          1

1개의 행이 선택되었습니다.

-- 5-4
SELECT owner, name, type, lock_mode, pin_mode, status
  FROM v$db_object_cache
 WHERE name IN ('P1', 'P2', 'SELECT /*+ TEST */ 1 FROM DUAL');

OWNER NAME                           TYPE      LOCK_MODE PIN_MODE STATUS
----- ------------------------------ --------- --------- -------- ------
TUNA  P2                             PROCEDURE NULL      NONE     VALID
      SELECT /*+ TEST */ 1 FROM DUAL CURSOR    NULL      NONE     VALID
      SELECT /*+ TEST */ 1 FROM DUAL CURSOR    NULL      NONE     VALID

3 행이 선택되었습니다.

 

접속을 종료하면 세션이 LCO에 설정한 NULL 락이 해제됩니다. 재접속 후 공유 풀을 flush하고 p2 프로시저를 실행하면 새로운 커서가 생성되고 program_id 값에 p2 프로시저의 오브젝트 ID가 표시됩니다.

-- 6-1
EXIT;

-- 6-2
ALTER SYSTEM FLUSH SHARED_POOL;

-- 6-3
EXEC p2;

-- 6-4
SELECT sql_id, program_id, program_line#, loads, invalidations, executions
  FROM v$sql
 WHERE sql_text = 'SELECT /*+ TEST */ 1 FROM DUAL';

SQL_ID        PROGRAM_ID PROGRAM_LINE# LOADS INVALIDATIONS EXECUTIONS
------------- ---------- ------------- ----- ------------- ----------
fb7abh8zr43xc     150907             5     1             0          1

1개의 행이 선택되었습니다.
저작자표시 비영리 변경금지 (새창열림)
'Oracle/Performance' 카테고리의 다른 글
  • Automatic SQL Transpiler
  • 서브쿼리 팩토링 절과 log file sync 이벤트
  • SQL*Net more data from client 이벤트와 SDU 설정
  • FORALL 문으로 수행한 DML 문의 수행 횟수
정희락
정희락
2007년부터 Oracle Database 성능 최적화에 주력해 왔으며, 현재 한국오라클 Engineered Systems Solution Engineering 팀에서 Solution Engineer로 근무하고 있습니다. 이 블로그는 개인적인 연구 목적으로 운영되며, Oracle 사의 공식 입장을 대변하지 않습니다.
  • 정희락
    TunA
    정희락
  • 전체
    오늘
    어제
    • 분류 전체보기 (199)
      • Oracle (171)
        • SQL (33)
        • PLSQL (10)
        • Performance (74)
        • Administration (37)
        • Installation (3)
        • Utilities (1)
        • JSON (8)
        • Vector (5)
      • Exadata (15)
      • SQL*Plus (2)
      • Linux (5)
      • Resources (6)
  • 블로그 메뉴

    • 홈
    • 태그
    • 방명록
  • 도서

    • 불친절한 SQL 프로그래밍
    • 불친절한 PL/SQL 프로그래밍
  • 링크

    • Connor McDonald
    • Frits Hoogland
    • Jonathan Lewis
    • Julian Dontcheff
    • Julian Dyke
    • Kun Sun
    • Maria Colgan
    • Martin Bach
    • Mike Dietrich
    • Tanel Poder
  • 공지사항

  • 인기 글

  • 태그

    12c
    19c
    21c
    23ai
    case study
  • 최근 댓글

  • 최근 글

  • hELLO· Designed By정상우.v4.10.3
정희락
V$SQL 뷰의 program_id, program_line# 칼럼
상단으로

티스토리툴바