如何监控并找出系统中存在的大量的未使用绑定变量的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