Oracle Hint, Tuning 강좌1.5 Literal SQL & Bind Variable SQL

(Soft Parsing/Hard Parsing)

탑크리에듀 교육센터

1.5 Literal SQL & Bind Variable SQL (Soft Parsing/Hard Parsing)

리터럴 (Literal) SQL 이란 ? SQL 문장 작성시 WHERE 절의 비교되는 값에 문자 / 숫자 상수값을 하드코딩해서 작성한 것을 말하며 , Bind Vari-

able 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 문장이 문법은 맞는지 (syntax Check), 테이블 및 칼럼은 존재하는지 , 권한이 있는지 등 (Semantic Check) 을 Dictionary Cache 를 이용하여 검사하게 된다 . 이 경우 CPU 사용이 매우 많아 지고 래치 (Latch, 가벼운 락 ,

SGA 메모리 구조에 대한 배타적인 접근 , SGA 의 특정영역을 탐색하거나 변경하고자 하는 프로세스는 반드시 해당 영역을 관장하는 latch 를 획득해야 한다 ) 의 사용도 증가하게 된다 .

Soft Parsing

소프트 파싱이란 실행하고자 하는 SQL 문장이 이미 Shared Pool/Library Cache 에 있으므로 이미 존재하는 SQL 에 관련된 정보 ( 파싱트리 ,

실행계획 ) 를 그대로 재사용 하는 것이다 . 그래서 대부분의 SQL 문장 실행시간은 처음보다 두 번째가 빠르다 .

Soft Parse 가 되기 위해서는 완벽하게 동일한 SQL 문장을 구사해야 하는데 하드 파싱의 대상에는 어떤 것이 있는지 알아 보자 .

- 같은 테이블을 질의 하더라도 사용자 계정이 다른 경우 다른 SQL 문장으로 간주 .

- 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 영역 /PLSQL 영역 (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 = 1SELECT COUNT(*) FROM MYEMP1 WHERE EMPNO = 2SELECT COUNT(*) FROM MYEMP1 WHERE EMPNO = 3SELECT COUNT(*) FROM MYEMP1 WHERE EMPNO = 4SELECT 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 1SELECT /*+ LITERAL */ COUNT(*) FROM MYEMP1 WHERE EMPNO = 5 1 1SELECT /*+ LITERAL */ COUNT(*) FROM MYEMP1 WHERE EMPNO = 1 1 1SELECT /*+ LITERAL */ COUNT(*) FROM MYEMP1 WHERE EMPNO = 2 1 1SELECT /*+ 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 = :1SELECT /*+ BIND */COUNT(*) FROM MYEMP1 WHERE EMPNO = :1SELECT /*+ BIND */COUNT(*) FROM MYEMP1 WHERE EMPNO = :1SELECT /*+ BIND */COUNT(*) FROM MYEMP1 WHERE EMPNO = :1SELECT /*+ BIND */COUNT(*) FROM MYEMP1 WHERE EMPNO = :1 -- 위에서 실행된 SQL 문장은 바인드 변수처리되어 동일한 SQL 문장으로 인식되어 소프트파싱 되 rh 파싱은 한번만 , 실행은 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

UNITY3D, C# 기초유니티 게임 프로그래밍 ( 주말주간 ( 토 / 일 ))고급자바스크립트 프로그래밍 for Node.JS, An-

gularJS2, React( 주말주간 ( 토 / 일 ))스프링 , 마이바티스를 이용한 통합구현 재직자향상과정 ( 평일주간 )

이번 달 확정 강좌

클릭하시면 해당 페이지로 연결됩니다 .

감사합니다 .

탑크리에듀 교육센터02-851-4790

www.topcredu.co.kr