分享彼此的優化經驗

 最近從朋友那看了一個某諮詢公司給一家企業做的一個優化專案的總結報告書，其歷時兩個月，10萬費用，4個人。
最終結果是效能和相應提升了30%，總共修改了3行程式碼和配置，共修改了3個單詞，不到20個字母~~~~。

    朋友總結了一句話，就是“程式碼質量越爛的專案，優化起來越容易！”。像上面這個專案，外行會認為做優化的人一定是
超一流的高手，有點石成金之能力。其實不然，朋友說，“我們其實只是改了他們系統裡一個明顯的漏洞，這個漏洞讓整個
資料庫50%的時間都在空轉，僅此而已”。
   
    之後他又給我看了這個專案db的statspack、addm、STA、SAA、以及幾個session的trace檔案，幾個效能試圖的定時統計
值，幾個java的thread dump檔案，jvm的統計資訊檔案，專案的架構...等等，想考考我還有多大的提升空間。我用一天的時
間仔細看了一下，得出的結論是--如果深度優化，這個專案保守估計還有300%的效能提升空間，也就是說按這個硬體的水平，
客戶響應時間可以縮短到三分之一。我把這一結果告訴朋友，他說我還是比較保守的，他們估計這個專案的優化空間可以達到
1000%！

    說了這許多的廢話，其實就是給下文做個鋪墊，立此貼的目的是想讓大家把平時工作中積累的關於程式碼優化的經驗拿出來
分享一下，也希望各位高人能積極響應之，共同完善這個東西。
    
     自己開頭，先拿我還算湊合的oracle資料庫優化開刀祭旗。
   1、  某專案，在pl/sql中執行top sql指令碼：
     --使用頻率
select sql_text, executions,sysdate
  from (select sql_text,
               executions,
               rank() over(order
              
               by executions desc) exec_rank
          from v$sql)
 where exec_rank <= 10;
 建crontab定時執行上面指令碼，求出其當前時間段使用頻率較高的sql，找到其第一條（就是執行最頻繁的語句），拷貝出來，
 貼上到一個新視窗，程式碼為：
 SELECT grp_addr
  FROM Table_1
 WHERE ID = :B1
   AND grp_ID = (SELECT MAX(grp_ID)
                         FROM Table_1
                        WHERE ID = :B1);
  按f5，求其執行計劃為：
  SELECT STATEMENT, GOAL = ALL_ROWS            Cost=4    Cardinality=1    Bytes=21
 TABLE ACCESS BY INDEX ROWID    Object wner=***    Object name=***    Cost=2    Cardinality=1    Bytes=21
  INDEX RANGE SCAN    Object wner=***    Object name=***    Cost=2    Cardinality=1   
可見其cost為4，試著用分析函式改寫之：
select first_value(grp_addr)  over (partition by ID order by grp_ID)   
from      Table_1 a
where a.ID = :B1; 
再按f5，執行計劃為：
SELECT STATEMENT, GOAL = ALL_ROWS            Cost=3    Cardinality=1    Bytes=21
 WINDOW SORT            Cost=3    Cardinality=1    Bytes=21
  TABLE ACCESS BY INDEX ROWID    Object wner=***    Object name=***    Cost=2    Cardinality=1    Bytes=21
可見cost下降為3，同時執行時間下降了0.011秒，別看不起這一點點的優化，該語句每天要執行次數百萬計，聚沙成塔，效果
還是有的。除去執行最頻繁的sql，還要優化的就是那些真正效能低下的sql了，這些語句都可以用top sql指令碼找到，然後就要
一一對其進行解決。   

 2、 通過等待事件判斷問題，執行一下語句，求系統非空閒等待事件：
 
