Скачать или смотреть 오라클소프트파싱,하드파싱이란? 리터럴SQL, 바인드변수SQL, Soft Parsing vs Hard Parsing(오라클교육, ORACLE동영상교육, 오라클학원)

오라클소프트파싱,하드파싱이란? 리터럴SQL, 바인드변수SQL, Soft Parsing vs Hard Parsing(오라클교육, ORACLE동영상교육, 오라클학원)

소프트 파싱 vs 하드 파싱
(Soft Parsing vs Hard Parsing)
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에 없으므로 완전히 전부 새로 파싱을 한다는 의미 입니다. SQL 실행의 4단계중 최적화(최적의 실행계획 생성), 로우 소스 생성(로우 소스 트리 생성) 과정을 수행한다는 의미 입니다.

Soft Parsing

소프트 파싱이란 실행하고자 하는 SQL 문장이 이미 Shared Pool/Library Cache에 있으므로 이미 존재하는 SQL에 관련된 정보(파싱트리, 실행계획)를 그대로 재사용 하므로 SQL 실행 4단계중 최적화(최적의 실행계획 생성), 로우 소스 생성(로우 소스 트리 생성) 과정을 생략 합니다. 그래서 대부분의 SQL문장 실행시간은 처음보다 두 번째가 빠르다.

소프트 파싱 되려면 완벽하게 동일한 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을 실행하면 하드파싱하게 된다.

ALTER SYSTEM FLUSH SHARED_POOL;

SET SERVEROUTPUT ON

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문장으로 인식되어 하드파싱 됨을 알수있다.
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

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문장으로 인식되어 소프트파싱 되rh 파싱은 한번만, 실행은 5번 하였다.
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