Oracle 資料庫最佳化實戰心得總結

1.最佳化應用程式和商務邏輯，這個是最重要的。

2.資料庫設計階段範式和反範式的靈活應用。一般情況下，對於頻繁訪問但是不頻繁修改的資料，內部設計應當物理不正常化；對於頻繁修改但並不頻繁訪問的資料，內部設計應當物理正常化。

3.充分利用記憶體，最佳化sga、pga等(11g已經實現了sga+pga自動化，但有的時候仍然需要手動進行調整)，適當的將小表keep到cache中。

4.最佳化sql語句
1）減少對資料庫的查詢次數，即減少對系統資源的請求，使用快照和顯形圖等分散式資料庫對象可以減少對資料庫的查詢次數。
2）盡量使用相同的或非常類似的SQL語句進行查詢，這樣不僅充分利用SQL共用池中的已經分析的文法樹，要查詢的資料在SGA中命中的可能性也會大大增加。
3）限制動態SQL的使用，雖然動態SQL很好用，但是即使在SQL共用池中有一個完全相同的查詢值，動態SQL也會重新進行文法分析。
4）避免不帶任何條件的SQL語句的執行。沒有任何條件的SQL語句在執行時，通常要進行FTS，資料庫先定位一個資料區塊，然後按順序依次尋找其它資料，對於大型表這將 是一個漫長的過程。
5）如果對有些表中的資料有約束，最好在建表的SQL語句用描述完整性來實現，而不是用SQL程式中實現。
6）可以通過取消自動認可模式，將SQL語句彙集一組執行後集中提交，程式還可以通過顯式地用COMMIT和ROLLBACL進行提交和復原該事務。
7）檢索大量資料時費時很長，設定行預取數則能改善系統的工作表現，設定一個最大值，當SQL語句返回行超過該值，數值庫暫時停止執行，除非使用者發出新的指令，開始組織並顯示資料，而不是讓使用者繼續等待。

5.最佳化io，將不同的資料檔案、控制檔案、記錄檔放在不同的磁碟，表和索引放在不同的資料表空間，設定合適的block大小，設定非同步io等。

6.適當建立唯讀資料表空間，減少資料區塊頭的更新和資料庫故障的發生。

7.表設計最佳化
1）分區
2）壓縮
3）建立合適的索引
4）設定合適的pctfree，減少行串連和行遷移
5）設定合適的storage，控製表片段
6）其他等

8.充分利用系統cpu資源，使用Parallel Query Option(PQO,並行查詢選擇)方式進行資料查詢，使用PQO方式不僅可以在多個CPU間分配SQL語句的請求處理，當所查詢的資料處於不同的磁碟時，一個個獨立的進程可以同時進行資料讀取。

9.最佳化資料庫連接
1）使用直接的OLE DB資料庫連接方式。
通過ADO可以使用兩種方式串連資料庫，一種是傳統的ODBC方式，一種是OLE DB方式。ADO是建立在OLE DB技術上的，為了支援ODBC，必須建立相應的OLE DB到ODBC的調用轉換，而使用直接的OLE DB方式則不需轉換，從而提高處理速度。
2）使用Connection Pool機制
在資料庫處理中，資源花銷最大的是建立資料庫連接，而且使用者還會有一個較長的串連等待時間。解決的辦法就是複用現有的Connection，也就是使用Connection Pool對象機制。
Connection Pool的原理是：IIS+ASP體系中維持了一個串連緩衝池，這樣，當下一個使用者訪問時，直接在串連緩衝池中取得一個資料庫連接，而不需重新串連資料庫，因此可以大大地提高系統的響應速度。

10.充分利用資料的幕後處理方案減少網路流量
1）合理建立暫存資料表或視圖
所謂建立暫存資料表或視圖，就是根據需要在資料庫基礎上建立新表或視圖，對於多表關聯後再查詢資訊的可建新表，對於單表查詢的可建立視圖，這樣可充分利用資料庫的容量大、可擴充性強等特點，所有條件的判斷、數值計算統計均可在資料庫伺服器後台統一處理後追加到暫存資料表中，形成資料結果的過程可用資料庫的過程或函數來實現。
2）資料庫打包技術的充分利用
利用資料庫描述語言編寫資料庫的過程或函數，然後把過程或函數打成包在資料庫後台統一運行包即可。
3）資料複製、快照、視圖，遠端程序呼叫技術的運用
資料複製，即將資料一次複製到本地，這樣以後的查詢就使用本機資料，但是只適合那些變化不大的資料。使用快照也可以在分散式資料庫之間動態複製資料，定義快照的自動重新整理時間或手工重新整理，以保證資料的引用參照完整性。調用遠程過程也會大大減少因頻繁的SQL語句調用而帶來的網路擁擠。

11.實施系統資源管理分配計劃
ORACLE提供了Database Resource Manager（DRM,資料庫總管）來控制使用者的資源分派，DBA可以用它分配使用者類和作業類的系統資源百分比。在一個OLDP系統中，可給聯機使用者指派75%的CPU資源，剩下的25%留給批使用者。另外，還可以進行CPU的多級分配。除了進行CPU資源分派外，DRM還可以對資源使用者組執行並行操作的限制。