查看: 5893|回复: 31

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

[复制链接]
认证徽章
论坛徽章:
150
蓝锆石
日期:2011-11-16 22:31:22萤石
日期:2011-11-17 13:05:31祖母绿
日期:2008-06-14 15:23:26海蓝宝石
日期:2011-11-16 22:25:15紫水晶
日期:2011-11-16 22:31:22红宝石
日期:2011-10-09 08:54:30蓝锆石
日期:2009-01-31 15:20:54萤石
日期:2008-12-22 15:22:00祖母绿
日期:2011-11-17 13:13:26海蓝宝石
日期:2008-07-05 14:52:18
发表于 2013-4-16 19:44 | 显示全部楼层 |阅读模式
本帖最后由 warehouse 于 2013-4-16 21:37 编辑

这是我长期维护的一家专科医院的系统,客户也没有反应慢,我在执行常规的例行检查时
发现DB Time很大,达到了2,221.28,这是8点~9点的awr报告,9点~10点的达到3000多了,
我认为系统会非常的慢,客户信息中心人员说没人打电话反应慢,看到这种情况不能无动于衷了,
等待事件就不用看了,平常就是gc buffer busy,建议客户修改应用的连接字符串,让同类应用
从一个节点连进去,他们一直也没有调整。
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了。这个表上的数据变化不是太频繁,
只能定期手动这样处理了。


论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2013-4-16 20:18 | 显示全部楼层
http://www.itpub.net/thread-1339053-1-1.html

和这个类似,oracle收集字符类型统计信息限制

使用道具 举报

回复
认证徽章
论坛徽章:
150
蓝锆石
日期:2011-11-16 22:31:22萤石
日期:2011-11-17 13:05:31祖母绿
日期:2008-06-14 15:23:26海蓝宝石
日期:2011-11-16 22:25:15紫水晶
日期:2011-11-16 22:31:22红宝石
日期:2011-10-09 08:54:30蓝锆石
日期:2009-01-31 15:20:54萤石
日期:2008-12-22 15:22:00祖母绿
日期:2011-11-17 13:13:26海蓝宝石
日期:2008-07-05 14:52:18
发表于 2013-4-16 20:23 | 显示全部楼层
dingjun123 发表于 2013-4-16 20:18
http://www.itpub.net/thread-1339053-1-1.html

和这个类似,oracle收集字符类型统计信息限制

恩,看起来差不多,我期间也尝试过reverse index,也是不走index

使用道具 举报

回复
论坛徽章:
9
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:002013年新春福章
日期:2013-02-25 14:51:24迷宫蛋
日期:2013-03-06 17:43:59鲜花蛋
日期:2013-04-26 22:57:09蛋疼蛋
日期:2013-06-05 15:38:56林肯
日期:2013-08-16 16:46:322015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39
发表于 2013-4-16 20:38 | 显示全部楼层
mark.

使用道具 举报

回复
论坛徽章:
17
2008新春纪念徽章
日期:2008-02-13 12:43:032014年新春福章
日期:2014-02-18 16:42:02优秀写手
日期:2013-12-18 09:29:13奥迪
日期:2013-09-12 15:57:04凯迪拉克
日期:2013-08-26 22:55:57红旗
日期:2013-08-15 13:57:06茶鸡蛋
日期:2013-05-29 11:38:412013年新春福章
日期:2013-02-25 14:51:24ITPUB季度 技术新星
日期:2012-02-16 14:53:162012新春纪念徽章
日期:2012-01-04 11:51:22
发表于 2013-4-16 20:41 | 显示全部楼层

使用道具 举报

回复
认证徽章
论坛徽章:
150
蓝锆石
日期:2011-11-16 22:31:22萤石
日期:2011-11-17 13:05:31祖母绿
日期:2008-06-14 15:23:26海蓝宝石
日期:2011-11-16 22:25:15紫水晶
日期:2011-11-16 22:31:22红宝石
日期:2011-10-09 08:54:30蓝锆石
日期:2009-01-31 15:20:54萤石
日期:2008-12-22 15:22:00祖母绿
日期:2011-11-17 13:13:26海蓝宝石
日期:2008-07-05 14:52:18
发表于 2013-4-16 20:45 | 显示全部楼层
本帖最后由 warehouse 于 2013-4-16 20:46 编辑

