Oracle Database/SQL Tuning 的幾個方法

*補充一下 以下為本人意見 跟任何資料庫理論無關、也跟任何原廠課程無關 
所以您可能在業界聽都沒聽過XD


下圖是Oracle Tuning Scope 的範圍  :

在所有的效能問題調教中, 
SQL Tuning往往占了60%的部分、
20%的部分是在架構問題以及物件設計、
而Instance Tuning占了15% , 
OS Tuning占了5%.

往往資料庫慢，DBA是最可憐的，可能被第一個怪罪，
因為必須要舉證是哪邊有問題，所以也要知道AP端是否下了太多無謂的select * 選擇太多欄位或JOIN 或subquery...沒有用綁定變量等等...
所以厲害一點的DBA也要懂得建議AP組有哪些SQL不對，甚至能夠協助改寫或優化SQL

建議在Tuning的順序要  由內而外開始調教 , 當然如果Instance 完全沒Tuning , 是一定不能用在商業運轉的...除非Server Loading 不重 , 從效益來看 , SQL tuning 與 Object Tuning 的效果最佳!!!

1.SQL Tuning : 從各表格條件中查出無謂的查詢條件 , 或重複的查詢條件

              減少查詢的區間 , 或透過where 的改寫 減少result set 的筆數

              比較常用的就是使用以下幾個技巧:

          1.1 避免SQL下重複比對條件
          1.2使用rownum減少查詢筆數
          1.3少用select distinct...(隱含sorting)
          1.4用union all取代union...(union 會把重複資料去除 , 隱含 sorting)
          1.5用NOT EXISTS取代NOT IN (排除小表格資料, 邏輯判斷較快)
          1.6用Inner Join取代Sub-query
          1.7用UNION取代OR
          1.8小心使用Like 語法 (會造成 full table scan /index fast full scan)

  其他技巧

   SQL Hint :  
                       i.force SQL to execute by specific index.

                         觀察where 的條件中, 是否可以強制選用某些索引. 或是使用/*+ FIRST_ROWS */ hint 把JOIN結果一筆一筆先顯示出來.                           

                       ii. Join two tables with different algorithm ( nested loop , merge join , hash join)
                         之前做join tuning 案例 :  http://jaychu649.blogspot.tw/2012/07/oracle-sql-outer-join-performance-tuning.html
                       iii. Other purposes...

詳細用法可以參考http://jaychu649.blogspot.tw/2012/01/hints-of-help-influnce-behaviour-of.html

   SQL profile : (for Oracle 10g+)  , select proper execution plan for specific SQL.

詳細用法 可以參考http://jaychu649.blogspot.tw/2012/08/oracle-10g-sql-tuning-advisor.html

2.Object Tuning : 
              其中隱含著ER-model的設計可能須調整 , 或可減少資料庫表格正規化的程度(正規化太嚴重,資料就會存在幾個不同的表格, 下SQL就常常要去JOIN) , 特別是現在硬碟空間相對便宜, 做正規化節省空間的優點早已不在...舉例如下:

              2.1 Analyze table/index , rebuild index ... etc.

              2.2 Migrate big tables to partition tables.

              2.3 Design Materialized views.
              2.4 Enable 表格壓縮功能(減少Disk IO , 但會增加CPU loading)

3.Instance Tuning : 
  3.1 針對資料庫參數做調教. 如果SGA、PGA採預設值不去優化 , Oracle資料庫跑的慢 是可想而知的.
  3.2 11g以後的DB 建議可以enable huge pages , 先把作業系統中挖一塊記憶體給Instance 的SGA用.
         https://jaychu649.blogspot.tw/2015/12/linux-transparent-huge-pageszy.html

4.Server and Network Tuning:
      OS Kernel Tuning : Oracle Installation Guide 會建議相關OS 的設定值
      Network Tuning : 網路Read / Write buffer , TCP tuning...etc

Ref:
http://dbak.com/wp-content/uploads/2011/02/Automatic-SQL-Tuning-in-Oracle-Database-10g-and-11g.pdf