Heim >Datenbank >MySQL-Tutorial >如何获取Oracle share pool中没有使用绑定变量的SQL

如何获取Oracle share pool中没有使用绑定变量的SQL

WBOY
WBOYOriginal
2016-06-07 17:36:11974Durchsuche

网站提供了一个函数remove_constants,来检查共享池中的SQL运行情况,处理思路是将查询条件值变为一个通用标记,如:select * fr

网站提供了一个函数remove_constants,,来检查共享池中的SQL运行情况,处理思路是将查询条件值变为一个通用标记,如:select * from t where object_id=1替换成select * from t where object_id=@ 。

SQL> drop table find_no_bind purge;
SQL> create table t as select * from dba_objects where 1=2;

--创建纪录表
SQL> create table find_no_bind as select sql_text from v$sqlarea;
SQL> alter table find_no_bind add sql_text_copy varchar2(1000);

--创建替换函数,把占位符替换成@
create or replace function
SQL> 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;
/


--先清一下share pool,正式环境不能这么干

SQL> alter system flush shared_pool;


SQL> begin
for i in 1..1000 loop
execute immediate 'select * from t where OBJECT_ID = '||i;
end loop;
end;
/


SQL> update find_no_bind set sql_text_copy = remove_constants(sql_text);

SQL> commit;


SQL> col sql_text_copy format a40
SQL> select sql_text_copy, count(*)
  2    from find_no_bind
  3  group by sql_text_copy
  4  having count(*) > 10;
SQL_TEXT_COPY                              COUNT(*)
---------------------------------------- ----------
SELECT * FROM T WHERE OBJECT_ID = @            1000

 

linux

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn