ITPub博客

首页 > 数字化转型 > ERP > 列上"数据特殊"、优化器统计信息的问题导致死活不走index诊断、调整过程

列上"数据特殊"、优化器统计信息的问题导致死活不走index诊断、调整过程

原创 ERP 作者:warehouse 时间:2013-04-16 19:50:52 0 删除 编辑
这是我长期维护的一家专科医院的系统,客户也没有反应慢,我在执行常规的例行检查时发现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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5098848