오라클 Literal SQL 체크하는 방법

오라클에서 SQL을 프로그램에서 이용할 때 2가지 방법이 있습니다. Static SQL 과 Dynamic SQL.

변수를 사용해서 조건절(Where)의 값이 바뀌어도 SQL 이 변하지 않는 것을 Static SQL 이라고 하고,

조건절에 변수를 사용하지 않고 상수를 직접 사용함으로써 상수값이 바뀌면 SQL 도 변하는 경우가 Dynamic SQL 입니다. 이 Dynamic SQL 을 Literal SQL 이라고도 부릅니다.

 

정확히 말하자면, 약간다르긴 한데...  Dynamic SQL은 프로그램개발시에 Dynamic 하게 SQL이 만들어지는 경우에 언급되는 개념이고, Literal SQL 이라는 용어는 주로 DB관리 측면에서 변수를 사용하는 것에 대비해서 말할때 쓰는 개념입니다. Anyway...

 

여러 경우의 수에 따라 SQL문을 다르게 만들어야 하는 경우, 하지만 하나의 프로그램에서 처리해야 하는 경우 Dynamic SQL을 사용합니다. 이 Dynamic SQL이 문제가 될 때가 있습니다. 변수를 사용하지 않기 때문에 Literal SQL을 너무 많이 생성한다는 것이죠.

 

SQL> select *
     from EMP
     where ename = 'SMITH';

SQL> select *
     from EMP
     where ename = 'JONES';

 

위 두 SQL은 우리가 보기엔 같지만, DB가 보기엔 다릅니다. 따라서 Shared Pool 에 파싱정보를 각각 생성합니다. 이런게 많아진다면... 그만큼 Shared Pool 을 많이 사용하겠죠? Shared Pool 공간이 부족해지면 ORA-04031 에러가 발생합니다. 그러면, 이런 Literal SQL 들을 찾아내서 변수를 사용하도록 조치할 필요가 있겠습니다.

 

아래의 SQL로 Literal SQL 을 찾아낼 수 있습니다.

 

select sql_text
from v$sqlarea
where executions = 1       -- 실행횟수가 1번 인 경우
  and parsing_schema_name in ('SCOTT')
order by sql_text;

 

'SCOTT' 유저 대신에 주로 사용하는 업무용 User 명을 넣어주면 됩니다.

in ( ) 대신에  not in ('SYS', 'SYSTEM') 과 같이 해서 sys, system 유저를 제외한 모든 유저를 대상으로 조사할 수도 있겠습니다.

 

 

전체 실행되는 SQL 들중에서 Literal SQL 이 차지하는 비율을 보고 싶으면 아래와 같이 확인할 수 있습니다.

 

/*
  Literal SQL을 얼마나 쓰는지 확인
*/
select a.cnt as total, b.cnt as literal, round(b.cnt/a.cnt*100,2) as percent
from (select count(*) as cnt from v$sqlarea where parsing_schema_name in ('SCOTT')) a,
     (select count(*) as cnt from v$sqlarea where executions = 1 and parsing_schema_name in ('SCOTT')) b
;

Total  Literal  Percent
55156	  32348	   58.65

 

위의 결과에서는 총 55천건의 SQL 중에서 32천건이 Literal SQL 이었네요. 58% 정도가 해당됩니다.

 

이 정보들은 v$sqlarea 라는 뷰에서 정보를 조회합니다. 이 뷰는 DB를 재기동하면 데이터가 없어집니다.

따라서, DB Startup 직후에 실행하면 안되고, 좀 운영하다가 조회해야 합니다.

 

 

     이것도 참고 하세요. ====>  오라클 에러 정보 (ORA-04031)