定位产生异常的语句如下:
select *
from (select t.*, rownum rn
from (select p.video_id, c.path, c.hosts, t.sha1
from converttype c
left join tasks t
on t.id = c.taskid
left join ugc_upload_progress p
on p.video_id = t.videoid
where p.convert_status = 1
and p.convert_success_date >sysdate - 1
and c.type = 0) t
where rownum < 10)
where rn >= 0
手动执行起来没有等到结束时间,时间过长。
查看执行计划如下:
SELECT STATEMENT, GOAL = ALL_ROWS 1066 1 1332
VIEW PPS_UGC 1066 1 1332
COUNT STOPKEY
HASH JOIN 1066 1 241
MERGE JOIN CARTESIAN 722 9364 1498240
TABLE ACCESS FULL PPS_UGC UGC_UPLOAD_PROGRESS 70 1 14
BUFFER SORT 652 32185 4699010
TABLE ACCESS FULL PPS_UGC CONVERTTYPE 652 32185 4699010
TABLE ACCESS FULL PPS_UGC TASKS 343 31510 2552310
一时感觉上没啥问题,考虑加索引试试能不能加快速度
倒动这几张表的数据到测试库测试索引,但是倒动后在测试库上执行速度很快,而且执行计划也和生产库不一致,如下:
SELECT STATEMENT, GOAL = ALL_ROWS 2272 9 11988
VIEW PPSCLIENT 2272 9 11988
COUNT STOPKEY
HASH JOIN 2272 95743 136050803
HASH JOIN 335 3675 1201725
TABLE ACCESS FULL PPSCLIENT UGC_UPLOAD_PROGRESS 57 3675 128625
TABLE ACCESS FULL PPSCLIENT TASKS 278 27956 8163152
TABLE ACCESS FULL PPSCLIENT CONVERTTYPE 204 31969 34974086
对比两个执行计划发现不一样的地方
生产库中多出了MERGE JOIN CARTESIAN和BUFFER SORT
然后怀疑是否是此导致查询速度过慢
网上查询,发现很多相似的案例和解决的方案,MERGE JOIN CARTESIAN就是代表笛卡尔连接的计划,这个笛卡尔连接可是相当相当的耗时间和空间的,必须要修改啊
方案一:对表进行表分析,可能会使执行计划恢复
analyze table *** compute statistics for table for all indexed columns for all indexes;
我进行表分析过后并没有成功改动执行计划。
方案二:改变系统隐藏参数_optimizer_mjc_enabled
alter system set "_optimizer_mjc_enabled" = false;
或是
alter session set "_optimizer_mjc_enabled" = false;
。。。这个还是不要动的好,一不注意生产库完蛋了就郁闷了
方案三:修改sql语句使其不使用笛卡尔连接
对语句的条件进行调试
发现问题出在时间的取值上
and p.convert_success_date >sysdate - 1
这个为什么出问题,真搞不懂啊
找到症状源头,对时间取值进行修改,我是修改成
and p.convert_success_date >sysdate - 1
and p.convert_success_date <=sysdate
这样执行计划就ok了,没有笛卡尔连接了:
SELECT STATEMENT, GOAL = ALL_ROWS 1067 1 1332
VIEW PPS_UGC 1067 1 1332
COUNT STOPKEY
FILTER
HASH JOIN 1067 1 241
HASH JOIN 414 1 95
TABLE ACCESS FULL PPS_UGC UGC_UPLOAD_PROGRESS 70 1 14
TABLE ACCESS FULL PPS_UGC TASKS 343 31510 2552310
TABLE ACCESS FULL PPS_UGC CONVERTTYPE 652 32185 4699010
执行出结果只在一瞬。。。
话说这样的修改有何意义,竟然能改变执行计划,真搞不明白