Oracle 定位没有使用绑定变量的SQL

orleenjrwz

[ora10g@hzmc ~]$ export ORACLE_SID=drb
[ora10g@hzmc ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Sep 25 12:27:49 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table t1 as select sql_text from v$sqlarea;

Table created.

SQL> alter table t1 add sql_text_wo_constants varchar2(1000);

Table altered.

SQL> create or replace function
  2  remove_constants( p_query in varchar2 ) return varchar2
  3  as
  4      l_query long;
  5      l_char  varchar2(1);
  6      l_in_quotes boolean default FALSE;
  7  begin
  8      for i in 1 .. length( p_query )
  9      loop
10           l_char := substr(p_query,i,1);
11           if ( l_char = '''' and l_in_quotes )
12           then
13               l_in_quotes := FALSE;
14           elsif ( l_char = '''' and NOT l_in_quotes )
15           then
16               l_in_quotes := TRUE;
17               l_query := l_query || '''#';
18           end if;
19           if ( NOT l_in_quotes ) then
20               l_query := l_query || l_char;
21           end if;
22       end loop;
23       l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
24       for i in 0 .. 8 loop
25           l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
26           l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
27       end loop;
28       return upper(l_query);
29   end;
30   /

Function created.

SQL>  update t1 set sql_text_wo_constants = remove_constants(sql_text);

4569 rows updated.

SQL> commit;

Commit complete.

SQL> select sql_text_wo_constants, count(*)  from t1
  2  group by sql_text_wo_constants having count(*) > 100  order by 2
  3   /

SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
  COUNT(*)
----------
DELETE FROM MC$DR_BACKUPPIECE_DATAFILE WHERE BSET_KEY=@
      1385

DELETE FROM MC$DR_BACKUPPIECE WHERE BSET_KEY=@
      1385

DELETE FROM MC$DR_BACKUPSET WHERE BS_ID=@ AND SUB_ID=@ AND DB_KEY=@ AND DB_ID=@
AND BSET_KEY=@
      1386

SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
  COUNT(*)