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

来源：互联网发布：ansible 管理windows 编辑：程序博客网时间：2024/04/27 23:44

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

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

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

SQL> conn /as sysdbaConnected.SQL> alter system flush shared_pool;System altered.

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

SQL> conn scott/oracleConnected.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 varchar2AS   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