SQL Tuning Guide는 SQL 파싱이 Syntax Check -> Semantic Check -> Shared Pool Check 순서로 수행된다고 설명하고 있지만 실제로는 Shared Pool Check가 가장 먼저 수행되는 것으로 보입니다.
테스트를 위해 아래와 같이 Shared Pool을 Flush하겠습니다.
-- 1-1
ALTER SYSTEM FLUSH SHARED_POOL;
-- 1-2: SYS
SELECT kglnaobj, kglhdnsd, kglobt03, kglobhd6
FROM x$kglcursor
WHERE kglhdadr != kglhdpar
AND kglnaobj IN ('SELECT * FORM T1', 'SELECT * FROM T1');
no rows selected
아래와 같이 쿼리를 수행하면 2-2번 쿼리는 Syntax 에러, 2-3번 쿼리는 Semantic 에러가 발생합니다.
-- 2-1
DROP TABLE t1 PURGE
-- 2-2
SELECT * FORM T1;
ORA-00923: FROM keyword not found where expected
-- 2-3
SELECT * FROM T1;
ORA-00942: table or view does not exist
X$KCLCURSOR 테이블을 조회하면 커서 LCO 핸들이 생성된 것을 확인할 수 있고, 커서 LCO의 kglobhd6 값이 00이므로 SQL Context가 저장되는 6번 LCO 힙이 생성되지 않은 것을 알 수 있습니다. 참고로 V$SQL_SHARED_MEMORY 뷰는 6번 LCO 힙이 존재하는 커서 LCO만 보여줍니다.
-- 3-1: SYS
SELECT kglnaobj, kglhdnsd, kglobt03, kglobhd6
FROM x$kglcursor
WHERE kglhdadr != kglhdpar
AND kglnaobj IN ('SELECT * FORM T1', 'SELECT * FROM T1');
KGLNAOBJ KGLHDNSD KGLOBT03 KGLOBHD6
---------------- -------- ------------- --------
SELECT * FORM T1 SQL AREA 4pdwmub38gfs3 00
SELECT * FROM T1 SQL AREA 0jjc60pmrntdv 00
2 rows selected.
-- 3-2
SELECT *
FROM v$sql_shared_memory
WHERE sql_text IN ('SELECT * FORM T1', 'SELECT * FROM T1');
no rows selected
파싱 과정에서 Shared Pool Check가 가장 먼저 수행되므로 Syntax 에러나 Semantic 에러가 발생하는 쿼리가 동시에 수행되면 하드 파싱에 의한 라이브러리 캐시 경합이 발생할 수 있습니다. 테스트를 위해 아래와 같이 Sematic 에러를 발생시키는 쿼리를 반복 수행하는 프로시저를 생성하겠습니다.
-- 4
CREATE OR REPLACE PROCEDURE p1
IS
v_time NUMBER := DBMS_UTILITY.GET_TIME;
v1 NUMBER;
BEGIN
WHILE (DBMS_UTILITY.GET_TIME - v_time) / 100 <= 10 LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM T1' INTO v1;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END LOOP;
END;
/
아래 예제는 10개 세션에서 10초동안 Semantic 에러가 발생하는 쿼리를 반복 수행하며 library cache lock 이벤트 대기 횟수를 측정합니다. 실행 결과에서 10개 세션에서 10초동안 library cache lock 이벤트를 2,472회 대기한 것을 확인할 수 있습니다.
-- 5
SET SERVEROUT ON
DECLARE
v_job NUMBER;
v_waits_bf NUMBER;
v_waits_af NUMBER;
PROCEDURE p1 (o_waits OUT NUMBER)
IS
BEGIN
SELECT NVL (MAX (total_waits), 0)
INTO o_waits
FROM v$system_event
WHERE event = 'library cache lock';
END;
BEGIN
p1 (v_waits_bf);
FOR i IN 1 .. 10 LOOP DBMS_JOB.SUBMIT (v_job, 'P1;'); END LOOP;
COMMIT;
DBMS_SESSION.SLEEP (10);
p1 (v_waits_af);
DBMS_OUTPUT.PUT_LINE (v_waits_af - v_waits_bf);
END;
/
2472
관련 링크