採用optimizer_index_cost_adj最佳化SQL語句

我們知道，oracle有的時候，可能會因為統計資訊的不準確或者是最佳化器的問題，導致語句選擇錯誤的執行計畫，如本來該走索引掃描的，但是採用了全表掃描。對於很多系統更趨向使用索引掃描，因為往往實際上，索引掃描的代價的確是小於全表掃描的。。。

我們看一個實際的例子
SQL> set autot trace exp
SQL> select count(*) from auction_auctions a, auction_bids b
  2  where a.id=b.auction and auction_type='a'
  3  and ends>to_date('2004-05-21','yyyy-mm-dd')
  4  and approve_status>=0;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=65151 Card=1 Bytes=80)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=65151 Card=955916 Bytes=76473280)
   3    2       VIEW OF 'index$_join$_001' (Cost=64015 Card=95048 Bytes=4467256)
   4    3         HASH JOIN
   5    4           HASH JOIN
   6    5             PARTITION RANGE (ITERATOR)
   7    6               INDEX (FAST FULL SCAN) OF 'IND_AUCTION_ZOO_CLO_STA_CAT' (NON-UNIQUE) (Cost=8713 Card=95048 Bytes=4467256)
   8    5             PARTITION RANGE (ITERATOR)
   9    8               INDEX (FAST FULL SCAN) OF 'PK_AUCTION_AUCTIONS_ID' (UNIQUE) (Cost=8713 Card=95048 Bytes=4467256)
  10    4           PARTITION RANGE (ITERATOR)
  11   10             INDEX (FAST FULL SCAN) OF 'IND_AUC_USER_CLO_TYP_BID_STA' (NON-UNIQUE) (Cost=8713 Card=95048 Bytes=4467256)
  12    2       INDEX (FAST FULL SCAN) OF 'IND_BIDS_AUCTIONSTATUS' (NON-UNIQUE) (Cost=397 Card=955920 Bytes=31545360)
         
在以上的例子中，對該表的一個查詢，採用了索引快速掃描的方法，並通過hash串連多個掃描結果，對於一個非常大的表，查詢時間在10s以下。

但是，當我們修改查詢條件為如下時：
     
SQL> select count(*) from auction_auctions a, auction_bids b
  2  where a.id=b.auction and auction_type='a'
  3  and ends>to_date('2004-07-01','yyyy-mm-dd')
  4  and approve_status>=0;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=35001 Card=1 Bytes=80)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=35001 Card=807907 Bytes=64632560)
   3    2       PARTITION RANGE (ITERATOR)
   4    3         TABLE ACCESS (FULL) OF 'AUCTION_AUCTIONS' (Cost=33888 Card=68567 Bytes=3222649)
   5    2       INDEX (FAST FULL SCAN) OF 'IND_BIDS_AUCTIONSTATUS' (NON-UNIQUE) (Cost=397 Card=955920 Bytes=31545360)
 
這個時候，我們可以看到，Oracle採用了全表掃描，雖然這個查詢語句所查詢的記錄數比上個查詢的記錄數要少，但是因為這個表是已ends分區的分區表，當ends>07-01時，只需要在一個分區中查詢即可，Oracle錯誤的計算了成本（Cost=35001，遠遠小於上面採用索引的成本Cost=65151），所以採用在分區範圍內的全表掃描。
但是實際情況是，該查詢需要>10分鐘才能獲得查詢結果。顯然，這裡oracle計算成本的時候是有問題的，這裡的全表掃描的速度遠遠低於索引掃描的時間。

那麼，我們有什麼辦法能讓oracle回到索引掃描的執行計畫呢？我們首先考慮hint。
SQL> select /*+ index(a IND_AUCTION_ZOO_CLO_STA_CAT) index(a PK_AUCTION_AUCTIONS_ID) index(a IND_AUC_USER_CLO_TYP_BID_STA) */
  2  count(*) from auction_auctions a, auction_bids b
  3  where a.id=b.auction and auction_type='a'
  4  and ends>to_date('2004-07-21','yyyy-mm-dd')
  5  and approve_status>=0;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=143358 Card=1 Bytes=80)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=143358 Card=807907 Bytes=64632560)
   3    2       PARTITION RANGE (ITERATOR)
   4    3         TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'AUCTION_AUCTIONS' (Cost=142245 Card=68567 Bytes=3222649)
   5    4           INDEX (SKIP SCAN) OF 'IND_AUC_USER_CLO_TYP_BID_STA' (NON-UNIQUE) (Cost=28314 Card=152804)
   6    2       INDEX (FAST FULL SCAN) OF 'IND_BIDS_AUCTIONSTATUS' (NON-UNIQUE) (Cost=397 Card=955920 Bytes=31545360)
 
可以看到，不管是怎麼指定索引，Oracle只能是很死板的選用一個它認為最快的索引，而不會使用上面的比較最佳化的多個索引快速掃描的方法，而且通過指定的索引掃描，oracle計算出來的成本也很大（Cost=143358），實際上，該語句的執行也是非常慢，甚至慢於分區全表掃描。
 
有什麼辦法，能讓oracle最簡單的使用上面的執行計畫呢？我們可以考慮參數OPTIMIZER_INDEX_COST_ADJ，這個初始化參數代表一個百分比,取值範圍在1到10000之間。該參數表示索引掃描和全表掃描成本的比較，預設值100表示索引掃描成本等價轉換與全表掃描成本。

我們修改該參數值
SQL> alter session set optimizer_index_cost_adj = 40;
Session altered.

SQL> select count(*) from auction_auctions a, auction_bids b
  2  where a.id=b.auction and auction_type='a'
  3  and ends>to_date('2004-07-01','yyyy-mm-dd')
  4  and approve_status>=0;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=30505 Card=1 Bytes=80)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=30505 Card=807907 Bytes=64632560)
   3    2       VIEW OF 'index$_join$_001' (Cost=29391 Card=68567 Bytes=3222649)
   4    3         HASH JOIN
   5    4           HASH JOIN
   6    5             PARTITION RANGE (ITERATOR)
   7    6               INDEX (FAST FULL SCAN) OF 'IND_AUCTION_ZOO_CLO_STA_CAT' (NON-UNIQUE) (Cost=3484 Card=68567 Bytes=3222649)
   8    5             PARTITION RANGE (ITERATOR)
   9    8               INDEX (FAST FULL SCAN) OF 'PK_AUCTION_AUCTIONS_ID' (UNIQUE) (Cost=3484 Card=68567 Bytes=3222649)
  10    4           PARTITION RANGE (ITERATOR)
  11   10             INDEX (FAST FULL SCAN) OF 'IND_AUC_USER_CLO_TYP_BID_STA' (NON-UNIQUE) (Cost=3484 Card=68567 Bytes=3222649)
  12    2       INDEX (FAST FULL SCAN) OF 'IND_BIDS_AUCTIONSTATUS' (NON-UNIQUE) (Cost=397 Card=955920 Bytes=31545360)
 
這個時候，我們發現，oracle又選擇了我們所希望的執行計畫，在這個執行計畫中，成本是30505
我們可以得到，Oracle認為走該執行計畫的原始成本是
(30505/40)*100=76263