Literal SQL & Bind Variable SQL 사용하기(SQL바인드변수,리터럴)
Literal SQL문을 많이 사용하면 Hard Parsing의 빈도를 높이게 되어 Library Cache내에서 Cache되는 SQL문들이 자주 age out 하게 되므로 주기를 빠르게 하고 Dictionary Cache의 사용율을 높이게 된다. 이러한 이유로 OLTP DB환경에서는 Shared SQL문 중에서 Literal SQL 문들을 찾아서 Bind Variable을 이용한 방법을 사용하도록 해야 한다.
Hard Parse
하드파싱이란 새 SQL 문장이 실행 되는 경우엔 Shared Pool에는 없으므로 완전히 전부 새로 파싱을 한다는 의미이다. 오라클은 Shared Pool에 새로운 SQL문장을 할당 하며 SQL 문장이 문법은 맞는지, 권한이 있는지 등을 검사 하게 됩니다. 이 경우 CPU 사용이 매우 많아 지게 되고 Latch의 사용도 증가 하게 됩니다.
Soft Parse
소프트 파싱이란 스행하고자 하는 SQL 문장이 이미 Shared Pool에 있어 이미 존재하는 SQL에 관련된 정보를 그대로 이용하는 겁니다.
결국 Soft Parse가 되기 위해서는 가능 하면 동일한 SQL 문장을 구사해야 한다.
우선 하드 파싱의 대상에는 어떤 것이 있는지 알아 보자.
- 우선 같은 테이블을 질의 하더라도 사용자 계정이 다른 경우 동일한 SQL 문장으로 간주 되지 않는다.
- SQL문장의 공백이 다른 경우
select * from emp
select * from emp는 다르다.
또한 동일한 질의라도 SQL문장의 대소문자 역시 다르면 이것 역시 하드 파싱의 대상이므로 삼가 해야 한다.
- SQL문장의 라인이 달라도 다르게 인식한다.
아래는 V$SALAREA라는 동적뷰에서 실행된 SQL 조회
select substr(sql_text,1,40) "SQL", count(*),
sum(executions) "총 실행 횟수"
from v$sqlarea
group by substr(sql_text,1,40)
having count(*) > 5
order by 2;
[실습]
SQL> conn / as sysdba
연결되었습니다.
공유 영역을 클리어 합니다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> conn scott/tiger
연결되었습니다.
SQL> select count(*) from emp;
COUNT(*)
----------
14
v$SQLAREA 뷰에서 확인
SQL> conn / as sysdba
SQL> select substr(sql_text,1,40) "SQL", count(*),
2 sum(executions) "총 실행 횟수"
3 from v$sqlarea
4 where sql_text like '%emp%'
5 group by substr(sql_text,1,40)
6 having count(*) > 0
7 order by 2;
SQL COUNT(*) 총 실행 횟수
---------- ----------------------------------
select count(*) from emp 1 1
이하 생략
SQL> conn scott/tiger
연결되었습니다.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) from emp;
COUNT(*)
----------
14
그런 다음 다시 v$SQLAREA에서 확인 하죠^^
SQL> select substr(sql_text,1,40) "SQL", count(*),
2 sum(executions) "총 실행 횟수"
3 from v$sqlarea
4 where sql_text like '%emp%'
5 group by substr(sql_text,1,40)
6 having count(*) > 0
7 order by 2;
SQL
---------------------------------------------------------------------
SQL COUNT(*) 총 실행 횟수
---------- -----------------------------
select count(*) from emp 1 3
이번에는 동일한 SQL 문장인데 공백을 더 넣어서 Hard Parsing이 일어나게 해 보자.
SQL> conn scott/tiger
연결되었습니다.
SQL> select count(*) from EMP;;
COUNT(*)
----------
14
V$sqlarea를 조회해 보자
SQL> select substr(sql_text,1,40) "SQL", count(*),
2 sum(executions) "총 실행 횟수"
3 from v$sqlarea
4 where sql_text like '%emp%'
5 group by substr(sql_text,1,40)
6 having count(*) > 0
7 order by 2;
SQL COUNT(*) 총 실행 횟수
---------- ---------------------------------------
select count(*) from EMP 1 1
select count(*) from emp 1 3
