最近給幾個比較重要的客戶優化了幾套Oracle資料庫, 套用一句名言: 性能好的資料庫都是相似的, 性能差的資料庫各有各的"不幸". 實際上性能好的資料庫基本看不到,除非是一些負載非常小的庫. 絕大部分資料庫都是處于亞健康狀态, 很多拿來做優化的庫, 要麼是瀕臨癱瘓,要麼是業務使用者實在是忍無可忍.
對優化不了解的使用者, 遇到性能問題很多時候最先想到的處理方法不是優化, 而是更換硬體, 更有甚者幹脆再開發一套新的應用.
今天介紹的這幾個案例, 就是讓大家多了解優化, 每一套"不幸"的資料庫, 經過優化之後, 基本上都能夠得到新生.
客戶1:
資料庫更新到12.2版本後, 共享記憶體(shared pool)一段時間後就增大到了100多G, 而buffer cache被擠壓到隻剩500多M, 導緻SQL執行效率非常低. 客戶之前找過專家改過幾個隐含參數(如_cursor_obsolete_threshold / _memory_imm_mode_without_autosga等), 還是沒有解決:
經過分析AWR發現, 這是一個ges resource dynamic相關的bug, 根據MOS提供的workaround做了調整, 這一步做到了治标. 其實oracle的bug一般不會輕易踩到, 這個bug就是因為應用的一些SQL沒有使用綁定變量;還有一些insert values過多的綁定變量, 而綁定變量的長度變化較大, 生成了大量的子遊标; 再有就是insert 後面的select 語句是由上千個union 組成, 這些都是誘發bug的原因, 這些問題都需要開發修改代碼, 才能從根本上解決問題. 如果把這些問題都解決了, 這個bug應該也就不會出現.
幾乎每個資料庫都存在一些索引和SQL寫法的問題,這個庫也不例外,主要問題通過參數調整先臨時解決了. 然後把TOP SQL涉及的表增加一些索引, 同時還發現一些SQL的寫法需要改進. 索引和SQL寫法在兩大類性能問題幾乎在所有的資料庫都存在, 索引可以補建, SQL寫法的問題隻能通過改代碼解決,oracle的優化器雖然很強大, 但是也不能随心所欲, 很多開發寫的SQL實作業務邏輯沒問題, 性能就慘不忍睹了.
加了索引之後, 這個庫的性能又得到了較大的提升. 治本的SQL寫法問題還需要開發對代碼做調整.
客戶2:
這是一個資料倉庫, 裡面有很多幾百G大表, 而且還在持續不斷增長. 每天大量的全表掃描(一天的讀取量大概是70T), 還好有oracle的一體機Exadata強大的硬體做支撐. 如果不是Oracle原廠的一體機Exadata的硬體, 很難支撐這樣的業務系統. Exadata強大的pmem(硬體-持久化記憶體)和offload(軟體), 可以讓100多G大表的全表掃描, 隻需要1秒左右, 真香.
即便是強大的硬體做支撐, 遇到統計資訊收集不準或是參數設定不當的情況, 也是7~8個小時也跑不出結果. 還有在大表中做delete的時候, 即使是一個不删除任何記錄的SQL, 也需要執行幾十分鐘. 而且随着資料量的不斷增長, 每天的業務加載時間的持續增長也在意料之中.
這個庫的優化手段說起來比較簡單, 就是對大表做分區 , 找出合适的字段, 使用合理的分區類型, 不但能實作幾百倍的性能提升, 而且不會随着資料量的增長而增加業務加載時間. 分區是資料倉庫最有效的的優化方法, 沒有之一. 除了分區裁剪可以大幅減少IO讀取, 分區還是compress/in-memory等技術的"最佳搭檔". 在很多開源資料庫, 經常聽到一個名詞叫"分表", 這是因為分區技術不太成熟; 但是在oracle資料庫, 很少聽說需要用到"分表", 因為分區就是最好的"分表". 之前遇到某移動客戶的賬務系統做了分表, 那SQL寫起來是相對的尬,如果架構師能多了解一些分區就好了.
同樣, 加索引和調整SQL寫法也是必不可少的, 這是每個資料庫優化都躲不過的, 有時候即使做了分區, SQL寫法配合不上也是白扯.
客戶3:
這是一個ETL加報表輸出庫, 很多SQL執行時間在1~2小時以上,還有一些執行7~8個小時以上最後報ora-01555錯誤,相當于消耗了大量的系統資源, 最後得到了一個寂寞.
這個資料庫最大的問題是修改了一個重要的優化器參數optimizer_mode, 将這個參數恢複到預設值後, 再加上其他的一些優化手段, 原來執行幾個小時的SQL, 執行時間都降到了1分鐘以下. 有的資料庫參數可以根據實際情況做一些調整, 但是像optimizer_mode這種參數, 千萬不要動, 這是在做"劣化", 而不是做優化.
介紹完3個優化案例, 再談談優化相關的一些理論, 索引是SQL優化最基本的技術, 也是OLTP系統最主要的優化手段, 很多人都覺得簡單, 但就是這項技術, 我花了整整兩天的時間給學員做教育訓練.
下面是我看到的幾個關于索引的優化案例, 看完之後感覺優化人員對索引的了解可能存在一些偏差, 舉兩個例子:
1. 下面3個sql, 建立索引應該能夠帶來很大的性能提升, 但是把常量1加到索引做聯合索引, 是完全沒有必要的, 這個做法暴露了對索引了解的不足:
SELECT ... FROM xxx WHERE col1 = :B1 OR col2 = :B1 ;
create index .. on xxx(col1,1);
create index .. on xxx(col2,1);
select ... from xxx WHERE col1 || col2= :B1;
create index .. on xxx(col1||col2,1);
SELECT ... FROM xxx WHERE col1= TRIM(:B1 );
create index .. on xxx(col1,1);
把常量加到聯合索引, 某些特殊情況确實需要, 但都不是上面這些情況.
2. 下面這個SQL, 給出的優化建議存在多個問題:
select ... from xxx
where c1=:b1 and c2=:b2 and c3=:b3 and c4=:b4 and c5=:b5;
目前已經存在(c1,c2,c3,c4,c5) 5 字段聯合索引 , 5個字段的NDV(number of distinct value) 分别是 97, 1, 1, 264, 572519 (表上記錄數 650萬)
優化人員給出的建議是建立(c5,c4,c1,c2,c3) 5字段聯合索引.
這個建立索引對目前SQL起到的優化作用可以說是微乎其微.因為都是等值條件, 聯合索引字段先後順序基本上沒有影響. 并不是說NDV大的字段就一定要放到聯合索引的最前面, 沒有這樣的理論.
上面的建議涉及到幾個索引的基本理論:
不需要把where 後面所有的條件都加到聯合索引(肥大的索引有很多弊端, 這裡不一一列舉), 這個索引隻用c4,c5 兩個字段聯合基本上就足夠了.
如果是等值條件, 聯合索引字段先後順序差别很小,基本可以忽略不計(c5,c4也是一樣的)
把NDV=1的c2和c3加到聯合索引, 意義不大; ndv=1 字段加入到索引,隻在某些特殊情況才有意義, 比如大部分是null值/查找不存在的值/加了rownum<=xx等.
結語:
很多人認為資料庫優化很簡單, 幾乎每個DBA的履歷上都寫着精通資料庫優化. 而實際情況卻是大部分DBA遇到資料庫性能問題時, 還是希望能通過修改資料庫的初始化參數得到解決, 最多也隻是加幾個簡單索引, 固定一些SQL的執行計劃而已. 很少從業務和開發的角度提出解決性能問題的根本原因.
而開發人員對越來越慢的系統, 有的會抱怨DBA維護水準差, 有的會認為硬體資源不足, 較少從架構設計/SQL寫法等自身原因分析問題, 而這部分恰恰是導緻性能惡化問題的主要根源.
當DBA和開發把知道的優化方法都使出來之後, 資料庫性能還是不能得到明顯的改善, 就隻能寄希望于進階硬體上了, 進階硬體能解決一大部分性能問題, 但是無法解決諸如SQL寫法低效/少索引/統計資訊過舊/參數設定不當/優化器缺陷等其他諸多問題.