如何监控并找出系统中存在的大量的未使用绑定变量的SQL

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

 

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

Sql代码  

1. C:\Documents and Settings\Administrator>set ORACLE_SID=ORCL  
2.   
3. C:\Documents and Settings\Administrator>sqlplus / as sysdba  
4.   
5. SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 4月 22 15:33:33 2010  
6.   
7. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
8.   
9.   
10. 连接到:  
11. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production  
12. With the Partitioning, OLAP and Data Mining options  
13.   
14. sys@ORCL>alter system flush shared_pool;  
15.   
16. 系统已更改。  

 

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

 

Sql代码  

1. sys@ORCL>conn scott/tiger  
2. 已连接。  
3. scott@ORCL> create table t2 (  
4.   2   a number);  
5.   
6. 表已创建。  
7.   
8. scott@ORCL>insert into t2 values (1);  
9.   
10. 已创建 1 行。  
11.   
12. scott@ORCL>insert into t2 values (2);  
13.   
14. 已创建 1 行。  
15.   
16. scott@ORCL>insert into t2 values (3);  
17.   
18. 已创建 1 行。  
19.   
20. scott@ORCL>insert into t2 values (4);  
21.   
22. 已创建 1 行。  
23.   
24. scott@ORCL>insert into t2 values (5);  
25.   
26. 已创建 1 行。  
27.   
28. scott@ORCL>insert into t2 values (6);  
29.   
30. 已创建 1 行。  
31.   
32. scott@ORCL>insert into t2 values (7);  
33.   
34. 已创建 1 行。  
35.   
36. scott@ORCL>insert into t2 values (8);  
37.   
38. 已创建 1 行。  
39.   
40. scott@ORCL>insert into t2 values (9);  
41.   
42. 已创建 1 行。  
43.   
44. scott@ORCL>commit;  
45.   
46. 提交完成。  

 

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

  

Sql代码  

1. scott@ORCL>edit  
2. 已写入 file afiedt.buf  
3.   
4.   1  create or replace function  
5.   2  remove_constants( p_query in varchar2 ) return varchar2  
6.   3  as  
7.   4      l_query long;  
8.   5      l_char  varchar2(1000);  
9.   6      l_in_quotes boolean default FALSE;  
10.   7  begin  
11.   8      for i in 1 .. length( p_query )  
12.   9      loop  
13.  10          l_char := substr(p_query,i,1);  
14.  11          if ( l_char = '''' and l_in_quotes )  
15.  12          then  
16.  13              l_in_quotes := FALSE;  
17.  14          elsif ( l_char = '''' and NOT l_in_quotes )  
18.  15          then  
19.  16              l_in_quotes := TRUE;  
20.  17              l_query := l_query || '''#';  
21.  18          end if;  
22.  19          if ( NOT l_in_quotes ) then  
23.  20              l_query := l_query || l_char;  
24.  21          end if;  
25.  22      end loop;  
26.  23      l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );  
27.  24      for i in 0 .. 8 loop  
28.  25          l_query := replace( l_query, lpad('@',10-i,'@'), '@' );  
29.  26          l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );  
30.  27      end loop;  
31.  28      return upper(l_query);  
32.  29* end;  
33. scott@ORCL>/  
34.   
35. 函数已创建。  

 

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

 

Sql代码  

1. scott@ORCL>conn / as sysdba  
2. 已连接。  
3. sys@ORCL>edit  
4. 已写入 file afiedt.buf  
5.   
6.   1  create table scott.t1 as select sql_text,sql_text sql_text_wo_constants from  
7.   2* v$sqlarea  
8. sys@ORCL>/  
9.   
10. 表已创建。  

 

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

 

Sql代码  

1. sys@ORCL>conn scott/tiger  
2. 已连接。  
3. scott@ORCL>update t1 set sql_text_wo_constants = remove_constants(sql_text);  
4.   
5. 已更新159行。  
6.   
7. scott@ORCL>select sql_text_wo_constants, count(*)  
8.   2    from t1  
9.   3   group by sql_text_wo_constants  
10.   4  having count(*) > 5  
11.   5   order by 2;  
12.   
13. SQL_TEXT_WO_CONSTANTS  
14. --------------------------------------------------------------------------------  
15. -------------------------------------------  
16.   COUNT(*)  
17. ----------  
18. INSERT INTO T@ VALUES (@)  
19.          9  
20.   
21.   
22.   
23. scott@ORCL>col SQL_TEXT_WO_CONSTANTS format a30;  
24. scott@ORCL>/  
25.   
26. SQL_TEXT_WO_CONSTANTS            COUNT(*)  
27. ------------------------------ ----------  
28. INSERT INTO T@ VALUES (@)               9  
29.   
30. scott@ORCL>  

 

 

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

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