透過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 :~

然後建立一個臨時的小表
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

還可以指定某些天，或者一些更為複雜的判斷條件。