列上"資料特殊"、優化器統計資訊的問題導致死活不走index診斷、調整過程

這是我長期維護的一家專科醫院的系統，客戶也沒有反應慢，我在執行常規的例行檢查時發現DB Time很大，達到2,221.28，這是8點～9點的awr報告，9點～10點的達到3000多了，我認為系統會非常的慢，客戶資訊中心人員說沒人打電話反應慢，看到這種情況不能無動於衷了，等待事件就不用看了，平常就是gc buffer busy，建議客戶修改應用的連線字串，讓同類應用從一個節點連進去，他們一直也沒有調整。[@more@]

1、[oracle@rac1 awr]$ more awrrpt_1_21391_21392.txt
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
RAC 2322290752 rac1 1 10.2.0.4.0 YES rac1
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 21391 15-Apr-13 08:00:30 296 9.4
End Snap: 21392 15-Apr-13 09:00:04 436 10.4
Elapsed: 59.57 (mins)
DB Time: 2,221.28 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 12,144M 14,016M Std Block Size: 8K
Shared Pool Size: 4,144M 2,272M Log Buffer: 14,316K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 27,649.10 3,155.32
Logical reads: 87,752.83 10,014.37
Block changes: 150.97 17.23
Physical reads: 1,917.31 218.80
Physical writes: 9.56 1.09
User calls: 267.23 30.50
Parses: 127.66 14.57
Hard parses: 7.03 0.80
Sorts: 18.57 2.12
Logons: 0.72 0.08
Executes: 389.24 44.42
Transactions: 8.76
% Blocks changed per Read: 0.17 Recursive Call %: 65.33
Rollback per transaction %: 11.88 Rows per Sort: 32.99
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 91.99 Redo NoWait %: 99.98
Buffer Hit %: 97.82 In-memory Sort %: 100.00
Library Hit %: 95.57 Soft Parse %: 94.50
Execute to Parse %: 67.20 Latch Hit %: 98.53
Parse CPU to Parse Elapsd %: 6.53 % Non-Parse CPU: 99.05
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 88.78 58.26
% SQL with executions>1: 96.37 89.03
% Memory for SQL w/exec>1: 96.12 80.51
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
gc buffer busy 22,598,195 85,541 4 64.2 Cluster
read by other session 2,552,261 14,842 6 11.1 User I/O
CPU time 9,420 7.1
db file scattered read 749,868 5,758 8 4.3 User I/O
db file sequential read 429,187 4,219 10 3.2 User I/O
--============================================
從awr報告來看db time時間非常大2,221.28，系統緩慢，過度消耗資源的sql如下：
369 8 1 369.4 0.3 fs1ky6wzgq7f8
Module: rbpacs.ui.exe
Update PATIENT_EXAM_IMAGE_RECORD set isupload='1' where FILENAME='1.2.840.10008.
20130415084746.472488001.1343983600.dcm'
337 9 1 336.7 0.3 87xwggmtcxdc6
Module: rbpacs.ui.exe
Update PATIENT_EXAM_IMAGE_RECORD set isupload='1' where FILENAME='1.2.840.10008.
20130415084401.386039390.1644951623.dcm'
288 6 1 288.3 0.2 c1kfcbwu3s05w
Module: rbpacs.ui.exe
Update PATIENT_EXAM_IMAGE_RECORD set isupload='1' where FILENAME='1.2.840.10008.
20130415084514.7037250269.90236399.dcm'
276 5 1 276.2 0.2 bzb1sqd2zypug
Module: rbpacs.ui.exe
Update PATIENT_EXAM_IMAGE_RECORD set isupload='1' where FILENAME='1.2.840.10008.
20130415084921.482057139.1317304679.dcm'
260 5 1 260.0 0.2 8vvhcdbp384n5
Module: rbpacs.ui.exe
Update PATIENT_EXAM_IMAGE_RECORD set isupload='1' where FILENAME='1.2.840.10008.
20130415084758.649431742.977802721.dcm'
259 6 1 258.8 0.2 gwuf1abajq3dq
Module: rbpacs.ui.exe
Update PATIENT_EXAM_IMAGE_RECORD set isupload='1' where FILENAME='1.2.840.10008.
20130415084521.749609468.847129272.dcm'
245 6 1 245.0 0.2 1zyxznd5yxsun
Module: rbpacs.ui.exe
Update PATIENT_EXAM_IMAGE_RECORD set isupload='1' where FILENAME='1.2.840.10008.
20130415084559.378861792.1676115503.dcm'
245 235 128 1.9 0.2 a04uqawmn2rur
Module: his_mzysz.exe
SELECT COUNT(*) FROM RESERVATION_RECORD WHERE ZNO = :B1
243 7 1 242.6 0.2 2p2jwyh4cp43d
Module: rbpacs.ui.exe
Update PATIENT_EXAM_IMAGE_RECORD set isupload='1' where FILENAME='1.2.840.10008.
20130415085001.1444328020.439661986.dcm'
--========================================
仔細觀查sql，發現就是由一個動作引起的：
Update PATIENT_EXAM_IMAGE_RECORD set isupload='1' where FILENAME='1.2.840.10008.
20130415085001.1444328020.439661986.dcm'
--========================================
從段的統計資訊上也能看出這個段上的logical read比其它物件要大很多：
SQL> select * from (
2 select inst_id,object_name,statistic_name,value from gv$segment_statistics order by value desc
3 )
4 where rownum<=20
5 ;
INST_ID OBJECT_NAME STATISTIC_NAME VALUE
---------- ------------------------------ -------------------- -------------------
1 PATIENT_EXAM_IMAGE_RECORD logical reads 22477877984
2 PATIENT_EXAM_IMAGE_RECORD logical reads 19996543760
2 PATIENT_EXAM_ITEM logical reads 4131012048
1 PATIENT_EXAM_ITEM logical reads 4000593760
2 MZYS_GHXX logical reads 2287062832
1 MZYS_GHXX logical reads 1843601424
2 ZY_BRZLB logical reads 1470320640
1 ZY_BRZLB logical reads 1299949888
2 PATIENT_EXAM logical reads 1293485392
1 RESERVATION_RECORD logical reads 1289239232
2 RESERVATION_RECORD logical reads 1213992704
1 PATIENT_EXAM logical reads 1208861936
2 SYS_LOB0000061979C00004$$ space used 1117192192
1 SYS_LOB0000061979C00004$$ space used 1105928192
1 SYS_LOB0000061979C00004$$ space allocated 1105199104
2 SYS_LOB0000061979C00004$$ space allocated 1093664768
2 PATIREGIINFO logical reads 1076013072
2 EPR_DIAGNOSIS logical reads 1031177056
2 CARD_REGISTER logical reads 1022895520
1 CARD_REGISTER logical reads 1016045792
20 rows selected.
SQL>
為什麼會這麼慢，看看上面慢的其中一條update語句的執行計劃吧：
SQL> select a.OPERATION,a.OPTIONS,a.COST,a.CPU_COST,a.IO_COST,a.TIME from v$sql_plan a where sql_id='g5mmarggz4088';
OPERATION OPTI COST CPU_COST IO_COST TIME
-------------------- ---- ---------- ---------- ---------- ----------
UPDATE STATEMENT 41421
UPDATE
TABLE ACCESS FULL 41421 2324093222 41266 498
SQL>
執行計劃裡很顯然是FULL，期間通過v$session_longops監控也發現訪問表PATIENT_EXAM_IMAGE_RECORD都是full
進一步檢視發現filename上有index：
SQL> select index_name from dba_ind_columns where table_name='PATIENT_EXAM_IMAGE_RECORD'
2 and column_name='FILENAME'
3 ;
INDEX_NAME
------------------------------
INDEX_FILENAME
SQL>
看看index的優化器統計資訊吧：
SQL> select b.blevel,b.leaf_blocks,b.distinct_keys,b.avg_leaf_blocks_per_key,b.avg_data_blocks_per_key,b.clustering_factor,b.status,b.last_analyzed
2 from dba_indexes b
3 where table_name='PATIENT_EXAM_IMAGE_RECORD'
4 and index_name='INDEX_FILENAME'
5 ;
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS LAST_ANALYZED
---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- --------------
3 97681 1 97681 3111545 3111545 VALID 02-4月 -13
SQL>
這裡我們發現index的統計資訊很顯然對執行計劃來說不利於使用index，因為DISTINCT_KEYS是1，也就是說這一列上幾乎都是重複的值，
我查了這個表上這一列的值發現幾乎沒有重複的，所以我斷定
問題是出在index的optimizer statistics上：
--====================================
index的大小：
SQL> select owner,segment_name,bytes/1024/1024/1024 g from dba_segments where segment_name='INDEX_FILENAME';
OWNER SEGMENT_NAME G
---------- ------------------------------ ----------
RBPACS_ORA INDEX_FILENAME .7578125
表的大小：
表RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD比較大1.6g左右：
SQL> select owner,segment_name,bytes/1024/1024/1024 g from dba_segments where segment_name='PATIENT_EXAM_IMAGE_RECORD';
OWNER SEGMENT_NAME G
---------- ------------------------------ ----------
RBPACS_ORA PATIENT_EXAM_IMAGE_RECORD 1.56933594
--===================================
SQL> Alter index RBPACS_ORA.INDEX_FILENAME rebuild online;
索引已更改。
SQL>
重新index之後發現優化器統計資訊已經沒有問題了，這個時侯還是
沒有喚醒我去看看列上的優化器統計資訊，直到做了10053之後看到trace裡table和index的
優化器統計資訊都沒有問題，看到trace裡有列的優化器統計資訊，於是查詢了列上的優化器統計資訊，最後發現了問題
，這裡感謝rollingpig提醒我做10053。
SQL>select b.blevel,b.leaf_blocks,b.distinct_keys,b.avg_leaf_blocks_per_key,b.avg_data_blocks_per_key,b.clustering_factor,b.status,b.last_analyzed
2 from dba_indexes b
3 where table_name='PATIENT_EXAM_IMAGE_RECORD'
4 and index_name='INDEX_FILENAME'
5 ;
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS LAST_ANALYZED
---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- --------------
3 59142 3597299 1 1 3096805 VALID 15-4月 -13
SQL>
重建之後index的大小：
SQL> select owner,segment_name,bytes/1024/1024/1024 g from dba_segments where segment_name='INDEX_FILENAME';
OWNER SEGMENT_NAME G
---------- ------------------------------ ----------
RBPACS_ORA INDEX_FILENAME .459960938
SQL>
重建index之後發現還是full，不走index，嘗試重新蒐集表的優化器統計資訊，蒐集之後發現索引上面的優化器統計資訊再次回到了重新建立index之前的資訊：
SQL>
exec dbms_stats.gather_table_stats('RBPACS_ORA','PATIENT_EXAM_IMAGE_RECORD');
PL/SQL procedure successfully completed.
SQL> SQL> SQL>
SQL>
SQL> select b.blevel,b.leaf_blocks,b.distinct_keys,b.avg_leaf_blocks_per_key,b.avg_data_blocks_per_key,b.clustering_factor,b.status,b.last_analyzed
2 from dba_indexes b
3 where table_name='PATIENT_EXAM_IMAGE_RECORD'
4 and index_name='INDEX_FILENAME'
5 ;
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS LAST_ANALYZED
---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- --------------
3 58044 1 58044 3058616 3058616 VALID 15-4月 -13
SQL>
這裡我以為index有問題了，於是嘗試分析一下表和index的結構，分析之前查過index的狀態了，確實valid：
SQL> analyze table RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD validate structure cascade online;
表已分析。
SQL> exec dbms_stats.gather_table_stats('RBPACS_ORA','PATIENT_EXAM_IMAGE_RECORD',cascade=>true);
PL/SQL 過程已成功完成。
蒐集之後問題依舊，想重新建立一下index試試：
SQL> drop index RBPACS_ORA.INDEX_FILENAME;
索引已刪除。
SQL> create index RBPACS_ORA.INDEX_FILENAME on RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD (FILENAME)
2 tablespace RBPACS_ORA_INDEX
3 pctfree 10
4 initrans 2
5 maxtrans 255
6 storage
7 (
8 initial 64K
9 minextents 1
10 maxextents unlimited
11 );
索引已建立。
重建之後問題依舊，嘗試shrink一下表吧
SQL> alter table RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD enable row movement;
表已更改。
SQL> alter table RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD shrink space;
表已更改。
SQL> exec dbms_stats.gather_table_stats('RBPACS_ORA','PATIENT_EXAM_IMAGE_RECORD',cascade=>true);
PL/SQL 過程已成功完成。
SQL>
SQL> alter system flush buffer_cache;
系統已更改。
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;

