找到未使用绑定变量的SQL

找出未使用绑定变量的SQL

关于使用绑定变量的好处，这里不做任何说明。

作为DBA，如何监控并找出系统中存在的大量的未使用绑定变量的SQL呢？利用TOM大师写的一段脚本，我们可以这样来找。

1.测试环境：10G R2，Windows XP SP2

2.登陆Oracle，先刷新下共享池。清除出共享池内的SQL。

C:\Documents and Settings\Administrator>set ORACLE_SID=ORCL

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 4月 22 15:33:33 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@ORCL>alter system flush shared_pool;

系统已更改。

3.创建测试表，并且发出大量没有使用绑定变量的SQL。

sys@ORCL>conn scott/tiger
已连接。
scott@ORCL> create table t2 (
  2   a number);

表已创建。

scott@ORCL>insert into t2 values (1);

已创建 1 行。

scott@ORCL>insert into t2 values (2);

已创建 1 行。

scott@ORCL>insert into t2 values (3);

已创建 1 行。

scott@ORCL>insert into t2 values (4);

已创建 1 行。

scott@ORCL>insert into t2 values (5);

已创建 1 行。

scott@ORCL>insert into t2 values (6);

已创建 1 行。

scott@ORCL>insert into t2 values (7);

已创建 1 行。

scott@ORCL>insert into t2 values (8);

已创建 1 行。

scott@ORCL>insert into t2 values (9);

已创建 1 行。

scott@ORCL>commit;

提交完成。

4.利用大师的脚本，创建函数。

scott@ORCL>edit
已写入 file afiedt.buf

  1  create or replace function
  2  remove_constants( p_query in varchar2 ) return varchar2
  3  as
  4      l_query long;
  5      l_char  varchar2(1000);
  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;
scott@ORCL>/

函数已创建。

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

scott@ORCL>conn / as sysdba
已连接。
sys@ORCL>edit
已写入 file afiedt.buf

  1  create table scott.t1 as select sql_text,sql_text sql_text_wo_constants from
  2* v$sqlarea
sys@ORCL>/

表已创建。

6.找出之间发出的大量的未使用绑定变量的问题SQL

sys@ORCL>conn scott/tiger
已连接。
scott@ORCL>update t1 set sql_text_wo_constants = remove_constants(sql_text);

已更新159行。

scott@ORCL>select sql_text_wo_constants, count(*)
  2    from t1
  3   group by sql_text_wo_constants
  4  having count(*) > 5
  5   order by 2;

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



scott@ORCL>col SQL_TEXT_WO_CONSTANTS format a30;
scott@ORCL>/

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

scott@ORCL>

7.问题SQL被找出来了！可见insert into ... values....（刚才发出的那些恶劣的sql），被反复拼凑后共执行了9次。每次都是不一样的SQL。共享池就这样被消耗了。这些拼出来的SQL将会很快被清除共享池，接下来再次运行的时候还要被硬解析。性能消耗之大。

关于硬解析在SQL整个执行阶段产生多少开销？以后的文章中，小弟将举一个现实的例子来证明这个开销会有多大。

找到未使用绑定变量的SQL

相关推荐