汗,那是我out了,cbo还的加强啊,折腾了一天

使用道具 举报

回复
认证徽章
论坛徽章:
150
蓝锆石
日期:2011-11-16 22:31:22萤石
日期:2011-11-17 13:05:31祖母绿
日期:2008-06-14 15:23:26海蓝宝石
日期:2011-11-16 22:25:15紫水晶
日期:2011-11-16 22:31:22红宝石
日期:2011-10-09 08:54:30蓝锆石
日期:2009-01-31 15:20:54萤石
日期:2008-12-22 15:22:00祖母绿
日期:2011-11-17 13:13:26海蓝宝石
日期:2008-07-05 14:52:18
发表于 2013-4-16 20:53 | 显示全部楼层
本帖最后由 warehouse 于 2013-4-16 20:55 编辑
maclean 发表于 2013-4-16 20:41
【Maclean Liu技术分享】拨开Oracle优化器迷雾探究Histogram之秘
http://t.askmaclean.com/thread ...


准确的说应该是32个字节了,客户正好是nvarchar,我算了一下low_value和high_value是16个字符。

SQL> select utl_raw.cast_to_nvarchar2('0031002E0032002E003800340030002E00310030003000300038002E00320030') from dual;
UTL_RAW.CAST_TO_NVARCHAR2('0031002E0032002E003800340030002E00310030003000300038002E00320030')
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
1.2.840.10008.20

使用道具 举报

回复
论坛徽章:
13
奥运会纪念徽章:跆拳道
日期:2012-09-18 13:36:08优秀写手
日期:2013-12-24 06:00:13雪佛兰
日期:2013-12-23 15:38:50奥运会纪念徽章:射击
日期:2013-03-11 14:17:07鲜花蛋
日期:2013-02-27 16:38:402013年新春福章
日期:2013-02-25 14:51:24奥运会纪念徽章:网球
日期:2013-01-30 16:45:43咸鸭蛋
日期:2013-01-29 12:27:30蜘蛛蛋
日期:2012-12-21 11:10:36蛋疼蛋
日期:2012-12-12 19:27:06
发表于 2013-4-16 21:03 | 显示全部楼层
手都很高,学习了!!

使用道具 举报

回复
认证徽章
论坛徽章:
171
ITPUB十周年纪念徽章
日期:2011-11-01 16:24:04ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:42ITPUB社区12周年站庆徽章
日期:2013-08-13 16:52:38itpub13周年纪念徽章
日期:2014-10-08 15:21:35ITPUB14周年纪念章
日期:2015-10-26 17:23:44ITPUB15周年纪念
日期:2018-02-09 14:12:58ITPUB18周年纪念章
日期:2018-09-17 10:09:49状元
日期:2015-11-19 12:58:23榜眼
日期:2015-11-19 12:58:23探花
日期:2015-11-19 12:58:23
发表于 2013-4-16 21:24 | 显示全部楼层
分析过程很详尽,顶老谢!

使用道具 举报

回复
论坛徽章:
43
ITPUB9周年纪念徽章
日期:2012-09-28 16:17:24马上有钱
日期:2014-06-16 17:13:52马上有对象
日期:2014-06-16 17:13:52马上加薪
日期:2014-06-16 17:13:52现任管理团队成员
日期:2014-06-17 02:21:03版主1段
日期:2014-06-17 02:21:04马上有车
日期:2014-10-24 22:35:032010数据库技术大会纪念徽章
日期:2015-04-23 10:33:192011数据库大会纪念章
日期:2015-04-23 10:33:192012数据库大会纪念章
日期:2015-04-23 10:33:19
发表于 2013-4-16 21:25 | 显示全部楼层
学习了

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表