淺談oracle查詢性能優化

    工作過程中對oracle使用過程中遇到問題的解決方法進行彙總，友善後續自己查詢。歡迎大家勘正

性能問題主要展現在資料查詢沒有在最長可接受時間範圍内出結果，對應到資料庫底層來講，資料庫記憶體進行大量的資料頁交換，大量的磁盤讀寫。

針對這種情況，确認資料庫的SGA大小，可以考慮将資料檔案分盤存放(調整表空間資料檔案的存放位置)，降低磁盤的通路壓力；調整資料表中的資料和索引存放至不同表空間；

 首先，确認查詢sql語句是否拼接編寫，是否使用綁定變量的方式；

 其次，檢視驗證查詢sql語句多表組合編寫方式，先過濾再組合；

 接着，檢視查詢sql語句的執行計劃，主要檢視對表掃描的方式，以及對索引的執行情況

 查詢中用到的各種hint方式主要有以下形式：

<1> /*+ALL_ROWS*/

:Uno,M-S4u0表明對語句塊選擇基于開銷的優化方法,并獲得最佳吞吐量,使資源消耗最小化.ITPUB個人空間Yk3B3P ZJ/w

<2>. /*+FIRST_ROWS*/

O0CfP_F3O0表明對語句塊選擇基于開銷的優化方法,并獲得最佳響應時間,使資源消耗最小化.

0_8LGx&[G0f4w0

<3>. /*+CHOOSE*/ITPUB個人空間`0Xbzf  u1C

表明如果資料字典中有通路表的統計資訊,将基于開銷的優化方法,并獲得最佳的吞吐量;

&HIQ$Q#@  fC3p b0表明如果資料字典中沒有通路表的統計資訊,将基于規則開銷的優化方法;ITPUB個人空間#SI:F*B|{zRn

<4>. /*+RULE*/

5i;b.g+pNj.v0表明對語句塊選擇基于規則的優化方法.

,@w_$R!dFJ3_UZ0

<5>. /*+FULL(TABLE)*/

6~+L&Ui$_0表明對表選擇全局掃描的方法.ITPUB個人空間~9Rf!F'Wh9H_r

<6>. /*+ROWID(TABLE)*/

1GTBP#p0提示明确表明對指定表根據ROWID進行通路.

~/g~4@:r'R\&f0

<7>. /*+CLUSTER(TABLE)*/

?ueD,n,z8@0提示明确表明對指定表選擇簇掃描的通路方法,它隻對簇對象有效.

_)[v4]?0

<8>. /*+INDEX(TABLE INDEX_NAME)*/

^Z0}A0SY0表明對表選擇索引的掃描方法.

'l;L@8vJK U wB,J;}0

<9>. /*+INDEX_ASC(TABLE INDEX_NAME)*/ITPUB個人空間7iEkMz+ch

表明對表選擇索引升序的掃描方法.ITPUB個人空間sqj%|noo

<10>. /*+INDEX_COMBINE*/

x ZdHO.BkjD6y0為指定表選擇位圖通路路經,如果INDEX_COMBINE中沒有提供作為參數的索引,将選擇出位圖索引的布爾組合方式.

"p4JqT~Q6T0

<11>. /*+INDEX_JOIN(TABLE INDEX_NAME)*/ITPUB個人空間!byRi L8A w&],E

提示明确指令優化器使用索引作為通路路徑.ITPUB個人空間/f6xi$m!~~;I

<12>. /*+INDEX_DESC(TABLE INDEX_NAME)*/

X~*J,Dj6^SM8tSZ![C0表明對表選擇索引降序的掃描方法.

/vrV}:u|0i3O^0l&

<13>. /*+INDEX_FFS(TABLE INDEX_NAME)*/ITPUB個人空間s*`emKTm%ME

對指定的表執行快速全索引掃描,而不是全表掃描的辦法.ITPUB個人空間#drx8V\$Ly

<14>. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/

4o0uSJ'cQ0提示明确進行執行規劃的選擇,将幾個單列索引的掃描合起來.ITPUB個人空間*MHp cc~

cp\-o)|_u^0

<15>. /*+USE_CONCAT*/

Lt$t5W%Q  FqA%_P&v0對查詢中的WHERE後面的OR條件進行轉換為UNION ALL的組合查詢.ITPUB個人空間esjY2p{D`

<16>. /*+NO_EXPAND*/ITPUB個人空間jbB*nT*dZO ]W

對于WHERE後面的OR  或者IN-LIST的查詢語句,NO_EXPAND将阻止其基于優化器對其進行擴充.

_'RcOUL0

<17>. /*+NOWRITE*/

+e*Is&mrz0禁止對查詢塊的查詢重寫操作.

<18>. /*+REWRITE*/ITPUB個人空間!vX}hk)r0R

可以将視圖作為參數.

<19>. /*+MERGE(TABLE)*/

3Z$ko1kROmy1s0能夠對視圖的各個查詢進行相應的合并.ITPUB個人空間J\V;Kd,]

<20>. /*+NO_MERGE(TABLE)*/

'J1\K ~_LKRrj0對于有可合并的視圖不再合并.

7L2O?iVF4r,E2Fm0t*U,fa8].\#C0ITPUB個人空間pZ1Q  

<21>. /*+ORDERED*/ITPUB個人空間Oic#CPf

根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接配接.ITPUB個人空間v9M(h7jU$xQ

<22>. /*+USE_NL(TABLE)*/ITPUB個人空間6uKPY!ld%KS'l

将指定表與嵌套的連接配接的行源進行連接配接,并把指定表作為内部表.

L9EzA ]"BMS+iJ0

<23>. /*+USE_MERGE(TABLE)*/ITPUB個人空間,`L&E6sg f

将指定的表與其他行源通過合并排序連接配接方式連接配接起來.

N r6o"} F5bB*|6c*B0

<24>. /*+USE_HASH(TABLE)*/

Tc\IR(~0将指定的表與其他行源通過哈希連接配接方式連接配接起來.ITPUB個人空間0G.Se'L]!Eo)e

<25>. /*+DRIVING_SITE(TABLE)*/ITPUB個人空間1m  U.^A:i9G

強制與ORACLE所選擇的位置不同的表進行查詢執行.

O%NoC\&S0

<26>. /*+LEADING(TABLE)*/ITPUB個人空間"fY4b/FZ"C&i

将指定的表作為連接配接次序中的首表.ITPUB個人空間qIrg6e qN

4K VSS7p;mw7x0<27>.  /*+CACHE(TABLE)*/

3mn0_O @K.M$t.Lz0當進行全表掃描時,CACHE提示能夠将表的檢索塊放置在緩沖區緩存中最近最少清單LRU的最近使用端ITPUB個人空間3B^MWr'[@Y

<28>. /*+NOCACHE(TABLE)*/

5Kn$Ba0B$d_0當進行全表掃描時,CACHE提示能夠将表的檢索塊放置在緩沖區緩存中最近最少清單LRU的最近使用端ITPUB個人空間Z;_!K^GW"Wk;A2UC

<29>. /*+APPEND*/

q?8lA5kcc0直接插入到表的最後,可以提高速度.

-\;c$Hn/kBB(`+^;~

<30>. /*+NOAPPEND*/

wY"f?(R)mj0通過在插入語句生存期内停止并行模式來啟動正常插入.