关于使用绑定变量的好处，这里不做任何说明。
作为DBA，如何监控并找出系统中存在的大量的未使用绑定变量的SQL呢？利用TOM大师写的一段脚本，我们可以这样来找。
?
1.测试环境：10G R2，Windows XP SP2
?
2.登陆Oracle，先刷新下共享池。清除出共享池内的SQL。
C:\Documents and Settings\Administrator>set ORACLE_SID=ORCLC:\Documents and Settings\Administrator>sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 4月 22 15:33:33 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options[email protected]>alter system flush shared_pool;系统已更改。
?
3.创建测试表，并且发出大量没有使用绑定变量的SQL。
?
[email protected]>conn scott/tiger已连接。[email protected]> create table t2 ( 2 a number);表已创建。[email protected]>insert into t2 values (1);已创建 1 行。[email protected]>insert into t2 values (2);已创建 1 行。[email protected]>insert into t2 values (3);已创建 1 行。[email protected]>insert into t2 values (4);已创建 1 行。[email protected]>insert into t2 values (5);已创建 1 行。[email protected]>insert into t2 values (6);已创建 1 行。[email protected]>insert into t2 values (7);已创建 1 行。[email protected]>insert into t2 values (8);已创建 1 行。[email protected]>insert into t2 values (9);已创建 1 行。[email protected]>commit;提交完成。
?
4.利用大师的脚本，创建函数。
??
[email protected]>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;[email protected]>/函数已创建。
?
5.复制出一张v$sqlarea的表：
?
[email protected]>conn / as sysdba已连接。[email protected]>edit已写入 file afiedt.buf 1 create table scott.t1 as select sql_text,sql_text sql_text_wo_constants from 2* v$sqlarea[email protected]>/表已创建。
?
6.找出之间发出的大量的未使用绑定变量的问题SQL
?
[email protected]>conn scott/tiger已连接。[email protected]>update t1 set sql_text_wo_constants = remove_constants(sql_text);已更新159行。[email protected]>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[email protected]>col SQL_TEXT_WO_CONSTANTS format a30;[email protected]>/SQL_TEXT_WO_CONSTANTS COUNT(*)------------------------------ ----------INSERT INTO T@ VALUES (@) 9[email protected]>
?
?
7.问题SQL被找出来了！可见insert into?... values....（刚才发出的那些恶劣的sql），被反复拼凑后共执行了9次。每次都是不一样的SQL。共享池就这样被消耗了。这些拼出来的SQL将会很快被清除共享池，接下来再次运行的时候还要被硬解析。性能消耗之大。
关于硬解析在SQL整个执行阶段产生多少开销？以后的文章中，小弟将举一个现实的例子来证明这个开销会有多大。
?