查询未绑定变量的sql

短命女 2022-08-13 19:39 171阅读 0赞

查询未绑定变量的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  
remFile: force\_matching.sql   
remDescription: Identify SQLs that are identical other than for literal values (Force matching candidates)  
rem     
remFrom 'Oracle Performance Survival Guide' by Guy Harrison  
remChapter 6 Page 157  
remISBN: 978-0137011957  
remSee www.guyharrison.net for further information  
rem    
remThis 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;