select sid,
       p1,
       p1raw,
       p2,
       p2raw,
       p3,
       p3raw,
       wait_time,
       seconds_in_wait,
       state,
       event,
       sysdate
  from v$session_wait
 where event not in
       ('AQ Proxy Cleanup Wait', 'ASM background timer', 'DIAG idle wait',
        'EMON idle wait', 'KSV master wait', 'LNS ASYNC archive log',
        'LNS ASYNC dest activation', 'LNS ASYNC end of log',
        'LogMiner: client waiting for transaction',
        'LogMiner: slave waiting for activate message',
        'LogMiner: wakeup event for builder',
        'LogMiner: wakeup event for preparer',
        'LogMiner: wakeup event for reader', 'Null event',
        'PX Deq Credit: need buffer', 'PX Deq Credit: send blkd',
        'PX Deq: Execute Reply', 'PX Deq: Execution Msg',
        'PX Deq: Par Recov Execute', 'PX Deq: Signal ACK',
        'PX Deq: Table Q Normal', 'PX Deq: Table Q Sample', 'PX Deque wait',
        'PX Idle Wait', 'Queue Monitor Shutdown Wait',
        'Queue Monitor Slave Wait', 'Queue Monitor Wait',
        'SQL*Net message from client', 'SQL*Net message to client',
        'SQL*Net more data from client',
        'STREAMS apply coord waiting for slave message',
        'STREAMS apply slave idle wait',
        'STREAMS apply slave waiting for coord message',
        'STREAMS capture process filter callback wait for ruleset',
        'STREAMS fetch slave waiting for txns',
        'STREAMS waiting for subscribers to catch up',
        'Streams AQ: RAC qmn coordinator idle wait',
        'Streams AQ: deallocate messages from Streams Pool',
        'Streams AQ: delete acknowledged messages',
        'Streams AQ: qmn coordinator idle wait',
        'Streams AQ: qmn slave idle wait',
        'Streams AQ: waiting for messages in the queue',
        'Streams AQ: waiting for time management or cleanup tasks',
        'Streams fetch slave: waiting for txns', 'class slave wait',
        'client message', 'dispatcher timer', 'gcs for action',
        'gcs remote message', 'ges remote message', 'i/o slave wait',
        'jobq slave wait', 'knlqdeq', 'lock manager wait for remote message',
        'master wait', 'null event', 'parallel query dequeue', 'pipe get',
        'pmon timer', 'queue messages', 'rdbms ipc message', 'slave wait',
        'smon timer', 'virtual circuit status', 'wait for activate message',
        'wait for unread message on broadcast channel',
        'wakeup event for builder', 'wakeup event for preparer',
        'wakeup event for reader', 'wakeup time manager');
發現大量db file sequential read事件，說明sql在硬碟io上有優化的可能，建crontab定時執行上面指令碼，找到某連續等待（就是
老有它在那裡討厭著~~~）的記錄的sid,執行以下程式碼求其對應sql：
select sql_text
  from v$sqltext_with_newlines st, v$session se
 where st.address = se.sql_address
   and st.hash_value = se.sql_hash_value
   and se.sid = :SID
 order by piece;
 找到其sql為：
 select * from TABLE_2 where acct_nbr = :B;
 （這裡要說一點，session和wait event都是動態的，而幾次對應操作都找到該sid則從另一側面說明其很不“動態”，賴在那裡不走）
 語句簡單，判斷為缺失索引（其實在addm和statspack裡可以得同樣結論），建立相關索引，速度大幅提高，客戶又提出該表有一定量
 的ddl會受影響，連續監控並詢問開發方該表每天插入資料不足萬條，而且不要求實時性，所以建議模仿c語言的copy on write策略，
 在業務低谷，刪除索引--批量插入--重建索引。
 
 3、通過addm。10g開始可以使用，本來有web oem用就簡單多了的，但客戶不讓起相應監聽（emctl start dbconsole），無奈。用命
 令行的。
 先生成一張db快照，引數'TYPICAL'的意思是以典型採集等級生成快照，還可以用ALL引數，則多了os相關資訊：
 begin
  dbms_workload_repository.create_snapshot('TYPICAL');
end;
/                         
等待一段時間（大約40分鐘，必須大於30分鐘，不然報間隔太短），再次執行上面的程式碼生成第二張快照。
執行：select * from dba_hist_snapshot a order by a.snap_id desc;找到最後的兩張快照（就是我自己生成的兩張），記錄其snap_id
欄位的值，執行以下指令碼：
DECLARE task_name VARCHAR2(30) := 'turning02';
task_desc VARCHAR2(30) := 'turning02';
task_id NUMBER;
BEGIN
  dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
  dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', 5209);
  dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 5212);
  dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 1);
  dbms_advisor.set_task_parameter(task_name, 'DB_ID', ********);
  dbms_advisor.execute_task(task_name);
