《收获，不止SQL优化》 - 脚本积累集合 - 1
这是杂货铺的第453篇文章
《收获，不止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;
执行结果，
我们在做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快照库中得到的，
monitor_database.sql
select s.snap_date, decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME", to_char(round(s.seconds/60,2)) "elapse(min)", round(t.db_time / 1000000 / 60, 2) "DB time(min)", s.redosize redo, round(s.redosize / s.seconds, 2) "redo/s", s.logicalreads logical, round(s.logicalreads / s.seconds, 2) "logical/s", physicalreads physical, round(s.physicalreads / s.seconds, 2) "phy/s", s.executes execs, round(s.executes / s.seconds, 2) "execs/s", s.parse, round(s.parse / s.seconds, 2) "parse/s", s.hardparse, round(s.hardparse / s.seconds, 2) "hardparse/s", s.transactions trans, round(s.transactions / s.seconds, 2) "trans/s" from (select curr_redo - last_redo redosize, curr_logicalreads - last_logicalreads logicalreads, curr_physicalreads - last_physicalreads physicalreads, curr_executes - last_executes executes, curr_parse - last_parse parse, curr_hardparse - last_hardparse hardparse, curr_transactions - last_transactions transactions, round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds, to_char(currtime, 'yy/mm/dd') snap_date, to_char(currtime, 'hh24:mi') currtime, currsnap_id endsnap_id, to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time from (select a.redo last_redo, a.logicalreads last_logicalreads, a.physicalreads last_physicalreads, a.executes last_executes, a.parse last_parse, a.hardparse last_hardparse, a.transactions last_transactions, lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo, lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads, lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads, lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes, lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse, lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse, lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions, b.end_interval_time lasttime, lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime, lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id, b.startup_time from (select snap_id, dbid, instance_number, sum(decode(stat_name, 'redo size', value, 0)) redo, sum(decode(stat_name, 'session logical reads', value, 0)) logicalreads, sum(decode(stat_name, 'physical reads', value, 0)) physicalreads, sum(decode(stat_name, 'execute count', value, 0)) executes, sum(decode(stat_name, 'parse count (total)', value, 0)) parse, sum(decode(stat_name, 'parse count (hard)', value, 0)) hardparse, sum(decode(stat_name, 'user rollbacks', value, 'user commits', value, 0)) transactions from dba_hist_sysstat where stat_name in ('redo size', 'session logical reads', 'physical reads', 'execute count', 'user rollbacks', 'user commits', 'parse count (hard)', 'parse count (total)') group by snap_id, dbid, instance_number) a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number order by end_interval_time)) s, (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time, lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id from dba_hist_sys_time_model a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number and a.stat_name = 'DB time') t where s.endsnap_id = t.endsnap_id order by s.snap_date ,time desc;
浙公网安备 33010602011771号