分享彼此的優化經驗

 最近從朋友那看了一個某諮詢公司給一家企業做的一個優化項目的總結報告書,其歷時兩個月,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/,如需轉載,請註明出處,否則將追究法律責任。

轉載於:http://blog.itpub.net/20577218/viewspace-703825/