Oracle 數據庫優化實戰心得總結
2024-08-29 13:55:39
供稿：網友
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還可以對資源用戶組執行并行操作的限制。
