通过shell和sql结合查找性能sql

在生产系统中，会发现一些潜在的sql问题，为了能够及时和准确的定位，我们可以借助sql_monitor来做性能sql的查找。可以在后台启用一个job不定时的去查找。
毕竟大半夜的我们去监控也是有难度的，我们希望一切都能很自然的处理，结果我在一个指定的目录下每隔10分钟去查找一次性能sql，如果当天已经有生成报告就不重新生成了。
结果不到一个多月，生成了800个报告，这么多报告是好事，毕竟已经有了详细的报告和数据，但是如果一下子消化这么多的报告，肯定是有难度，而且没有很强的针对性，可能有些sql在一个月中的几天才会运行。有些sql可能每天都会运行，有些可能就运行一次，很长时间不会再次运行，我们需要关注的就是那些运行频繁的问题sql语句。
-rw-r--r-- 1 prodbuser dba 231786 Aug  5 10:20 c03451mgv0vwu_rpt.lst_140805
-rw-r--r-- 1 prodbuser dba 276935 Jul 29 14:00 c0tua88kkmhny_rpt.lst_140729
-rw-r--r-- 1 prodbuser dba 180899 Jul 21 16:24 c166x1kn4y73a_rpt.lst_140721
-rw-r--r-- 1 prodbuser dba 170178 Jul 29 13:10 c166x1kn4y73a_rpt.lst_140729
-rw-r--r-- 1 prodbuser dba 185278 Aug  5 11:20 c166x1kn4y73a_rpt.lst_140805
-rw-r--r-- 1 prodbuser dba 141035 Jul 29 05:20 c1pvtcxrr9vzu_rpt.lst_140729
-rw-r--r-- 1 prodbuser dba 162326 Aug  4 11:39 c3xwtzv3t21ws_rpt.lst_140804
-rw-r--r-- 1 prodbuser dba 189053 Jul 29 01:39 c55p46kgh6kva_rpt.lst_140729
-rw-r--r-- 1 prodbuser dba 434126 Jul 22 06:27 c7cay05hyg6hj_rpt.lst_140722
-rw-r--r-- 1 prodbuser dba 436240 Jul 25 01:36 c7cay05hyg6hj_rpt.lst_140725
-rw-r--r-- 1 prodbuser dba 428237 Jul 26 05:07 c7cay05hyg6hj_rpt.lst_140726
-rw-r--r-- 1 prodbuser dba 435485 Jul 27 03:08 c7cay05hyg6hj_rpt.lst_140727
-rw-r--r-- 1 prodbuser dba 404379 Jul 28 04:59 c7cay05hyg6hj_rpt.lst_140728
-rw-r--r-- 1 prodbuser dba 440166 Aug  1 03:07 c7cay05hyg6hj_rpt.lst_140801
-rw-r--r-- 1 prodbuser dba 422197 Aug  2 02:18 c7cay05hyg6hj_rpt.lst_140802
-rw-r--r-- 1 prodbuser dba 427935 Aug  3 00:58 c7cay05hyg6hj_rpt.lst_140803
-rw-r--r-- 1 prodbuser dba 299736 Jul 21 17:19 c8hmrrzkwyf9k_rpt.lst_140721
-rw-r--r-- 1 prodbuser dba 343073 Jul 24 08:15 cafq05qjz686u_rpt.lst_140724
-rw-r--r-- 1 prodbuser dba 111137 Jul 25 15:06 cdbz6j8ndw0rj_rpt.lst_140725
-rw-r--r-- 1 prodbuser dba 107362 Aug  5 17:20 cdbz6j8ndw0rj_rpt.lst_140805
-rw-r--r-- 1 prodbuser dba 132277 Jul 21 17:19 cfun4v7jffbsg_rpt.lst_140721
-rw-r--r-- 1 prodbuser dba 176369 Aug  4 17:20 cg4fj6y69nr0g_rpt.lst_140804
-rw-r--r-- 1 prodbuser dba 189053 Aug  2 02:08 cgujc2mdzgkq7_rpt.lst_140802
-rw-r--r-- 1 prodbuser dba 300491 Jul 21 16:22 cjqdgd14xjwjm_rpt.lst_140721
-rw-r--r-- 1 prodbuser dba 272707 Jul 22 11:27 cjqdgd14xjwjm_rpt.lst_140722
-rw-r--r-- 1 prodbuser dba 273764 Jul 23 11:45 cjqdgd14xjwjm_rpt.lst_140723
-rw-r--r-- 1 prodbuser dba 272858 Jul 24 10:25 cjqdgd14xjwjm_rpt.lst_140724
-rw-r--r-- 1 prodbuser dba 283579 Jul 25 01:36 cjqdgd14xjwjm_rpt.lst_140725
-rw-r--r-- 1 prodbuser dba 277992 Jul 26 00:37 cjqdgd14xjwjm_rpt.lst_140726
-rw-r--r-- 1 prodbuser dba 280710 Jul 27 09:28 cjqdgd14xjwjm_rpt.lst_140727
-rw-r--r-- 1 prodbuser dba 282220 Jul 28 05:09 cjqdgd14xjwjm_rpt.lst_140728
-rw-r--r-- 1 prodbuser dba 277690 Jul 30 01:31 cjqdgd14xjwjm_rpt.lst_140730
-rw-r--r-- 1 prodbuser dba 249000 Aug  1 11:58 cjqdgd14xjwjm_rpt.lst_140801
-rw-r--r-- 1 prodbuser dba 296867 Aug  2 13:08 cjqdgd14xjwjm_rpt.lst_140802
-rw-r--r-- 1 prodbuser dba 285240 Aug  3 01:28 cjqdgd14xjwjm_rpt.lst_140803
-rw-r--r-- 1 prodbuser dba 295055 Aug  4 10:49 cjqdgd14xjwjm_rpt.lst_140804
看到这么多的报告都有点晕，不知道该从哪里开始查起。
而且直接在生产环境没有目的的进行语句的执行计划抽取，性能问题也会做很多额外的无用功。

