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개의 행이 선택되었습니다.