Literal 리터럴 SQL Check 스크립트

haisins

7년 ago

select 	 sql_id AS "SQL ID"
  ,nvl(replace(replace(replace(replace(to_char(substr(replace(sql_text,',','_'),1,2500)),chr(9),' '),chr(10),' '),chr(13),' '),',','_'), '** Not Found **') AS "SQL TEXT"
	,cnt As "version Count"
	,executions as "Exec (Max)"
	,sharable_mem as "Used Memory (bytes)"
	,module as "Representative Client"
from	(
	select	 substr(replace(sql_text,',','_'),1,60) sql_partial
	        ,max(to_char(substr(sql_fulltext,1,2500))) sql_text
		,count(*) cnt
		,sum(sharable_mem) sharable_mem
		,max(hash_value) hash_value
		,max(sql_id) sql_id
		,max(executions) executions
		,max(module) module
	from v$sql
	where executions > 0
	  and executions < 5
	group by substr(replace(sql_text,',','_'),1,60)
	having count(*) >= 10
	order by cnt desc, sharable_mem desc
	)
where rownum <= 123
/

select sql_id AS "SQL ID"

,nvl(replace(replace(replace(replace(to_char(substr(replace(sql_text,',','_'),1,2500)),chr(9),' '),chr(10),' '),chr(13),' '),',','_'), '** Not Found **') AS "SQL TEXT"

,cnt As "version Count"

,executions as "Exec (Max)"

,sharable_mem as "Used Memory (bytes)"

,module as "Representative Client"

from (

select substr(replace(sql_text,',','_'),1,60) sql_partial

,max(to_char(substr(sql_fulltext,1,2500))) sql_text

,count(*) cnt

,sum(sharable_mem) sharable_mem

,max(hash_value) hash_value

,max(sql_id) sql_id

,max(executions) executions

,max(module) module

from v$sql

where executions > 0

and executions < 5

group by substr(replace(sql_text,',','_'),1,60)

having count(*) >= 10

order by cnt desc, sharable_mem desc

)

where rownum <= 123