Digital Recipe
Literal SQL & Bind Variable SQL 본문
Literal SQL & Bind Variable SQL
원본 : http://ojc.asia/bbs/board.php?bo_table=LecOrccleTun&wr_id=110
Literal SQL & Bind Variable SQL(Soft Parsing/Hard Parsing)
리터럴(Literal) SQL이란? SQL문장 작성시 WHERE절의 비교되는 값에 문자/숫자 상수값을 하드코딩해서 작성한 것을 말하며, Bind Variable SQL이란 WHERE절의 특정값을 표시하는 자리에 바인드 변수 형태(:B)로 표시한 것을 말한다.
리터럴 SQL문을 많이 사용하면 하드파싱의 빈도를 높이게 되어 Library Cache내에서 Cache되는 SQL문들이 자주 age out 하게 되므로 주기를 빠르게 하고 Dictionary Cache의 사용율을 높이게 된다. 이러한 Shared SQL Area의 SQL문 중에서 리터럴 SQL 문들을 찾아서 Bind Variable을 이용한 방법으로 바꾸어야 성능향상에 도움이 된다.
Hard Parsing
하드파싱이란 SQL 문장이 처음 실행 되는 경우엔 Shared Pool/Library Cache에 없으므로 완전히 전부 새로 파싱을 한다는 의미이다. 오라클은 Shared Pool에 새로운 SQL문장을 할당 하며 SQL 문장이 문법은 맞는지, 테이블 및 칼럼은 존재하는지, 권한이 있는지 등을 Dictionary Cache를 이용하여 검사하게 되고 이 경우 CPU 사용이 매우 많아 지게 되고 래치(Latch, 가벼운 락, SGA메모리 구조에 대한 배타적인 접근, SGA의 특정영역을 탐색하거나 변경하고자 하는 프로세스는 반드시 해당 영역을관장하는 latch를 획득해야 한다)의 사용도 증가 하게 된다.
Soft Parsing
소프트 파싱이란 실행하고자 하는 SQL 문장이 이미 Shared Pool/Library Cache에 있으므로 이미 존재하는 SQL에 관련된 정보(파싱트리, 실행계획)를 그대로 재사용 하는 것이다. 그래서 대부분의 SQL문장 실행 시간은 처음보다 두번째가 빠르다.
Soft Parse가 되기 위해서는 완벽하게 동일한 SQL 문장을 구사해야 하는데 하드 파싱의 대상에는 어떤 것이 있는지 알아 보자.
- 같은 테이블을 질의 하더라도 사용자 계정이 다른 경우 다른 SQK문장으로 간주.
- SQL문장의 공백이 다른 경우
“select * from emp” 문장과 “select * from emp” 문장은 다르다.
- SQL문장의 라인이 다른 경우
“select *
from emp
where empno = 7369” 문장과 “select * from emp where empno = 7369”문장은 다르다.
- 동일한 질의라도 SQL 문장의 대소문자가 다르면 이것 역시 하드 파싱의 대상이다.
“select * from emp” 문장과 “select * from EMP” 문장은 다르다.
-- SHARD_POOL을 FLUSH하면 공유 SQL영역/PL/SQL영역(SQL statements, stored procedures, function, packages, and triggers)이 CLEAR된다(현재 실행되는 부분은 제외). 또한 SHARED POOL에 적재된 딕셔너리 캐시를 삭제하므로 SQL을 실행하면 하드파싱하게 된다.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
v_sql VARCHAR2(500);
v_cnt NUMBER;
BEGIN
FOR I IN 1..5 LOOP
v_sql := 'SELECT / *+ LITERAL * / COUNT(*) FROM MYEMP1 WHERE EMPNO = ' || i;
DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql INTO v_cnt;
END LOOP;
END;
/
SELECT COUNT(*) FROM MYEMP1 WHERE EMPNO = 1
SELECT COUNT(*) FROM MYEMP1 WHERE EMPNO = 2
SELECT COUNT(*) FROM MYEMP1 WHERE EMPNO = 3
SELECT COUNT(*) FROM MYEMP1 WHERE EMPNO = 4
SELECT COUNT(*) FROM MYEMP1 WHERE EMPNO = 5
-- 위에서 실행된 SQL문장은 전부 다른 SQL문장으로 인식되어 하드파싱 됨을 알수있다.
SQL> SELECT SUBSTR(sql_text,1,60) "SQL", COUNT(*),
SUM(EXECUTIONS) "총실행횟수"
FROM V$SQLAREA
WHERE SQL_TEXT LIKE '%LITERAL%'
GROUP BY SUBSTR(sql_text,1,60)
HAVING COUNT(*) > 0
ORDER BY 2;
SQL COUNT(*) 총실행 횟수
--------------------------------------------------------------------------------------------------------------
SELECT /*+ LITERAL */ COUNT(*) FROM MYEMP1 WHERE EMPNO = 4 1 1
SELECT /*+ LITERAL */ COUNT(*) FROM MYEMP1 WHERE EMPNO = 5 1 1
SELECT /*+ LITERAL */ COUNT(*) FROM MYEMP1 WHERE EMPNO = 1 1 1
SELECT /*+ LITERAL */ COUNT(*) FROM MYEMP1 WHERE EMPNO = 2 1 1
SELECT /*+ LITERAL */ COUNT(*) FROM MYEMP1 WHERE EMPNO = 3 1 1
SQL> DECLARE
v_sql VARCHAR2(500);
v_cnt NUMBER;
BEGIN
FOR i IN 1..5 LOOP
v_sql := 'SELECT / *+ BIND * /COUNT(*) FROM MYEMP1 WHERE EMPNO = :1';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE v_sql INTO v_cnt USING i;
END LOOP;
END;
/
SELECT /*+ BIND */COUNT(*) FROM MYEMP1 WHERE EMPNO = :1
SELECT /*+ BIND */COUNT(*) FROM MYEMP1 WHERE EMPNO = :1
SELECT /*+ BIND */COUNT(*) FROM MYEMP1 WHERE EMPNO = :1
SELECT /*+ BIND */COUNT(*) FROM MYEMP1 WHERE EMPNO = :1
SELECT /*+ BIND */COUNT(*) FROM MYEMP1 WHERE EMPNO = :1
-- 위에서 실행된 SQL문장은 바인드 변수처리되어 동일한 SQL문장으로 인식되어 소프트파싱 되어 파싱은 한번만, 실행은 5번 하였다.
SQL> SELECT SUBSTR(sql_text,1,60) "SQL", COUNT(*),
SUM(EXECUTIONS) "총실행횟수"
FROM V$SQLAREA
WHERE SQL_TEXT LIKE '%BIND%'
GROUP BY SUBSTR(sql_text,1,60)
HAVING COUNT(*) > 0
ORDER BY 2;
SQL COUNT(*) 총실행 횟수
-------------------------------------------------------------------------------------------------------------
SELECT /*+ BIND */COUNT(*) FROM MYEMP1 WHERE EMPNO = :1 1 5