System altered.
SQL>
上面我折騰了一頓問題依舊...加提示可以走index：
--===============================================
SQL> select /*+ index(PATIENT_EXAM_IMAGE_RECORD INDEX_FILENAME) */ * from RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD where FILENAME='1.2.840.10008.20130416082353.354293646.1792346492.dcm';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3241136475
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3585K| 1282M| 3148K (1)| 10:29:38 |
| 1 | TABLE ACCESS BY INDEX ROWID| PATIENT_EXAM_IMAGE_RECORD | 3585K| 1282M| 3148K (1)| 10:29:38 |
|* 2 | INDEX RANGE SCAN | INDEX_FILENAME | 3585K| | 59400 (1)| 00:11:53 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FILENAME"=U'1.2.840.10008.20130416082353.354293646.1792346492.dcm')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1799 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD where FILENAME='1.2.840.10008.20130416082353.354293646.1792346492.dcm' ;
Elapsed: 00:00:01.82
Execution Plan
----------------------------------------------------------
Plan hash value: 4170066599
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3585K| 1282M| 43830 (1)| 00:08:46 |
|* 1 | TABLE ACCESS FULL| PATIENT_EXAM_IMAGE_RECORD | 3585K| 1282M| 43830 (1)| 00:08:46 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FILENAME"=U'1.2.840.10008.20130416082353.354293646.1792346492.dcm')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
194105 consistent gets
0 physical reads
0 redo size
1799 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
很明顯走index和full邏輯讀差了很多：194105和6，簡直相差太大了。