END;
/
其中的*******是你資料庫的db_id全球唯一標識，在v$database裡記錄，然後檢視生成的報告：
SELECT dbms_advisor.get_task_report('turning02', 'TEXT', 'ALL') FROM DUAL;
螢幕上會有一大堆的英文，複製之到ue啊什麼的，這個報告其實就是這段時間db裡的即時狀態的分析，有什麼不好的sql，少什麼索引，
有沒有物理熱塊什麼的。我們先搜尋‘index’關鍵字（因為一直是用oem的，按個按鈕就ok了，命令列的不知道怎麼導成檔案~~~慚愧），看
看有沒有建議建什麼索引，然後往下看看有沒有提示一些有問題的sql，總之addm是很方便的，不僅能找到問題，連解決的指令碼都會給你寫
好，問題sql也會提出修改建議，各位自己複製出來看看執行時間、執行計劃什麼的就行了。

總結下上面的，首先掌握10/90原則，就是90%的效能問題是10%的原因造成的，而db的效能問題90%是在sql語句上的，所以先從sql下手，上
面3條路其實是“條條大路通羅馬”的，找出的問題程式碼大同小異，大家可以用下面的top sql指令碼找出來，或者從作業系統裡用top看cup佔用
較高的帶ora的程式，記錄其pid然後：
SELECT p.pid,p.spid,s.sid,p.username,s.TYPE,
         s.SERIAL#,s.SCHEMANAME,s.OSUSER,s.MACHINE,
         s.PROCESS,p.PROGRAM,s.MODULE,s.STATUS,
         s.terminal,logon_time
FROM v$process p, v$session s
WHERE p.addr=s.paddr
and spid ='*****';
看看是哪個session佔用cpu多，然後給這個session做個sql trace 定位問題sql，這些都是極其簡單的。
    sql優化的原則是1、讓語句儘量少執行 2、讓語句少佔用系統資源。第一點需要對業務流程很瞭解，就好像我一開始提到的那個例子，
系統大量資源是在“空跑”。第二點可以通過sql語句的優化解決。上面寫的只是大致的思路，而每個結論都不是一蹴而就的，每個指令碼都要定時、
長期執行，並把結果插入一張表（或log），我在指令碼里加了sysdate就是要插表，然後按時間來看的。而找到的問題sql也不是一條，要逐個
解決，而幾條“路”又是一個互相印證的關係，比如先看等待事件再出addm，給出問題最大的sql是基本相同的，而在第二點裡建立了索引後
addm裡這個問題語句也就消失了。




    db方面的優化，sql優化基本上能解決大部分問題了，db側動的可以少些（畢竟要顧慮客戶方dba的面子啊~~~），主要是一些頻繁ddl的表建
索引有困難，就要考慮從邏輯上修改業務，或者是表分割槽來儘量減少access full的代價。
   
    舉個例子，客戶經常要查一張大表裡某時間段的資料（做環比報告），而該表插入資料頻繁，建索引影響明顯，而做時間軸的範圍分割槽能
較好的平衡這兩方面的問題。在分割槽時還遇到了一個問題，我想把不同分割槽放在不同的檔案上，而不同的檔案再放在不同的io通道上（一組raid
算一條io通道），這樣可以儘量分散物理io的壓力到不同的io通道及硬碟上，讓硬碟做到“有活大家幹”，但客戶lvm這一層遮蔽了底層的物理實現
最後還是費了些周折找到清楚該系統物理架構的人員解決的問題。

    在優化工作中我遇到過一個sql只是通過“謂詞後推”語句速度就快了30%多，就是說這些優化動作就是要細心的逐個尋找並解決。逐步累積最後效
果就是顯著的了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20577218/viewspace-703825/，如需轉載，請註明出處，否則將追究法律責任。