查询未绑定变量的sql

查询未绑定变量的sql 

--10g之前

select substr(sql_text, 1, 40) "SQL", count(*), sum(executions) "TotExecs"
  from v$sqlarea
 where executions < 5
 group by substr(sql_text, 1, 40)
having count(*) > 30
 order by 2 desc;

10g之后v$sql增加了force_matching_signature字段 The signature used when the CURSOR_SHARING parameter is set to FORCE

如果两个sql语句除了字面变量的值之外都是相同的，那么他们将拥有相同的force_matching_signature

rem *********************************************************** 
rem
rem File: force_matching.sql 
rem Description: Identify SQLs that are identical other than for literal values (Force matching candidates)
rem   
rem From 'Oracle Performance Survival Guide' by Guy Harrison
rem Chapter 6 Page 157
rem ISBN: 978-0137011957
rem See www.guyharrison.net for further information
rem  
rem This work is in the public domain NSA 
rem   
rem
rem ********************************************************* 
set pages 1000
set lines 100
set echo on 

column schema format a20
column sql_text format a80 

WITH force_matches AS
       (SELECT force_matching_signature,
               COUNT( * )  matches,
               MAX(sql_id || child_number) max_sql_child,
               DENSE_RANK() OVER (ORDER BY COUNT( * ) DESC)
                  ranking
        FROM v$sql
        WHERE force_matching_signature <> 0
          AND parsing_schema_name <> 'SYS'
        GROUP BY force_matching_signature
        HAVING COUNT( * ) > 5)
SELECT sql_id,  matches, parsing_schema_name schema, sql_text
  FROM    v$sql JOIN force_matches
    ON (sql_id || child_number = max_sql_child)
WHERE ranking <= 10
ORDER BY matches DESC;