oracle 學習筆記---效能優化（3）

zhengbao_jun發表於2010-08-04

9.怎麼避免使用特定索引

在很多時候，Oracle會錯誤的使用索引而導致效率的明顯下降，我們可以使用一點點技巧而避免使用不該使用的索引，如：

表test,有欄位a,b,c,d，在a,b,c上建立聯合索引inx_a(a,b,c)，在b上單獨建立了一個索引Inx_b(b)。

在正常情況下，where a=? and b=? and c=?會用到索引inx_a，where b=?會用到索引inx_b，但是，where a=? and b=? and c=? group by b會用到哪個索引呢？在分析資料不正確（很長時間沒有分析）或根本沒有分析資料的情況下，oracle往往會使用索引inx_b。通過執行計劃的分析，這個索引的使用，將大大耗費查詢時間。

當然，我們可以通過如下的技巧避免使用inx_b，而使用inx_a。

where a=? and b=? and c=? group by b||'' --如果b是字元

where a=? and b=? and c=? group by b+0 --如果b是數字

通過這樣簡單的改變，往往可以是查詢時間提交很多倍

當然，我們也可以使用no_index提示，相信很多人沒有用過，也是一個不錯的方法：

select /*+ no_index(t,inx_b) */ * from test t

where a=? and b=? and c=? group by b

舉例：

本來在CM_USER上有索引IDX_CM_USER4(ACC_ID)和IDX_CM_USER8(BILL_ID),可是執行如下語句的時候很慢。

select * from CM_USER where acc_id =1200007175

and user_status>0 and bill_id like '13%' order by acc_id,bill_id

用explain分析，發現執行計劃是用IDX_CM_USER8.如下查詢

select * from user_indexes where table_name ='CM_USER' 發現IDX_CM_USER8沒有分析過。

用下面語句執行計劃改變

select /*+INDEX(CM_USER IDX_CM_USER4)*/* from CM_USER where acc_id =1200007175 and user_status>0 and bill_id like '13%' order by acc_id,bill_id

或者分析索引

exec dbms_stats.gather_index_stats(ownname => 'QACS1',indname => 'IDX_CM_USER8',estimate_percent => 5 );

可以發現執行計劃恢復正常。

10.Oracle什麼時候會使用跳躍式索引掃描

這是9i的一個新特性跳躍式索引掃描(Index Skip Scan).

例如表有索引index(a,b,c)，當查詢條件為where b=?的時候，可能會使用到索引index(a,b,c)，如，執行計劃中出現如下計劃：

INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE)

Oracle的優化器(這裡指的是CBO)能對查詢應用Index Skip Scans至少要有幾個條件:

<1> 優化器認為是合適的。

<2> 索引中的前導列的唯一值的數量能滿足一定的條件（如重複值很多）。

<3> 優化器要知道前導列的值分佈(通過分析/統計表得到)。

<4> 合適的SQL語句

等。

11.怎麼樣建立使用虛擬索引

可以使用nosegment選項，如

create index virtual_index_name on table_name(col_name) nosegment;

如果在哪個session需要測試虛擬索引，可以利用隱含引數來處理

alter session set "_use_nosegment_indexes" = true;

就可以利用explain plan for select ……來看虛擬索引的效果，利用@$ORACLE_HOME/rdbms/admin/utlxpls檢視執行計劃，最後，根據需要，我們可以刪除虛擬索引，如普通索引一樣

drop index virtual_index_name;

注意：虛擬索引並不是物理存在的，所以虛擬索引並不等同於物理索引，不要用自動跟蹤去測試虛擬索引，因為那是實際執行的效果，是用不到虛擬索引的。

12.怎樣監控無用的索引

Oracle 9i以上，可以監控索引的使用情況，如果一段時間內沒有使用的索引，一般就是無用的索引

語法為：

開始監控：alter index index_name monitoring usage;

檢查使用狀態：select * from v$object_usage;

停止監控：alter index index_name nomonitoring usage;

當然，如果想監控整個使用者下的索引，可以採用如下的指令碼：

set heading off

set echo off

set feedback off

set pages 10000

spool start_index_monitor.sql

SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'

FROM dba_indexes

WHERE wner = USER;

spool off

set heading on

set echo on

set feedback on

------------------------------------------------