可以使用如下的命令来生成sql语句，然后在其他的环境中运行，做问题sql语句的分析，用sql语句来分析sql语句，这样一物降一物。
 ls -l *.lst* |awk '{print $9}'|awk -F_rpt.lst_ '{print "insert into issue_sql values('\''"$1 "'\'', " $2");"}' > issue_sql.sql
生成的sql语句类似下面的形式。
 insert into issue_sql values('07aw4r5syzydx', 140818);
insert into issue_sql values('091n6gmzgwxzs', 140805);
insert into issue_sql values('0cdthzpx2jn4q', 140722);
insert into issue_sql values('0cdthzpx2jn4q', 140727);
insert into issue_sql values('0cdthzpx2jn4q', 140729);
insert into issue_sql values('0cdthzpx2jn4q', 140803);
insert into issue_sql values('0cdthzpx2jn4q', 140805);
insert into issue_sql values('0d0n1waazr2fs', 140722);

拷贝到别的环境去。

> scp issue_sql.sql xxxxx@xxxx.19.xxxx.47:~

然后创建一个临时的小表
create table issue_sql (sql_id varchar2(30),sql_date number);    

@issue_sql.sql

然后就开始使用sql语句来分析了，先来一个大概的，看看哪些sql语句出现的频率最高。

select *from (select  sql_id,count(*)cnt from issue_sql group by sql_id) order by cnt desc;

SQL> select *from (select  sql_id,count(*)cnt from issue_sql group by sql_id) order by cnt desc;

SQL_ID                                CNT
------------------------------ ----------
648600hq1s1s8                          25
4gz51fphuarsw                          23
94mgu2k08hm4r                          23
4ad8ypr3nf6vm                          22
2nt0hq33qb1a6                          16

SQL_ID                                CNT
------------------------------ ----------
9q6ta1c5x1nxn                          16
b56c37kvdwn1m                          15
bdyfy49zwbaza                          15
b3dzwjrn3psq7                          15
dg938muvq587v                           2
30kfnx73k75jf                           2
4a1nadt1xpqrf                           2

SQL_ID                                CNT
------------------------------ ----------
9cbk5x6hwq0mu                           2
3rkmrqq7wsvas                           1

比如我们想看看八月份以来哪些sql语句执行频率最高，可以使用如下的方式：
SQL> select *from (select  sql_id,count(*)cnt from issue_sql where sql_date like '1408%' group by sql_id) order by cnt desc;

SQL_ID                                CNT
------------------------------ ----------
648600hq1s1s8                          13
99pnz5pr7tgpb                          13
4ad8ypr3nf6vm                          13
cjqdgd14xjwjm                           4

还可以指定某些天，或者一些更为复杂的判断条件。