使用Function查找未使用绑定变量的SQL

如何监控并找出系统中存在的大量的未使用绑定变量的SQL呢？利用TOM大师写的一个函数，我们可以按以下的方法进行查找。

测试环境：10G R2，Red Hat Enterprise Linux Server release 5.2

1.使用DBA用户登录ORACLE，刷新共享池，清除出共享池内的SQL。

SQL> conn /as sysdba
Connected.
SQL> alter system flush shared_pool;

System altered.

2.创建测试表，并且执行没有使用绑定变量的SQL。

SQL> conn scott/oracle
Connected.
SQL> create table t2 (a number);

Table created.

SQL> insert into t2 values(1);

1 row created.

SQL> insert into t2 values(2);

1 row created.

SQL> insert into t2 values(3);

1 row created.

SQL> insert into t2 values(4);

1 row created.

SQL> insert into t2 values(5);

1 row created.

SQL> insert into t2 values(6);

1 row created.

SQL> insert into t2 values(7);

1 row created.

SQL> insert into t2 values(8);

1 row created.

SQL> insert into t2 values(9);

1 row created.

SQL> commit;

Commit complete.

3.使用TOM大师的脚本创建函数。

CREATE OR REPLACE FUNCTION remove_constants (p_query IN varchar2)
   RETURN varchar2
AS
   l_query       long;
   l_char        varchar2 (1000);
   l_in_quotes   boolean DEFAULT FALSE ;
BEGIN
   FOR i IN 1 .. LENGTH (p_query)
   LOOP
      l_char := SUBSTR (p_query, i, 1);

      IF (l_char = '''' AND l_in_quotes)
      THEN
         l_in_quotes := FALSE;
      ELSIF (l_char = '''' AND NOT l_in_quotes)
      THEN
         l_in_quotes := TRUE;
         l_query := l_query || '''#';
      END IF;

      IF (NOT l_in_quotes)
      THEN
         l_query := l_query || l_char;
      END IF;
   END LOOP;

   l_query := TRANSLATE (l_query, '0123456789', '@@@@@@@@@@');

   FOR i IN 0 .. 8
   LOOP
      l_query := REPLACE (l_query, LPAD ('@', 10 - i, '@'), '@');
      l_query := REPLACE (l_query, LPAD (' ', 10 - i, ' '), ' ');
   END LOOP;

   RETURN UPPER (l_query);
END;
/

 

5.复制出一张v$sqlarea的表。

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

Table created.

SQL> commit;

Commit complete.

 

6.找出未使用绑定变量的问题SQL。

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

512 rows updated.

SQL>   SELECT   sql_text_wo_constants, COUNT ( * )
        FROM   t1
    GROUP BY   sql_text_wo_constants
      HAVING   COUNT ( * ) > 5
    ORDER BY   2;

SQL_TEXT_WO_CONSTANTS            COUNT(*)
------------------------------ ----------
INSERT INTO T@ VALUES(@)                9