주 콘텐츠로 건너뛰기
📓 Tibero Online Manual Click here 📓

검색

Welcome to Tibero GTS!

바인드 변수 SQL, 리터럴 SQL 성능 비교 예제

문서유형ㅣ기술정보

분야ㅣ튜닝

문서번호ㅣTTUTI017

 

개요

Literal SQL(리터럴 SQL)과 Bind Variable SQL(바인드 변수 SQL)의 차이점과 예제를 통한 성능 비교를 안내합니다.
 
리터럴 SQL을 많이 사용하면 하드 파싱(Hard Parsing) 빈도가 높아지고, Library Cache에서 SQL이 자주 교체되면서 성능에 불리합니다. Shared SQL Area 의 SQL 문 중에서 리터럴 SQL 문들을 찾아서 바인드 변수(Bind Variable)을 이용한 방법으로 바꾸어야 성능 향상에 도움이 됩니다.
 
 

방법

리터럴 SQL 예제

WHERE 구문의 값이 다른 리터럴 SQL 예제입니다. 서로 다른 SQL 문으로 인식되어 하드 파싱을 하게 됩니다. SHARD_POOL 을 FLUSH 하면 공유 SQL 영역/PSM 영역(SQL statements, stored procedures, function, packages, and triggers)이 CLEAR 됩니다. 또한 SHARED POOL에 적재된 딕셔너리 캐시를 삭제하므로 SQL 을 실행하면 하드파싱하게 됩니다.
SQL> CREATE TABLE EMP1(EMPNO NUMBER, ENAME VARCHAR(20));
SQL> INSERT INTO EMP1 SELECT LEVEL,'TIBERO'||LEVEL FROM DUAL CONNECT BY LEVEL <=5; SQL> INSERT INTO EMP1 SELECT LEVEL,'TIBERO'||LEVEL FROM DUAL CONNECT BY LEVEL <=3; SQL> COMMIT;
SQL> SELECT * FROM EMP1;
EMPNO ENAME
---------- --------------------
1 TIBERO1
2 TIBERO2
3 TIBERO3
4 TIBERO4
5 TIBERO5
1 TIBERO1
2 TIBERO2
3 TIBERO3
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 /* LITERAL001 */ COUNT(*) FROM EMP1
WHERE EMPNO = ' || i;
DBMS_OUTPUT.PUT_LINE(v_sql); EXECUTE IMMEDIATE v_sql INTO v_cnt; DBMS_OUTPUT.PUT_LINE(v_cnt); END LOOP;
END;
/
SELECT /* LITERAL001 */ COUNT(*) FROM EMP1
WHERE EMPNO = 1
2SELECT /* LITERAL001 */ COUNT(*)
FROM EMP1
WHERE EMPNO = 2
2
SELECT /* LITERAL001 */ COUNT(*)
FROM EMP1
WHERE EMPNO = 3
2
SELECT /* LITERAL001 */ COUNT(*)
FROM EMP1
WHERE EMPNO = 4
1
SELECT /* LITERAL001 */ COUNT(*)
FROM EMP1
WHERE EMPNO = 5
1

 

V$SQLAREA 에서 수행 쿼리에 대한 정보를 조회할 수 있습니다. 앞에서 실행된 SQL 문장은 전부 다른 SQL 문장으로 인식되어 하드파싱 됨을 알 수 있습니다.

SQL> col sql for a40
SQL> SELECT SUBSTR(sql_text,1,200) "SQL", COUNT(*), SUM(EXECUTIONS) FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'SELECT /* LITERAL001%' GROUP BY SUBSTR(sql_text,1,200)
HAVING COUNT(*) > 0 ORDER BY COUNT(*);
SQL
COUNT(*) SUM(EXECUTIONS)
---------------------------------------- ---------- ---------------
SELECT /* LITERAL001 */ COUNT(*)                 1               1
FROM EMP1
WHERE EMPNO = 4
SELECT /* LITERAL001 */ COUNT(*)                 1               1
FROM EMP1
WHERE EMPNO = 3
SELECT /* LITERAL001 */ COUNT(*)                 1               1
FROM EMP1
WHERE EMPNO = 1SELECT /* LITERAL001 */ COUNT(*) FROM EMP1
WHERE EMPNO = 2
11
SELECT /* LITERAL001 */ COUNT(*)                 1               1
FROM EMP1
WHERE EMPNO = 5

 

바인드 변수 SQL 예제

이번에는 바인드 변수로 처리해서 쿼리를 실행합니다. WHERE 구문의 값이 다른 리터럴 SQL 예제입니다.
SQL> DECLARE
v_sql VARCHAR2(100);
v_cnt NUMBER;
BEGIN
FOR i IN 1..5 LOOP
v_sql := 'SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i';
DBMS_OUTPUT.PUT_LINE(V_SQL ||' <---- '||i);
EXECUTE IMMEDIATE v_sql INTO v_cnt USING i;
DBMS_OUTPUT.PUT_LINE(v_cnt);
END LOOP;
END;
/
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i <---- 1
2
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i <---- 2
2
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i <---- 3
2
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i <---- 4
1
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i <---- 5
1


앞에서 실행된 SQL 문장은 바인드 변수 처리되어 동일한 SQL 문장으로 인식되어 소프트파싱 되고 파싱은 1번만 진행되고, 실행은 5 번 진행되었습니다. 

SQL> COL SQL FOR A55
SQL> SET LINESIZE 90
SQL> SELECT SUBSTR(sql_text,1,200) "SQL", COUNT(*), SUM(EXECUTIONS) 
FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'SELECT /* BIND001%' 
GROUP BY SUBSTR(sql_text,1,200) 
HAVING COUNT(*) > 0
ORDER BY 2;

SQL                                                     COUNT(*)   SUM(EXECUTIONS)
------------------------------------------------------- ---------- ---------------
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i          1               5

1 row selected.

 

 

 

관련 대상