优化大师来看看我SQL

Question

SELECT COUNTVALUE,MINVALUE,MAXVALUE,AVGVALUE FROM  ME_STRG_EC_IO  WHERE (DAYHOUR = 2005093015) AND ((Y_MESSYU >= 35.663 AND Y_MESSYU < 35.664 AND X_MESSYU >= 139.673 AND X_MESSYU < 139.674))
巨慢达不到要求  看有无办法弄快点

Answer 1

不知道BETWEEN AND   INDEX发挥作用了没

Answer 2

条件上没INDEX啥意思哦?INDEX是'   CREATE INDEX  XXXX  ON t_CAD(DAYHOUR ,Y_MESSYU ,X_MESSYU,)

Answer 3

你的条件都是and，把括号都去掉看看
Y_MESSYU >= 35.663 AND Y_MESSYU  < 35.664改成Y_MESSYU between 35.663 AND 35.664 and Y_MESSYU <>35.664看看

Answer 4

 WHERE (DAYHOUR = 2005093015) 
  AND ((Y_MESSYU >= 35.663 
AND     Y_MESSYU  < 35.664 
AND     X_MESSYU >= 139.673 
AND     X_MESSYU  < 139.674)) 
改成 WHERE (DAYHOUR = 2005093015) and (y_messyu between 35.663 and 35.664) and (x_messyu between 139.673 and 139.674)
看看

Answer 5

<>  用了不是更慢呀?

Answer 6

不知道BETWEEN AND      对 INDEX  起作用了没

Answer 7

楼上是说   EXPLAIN PLAN  ?

Answer 8

修改前
|   0 | SELECT STATEMENT  |               |     1 |    90 |    18   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ME_STRG_EC_IO |     1 |    90 |    18   (0)| 00:00:01 |
修改后-----5楼的方法
|   0 | SELECT STATEMENT  |               |     2 |   180 |    18   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ME_STRG_EC_IO |     2 |   180 |    18   (0)| 00:00:01 |

Answer 9

3 -----------------------------------------------------------------------------------
4 | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
5 -----------------------------------------------------------------------------------
6 |   0 | SELECT STATEMENT  |               |     2 |   180 |    18   (0)| 00:00:01 |
7 |*  1 |  TABLE ACCESS FULL| ME_STRG_EC_IO |     2 |   180 |    18   (0)| 00:00:01 |

Answer 10

你的索引怎么建在t_CAD上？查询又是用的ME_STRG_EC_IO ？

Answer 11

你的索引怎么建在t_CAD上？查询又是用的ME_STRG_EC_IO ？

Answer 12

我就是查询没看见 索引啊~  郁闷死了~
但是我就算
EXPLAIN PLAN FOR
SELECT COUNTVALUE,MINVALUE,MAXVALUE,AVGVALUE FROM  ME_STRG_EC_IO
where DAYHOUR = 2005093015
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);  结果是 1 Plan hash value: 1052544228
2  
3 -----------------------------------------------------------------------------------
4 | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
5 -----------------------------------------------------------------------------------
6 |   0 | SELECT STATEMENT  |               | 12258 |   766K|    18   (0)| 00:00:01 |
7 |*  1 |  TABLE ACCESS FULL| ME_STRG_EC_IO | 12258 |   766K|    18   (0)| 00:00:01 |
8 -----------------------------------------------------------------------------------
9  
10 Predicate Information (identified by operation id):
11 ---------------------------------------------------
12  
13    1 - filter(TO_NUMBER("DAYHOUR")=2005093015)
14  
15 Note
16 -----
17    - dynamic sampling used for this statement

Answer 13

啊~~~~~~~~~  我昏迷  我做测试 INDEX没改  等下~  和和 

Answer 14

不对   create index  pppp   on ME_STRG_EC_IO(DAYHOUR,Y_MESSYU,X_MESSYU)  已经改了

Answer 15

用dbms_status.gather_table_stats来更新这个表的统计值。
DAYHOUR的字段类型是number吗？

Answer 16

是统计信息陈旧了吗？
单独在DAYHOUR列上建个索引。

Answer 17

很怪 INDEX信息没出来    BOBFANG说的关于用dbms_status.gather_table_stats来更新这个表的统计值。 
                        我不是很清楚 什么意思 

