LCO에 대한 과도한 Pin으로 인한 library cache: mutex X 경합을 해소하기 위해 DBMS_SHARED_POOL.MARKHOT 프로시저 적용을 고려할 수 있습니다.
테스트를 위해 아래와 같이 테이블과 함수를 생성하겠습니다.
-- 1-1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1) AS SELECT ROWNUM FROM XMLTABLE ('1 to 100');
CREATE TABLE t2 (c1) AS SELECT ROWNUM FROM XMLTABLE ('1 to 100');
-- 1-2
CREATE OR REPLACE FUNCTION f1 (i_val IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN i_val;
END;
/
아래와 같이 f1 함수를 사용하는 SQL 문을 100회 수행하겠습니다.
-- 2
DECLARE
v1 NUMBER;
BEGIN
FOR i IN 1 .. 100 LOOP
SELECT MAX (f1 (c1)) INTO v1 FROM t1;
END LOOP;
END;
/
아래는 V$DB_OBJECT_CACHE 뷰와 V$SQL뷰를 조회한 결과입니다. f1 함수와 SQL 문의 Pin 횟수가 100회이므로 PL/SQL 함수를 사용하는 SQL 문을 수행하면 PL/SQL 함수도 함께 Pin되는 것을 알 수 있습니다.
-- 3-1
SELECT owner, name, namespace, pinned_total, property, full_hash_value, addr
FROM v$db_object_cache
WHERE name IN ('F1', 'SELECT MAX (F1 (C1)) FROM T1')
ORDER BY owner, name, property;
OWNER NAME NAMESPACE PINNED_TOTAL PROPERTY FULL_HASH_VALUE ADDR
----- ---------------------------- --------------- ------------ -------- -------------------------------- ----------------
TUNA F1 TABLE/PROCEDURE 103 dd6291623780db0a953664812f4c1dba 00007FFC98CCB9A0
SELECT MAX (F1 (C1)) FROM T1 SQL AREA 1 36719b0ff905d2831187f72eaca494e4 00007FFC98CB7030 --> PARENT
SELECT MAX (F1 (C1)) FROM T1 SQL AREA 101 36719b0ff905d2831187f72eaca494e4 00007FFC98CB58D0 --> CHILD
3 rows selected.
-- 3-2
SELECT sql_id, child_number, parse_calls, address, child_address
FROM v$sql
WHERE sql_text = 'SELECT MAX (F1 (C1)) FROM T1';
SQL_ID CHILD_NUMBER PARSE_CALLS ADDRESS CHILD_ADDRESS
------------- ------------ ----------- ---------------- ----------------
131zr5uqa9574 0 1 00007FFC98CB7030 00007FFC98CB58D0
1 row selected.
sys 유저에서 아래와 같이 DBMS_SHARED_POOL.MARKHOT 프로시저를 수행하겠습니다.
-- 4: sys
EXEC DBMS_SHARED_POOL.MARKHOT (hash => 'dd6291623780db0a953664812f4c1dba', namespace => 1);
EXEC DBMS_SHARED_POOL.MARKHOT (hash => '36719b0ff905d2831187f72eaca494e4', namespace => 0);
재접속 후 아래와 같이 f1 함수를 사용하는 두 SQL 문을 100회씩 수행하겠습니다.
-- 5: reconnect
DECLARE
v1 NUMBER;
BEGIN
FOR i IN 1 .. 100 LOOP
SELECT MAX (f1 (c1)) INTO v1 FROM t1;
SELECT MAX (f1 (c1)) INTO v1 FROM t2;
END LOOP;
END;
/
아래는 V$DB_OBJECT_CACHE 뷰와 V$SQL뷰를 다시 조회한 결과입니다. 앞서 조회한 LCO의 property 값에 HOT이 표시되고, property 값이 HOCOPY1으로 표시되는 LCO가 새로 생성된 것을 볼 수 있습니다. HOTCOPY의 개수는 _kgl_hot_object_copies 파라미터에 의해 결정되며 기본값은 cpu_count / 2로 알려져 있습니다. t1 테이블을 조회하는 SQL 문은 Hot LCO이므로 SQL ID가 다른 새로운 커서가 생성되는 반면, t2 테이블의 조회하는 SQL 문은 Hot LCO인 f1 함수를 사용했지만 새로운 커서가 생성되지 않은 것을 볼 수 있습니다.
-- 6-1
SELECT owner, name, namespace, pinned_total, property, full_hash_value, addr
FROM v$db_object_cache
WHERE name IN ('F1', 'SELECT MAX (F1 (C1)) FROM T1', 'SELECT MAX (F1 (C1)) FROM T2')
ORDER BY owner, name, property;
OWNER NAME NAMESPACE PINNED_TOTAL PROPERTY FULL_HASH_VALUE ADDR
----- ---------------------------- --------------- ------------ -------- -------------------------------- ----------------
TUNA F1 TABLE/PROCEDURE 103 HOT dd6291623780db0a953664812f4c1dba 00007FFC98CCB9A0
TUNA F1 TABLE/PROCEDURE 203 HOTCOPY1 cb884c05ca9dbdcbb35d2b51f7f7ebc7 00007FFC98BED440
SELECT MAX (F1 (C1)) FROM T1 SQL AREA 1 HOT 36719b0ff905d2831187f72eaca494e4 00007FFC98CB7030 --> PARENT
SELECT MAX (F1 (C1)) FROM T1 SQL AREA 101 HOT 36719b0ff905d2831187f72eaca494e4 00007FFC98CB58D0 --> CHILD
SELECT MAX (F1 (C1)) FROM T1 SQL AREA 1 HOTCOPY1 aab94c9808a83f0830f45c309d4b6d65 00007FFC98BE9F10 --> PARENT
SELECT MAX (F1 (C1)) FROM T1 SQL AREA 101 HOTCOPY1 aab94c9808a83f0830f45c309d4b6d65 00007FFC98BE8480 --> CHILD
SELECT MAX (F1 (C1)) FROM T2 SQL AREA 1 e7b5983639afe143bfca644c9e8cceed 00007FFC98BE9BE0 --> PARENT
SELECT MAX (F1 (C1)) FROM T2 SQL AREA 101 e7b5983639afe143bfca644c9e8cceed 00007FFC98BE25F0 --> CHILD
8 rows selected.
-- 6-2
SELECT sql_id, child_number, parse_calls, address, child_address
FROM v$sql
WHERE sql_text IN ('SELECT MAX (F1 (C1)) FROM T1', 'SELECT MAX (F1 (C1)) FROM T2');
SQL_ID CHILD_NUMBER PARSE_CALLS ADDRESS CHILD_ADDRESS
------------- ------------ ----------- ---------------- ----------------
131zr5uqa9574 0 1 00007FFC98CB7030 00007FFC98CB58D0 --> HOT
31x2w62fnqvb5 0 1 00007FFC98BE9F10 00007FFC98BE8480 --> HOTCOPY1
bzkm49kg8tmrd 0 1 00007FFC98BE9BE0 00007FFC98BE25F0
3 rows selected.
sys 유저에서 아래와 같이 DBMS_SHARED_POOL.UNMARKHOT 프로시저를 수행하겠습니다.
-- 7: sys
EXEC DBMS_SHARED_POOL.UNMARKHOT (hash => 'dd6291623780db0a953664812f4c1dba', namespace => 1);
EXEC DBMS_SHARED_POOL.UNMARKHOT (hash => '36719b0ff905d2831187f72eaca494e4', namespace => 0);
아래는 V$DB_OBJECT_CACHE 뷰와 V$SQL뷰를 다시 조회한 결과입니다. HOT LCO의 property 값이 널로 표시되고 HOTCOPY LCO의 값이 OBSCOPY(Obsoleted LCO)로 표시되는 것을 볼 수 있습니다.
-- 8-1
SELECT owner, name, namespace, pinned_total, property, full_hash_value, addr
FROM v$db_object_cache
WHERE name IN ('F1', 'SELECT MAX (F1 (C1)) FROM T1', 'SELECT MAX (F1 (C1)) FROM T2')
ORDER BY owner, name, property;
OWNER NAME NAMESPACE PINNED_TOTAL PROPERTY FULL_HASH_VALUE ADDR
----- ---------------------------- --------------- ------------ -------- -------------------------------- ----------------
TUNA F1 TABLE/PROCEDURE 104 dd6291623780db0a953664812f4c1dba 00007FFC98CCB9A0
TUNA F1 TABLE/PROCEDURE 204 OBSCOPY1 cb884c05ca9dbdcbb35d2b51f7f7ebc7 00007FFC98BED440
SELECT MAX (F1 (C1)) FROM T1 SQL AREA 1 36719b0ff905d2831187f72eaca494e4 00007FFC98CB7030 --> PARENT
SELECT MAX (F1 (C1)) FROM T1 SQL AREA 101 36719b0ff905d2831187f72eaca494e4 00007FFC98CB58D0 --> CHILD
SELECT MAX (F1 (C1)) FROM T1 SQL AREA 1 OBSCOPY1 aab94c9808a83f0830f45c309d4b6d65 00007FFC98BE9F10 --> PARENT
SELECT MAX (F1 (C1)) FROM T1 SQL AREA 101 OBSCOPY1 aab94c9808a83f0830f45c309d4b6d65 00007FFC98BE8480 --> CHILD
SELECT MAX (F1 (C1)) FROM T2 SQL AREA 1 e7b5983639afe143bfca644c9e8cceed 00007FFC98BE9BE0 --> PARENT
SELECT MAX (F1 (C1)) FROM T2 SQL AREA 101 e7b5983639afe143bfca644c9e8cceed 00007FFC98BE25F0 --> CHILD
8 rows selected.
-- 8-2
SELECT sql_id, child_number, parse_calls, address, child_address
FROM v$sql
WHERE sql_text IN ('SELECT MAX (F1 (C1)) FROM T1', 'SELECT MAX (F1 (C1)) FROM T2');
SQL_ID CHILD_NUMBER PARSE_CALLS ADDRESS CHILD_ADDRESS
------------- ------------ ----------- ---------------- ----------------
131zr5uqa9574 0 1 00007FFC98CB7030 00007FFC98CB58D0 --> HOT
31x2w62fnqvb5 0 1 00007FFC98BE9F10 00007FFC98BE8480 --> HOTCOPY1
bzkm49kg8tmrd 0 1 00007FFC98BE9BE0 00007FFC98BE25F0
3 rows selected.
관련 링크