SQL> show parameter mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string CHOOSE
remote_dependencies_mode string TIMESTAMP
SQL>
SQL>
SQL> alter session set optimizer_mode=rule;
Session altered.
SQL> set autotrace traceonly
SQL> select * from RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD where FILENAME='1.2.840.10008.20130416082353.354293646.1792346492.dcm' ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3241136475
-----------------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| PATIENT_EXAM_IMAGE_RECORD |
|* 2 | INDEX RANGE SCAN | INDEX_FILENAME |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FILENAME"=U'1.2.840.10008.20130416082353.354293646.179234
6492.dcm')
Note
-----
- rule based optimizer used (consider using cbo)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1799 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
--=============================================

SQL> drop index RBPACS_ORA.INDEX_FILENAME;

Index dropped.

SQL> create index RBPACS_ORA.INDEX_FILENAME on RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD (FILENAME) reverse

2 tablespace RBPACS_ORA_INDEX

3 pctfree 10

4 initrans 2

5 maxtrans 255

6 storage

7 (

8 initial 64K

9 minextents 1

10 maxextents unlimited

11 );

Index created.

嘗試reverse index問題依舊...

--=============================================
SQL> alter session set tracefile_identifier=10053;
Session altered.
SQL> alter session set events='10053 trace name context forever,level 2';
Session altered.
SQL> select * from RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD where FILENAME='1.2.840.10008.20130416082353.354293646.1792346492.dcm' ;
這裡顯示的是一行資料的查詢結果，省去了...
SQL> alter session set events='10053 trace name context off';
Session altered.
--===================================
10053的trace的tratce結果如下，trace裡也看到了走full的成本確實比走index小很多：
full 的成本：43829.99
index的成本：3168635.79
--====================================
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
select * from RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD where FILENAME='1.2.840.10008.20130416082353.354293646.1792346492.dcm'
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=81883 hint_alias="PATIENT_EXAM_IMAGE_RECORD"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 1251 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PATIENT_EXAM_IMAGE_RECORD Alias: PATIENT_EXAM_IMAGE_RECORD
#Rows: 3609477 #Blks: 199400 AvgRowLen: 369.00
Index Stats::
Index: INDEX1 Col#: 7
LVLS: 2 #LB: 21598 #DK: 105241 LB/K: 1.00 DB/K: 8.00 CLUF: 904549.00
Index: INDEX_FILENAME Col#: 2
LVLS: 3 #LB: 59332 #DK: 3608845 LB/K: 1.00 DB/K: 1.00 CLUF: 3107921.00
Index: PK_IMAGEID Col#: 1
LVLS: 2 #LB: 24084 #DK: 3571103 LB/K: 1.00 DB/K: 1.00 CLUF: 2693143.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#2): FILENAME(NVARCHAR2)
AvgLen: 107.00 NDV: 1 Nulls: 0 Density: 1.3791e-07
Histogram: Freq #Bkts: 1 UncompBkts: 5774 EndPtVals: 1
Table: PATIENT_EXAM_IMAGE_RECORD Alias: PATIENT_EXAM_IMAGE_RECORD
Card: Original: 3609477 Rounded: 3609164 Computed: 3609164.44 Non Adjusted: 3609164.44
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 43829.99 Resp: 43829.99 Degree: 0
Cost_io: 43620.00 Cost_cpu: 3152482716
Resp_io: 43620.00 Resp_cpu: 3152482716
Access Path: index (AllEqRange)
Index: INDEX_FILENAME
resc_io: 3166982.00 resc_cpu: 24827253174
ix_sel: 0.99991 ix_sel_with_filters: 0.99991
Cost: 3168635.79 Resp: 3168635.79 Degree: 1
Best:: AccessPath: TableScan
Cost: 43829.99 Degree: 1 Resp: 43829.99 Card: 3609164.44 Bytes: 0
--===================================
SQL> show parameter trace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_trace integer 0
sql_trace boolean FALSE
trace_enabled boolean TRUE
tracefile_identifier string 10053
SQL>
--==================================
列上的統計資訊：
select * from dba_tab_col_statistics where table_name='PATIENT_EXAM_IMAGE_RECORD'
上面查詢的結果如下：
RBPACS_ORA PATIENT_EXAM_IMAGE_RECORD FILENAME 1 0031002E0032002E003800340030002E00310030003000300038002E00320030 0031002E0032002E003800340030002E00310030003000300038002E00320030 1.37905908168087E-7 0 1 2013-4-16 12:07:20 5774 YES NO 107 FREQUENCY
--===========================================
這裡num_distinct的值是1，low_value和high_value的值相同，都是0031002E0032002E003800340030002E00310030003000300038002E00320030
把0031002E0032002E003800340030002E00310030003000300038002E00320030轉成實際的值發現是：
1.2.840.10008.20，而1.2.840.10008.20僅僅是index列filename上的前16個字元，後面的字串根本就沒有
計算出來，而index列filename上的值是一個nvarchar型別，裡面存放的是一個檔名，這些檔名的
前面25個字元都是相同的，所以問題也清楚了，但是oracle為什麼這樣沒弄明白。
--===========================================
說到這裡已經明白了，問題是出在index列的優化器統計資訊上：
SQL> select utl_raw.cast_to_nvarchar2('0031002E0032002E003800340030002E00310030003000300038002E00320030') from dual;
UTL_RAW.CAST_TO_NVARCHAR2('0031002E0032002E003800340030002E00310030003000300038002E00320030')
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
1.2.840.10008.20
SQL>
SQL> exec dbms_stats.delete_column_stats('RBPACS_ORA','PATIENT_EXAM_IMAGE_RECORD','FILENAME');
PL/SQL 過程已成功完成。
SQL>
SQL> set autotrace traceonly
SQL> set linesize 200
SQL> select * from RBPACS_ORA.PATIENT_EXAM_IMAGE_RECORD where FILENAME='1.2.840.10008.20130416082353.354293646.1792346492.dcm'
2 ;

執行計劃
----------------------------------------------------------
Plan hash value: 3241136475
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36095 | 12M| 12443 (1)| 00:02:30 |
| 1 | TABLE ACCESS BY INDEX ROWID| PATIENT_EXAM_IMAGE_RECORD | 36095 | 12M| 12443 (1)| 00:02:30 |
|* 2 | INDEX RANGE SCAN | INDEX_FILENAME | 14438 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FILENAME"=U'1.2.840.10008.20130416082353.354293646.1792346492.dcm')

統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1452 bytes sent via SQL*Net to client
346 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
--===================================================
SQL> exec dbms_stats.lock_table_stats('RBPACS_ORA','PATIENT_EXAM_IMAGE_RECORD');
PL/SQL 過程已成功完成。
SQL>
解決辦法就是刪除了index列filename上的優化器統計資訊，單獨對index蒐集了一下優化器統計資訊，（
不能通過蒐集表或者讓資料庫自動蒐集，他們蒐集之後index和index列filename上的優化器統計資訊
都不對），最後把這張表上的優化器統計資訊暫時鎖定了。這樣就可以走index了。這個表上的資料變化不是太頻繁，
只能定期手動這樣處理了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1060401/，如需轉載，請註明出處，否則將追究法律責任。