Answer 18

21楼的做法 create index  pppp   on ME_STRG_EC_IO(DAYHOUR)结果是
1 Plan hash value: 1052544228
2  
3 -----------------------------------------------------------------------------------
4 | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
5 -----------------------------------------------------------------------------------
6 |   0 | SELECT STATEMENT  |               | 12258 |   766K|    18   (0)| 00:00:01 |
7 |*  1 |  TABLE ACCESS FULL| ME_STRG_EC_IO | 12258 |   766K|    18   (0)| 00:00:01 |
8 -----------------------------------------------------------------------------------
9  
10 Predicate Information (identified by operation id):
11 ---------------------------------------------------
12  
13    1 - filter(TO_NUMBER("DAYHOUR")=2005093015)
14  
15 Note
16 -----
17    - dynamic sampling used for this statement

Answer 19

执行计划里怎么有个TO_NUMBER("DAYHOUR")的转换？

Answer 20

根据执行计划的提示，表也没分析过。你应该再做个表分析。简单地用analyze table xxx compute statistics;

Answer 21

甲骨文兄   ^0^  我的建表语句陈旧  已更新 因为不是在我本机测试 所以 没检查  现在已经检查了  所有都是最新了我正在重新插入数据INDEX是不是出现在   analyze table      BTS_STRG_EC_IO       compute statistics 的分析结果里呢

Answer 22

分析了表后 还是没INDEX信息
1 Plan hash value: 1052544228
2  
3 -----------------------------------------------------------------------------------
4 | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
5 -----------------------------------------------------------------------------------
6 |   0 | SELECT STATEMENT  |               | 12567 |   797K|    17   (0)| 00:00:01 |
7 |*  1 |  TABLE ACCESS FULL| ME_STRG_EC_IO | 12567 |   797K|    17   (0)| 00:00:01 |
8 -----------------------------------------------------------------------------------
9  
10 Predicate Information (identified by operation id):
11 ---------------------------------------------------
12  
13    1 - filter("DAYHOUR"=2005093015)
14  
15 Note
16 -----
17    - dynamic sampling used for this statement

Answer 23

在插数据？
你执行这个语句：
analyze table xxx compute statistics for table
for all indexes
for all indexed columns;然后再试试。如果还用不上索引，你就用HINT试试，看看两者执行计划的统计量差别大不大。

Answer 24

等等以防万一我确认下我的操作
1
   EXPLAIN PLAN FOR
 SELECT COUNTVALUE,MINVALUE,MAXVALUE,AVGVALUE FROM 
 BTS_STRG_EC_IO WHERE DAYHOUR = 2005093015
2  analyze table BTS_STRG_EC_IO compute statistics for table 
for all indexes 
for all indexed columns; 3   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
对不对啊~~ 大哥

Answer 25

先analyze table再explain plan，不过有时候explain plan不准的(我就遇到过这种情况)，最好用10046跟踪一下。

Answer 26

这个问题整了一下午 - -;   INDEX搞不好的话速度上不去的.....
   各位帮忙一起分析我明天会追加分的!!

Answer 27

请问ME_STRG_EC_IO表有多少记录，其中DAYHOUR = 2005093015又有多少记录。

Answer 28

还有就是仔细核对表结构。看看DAYHOUR到底是什么类型。

Answer 29

ME_STRG_EC_IO  12618 记录
表结构COUNTVALUE NUMBER Y
MAXVALUE NUMBER Y
MINVALUE NUMBER Y
AVGVALUE NUMBER Y
DAYHOUR  NUMBER Y
X_MESSYU NUMBER Y
Y_MESSYU NUMBER Y
HASSERITSU NUMBER Y
HASSESUU NUMBER Y

Answer 30

所有记录的时间DAYHOUR = 2005093015  全是一样时间

Answer 31

很郁闷的是  执行了 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 没有INDEX信息 - -;   我汗哦 

Answer 32

不是我现在测试是
SELECT COUNTVALUE,MINVALUE,MAXVALUE,AVGVALUE FROM  ME_STRG_EC_IO 
where DAYHOUR = 2005093015 
这句  只有一个 字段的 情况
关于联合INDEX还是单独 只有等这一个的情况解决了才能比较速度现在就是INDEX是否用上的信息没出来

