[分享] Oracle存儲過程編寫經驗和優化措施

http://forum.twbts.com/thread-5943-1-1.html

整理文件時，發現幾年前搜集到的一些文件，出處已不知了，不過內容蠻受用，分享給大家。
1        開發人員如果用到其他庫的Table或View，務必在當前庫中建立View來實現跨庫操作，最好不要直接使用“databse.dbo.table_name”，因為sp_depends不能顯示出該SP所使用的跨庫table或view，不方便校驗。
2        開發人員在提交SP前，必須已經使用set showplan on分析過查詢計畫，做過自身的查詢優化檢查。
3        高程式運行效率，優化應用程式，在SP編寫過程中應該注意以下幾點：
3.1        SQL的使用規範：
3.1.1        儘量避免大事務操作，慎用holdlock子句，提高系統併發能力。
3.1.2        儘量避免反復訪問同一張或幾張表，尤其是資料量較大的表，可以考慮先根據條件提取資料到臨時表中，然後再做連接。
3.1.3        儘量避免使用游標，因為游標的效率較差，如果游標操作的資料超過1萬行，那麼就應該改寫；如果使用了游標，就要儘量避免在游標迴圈中再進行表連接的操作。
3.1.4        注意where字句寫法，必須考慮語句順序，應該根據索引順序、範圍大小來確定條件子句的前後順序，盡可能的讓欄位順序與索引順序相一致，範圍從大到小。
3.1.5        不要在where子句中的“=”左邊進行函數、算術運算或其他運算式運算，否則系統將可能無法正確使用索引。
3.1.6        儘量使用exists代替select count(1)來判斷是否存在記錄，count函數只有在統計表中所有行數時使用，而且count(1)比count(*)更有效率。
3.1.7        儘量使用“>=”，不要使用“>”。
3.1.8        注意一些or子句和union子句之間的替換
3.1.9        注意表之間連接的資料類型，避免不同類型資料之間的連接。
3.1.10        注意存儲過程中參數和資料類型的關係。
3.1.11        注意insert、update操作的資料量，防止與其他應用衝突。如果資料量超過200個資料頁面（400k），那麼系統將會進行鎖升級，頁級鎖會升級成表級鎖。
3.2        索引的使用規範：
3.2.1        索引的創建要與應用結合考慮，建議大的OLTP表不要超過6個索引。
3.2.2        盡可能的使用索引欄位作為查詢條件，尤其是聚簇索引，必要時可以通過index index_name來強制指定索引
3.2.3        避免對大表查詢時進行table scan，必要時考慮新建索引。
3.2.4        在使用索引欄位作為條件時，如果該索引是聯合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用。
3.2.5        要注意索引的維護，週期性重建索引，重新編譯存儲過程。
3.3        tempdb的使用規範：
3.3.1        儘量避免使用distinct、order by、group by、having、join、***pute，因為這些語句會加重tempdb的負擔。
3.3.2        避免頻繁創建和刪除臨時表，減少系統表資源的消耗。
3.3.3        在新建臨時表時，如果一次性插入資料量很大，那麼可以使用select into代替create table，避免log，提高速度；如果資料量不大，為了緩和系統表的資源，建議先create table，然後insert。
3.3.4        如果臨時表的資料量較大，需要建立索引，那麼應該將創建臨時表和建立索引的過程放在單獨一個子存儲過程中，這樣才能保證系統能夠很好的使用到該臨時表的索引。
3.3.5        如果使用到了臨時表，在存儲過程的最後務必將所有的臨時表顯式刪除，先truncate table，然後drop table，這樣可以避免系統表的較長時間鎖定。
3.3.6        慎用大的臨時表與其他大表的連接查詢和修改，減低系統表負擔，因為這種操作會在一條語句中多次使用tempdb的系統表。
3.4        合理的演算法使用：
根據上面已提到的SQL優化技術和ASE Tuning手冊中的SQL優化內容,結合實際應用,採用多種演算法進行比較,以獲得消耗資源最少、效率最高的方法。具體可用ASE調優命令：set statistics io on, set statistics time on , set showplan on 等。

[分享] Oracle中存儲過程和Sql語句的優化重點

http://forum.twbts.com/thread-5944-1-1.html


整理文件時，發現幾年前搜集到的一些文件，出處已不知了，不過內容蠻受用，分享給大家。
1.        全表掃描和索引掃描
1.1        大資料量表儘量要避免全表掃描，全部掃描會按順序每條記錄掃描，對於>100萬資料表影響很大。
1.2        Oracle中通過RowID訪問資料是最快的方式
1.3        對欄位進行函數轉換，或者前模糊查詢都會導致無法應用索引而進行全表掃描
1.4        對Oracle共用池和緩衝區中的Sql必須要大小寫都完全用上才能夠匹配上
2        順序問題
2.1        Oracle按照從右到左的順序對資料表進行解析。因此From最後面的表為基礎表，一般要選擇記錄數最少的表作為基礎表。
2.2        對於Where條件的順序，過濾到最大查詢記錄數量的條件必須寫在Where條件的結尾處。
2.3        Where條件中涉及到使用複雜函數判定的必須注意要寫到Where條件的最前面
3        索引方面
3.1        記錄數少的表保留有主鍵索引就可以了，不要再去建其他索引，全表掃描也很快
3.2        索引最好單獨建立表空間，必要時候對索引進行重建
3.3        必要時候可以使用函數索引，但不推薦使用
3.4        Oracle中的視圖也可以增加索引，但一般不推薦使用
3.5        *Sql語句中大量使用函數時候會導致很多索引無法使用上，要針對具體問題分析
4        其他
4.1        避免使用Select *,因為系統需要去幫你將*轉換為所有的列名，這個需要額外去查詢資料字典。
4.2        Count(1)和Count(*)差別不大。
4.3        多使用Decode函數來作簡單的代碼和名稱間的轉換，以減少表關聯
4.4        使用Truncate替代delete來刪除記錄，但Truncate資料不記錄日誌，無法進行回滾
4.5        對於複雜的存儲過程可以多次提交的資料的要多分多次Commit，否則長事務對系統性能影響很大
4.6        Distinct和Having子句都是耗時操作，應該盡可能少使用
4.7        在不需要考慮重複記錄合併時候用Union All來代替Union
4.8        使用顯性游標而不使用隱性游標，特別是大資料量情況下隱性游標對性能影響很大
4.9        是否使用函數的問題
4.10        用直接的表關聯來代替Exist.用Exist或Not Exists來代理In。In進行子查詢效率很差。
5        SQL語句分析
5.1        通過SQLPLUS中的SET TRACE 功能對Sql語句的性能進行分析
5.2        通過Toad或PL/SQL Developer對語句的性能進行和索引的使用情況進行分析
5.3        對Oracle缺省的優化不滿意可以強制使用Hint，但一般不推薦使用
5.4        對Flag等只存儲是或否資訊的欄位，一般不推薦建立索引。必要可以採用點陣圖索引
5.5        *存在遞迴查詢情況如果關聯Table太多對性能會造成較大影響，往往推薦採用臨時表轉為分步驟操作提高性能
5.6        *儘量使用表關聯查詢而不使用函數，但涉及類似於代碼表要重複關聯多次取資料問題時候又適合使用函數