SQL性能优化实践-优快云博客

select distinct oi.customer_id, oi.order_id, oi.create_time, 1 type

from t_to_order_info oi

left join t_TO_Consign_Pymt_Record cpr

on oi.order_id = cpr.order_id

where 1 = 1

and exists (select 1

from t_TO_Order_Tickets

where t_TO_Order_Tickets.Order_ID = oi.Order_ID

and to_char(Departure_Time, 'yyyyMMdd') =

to_char(sysdate, 'yyyyMMdd'))

and not exists

(select 1 from t_to_order_change oc where oc.order_id = oi.order_id)

and ((cpr.pymt_type = 1 or

cpr.pymt_type = 3 and oi.guest_pymt_sts_id = 2)

(cpr.pymt_type = 2 and

cpr.partner_id in

(select p.partner_id

from t_tp_partner p

where p.is_company_inner = 1) and oi.guest_pymt_sts_id = 2)

or (cpr.pymt_type = 2 and

cpr.partner_id in

(select p.partner_id

from t_tp_partner p

where p.agent_class_id = 1

and p.is_company_inner != 1) and

to_char(oi.issue_time) <> ' ')

(cpr.pymt_type = 2 and

cpr.partner_id in

(select p.partner_id

from t_tp_partner p

where p.agent_class_id != 1) and to_char(oi.issue_time) <> ' '))

这么一条SQL，要跑30多秒，开发让调优

我第一的想法是看执行计划，t_to_order_info和t_TO_Order_Tickets这两张表是hash连接，但是两表走的是全表扫描，我首先是将目标定向了t_to_order_info走全表扫描的原因，以为是exists造成的，在这里我并不知道是哪里没走索引造成的，我将SQL多运行了几次，然后再em工具里面的TOP SQL里面找到了这条SQL，看了oracle给的建议

首先运行了如下命令收集了索引的统计信息

dbms_stats.gather_index_stats(ownname => 'TEST20110217', indname => 'IDX_BT_T_TO_ORDER_HANG', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

dbms_stats.gather_index_stats(ownname => 'TEST20110217', indname => 'TO_RELATIONCHANGE_FK', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

dbms_stats.gather_index_stats(ownname => 'TEST20110217', indname => 'PK_T_TO_ORDER_CHANGE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

end;

做完次操作后效率没有提高，再看到oracle给的一个建议，就是说

t_TO_Order_Tickets

where t_TO_Order_Tickets.Order_ID = oi.Order_ID

and to_char(Departure_Time, 'yyyyMMdd') =

to_char(sysdate, 'yyyyMMdd'))

这里出了问题，Departure_Time本来是有索引的，但是这里没有走索引，因为to_char的方式无法让它再走索引了，这是问题的关键，这种问题可以通过函数索引来解决

SQL> create index TEST20110217.IDX_HANSU on TEST20110217.T_TO_ORDER_TICKETS(TO_CHAR("DEPARTURE_TIME",'yyyyMMdd'));

SQL> exec dbms_stats.gather_index_stats('TEST20110217','IDX_HANSU');

上面创建了函数索引变收集了新索引的统计信息，SQL的效率得到了大大的提高，差不多1秒之类就OK了，同时验证了一点就是一个列上函数索引和普通索引可以并存他们各走各的，不影响性能，问题本来在这里就已经解决了了，但是开发的后面需要在 DEPARTURE_TIME+1这将再次引起不走索引的情况，那么必须通过改写SQL的方式来解决

and Departure_Time between to_date( to_char(sysdate-1,'YYYY-MM-DD HH24:MI:SS') ,'YYYY-MM-DD HH24:MI:SS')

and to_date( to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') ,'YYYY-MM-DD HH24:MI:SS'))

其实问题的关键就在departure_time，用上面这种方式不变departure_time，改变等号的那边方可解决走索引的问题。

总结：

整个问题的关键地方是如何定位到问题是departure_time列不走索引而引起的性能问题，

这次是通过em工具找出来的提醒，那么如果下次在em工具中找不到TOP SQL,自己如何分

析定位到这个点上面来。t_TO_Order_Tickets 这张表后面where 字段有等号的地方，就

是这种字段。这次调优没有经验，下次在看到如果在时间字段上面做了手段，就应该将

问题快速的定位到这个上面来

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10678398/viewspace-715093/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10678398/viewspace-715093/