oracle的優化工具很是豐富，大家基本都會常用到的awr,ash,addm自不必多說,還有大量的tuning包，可能在em中去用感覺還友善一些，就是動動滑鼠，所見即所得，而且sql monitor作為11g的新特性，對于調優來說也是如虎添翼。但是作為前線的工程師們，總是會有各種環境的挑戰，想用圖形工具還是比較困難的，優化工具雖好，但是使用起來如果不太友善，或者很多時候不能很快定位問題，就會讓人感覺使不上勁，眉毛胡子一把抓。這也是我簡單定制這些工具的一個動力。所謂磨刀不誤砍柴工，這些強大的工具簡單包裝一下，就會有不一樣的效果來。我基本會從下面的幾個方面來說明一下我所做過的簡單定制。如果能夠幫助到大家，我深感榮幸。
目錄
簡單定制的思路
簡單定制awr
簡單定制ash
簡單定制addm
簡單定制osm
1
簡單定制的思路
如果說定制的思路，那麼我目前是使用了sql,shell和pl/sql之間做轉換或者互相調用來實作。不管怎麼樣，能夠實作定制需求就行。
2
簡單定制awr
1.1 定制awr的動力
首先來看看第一個優化工具awr,大家在做性能問題診斷的時候，awr是不可或缺的工具，使用?/rdbms/admin/awrrpt.sql可能大家使用的多了, 有時候感覺輸入參數還是有些太繁瑣了。一邊複制，一邊在一個快照清單中翻找對應的快照，其實還是不太友善。
比如我想檢視某一周以前的早上8點到9點的快照，生成一個awr報告來對比現在的性能情況，那麼我首先得算一下大概需要多少天的快照，然後從一個冗長的快照清單中去翻找需要的快照号，快照清單類似下面的格式。
instance db name snap id snap started level
------------ ------------ --------- ------------------ -----
test01 test01 1274 19 oct 2014 08:00 1
1275 19 oct 2014 08:30 1
1276 20 oct 2014 09:00 1
好不容易找到begin snap_id，拷貝完成，突然忘了看end snap_id,又得上下翻屏，其實，我隻是想生成一個報告而已。
對于曆史問題的分析如果經常需要找曆史awr報告，這種情況就尤其痛苦，是以我決定改變這種狀況。
我是從$oracle_home/rdbms/admin/awrrpt.sql開始分析的，其實這個腳本的調用關系如下圖所示。
awrinput.sql 是做輸入參數的校驗的，
awrinpnm.sql 是做報告檔案名處理的。
是以最關鍵的就是第三個腳本了。内容就是調用dbms_workload_repository的方法了。内容如下：
select output from table(dbms_workload_repository.&fn_name( :dbid,:inst_num,:bid, :eid,:rpt_options ));
既然說要定制，就需要把這些參數都給拿下。
于是我沒有急于馬上風風火火定制，而是發現其實我可以先定制一些曆史快照的使用情況。
比如我想得到某天某個時間段的快照情況，那麼我隻需要簡單輸入對應的時間參數就可以馬上得到一個快照清單了，根據需要随時調整也沒問題。
比如我想知道2014年10月19日3點到8點的快照情況，輸出應該類似下面的樣式。
db_name snap_id snapdat lvl
--------- ---------- -------------------------- ----------
test01 1262 19 oct 2014 02:00 1
1263 19 oct 2014 03:00 1
1264 19 oct 2014 04:00 1
1265 19 oct 2014 05:00 1
1266 19 oct 2014 06:00 1
1267 19 oct 2014 07:00 1
1268 19 oct 2014 08:00 1
1269 19 oct 2014 09:00 1
于是我寫了腳本先實作這個功能，這樣調用腳本 sh showsnap.sh 20141019 3 8
快照前後各寬裕一個小時，這樣我就輕松的得到快照清單了。
我們在這個基礎上更進一步，其實輸出快照号的同時也可以同時輸出db time的情況，這是在定制後得到的db time情況。
得到的是快照點的對應的db time，比如快照36343~36344，持續60分鐘，db time為85分鐘，以此類推。得到這樣的db time清單，還是可以發現很多潛在的問題，可以充分結合awr來進行診斷，但是不足之處還是後知後覺，這部分功能可以進一步進行深化，先不做更多讨論。
可以這樣運作腳本生成上面的結果 sh showsnap2.sh 20150827 5 10
showsnap2.sh這個腳本的實作思路主要是基于下面的幾個資料字典。
由于定制前做了一些準備工作，是以定制awr的時候就會更加靈活。
1.4 定制awrrpt.sql
定制awr的核心部分就是下面的這句。
是以我定制的語句如下，使用print生成了參數清單，然後直接在dbms_workload_repository.awr_report_html一次性傳入值，如果是text格式的，直接調用awr_report_text即可。
使用的語句如下：
print "
whenever sqlerror exit 5
set feedback off
set head off
set pages 0
connect ${db_conn_str}@${sh_db_sid}\n
select d.dbid||','||i.instance_number||','||$1||','||$2||',0' text
from v\$database d,
v\$instance i ;
" | sqlplus -s /nolog > awr_inputs.lst
sqlplus -s ${db_conn_str}@${sh_db_sid} <<>
set pages 0
set linesize 1500
set termout on;
spool awrrpt_$1_$2.lst
select output from table(dbms_workload_repository.awr_report_html( `cat awr_inputs.lst`));
spool off;
set termout off;
clear columns sql;
eof
如果awrrpt.sql完成了之後，還有一個福利就是awrsqrpt.sql，我們可以抓取到快照時間範圍内sql的執行計劃情況。這個時候唯一需要補充參數就是sql_id，需要調用dbms_workload_repository.awr_sql_report_text就可以得到文本格式執行計劃報告。
如果想得到html格式，就是dbms_workload_repository.awr_sql_report_html
這樣調用即可 sh genawrsqltext.sh 12315 12316 xxxxx 其中xxxxx為sql_id
3
簡單定制ash
awr的簡單定制如此，ash的部分也是如法炮制，是以整個分析的思路也是相似的。
核心步驟也是dbms_workload_repository.ash_report_html。當然主要差别在于ash中是不依賴快照的，需要傳入的參數為兩個時間戳，精确到秒，比如20151208010000 20151208020000 就是12月8号的淩晨1點到2點的ash需要的參數。
這樣調用即可 sh genashhtml.sh 20151208010000 20151208020000 即可。
腳本主要内容如下：
select d.dbid||','||i.instance_number||',to_date('||chr(39)||$1||chr(39)||','||chr(39)||'yyyymmddhh24miss'||chr(39)||'),to_date('||chr(39)||$2||chr(39)||','||chr(39)||'yyyymmddhh24miss'||chr(39)||')' text from v\$database d, v\$instance i ;
select output from table(dbms_workload_repository.ash_report_html(`cat ash_inputs.lst`));
4
簡單定制addm
對于addm而言，差别就比較大了。因為addm的部分涉及到pl/sql的一些方面，addm會調用dbms_advisor.create_task生成一個任務，通過dbms_advisor.set_task_parameter來設定一些參數屬性，然後通過dbms_advisor.execute_task來執行任務，最後通過 dbms_advisor.get_task_report(‘$task_name’,‘text’,‘typical’)來輸出最終的報告。
當然addm的部分還是和快照相關，調用的方法類似下面的方式sh genaddmhtml.sh 12315 12316 ，其中12315,12316是對應的快照号。
dbms_output.put_line(task_name);
dbms_advisor.create_task('addm',task_name);
dbms_advisor.set_task_parameter(task_name, 'start_snapshot', $1);
dbms_advisor.set_task_parameter(task_name, 'end_snapshot', $2);
dbms_advisor.execute_task(task_name);
end;
/
prompt
prompt generating the addm report for this analysis ...
prompt
set long 1000000 pagesize 0 longchunksize 1000
column get_clob format a80
select dbms_advisor.get_task_report('$task_name', 'text', 'typical')
from sys.dual;
5
簡單定制osm
5.1 osm簡介
在sql調優中，對于sql語句的實時監控顯得尤為重要，如果某條sql語句的性能比較差。可能從前端的直覺感覺就是執行時間比較長。
對于dba來說，可能關注的相關因素需要多一些。
1）可以通過top指令來監控sql的性能情況，檢視cpu使用率較高的oracle process,然後通過檢視session和process得綁定得到對應的session,然後得到對應的sql語句。
2) 如果已經過去了一段時間，而且在緩存中已經沒有對應的sql語句了，可以通過awr得到一個大體的報告做分析，排查問題的大體範圍，在這個基礎上定位更精準的時間段，做一個ash。
3) 如果已經定位到sql_id了，想做進一步的分析，可以通過awrsqrpt來得到對應時間段的執行計劃
。。。
對于執行計劃的分析方式就更多了，但是oracle也提供了一些比較友善的功能集，你用或者不用，它就在那裡。
sql monitor是一個實時的sql監控工具，11g裡對dbms_tune做了不少的改進和提升。動态視圖v$sql_monitor中有被監控的sql語句的一些明細資訊。
一般對于執行時間超過5秒的sql語句，都會成為監控對象
如果想得到sql monitor報告也絕非難事，可以通過dbms_sqltune.report_sql_monitor的方式得到一個html報告。比如下面的小例子。
select dbms_sqltune.report_sql_monitor(
sql_id => '$1',
report_level => 'all',
type=>‘html'
) comm from dual;
任何性能瓶頸都會暴露在你的眼前，這種圖形化的調優其實還是很不錯的。在無法使用圖形界面的環境，得到一個html報告絕對是一種很難得的福利。
osm中的v$sql_monitor類似v$session的機制，資料都會重新整理
自動生成sql monitor的html或text 格式報告
報告生成頻率的控制
對報告進行篩查，得到報告中的top sql
對曆史報告進行深度分析
但是有個問題就是比如某個sql語句造成的問題已經發生了一段時間，想檢視之前的執行情況，v$sql_monitor基本上就無從得知了，因為資料已經被刷出去了。
這個時候想檢視更多的資訊就比較困難了，為了能夠及時和準确的定位，我們可以借助sql_monitor在背景啟用一個job不定時的去查找，把這部分資料給緩存起來，目前我是采用平面檔案的形式。
整個流程圖如下：
如果大半夜有性能問題但是不是很緊要的情況下，我希望一切都能很自然一些，我可以在工作時間更高效的處理。從我的實踐來看，我是在一個指定的目錄下每隔10分鐘去查找一次性能sql，如果當天已經有生成報告就不重新生成了，這個是報告生成的頻度控制。
每天上班之後，我可以調用腳本根據時間來過濾得到一個排行榜，duration時間最長的語句肯定是需要重點關注的。
grep -a2 duration *$date |grep s\<|sed 's///g'|sed 's/<\/td>//g'|sort -n -k2
結果不到一個多月，生成了800個報告，這麼多報告是好事，畢竟已經有了詳細的報告和資料，但是如果一下子消化這麼多的報告，肯定是有難度，而且沒有很強的針對性，可能有些sql在一個月中的幾天才會運作。有些sql可能每天都會運作，有些可能就運作一次，很長時間不會再次運作，我們需要關注的就是那些運作頻繁的問題sql語句。sql monitor的報告類似下面的格式。
-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 [email protected]:~
然後建立一個臨時的小表
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
30kfnx73k75jf 2
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;
648600hq1s1s8 13
99pnz5pr7tgpb 13
4ad8ypr3nf6vm 13
cjqdgd14xjwjm 4
還可以指定某些天，或者一些更為複雜的判斷條件。
對于語句的執行時間進行更細粒度的監控，如果沒有圖形工具的情況，手工分析還是不爽，如果會一些開發，可以繼續定制。比如我采用java語言嘗試對sql的執行時間進行統計，可以生成一個時間曲線圖。用java是因為我隻會用java做一點點字元串處理的功能j。
要做這部分工作是因為總是在分析資料的時候沒有一個整體的感覺，而且又沒有圖形界面可用，隻好在字元界面裡想點辦法，這個沒有大規模采用，也是因為本身有華而不實的成分，也算自娛自樂吧。
上面僅供自娛自樂，如果有完備的環境最好還是不要這麼幹。
是以這些簡單的定制，其實也會發現在一定程度上，我們可以讓這些性能診斷工具更符合我們的口味。在不影響性能，能夠大幅度提高問題處理水準的情況下，還是推薦按需定制。
當然我隻是抛磚引玉，還有更多的優化工具，比如dbms_tune可以有更多定制，sqltrpt也是一個很容易被大家忽略的神器。
作者介紹：楊建榮
【dba+社群】北京發起人
oracle ace-a,yep成員,現就職于搜狐暢遊，擁有6年以上的資料庫開發和運維經驗，曾任amdocs dba,負責亞太電信營運商的資料業務支援，擅長電信資料業務,資料庫遷移和性能調優。
擁有oracle 10g ocp,ocm, mysql ocp認證,對shell,java有一定的功底,曾在2015年資料庫大會進行關于資料遷移和更新的主題分享，現在每天仍在孜孜不倦的進行技術分享，每天通過微信，技術部落格共享，已連續堅持550多天。
<b></b>
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2015-12-22</b>