Find similar SQL statements
1. Find similar SQL statements using gv$open_cursor
- select saddr, sid, user_name, address,hash_value,sql_id, sql_text
- from gv$open_cursor
- where sid in
- (select sid from v$open_cursor
- group by sid having count(*) > &threshold);
Added sql_id and sort by sql_text to find similar sql's
- --
- -- Added sql_id and sort by sql_text to find similar sql's
- --
- select sql_id, sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
- group by sql_id, sql_text, user_name
- order by sql_text desc, count(*) desc;
Find SQL_FULL_TEXT by SQL_ID
- --
- -- Find SQL_FULL_TEXT by SQL_ID
- --
- select s.sql_id, s.sql_fulltext, s.sql_text
- /*,to_char(substr(s.sql_fulltext, 1, 32767)) as sql_fulltext*/
- from v$sql s
- where s.sql_id = '&s_sql_id'
2. Find similar SQL statements by first 80 characters in SQL_TEXT
- SELECT substr(sql_text, 1, 80), COUNT(1)
- , round(SUM(elapsed_time) / power(10, 6), 3) as total_elapsed_time
- FROM v$sql
- WHERE 1 = 1
- --and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
- --and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%'
- GROUP BY substr(sql_text, 1, 80)
- HAVING COUNT(1) > 10
- ORDER BY 2 desc
2.1. Find similar SQL statements by first 80 characters in SQL_TEXT which included specified tables:
- SELECT substr(s.sql_text, 1, 80), COUNT(1)
- , round(SUM(elapsed_time) / power(10, 6), 3) as total_elapsed_time
- FROM v$sql s
- join (
- select owner, object_name from dba_objects
- where owner = 'SCOTT'
- and object_type = 'TABLE'
- and (
- object_name like upper('emp%')
- or
- object_name like upper('sal%')
- or
- object_name in ('TAB1', 'TAB2', 'TAB3')
- )
- ) o
- on o.owner = s.parsing_schema_name
- WHERE 1 = 1
- and upper(sql_fulltext) like '%' || upper(o.object_name) || '%'
- and parsing_schema_name = 'SCOTT'
- --and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
- --and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%'
- GROUP BY substr(s.sql_text, 1, 80)
- HAVING COUNT(1) > 10
- ORDER BY 2 desc
2.3. Find similar SQL statements that have only one execution to see whether they are similar using V$SQLSTATS:
- SELECT SUBSTR(SQL_TEXT, 1, 80), COUNT(*)
- FROM V$SQLSTATS
- WHERE EXECUTIONS < 4
- GROUP BY SUBSTR(SQL_TEXT, 1, 80)
- HAVING COUNT(*) > 1
- ORDER BY 2 DESC;
3. Find SQL_FULL_TEXT by first 80 characters of SQL_TEXT
- --
- -- Find SQL_FULL_TEXT by first 80 characters of SQL_TEXT
- --
- select s.sql_id,
- round(s.sharable_mem / 1024 / 1024, 2) as sharable_mem,
- round(s.persistent_mem / 1024 / 1024, 2) as persistent_mem,
- round(s.runtime_mem / 1024 / 1024, 2) as runtime_mem,
- substr(sql_text, 1, 80), s.sql_fulltext
- /*,to_char(substr(s.sql_fulltext, 1, 32767)) as sql_fulltext*/
- from v$sql s
- where s.sql_text like '%' || '&s_sql_text' || '%'
- order by dbms_lob.getlength(s.sql_fulltext) desc
Find the SQL statements with hard parses
Script copied from forum topic www.orafaq.com - how to find the SQL statement which have many hard parse and modified for personal needs.
If you want to find top sql's with literals, then you can do that with the following script:
- -- E. Nossova, Product TuTool : www.tutool.de
-
- set pagesize 0
- set feedback off
- set verify off
- set linesize 180
-
- col nline print newline
- col force_match_sig format 9999999999999999999999999
- col pct format 99990.99
-
- /* reports top SQL's with literals from the sqlarea,
- input parameters:
- min_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
- format,
- default: trunc(sysdate) 00:00:00,
- max_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
- format,
- default: sysdate,
- top_n - the number of the top sql's
- (default: 10) */
-
- define min_first_load_time='&min_first_load_time'
- define max_first_load_time='&max_first_load_time'
- define top_n='&top_n'
-
- select 'Force Matching Signature='||t.force_match_sig||', Count='||max(t.cnt)||', PCT='||max(t.pct)||'%, Min. Username='||min(s.username)||', Max. Username='||max(s.username)||', Min. First Load Time='||max(min_first_load_time)||', Max. First Load Time='||max(max_first_load_time), max(sql_text) nline from
- (select u.username, a.force_matching_signature force_match_sig, a.sql_text
- from v$sql a, dba_users u
- where
- a.parsing_user_id = u.user_id and
- to_date(a.first_load_time,'yyyy-mm-dd/hh24:mi:ss') between
- to_date(nvl('&min_first_load_time',to_char(trunc(sysdate) /*- 1/24*/,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
- to_date(nvl('&max_first_load_time',to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
- a.force_matching_signature != 0 and
- a.exact_matching_signature != 0 and
- a.force_matching_signature != a.exact_matching_signature ) s,
- (select * from
- (select
- force_matching_signature force_match_sig,
- count(*) cnt,
- min(first_load_time) min_first_load_time,
- max(first_load_time) max_first_load_time,
- round((ratio_to_report(count(*)) over ())*100, 2) pct
- from v$sql
- where
- to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss') between
- to_date(nvl('&min_first_load_time',to_char(trunc(sysdate) /*- 1/24*/,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
- to_date(nvl('&max_first_load_time',to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
- force_matching_signature != 0 and
- exact_matching_signature != 0 and
- force_matching_signature != exact_matching_signature
- -- Excluded from report SQL Tuning statements
- and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
- and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%'
- group by force_matching_signature
- order by 2 desc
- )
- where
- rownum <= nvl(abs('&top_n'),10)) t
- where
- s.force_match_sig = t.force_match_sig
- group by t.force_match_sig
- order by max(t.cnt) desc
- /
-
-
-
-
- undefine min_first_load_time
- undefine max_first_load_time
- undefine top_n
-
- set feedback on
- set verify on
- set linesize 80
Input substitution variables:
- /* reports top SQL's with literals from the sqlarea,
- input parameters:
- min_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
- format,
- default: trunc(sysdate) 00:00:00,
- max_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
- format,
- default: sysdate,
- top_n - the number of the top sql's
- (default: 10) */
-
- Enter value for min_first_load_time: 11.01.2018 00:00:00
-
- Enter value for max_first_load_time: 11.01.2018 23:59:59
-
- Enter value for top_n: 10
Output:
- Force Matching Signature=15954354564733902021, Count=317, PCT=14.43%, Min. Username=SCOTT, Max. Username=SCOTT, Min. First Load Time=2018-01-11/01:00:04, Max. First Load Ti
- me=2018-01-11/09:00:14
- select count(*) from user_sequences where upper(sequence_name) = upper('SQz6zSqA41Lx0AAAE7MhJ30bj4')
- ...
Get all sql_id versions of matching SQLs by "Force Matching Signature":
- set pagesize 0
- set feedback on
- set verify on
-
- select s.sql_id, s.sql_fulltext, s.sql_text, s.sharable_mem, s.persistent_mem, s.runtime_mem
- from v$sql s
- where s.force_matching_signature = '&force_matching_signature'
- order by dbms_lob.getlength(s.sql_fulltext) desc;
Input substitution variables:
- Enter value for force_matching_signature: 15954354564733902021