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 owner=***    Object name=***    Cost=2    Cardinality=1    Bytes=21
  INDEX RANGE SCAN    Object owner=***    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 owner=***    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%多，就是說這些最佳化動作就是要細心的逐個尋找並解決。逐步累積最後效
果就是顯著的了。