oracle查询各个池的使用率,ORACLE 查询共享池中SQL运行情况 (是否绑定变量)

一、执行多次查询：

SQL> begin

for i in 1..100 loop

execute immediate 'select * from emp where empno=' || i;

end loop;

end;

/

二、创建测试表

SQL> create table test as select sql_text from v$sqlarea;

Table created

三、添加字段

SQL> alter table test add constraintname 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) loop

8  l_char := substr(p_query, i, 1);

9  if (l_char = '''' and l_in_quotes) then

10  l_in_quotes := false;

11  elsif(l_char = '''' and not l_in_quotes) then

12  l_in_quotes := true;

13  l_query := l_query || '''#';

14

15  end if;

16  if(not l_in_quotes) then

17  l_query := l_query || l_char;

18  end if;

19  end loop;

20  l_query := translate(l_query, '0123456789','@@@@@@@@@@');

21  for i in 0..8 loop

22  l_query := replace(l_query, lpad('@',10-i,'@'), '@');

23  l_query := replace(l_query, lpad(' ',10-i,' '), ' ');

24  end loop;

25  return upper(l_query);

26  end;

27  /

Function created

PL/SQL procedure successfully completed

五、更新数据

SQL> update test set constraintname = remove_constants(sql_text);

1263 rows updated

SQL> commit;

Commit complete

六查询、

SQL> select constraintname ,count(*) from test group by constraintname having count(*) > 50 order by 2;

CONSTRAINTNAME                                                                     COUNT(*)

-------------------------------------------------------------------------------- ----------

SELECT * FROM EMP WHERE EMPNO=@                                                         100

谓词条件被@代替，通过此函数，可以较为容易的找到哪些SQL没有绑定变量。

阅读(1141) | 评论(0) | 转发(0) |