SQL直方图统计信息不准确导致查询缓慢问题

SQL优化笔记：
本人菜鸟一枚，今天下午时候遇到一个很棘手的SQL优化问题，具体情况如下描述：
sELECT t1.TEST_NO test_req_no
, t1.ITEM_NO test_item_no
, t.test_no
, t.test_req_no test_req_no_del
, LAB_ITEM_CODE
, LAB_ITEM_NAME
, REPORT_ITEM_CODE
, REPORT_ITEM_NAME
, RESULT
, UNITS_CODE
, UNITS_NAME
, ABNORMAL_INDICATOR
, INSTRUMENT_ID
, RESULT_DATE_TIME
, TEST_METHOD_CODE
, TEST_METHOD_NAME
, REFER_CONTEXT
, LOWER_LIMIT
, UPPER_LIMIT
, MEDIA_INDICATOR
, FIRST_MEDIA
, FIRST_PATH
, t1.active_code
, t1.chg_seq
, t.hospital_code
, t.hospital_name
FROM CDR.LAB_RESULT t , cdc.lab_result_chg t1
where t.test_req_no(+)=t1.test_no
  and t.test_item_no(+)=t1.item_no
  and t1.process_code='0'
  order by chg_seq

首先贴出执行计划：

1、首先检查了查询字段有没有加索引-----都加了
2、对表进行统计分析（由于之前对view中的一个表做过insert【但是insert不会影响水平线的准确性吧】）----没问题
实在是没辙了，就找ding大师求教：
1、判断统计信息是否准确：

第二行就是我要得出的数据， 这样就发现执行计划统计出来的结果不准确导致查询很慢 2、使用hint强制优化查询： 具体的leading use_nl优化技术：http://blog.chinaunix.net/uid-574845-id-2734102.html 发现查询只需要0.9秒查询出结果 3、手工的进行直方图分析 exec dbms_stats.gather_table_stats(ownname=>'CDC'  ,tabname=>'lab_result_chg'  ,method_opt=>'for columns size 10 process_code' ,estimate_percent=>100 ,cascade=>true ,no_invalidate=>false);

切换到CDC用户下查询统计信息： SELECT * FROM user_histograms s --Dba_Histograms   WHERE table_name = upper('lab_result_chg')：

OK，现在统计信息准确了，去掉hint提示重新查询，看执行计划：
4、去掉hint查询也是1s左右就出来结果了
新的执行计划如下：

 1 SQL> select * from table(dbms_xplan.display);
 2 
 3 PLAN_TABLE_OUTPUT
 4 ----------------------------------------------------------------------------------------------------
 5 Plan hash value: 3705668283
 6 
 7 ----------------------------------------------------------------------------------------------------
 8 | Id  | Operation                      | Name                 | Rows  | Bytes | Cost (%CPU)| Time
 9 ----------------------------------------------------------------------------------------------------
10 |   0 | SELECT STATEMENT               |                      |    71 | 58717 |   145   (1)| 00:00:0
11 |   1 |  TABLE ACCESS BY INDEX ROWID   | LAB_TEST_MASTER      |     1 |    16 |     4   (0)| 00:00:0
12 |*  2 |   INDEX RANGE SCAN             | IDX_LAB_MASTER_02    |     1 |       |     3   (0)| 00:00:0
13 |*  3 |  COUNT STOPKEY                 |                      |       |       |            |
14 |*  4 |   TABLE ACCESS FULL            | LAB_TEST_ITEMS_HEREN |     2 |    30 |    27   (0)| 00:00:0
15 |*  5 |  COUNT STOPKEY                 |                      |       |       |            |
16 |*  6 |   TABLE ACCESS FULL            | LAB_TEST_ITEMS_HEREN |     2 |    44 |    27   (0)| 00:00:0
17 |   7 |  SORT ORDER BY                 |                      |    71 | 58717 |   145   (1)| 00:00:0
18 |   8 |   NESTED LOOPS OUTER           |                      |    71 | 58717 |   144   (0)| 00:00:0
19 |   9 |    TABLE ACCESS BY INDEX ROWID | LAB_RESULT_CHG       |    71 |  1136 |     2   (0)| 00:00:0
20 |* 10 |     INDEX RANGE SCAN           | INX_LAB_RESULT_CHG01 |    71 |       |     1   (0)| 00:00:0
21 |  11 |    VIEW PUSHED PREDICATE       | LAB_RESULT           |     1 |   811 |     2   (0)| 00:00:0
22 |  12 |     TABLE ACCESS BY INDEX ROWID| LAB_RESULT           |     1 |    58 |     2   (0)| 00:00:0
23 |* 13 |      INDEX RANGE SCAN          | PK_LAB_RESULT        |     1 |       |     1   (0)| 00:00:0
24 ----------------------------------------------------------------------------------------------------
25 
26 Predicate Information (identified by operation id):
27 ---------------------------------------------------
28 
29    2 - access("T1"."TEST_REQ_NO"=:B1 AND "T1"."HOSPITAL_CODE"=1)
30    3 - filter(ROWNUM=1)
31    4 - filter("T1"."TEST_NO"=:B1 AND "T1"."ITEM_NO"=:B2)
32    5 - filter(ROWNUM=1)
33    6 - filter("T1"."TEST_NO"=:B1 AND "T1"."ITEM_NO"=:B2)
34   10 - access("T1"."PROCESS_CODE"='0')
35   13 - access("T"."TEST_NO"="T1"."TEST_NO" AND "T"."ITEM_NO"="T1"."ITEM_NO")
36 
37 已选择31行。
38 
39 
40 执行计划
41 ----------------------------------------------------------
42 Plan hash value: 2137789089
43 
44 ---------------------------------------------------------------------------------------------
45 | Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
46 ---------------------------------------------------------------------------------------------
47 |   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |
48 |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |
49 ---------------------------------------------------------------------------------------------
50 
51 
52 统计信息
53 ----------------------------------------------------------
54          29  recursive calls
55          12  db block gets
56          58  consistent gets
57           0  physical reads
58           0  redo size
59        2968  bytes sent via SQL*Net to client
60         381  bytes received via SQL*Net from client
61           4  SQL*Net roundtrips to/from client
62           1  sorts (memory)
63           0  sorts (disk)
64          31  rows processed
65 
66 SQL>

 

 

 

 

posted on 2013-04-16 17:40  北方佳人  阅读(340)  评论(0)    收藏  举报

导航