客户反馈，Oracle重启库操作后，监控告警出现pin比例低于25%

根据Oracle体系结构的理解，重启库后，硬解析及buffer命中率肯定有一段时间低。

生成不同时段的AWR报告：不要生成rac awr，其中没有实例级别的实例缓存命中指标，使用@?/rdbms/admin/awrrpt.sql

Instance Efficiency Percentages (Target 100%)

Execute to Parse %: 29.76

 

发现客户数据库，存在硬解析很高的情况：

1.最完美的解决方式，修改应用程序，使用绑定变量；

2.修改数据库参数：cursor_sharing= force，bug多，除非特殊情况；

3.本次系统负载不高，仅仅只是监控告警，如果能得到解析的SQL文本就更好了，如下基于此需求，列出SQL文本。

 

3.1 SQL 公司大牛提供

--解析高的SQL

select substr(a.SQL_TEXT, 1, 50), count(*)

from v$sql a

where last_load_time like '2015-11-12%' and first_load_time like '2015-11-12%'

group by substr(a.SQL_TEXT, 1, 50)

having count(*)>10

order by count(*);

 

3.2 SQL审核提供

根据v$sql中exact_matching_signature和force_matching_signature，来判断是否采用了绑定变量，

select a.username, 

t.sql_text, 

to_char(t.force_matching_signature) as force_matching_signature, 

count(*) as counts 

from v$sql t, all_users a 

where t.force_matching_signature > 0 and 

t.parsing_user_id = a.user_id and 

t.force_matching_signature <> t.exact_matching_signature 

group by t.force_matching_signature, t.sql_text, a.username 

having count(*) > 20 

order by count(*) desc;

 

3.3 SQL 根据书本，收获不止SQL优化

未使用绑定变量的SQL比较类似，通过@替换相似部分，然后提取相同的分组，从而找出未使用绑定变量的SQL，过程如下，

drop table t_bind_sql purge;

create table t_bind_sql as select sql_text,module from v$sqlarea;

alter table t_bind_sql add sql_text_wo_constants varchar2(1000);

create or replace function 

remove_constants( p_query in varchar2 ) return varchar2

as

    l_query long;

    l_char  varchar2(10);

    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;

/

update t_bind_sql set sql_text_wo_constants = remove_constants(sql_text);

commit;

 

接下来用如下方式就可以快速定位了：

set linesize 266

col  sql_text_wo_constants format a30

col  module format  a30

col  CNT format  999999

select sql_text_wo_constants, module,count(*) CNT 

from t_bind_sql group by sql_text_wo_constants,module 

having count(*) > 100 order by 3 desc;