如何确定系统中是否存在绑定变量的情况

http://blog.csdn.net/gltyi99/article/details/8307588

如何确定系统中是否存在绑定变量的情况

在出现library cache中latch争用时，我们首先想到SQL语句没有使用绑定变量，那么如何确定那条SQL呢？
谭老师的书上给出了一个例子，这里整理一下：

ASKTOM 网站也提供了一个不错的函数remove_constans()来检查共享池中的SQL 的运行情况.

建立测试环境：
SQL> create table t1 as select sql_text from v$sqlarea;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
      2804

SQL> 
SQL> alter table t1 add sql_text_wo_constants varchar2(1000);

Table altered.

SQL> create or replace function remove_constants( p_query in varchar2 ) return varchar2
  2  as
  3  l_query long;
  4  l_char varchar2(1);
  5  l_in_quotes boolean default FALSE;
  6  begin
  7     for i in 1 .. length( p_query )
  8     loop
  9             l_char := substr(p_query,i,1);
10             
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             
20             if ( NOT l_in_quotes ) then
21                     l_query := l_query || l_char;
22             end if;
23     
24     end loop;
25     l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
26     
27     for i in 0 .. 8 loop
28             l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
29             l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
30     end loop;
31     
32     return upper(l_query);
33  end;
34  /

Function created.

将v$sqlarea 视图中的数据用remove_constants 处理后，更新到t1 表中：
SQL> update t1 set sql_text_wo_constants=remove_constants(sql_text) ;

2804 rows updated.

查出除了谓词条件不同的SQL 语句和他们的执行次数，在这里是查询SQL 没有被重用超过100 次的SQL 语句：
SQL> select sql_text_wo_constants, count(*) from t1 group by sql_text_wo_constants having count(*) > 100 order by 2;

no rows selected

下面来测试一下：
建立一个存储过程循环执行查询操作，例如：
declare
begin
    for i in 1..1000 loop
        execute immediate 'select * from t where id=' || i;
    end loop;
end ;
/

SQL> declare
  2  begin
  3     for i in 1..1000 loop
  4             execute immediate 'select * from t where id=' || i;
  5     end loop;
  6  end ;
  7  /

PL/SQL procedure successfully completed.
这时，我们通过直接查询t1表，是无法得到想要的数据的。
SQL> select sql_text_wo_constants, count(*) from t1 group by sql_text_wo_constants having count(*) > 100 order by 2;

no rows selected

SQL> update t1 set sql_text_wo_constants=remove_constants(sql_text) ;

2804 rows updated.

SQL> select sql_text_wo_constants, count(*) from t1 group by sql_text_wo_constants having count(*) > 100 order by 2;

no rows selected

当发现有libaray cache的latch争用时，首先，我们要将t1表清空。
SQL> truncate table t1;

Table truncated.

SQL> insert into t1 select sql_text from v$sqlarea;
insert into t1 select sql_text from v$sqlarea
            *
ERROR at line 1:
ORA-00947: not enough values

其次，将v$sqlarea性能视图的sql_text内容导入到t1表。
SQL> insert into t1 select sql_text,'' from v$sqlarea;

3845 rows created.
使用remove_constants函数，整理sql_text内容
SQL> update t1 set sql_text_wo_constants=remove_constants(sql_text) ;

3845 rows updated.

最后，使用 查出除了谓词条件不同的SQL 语句和他们的执行次数的查询语句：
SQL> select sql_text_wo_constants, count(*) from t1 group by sql_text_wo_constants having count(*) > 100 order by 2;

SQL_TEXT_WO_CONSTANTS                                COUNT(*)
-------------------------------------------------- ----------
SELECT * FROM T WHERE ID=@                               1000

SQL> 
我们就很容易的确定问题SQL了。

asktom上的函数

create or replace function remove_constants( p_query in varchar2 ) return varchar2 as l_query long; l_char varchar2(1); 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; /