一条SQL困扰我很久了，大大们帮我分析下，怎么优化！

sundog315

把INDEX（end_heap ,start_heap）拆开建两个索引试试

dingjun123

把计划贴下，先set serveroutput off,然后按Yong Huang 的办法贴计划

我觉得慢在join,先调整那join先，你的子查询只有1000条记录，排序慢不到哪去啊

whzhaha

看执行计划没什么意义了吧 ，那种方式执行计划确实看不了，PL/sql附件传下

whzhaha

其实我现在觉得只有两种可能  一是数据量多点时 distance计算比较慢，随即不用算，但是我随机去20条也还好
2.就是筛选数据放硬盘排序了  我一直怀疑后者
干脆我把函数也发下

CREATE OR REPLACE FUNCTION distance(lat1 NUMBER,lon1 NUMBER,lat2 NUMBER,lon2 NUMBER)
RETURN NUMBER IS
   num NUMBER;
   PI  NUMBER;
BEGIN
  PI := 3.141592625;
  num:=2 * 6378.137* ASIN(SQRT(power(SIN(PI * (lat1 - lat2) / 360), 2)
   +COS(PI * lat1 / 180)* COS(lat2* PI / 180) * power(SIN(PI * (lon1 - lon2) / 360), 2)));
RETURN ROUND(num*1000);
END;

whzhaha

有个新发现 原来我系统是MYSQL下的，由于想提升点性能，就这次迁移到ORACLE上来了，同一一个函数，同样多数据
select h.lat,h.lon,h.station_id stationId from busstation  h  where distance(30.002,120.1123,h.lat,h.lon)<=11500
oracle需要100秒
MYSQL下才0.11秒悲剧了。ORACLE自定义函数性能也太差了吧，现在也不知道怎么弄了 NND

Yong Huang

whzhaha,

dingjun123 said twice you need to run "set serveroutput off" in sqlplus before you run the SQLs I provided in msg #3. You must have enabled serveroutput on somewhere, perhaps in glogin.sql. I didn't tell you to turn it off because people normally don't do that.

Before you run your good and bad SQLs, flush buffer cache (hope it won't affect other apps too much):
alter system flush buffer_cache;
That way, we can compare disk read numbers.

You can just attach a text file instead of an image. Or just show it on the web page if you know how to post with fixed-width font such as courier.

> select h.lat,h.lon,h.station_id stationId from busstation  h  where distance(30.002,120.1123,h.lat,h.lon)<=11500
> oracle需要100秒
> MYSQL下才0.11秒。

On Oracle, do you see a lot of disk activity and on MySQL, very little? If that's not the case, is CPU usage high on Oracle? There could be two causes, disk reads and CPU computation. I want to see which of the two plays the major role. If it's disk, what's Oracle's db_cache_size and pga_aggregate_target_size? If it's CPU, what are the CPU speeds on both machines?

Yong Huang

whzhaha

thinks very much,Yong Huang.
我尝试了不去计算distance，效率很快，那么现在基本问题确认是自定义函数导致性能问题，MySQL和oracle服务器都是普通的开发机器，MYSQL还是在WINDOWS下，oracle在LUNIX下，两者配置差不多。
我明天去具体看看具体参数，至于db_cache_size 和pga_aggregate_target_size我是默认安装后就没改过。明天尝试修改下看看。
我找了些资料，还有尝试写些其他函数在ORACLE和MYSQL里对比下，性能确实有很大差别，ORACLE对用户定义函数性能很差，但它自身的系统函数性能还不错的，也可能是我ORACLE系统参数配置问题，我明天把db_cache_size 和pga_aggregate_target_size参数修改执行下，看看有没效果，看来ORACLE定义函数用到查询中还是要慎重，我这里是必须要用数据库处理，不然的话应该拿到应运中去处理计算的而不是依赖数据库。

[ 本帖最后由 whzhaha 于 2010-10-19 00:43 编辑 ]