Oracle执行计划之SQL优化

前段时间一个项目频繁报weblogic相关的错误，具体错误如下为：

 <[ACTIVE] ExecuteThread: '15' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1464601972012> <BEA-000337> <[STUCK] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "608" seconds working on the request "Workmanager: default, Version: 0, Scheduled=true, Started=true, Started time: 608295 ms
   
   
  
  
   
   
    
    ", which is more than the configured time (StuckThreadMaxTime) of "600" seconds. Stack trace:
   
   
  
  
   
   
  
  
    
    
   
    java.net.SocketInputStream.socketRead0(Native Method)
   
   
  
  
   
   
  
  
    
    
   
    java.net.SocketInputStream.read(SocketInputStream.java:129)
   
   
  
  
   
   
  
  
    
    
   
    oracle.net.ns.Packet.receive(Packet.java:300)
   
   
  
  
   
   
  
  
    
    
   
    oracle.net.ns.DataPacket.receive(DataPacket.java:106)
   
   
  
  
   
   
  
  
    
    
   
    oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:315)
   
   
  
  
   
   
  
  
    
    
   
    oracle.net.ns.NetInputStream.read(NetInputStream.java:260)
   
   
  
  
   
   
  
  
    
    
   
    oracle.net.ns.NetInputStream.read(NetInputStream.java:185)
   
   
  
  
   
   
  
  
    
    
   
    oracle.net.ns.NetInputStream.read(NetInputStream.java:102)
   
   
  
  
   
   
  
  
    
    
   
    oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:124)
   
   
  
  
   
   
  
  
    
    
   
    oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:80)

这个问题最终结果导致weblogic server线程阻塞，server最初报出警告、最后宕机，因为weblogic server是一个集群环境，每个server最大线程数也很少，最大线程数大致在25，虽然执行时间超过600s后，weblogic本身会回收线程，但在某一段时间访问用户比较多时，server还是经常宕机，这样问题就比较严重，所以得尽快排查并解决该问题。

后续安排人员进行排查，首先将该业务报错的sql在sql plus中进行执行，执行时间虽然有些慢，大致在5~6秒，重复执行几次也差不多是这个时间，这时判断该错误应该和sql执行效率没有关系，sql如下：

SELECT
*
FROM
(
SELECT
o.*, Rownum rowno
FROM
(
SELECT
con1.res_id,
conrel.title,
conrel.class_id,
con1.res_abstract,
con1.title_img,
con1.res_author,
con1.res_wordcount,
con1.res_content,
con1.res_type,
con1.res_keywords,
con1.editor,
con1.person,
con1.job,
conrel.pub_date,
(
CASE
WHEN con1.RES_TYPE = 0 THEN
1
ELSE
0
END
) AS con_type,
con1.enter_type,
con1.url,
con1.res_id AS source_id,
con1.thumb_img,
con1.medium_img,
con1.media_url,
con1.PRODUCT_COLUMN_ID AS master_id,
con1.RES_SUBTOPIC,
con1.product_id,
con1.pre_type,
con1.tpl_id,
con1.res_catid,
con1.res_catname,
con1.CAT_REGION,
con1.RES_SOURCE,
con1.small_img,
con1.large_img,
con1.html_url,
con1.tag_id,
con1.tag_name,
con1.cat_regionid,
con1.video_flv_300,
con1.video_flv_500,
con1.video_flv_1024,
con1.video_mp4_300,
con1.video_mp4_500,
con1.video_mp4_1024,
con1.GOODS_ID,
con1.GOODS_TYPE
FROM
eps_product_res con1,
tp_con_class_rel conrel
WHERE
con1.res_id = conrel.con_id
AND conrel. STATUS = 2
AND conrel.class_id = 51
AND con1.enter_type = 0
AND conrel.pub_date >= to_date (
'2016-06-01 00:00:00',
'yyyy-MM-dd HH24:mi:ss'
)
AND conrel.pub_date <= to_date (
'2016-06-28 00:00:00',
'yyyy-MM-dd HH24:mi:ss'
)
ORDER BY
conrel.display_order ASC,
pub_date DESC
) o
)
WHERE
1 = 1
AND rowno >= 0
AND rowno <= 30

判断可能问题是在用户单位网络延迟或者weblogic 本身的虚拟机器（这里说一下用户weblogic部署的服务器为虚拟机），这种问题不就不太好判断了，因为用户单位网络复杂、各部门关系也复杂，协调起来迟缓、麻烦，刚好在中间一段时间内有国家领导出访，用户访问量在这段时间比较平常多，导致weblogic server频繁出现警告及宕机，没辙，只能人为重启，还好是集群环境，重启其中部分server用户感觉不到。就这么恶心的临时重启来解决问题，连周末都没过好，得有人盯着server的运行情况。

问题没解决还得继续想办法解决，于是继续找用户协调数据库相关部门配合排查，同时程序也加调试日志，输出执行时长，通过调试日志执行时长来看，确实频繁出现业务sql执行大于600s情况，但就是无法判断是在数据库本身还是在网络上，因为之前手动执行sql没出现过这么慢的情况，后续和合作厂商进行交流，说他们也出现了这种情况，正常sql执行挺快，但是也会出现比较慢，出现执行比较慢的原因是没有走oracle的执行计划，执行计划具体机制也不清楚，可能需要优化sql，但这sql相对也不复杂，重写影响会比较大，因为整个系统用此类sql的地方较多，所以一时也没想好怎么来解决，后来用户协调到了oracle工程师过来进行交流，还是oracle工程师给力，给出sql会出现不走执行计划的情况，但具体原因不明（我没参加这次交流），最后oracle工程师给了一些解决建议：

1、在这条sql中加一个时间限制范围条件，加了就让oracle每次都走执行计划，我猜的。

2、针对where后面有限制条件的属性加上索引，应该是提高执行时间。

3、分页条数限制rowno放到内层语句中，难道也是为了sql执行都走执行计划，没弄明白。

后面按这些建议对sql做了优化，果然最近两三周没有再出现类似的问题，问题应该是解决了，但sql执行计划机制没有弄明白，后面得花些时间研究。

秒客网

Oracle执行计划之SQL优化

相关文章