Answer 33

全部DAYHOUR列就这一个值？那在这个列上还用建什么索引？
你的：SELECT COUNTVALUE,MINVALUE,MAXVALUE,AVGVALUE FROM  ME_STRG_EC_IO  
where DAYHOUR = 2005093015
就是全表扫描了，又怎么会用上索引 。 

Answer 34

啊... 对哈~  我晕了 那就把这条件删除了用 X_MESSYU  Y_MESSYU   来建立 INDEX 

Answer 35

但是没用上的话也不应该 影响速度的吧??
INDEX是'   CREATE INDEX  XXXX  ON t_CAD(DAYHOUR ,Y_MESSYU ,X_MESSYU,)
DAYHOUR 没用上 但是 Y_MESSYU ,X_MESSYU 是用上了的
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
AAAA         ME_STRG_EC_IO YES          YES  05/23/2008 11:30:04

Answer 36

问题基本解决  现在向大家汇报下 算是小小经验基于 以下SQL select * from t_cad where
  to_char(measure_date, 'yyyymmddhh24') <>  2005093015   and  
  ((LATITUDE BETWEEN 35.665 AND 35.67
  AND LONGITUDE BETWEEN 139.71 AND 139.715)) 做查询优化  1.INDEX建立 用联合INDEX ( LATITUDE,LATITUDE )
     处理结果是4 | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
5 ----------------------------------------------------------------------------------------------
6 |   0 | SELECT STATEMENT            |                |     2 |   416 |    53   (0)| 00:00:01 |
7 |*  1 |  TABLE ACCESS BY INDEX ROWID| T_CAD          |     2 |   416 |    53   (0)| 00:00:01 |
8 |*  2 |   INDEX RANGE SCAN          | MEASURE_DATEWW |  1671 |       |     7   (0)| 00:00:01 |--------------------我----------是-----------分割===============线-----------------------     2 但是如果 2个单独 INDEX  处理结果是4 | Id  | Operation                        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
5 ----------------------------------------------------------------------------------------------------
6 |   0 | SELECT STATEMENT                 |                 |     2 |   416 |    30  (10)| 00:00:01 |
7 |*  1 |  TABLE ACCESS BY INDEX ROWID     | T_CAD           |     2 |   416 |    30  (10)| 00:00:01 |
8 |   2 |   BITMAP CONVERSION TO ROWIDS    |                 |       |       |            |          |
9 |   3 |    BITMAP AND                    |                 |       |       |            |          |
10 |   4 |     BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |
11 |   5 |      SORT ORDER BY               |                 |       |       |            |          |
12 |*  6 |       INDEX RANGE SCAN           | MEASURE_DATEWW1 |       |       |     6   (0)| 00:00:01 |
13 |   7 |     BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |
14 |   8 |      SORT ORDER BY               |                 |       |       |            |          |
15 |*  9 |       INDEX RANGE SCAN           | MEASURE_DATEWW  |       |       |     6   (0)| 00:00:01 |

Answer 37

Y_MESSYU ,X_MESSYU分别建索引的方式更好些。9i以上的版本数据库中，如果采用CBO，表的统计值经常更新，那么类似X between ... and ... and Y between ... and ...的查询是可以同时使用X和Y的索引，两个子集做bitmap and。

Answer 38

你用auto trace跟踪显示一下统计量，看看哪种方式的逻辑读和物理读高些，就好比较哪种方式的索引好了。

Answer 39

可以看见  INDEX 建立2个单独的耗费CPU高  所以满  但是不建立INDEX的话呢?
4 | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
5 ---------------------------------------------------------------------------
6 |   0 | SELECT STATEMENT  |       |     2 |   416 |   503   (1)| 00:00:07 |
7 |*  1 |  TABLE ACCESS FULL| T_CAD |     2 |   416 |   503   (1)| 00:00:07 |
看出COST 很高 时间也最久希望高手补充
晚点再结贴~  大家可以多补充 互相帮助和和~ 

Answer 40

