Literal SQL 조회하는 방법

Oracle에서 OLTP 업무를 위한 SQL 구현시 Literal SQL을 사용하지 말라는 얘기를 한다. 모든 SQL에 일관되게 적용할 수는 없겠지만 몇몇 예외를 제외하고는 Bind Variable을 사용하여 구현하는 것이 좋다.

따라서, DBA들이 개발자에게 Literal SQL을 검색해서 수정 요청을 하는 경우가 많은데 Literal SQL을 조회하는 방법에 대해서 알아보도록 하자.

크게 두가지 방법으로 Literal SQL을 검색한다. 첫번째는 sql_text를 이용한 검색이고, 두번째는 plan_hash_value를 통해서 찾을 수가 있다.

1. sql_text 이용한 검색 방법

사용된 sql의 text를 50자를 짜라서 하나의 데이터로 간주해서 동일한 sql을 찾는 방법이다.
물론 이 방법도 100% 정확하지는 않다. 왜냐하면 50자는 동일하지만 그 다음의 sql_text는 다를 수 있기 때문이다.  다양하게 구현할 수 있겠지만 간략하게 literal sql은 아래와 같다.

SELECT hash_value,
       module,
       first_load_time,
       SUBSTR (sql_text, 1, 50)
FROM   v$sqlarea
WHERE  SUBSTR (sql_text, 1, 50) IN (SELECT SUBSTR (sql_text, 1, 50)
        FROM   v$sqlarea
        HAVING COUNT (*) > 4
        GROUP BY SUBSTR (sql_text, 1, 50))
ORDER BY sql_text;



2. plan_hash_value 이용한 검색 방법

동일한 sql 이지만 literal 을 사용하여 다른 sql 으로 시스템에 사용된 sql은 대부분 동일한 실행계획을 가질 확률이 높다. 따라서 plan_hash_value을 이용하여 Literal SQL을 찾을 수도 있다.
plan_hash_value를 통해서 찾은 sql도 100% 다 Literal SQL은 아니고 Literal SQL candidates할 수 있겠다.


select *    
from   (select PLAN_HASH_VALUE hash,
               count(PLAN_HASH_VALUE) cnt ,
               substr(sql_text, 0, 50)
        from   v$sql
        where  plan_hash_value > 0
        group by PLAN_HASH_VALUE , substr(sql_text, 0, 50)
        order by count(PLAN_HASH_VALUE) )
where  cnt > 5

그 밖에 좋은 방법이 있다면 공유를 해주시기 바랍니다.