《收获，不止SQL优化》 - 脚本积累集合 - 1

Love The Way You Lie 2022-02-27 09:06 192阅读 0赞

这是杂货铺的第453篇文章

[《收获，不止SQL优化》][SQL]这本书，有很多即用的脚本工具，或者根据自己的需求，改造重用，可以积累到自己的工具库中。

以下两个脚本，官方来源：

https://github.com/liangjingbin99/shouhuo/tree/master/%E7%AC%AC05%E7%AB%A0

**1. 找出未使用绑定变量的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;

执行结果，

![640?wx\_fmt=png][640_wx_fmt_png]

我们在做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;

**2. 确定数据库峰值的脚本**

这个脚本，能检查系统各维度的规律，对确定数据库峰值的时间点起到一定的指导作用。

官方脚本有一点小错误，应该是笔误，各位可以跑跑看，我更新了一版，

https://github.com/bisal-liu/oracle/blob/master/tools/monitor\_database.sql

执行结果，是按照小时保存，包含了DB Time、REDO量、逻辑读(/s)、物理读(/s)、执行次数(/s)、解析次数(/s)、硬解析次数(/s)、交易量(/s)，基本就是AWR报告中概要以及Load Profile部分的内容，其实从SQL看，是从dba\_hist\_snapshot进行统计，说明是从AWR快照库中得到的，

![640?wx\_fmt=png][640_wx_fmt_png 1]

历史文章：

《[《收获，不止SQL优化》 - 调优信息一键生成脚本学习][SQL]》

《[《收获，不止SQL优化》 - 获取执行计划的方法对比][SQL_ -]》

[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247488420&idx=1&sn=ca3781cfdc87c5a290eb0beb649f5444&chksm=ea815657ddf6df419282bda2b1d0e1af2c5a1b52efaf4bf9710cd732b7f8d9f0570851fc17be&scene=21#wechat_redirect
[640_wx_fmt_png]: /images/20220227/8349c14cccbf443bb8207dc68732297e.png
[640_wx_fmt_png 1]: /images/20220227/5ec4ea74787b4e79bba80ee9db3b4262.png
[SQL_ -]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247488435&idx=1&sn=1d26efd9aaf5a631fd11b7f338353ca4&chksm=ea815640ddf6df568d1afd5c0de9ebc6ead7aecb1d319fcac4cb837ec2d394203cf72ea6cf80&scene=21#wechat_redirect