개요
Litera 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
SQL
복사
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
복사
바인드 변수 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 문장은 바인드 변수 처리되어 동일한 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.
SQL
복사