리터럴 sql 조회 쿼리

OS환경 : Oracle Linux 6.8 (64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : 리터럴 sql 검색 쿼리, literal sql 조회

Oracle에서 OLTP 업무를 위한 SQL 구현시 Literal SQL을 사용하지 말라는 얘기를 함

모든 SQL에 일관되게 적용할 수는 없겠지만 몇몇 예외를 제외하고는 바인드 변수를 사용해서 구현하는 것이 좋음

아래는 리터럴 sql을 조회하는 쿼리임(10g 부터 사용가능)

SQL> 
select *
from (
  select parsing_schema_name, sql_id, sql_text, executions
       , sum(executions) over (partition by force_matching_signature ) executions_sum
       , row_number() over (partition by force_matching_signature order by sql_id desc) rnum
       , count(*) over (partition by force_matching_signature ) cnt
       , force_matching_signature
  from   gv$sqlarea s
  where  force_matching_signature != 0
)
where  cnt > 5
--and    rnum = 1
order by cnt desc, sql_text;
 
PARSING_SCHEMA_NAME           SQL_ID
------------------------------ -------------
SQL_TEXT
---------------------------------------------------------------------------------
EXECUTIONS EXECUTIONS_SUM    RNUM        CNT FORCE_MATCHING_SIGNATURE
---------- -------------- ---------- ---------- ------------------------
SYS                   52kd8b19d979d
SELECT SUM(COUNT) FROM GV$GOLDENGATE_CAPABILITIES WHERE NAME LIKE 'DBENCRYPTION'
     1        8       3          8           1.1803E+19
 
SYS                   1qhy295stgt94
SELECT SUM(COUNT) FROM GV$GOLDENGATE_CAPABILITIES WHERE NAME LIKE 'DBLOGREADER'
     1        8       7          8           1.1803E+19

또는 아래쿼리 사용

SQL> 
select substrb(RPAD(sql_text,200,' '), 1, 200) "SQL",
count(*) "Count",
sum(executions) "TotExecs",
sum(sharable_mem) "Mem",
to_char(to_date(min(first_load_time), 'YYYY.MM.DD HH24:MI:SS'), 'YYYY.MM.DD HH24:MI:SS') "Start Time",
to_char(to_date(max(first_load_time), 'YYYY.MM.DD HH24:MI:SS'), 'YYYY.MM.DD HH24:MI:SS') "End TIme",
max(hash_value) "Hashval"
from v$sql
where executions < 5
group by substrb(RPAD(sql_text,200,' '), 1, 200)
having count(*) > 30
order by 2 desc;
 
SQL
-----------------------------------------------------------------------------------------------------------------
     Count   TotExecs         Mem Start Time         End TIme            Hashval
---------- ---------- ---------- ------------------- ------------------- ----------
select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ no_expand_table(t) index_rs(t)   no_parallel(t) 
no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_samplin
       284      590     6721114 2019.02.09 06:00:05 2019.02.11 22:00:11 4215379998


1 row selected.

참조 : 

https://adenkang.tistory.com/34

https://cafe.naver.com/prodba/50512

https://cafe.naver.com/dbian/61