汗`  ~ 我以为直接根据 Cost (%CPU)  判断如果直接  Cost (%CPU)  的话   联合比2个独立的消耗CPU 少 

Answer 41

优化是有针对性的，CPU是成本，IO也是成本，执行时间也是成本。
你的目标是执行时间最少，CPU成本一般情况下，CPU耗费代表时间，这个观点需要澄清一下。

Answer 42

前几天CSDN上就有个问题，
相同执行计划，相同SQL，但执行时间差别10倍还多。
就是I/O导致。

Answer 43

给你个链接，
这就是我说的那个问题，希望能够你帮助。http://topic.csdn.net/u/20080513/15/8cd0b66e-6505-4a8b-988b-bdeb39a9bb6e.html

Answer 44

auto trace  没搞定....出不来呢?

Answer 45

  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=59 Card=46 Bytes=1
         288)  1    0   SORT (ORDER BY) (Cost=59 Card=46 Bytes=1288)
  2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_CAD' (TABLE) (Cost=5
         8 Card=46 Bytes=1288)  3    2       INDEX (RANGE SCAN) OF 'MEASURE_DATEWW' (INDEX) (Cost=1
         2 Card=2000)
-----------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=504 Card=46 Bytes=
         1288)  1    0   SORT (ORDER BY) (Cost=504 Card=46 Bytes=1288)
  2    1     TABLE ACCESS (FULL) OF 'T_CAD' (TABLE) (Cost=503 Card=46
          Bytes=1288)谁更快也看不出来啊.....只是看到 COST  CARD的话用INDEX要跑2000行....会步会时间更多哦

Answer 46

如果是优化，包括2方面，不能只从sql的角度，也包括数据结构的调整

Answer 47

数据是变不了了~  只能改SQL要是有出力多少时间的LOG就对了`

Answer 48

可以使用PL/SQL提供很方便的执行计划查看功能，
查看I/O成本。

Answer 49

sql没什么问题,不需要括号
关键是索引
如果数据量特大
改为分区表及分区索引

Answer 50

我使用了profiler.....感觉时间变化不稳定.....我汗一个先~数据量的话呢...一天2百万左右吧但是表结构不复杂就是了 ~  反正不能全表检索 ~ 到了后来就上亿数据  全表就死了~

Answer 51

就是 profiler  么?  我没看见I/O   我知道为什么重视I/O因为看你你的日志
不重视不行啊~ 但是没看见那有关于I/O的

Answer 52

如果用sqlplus，可以用：set autotrace traceonly，再执行相关SQL。
如果用别的什么软件，如果找不到相关看计划的选项，可以用Oracle的方式，如下：
假设你的相关参数都为默认，dump目录空间也够。
1.alter session set sql_trace=true;
2.该回话下执行相关sql
3.把sql_trace设为false
4.根据user_dump_dest参数定位到udump目录下去找生成的跟踪文件，确定跟踪文件名称的SQL我就不贴了。
5.用tkprof格式化该文件，就可以看了。当然也可以用10046事件看到比sql_trace更全的信息。

Answer 53

SELECT COUNTVALUE,MINVALUE,MAXVALUE,AVGVALUE FROM  ME_STRG_EC_IO  
WHERE (Y_MESSYU >= 35.663 AND Y_MESSYU  < 35.664) AND (X_MESSYU >= 139.673 
AND X_MESSYU  < 139.674) 
AND (DAYHOUR = 2005093015) 
-------------------------------------------------
试试上面的语句。
注意：Y_MESSYU X_MESSYU DAYHOUR 分别是索引，
一定要把 DAYHOUR = 2005093015 放在该语句的最后。
 

Answer 54

补充： 考虑oracle sql执行顺序，我想把 DAYHOUR = 2005093015 放在该select语句末尾测试
看看结果~~~~

Answer 55

LZ这个是基于CBO的，不用考虑sql执行顺序。

Answer 56

甲骨文兄的TRACE方式确实可以看见时间

Answer 57

STAT #4 id=1 cnt=236 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=2281 pr=2075 pw=0 time=833900 us)'
STAT #4 id=2 cnt=236 pid=1 pos=1 obj=60567 op='TABLE ACCESS FULL T_CAD (cr=2281 pr=2075 pw=0 time=444627 us)'
我的时间是不是  833900 us 加 444627 us  ???