一个sql查询20多秒，表最多才8000数据，求优化。

Question

贴的格式不好，再来一次
select a.*,
       b.*,
       to_char(a.launch_time, 'yyyy-MM-dd hh24:mi:ss') l_time,
       e.ename,
       ed.dictname,
       d.pathname servicecatalogname,
       b.applicant,
       b.applicant_com,
       t.customerorganname,
       (select tmp.uname
          from (select row_number() over(partition by m.instance_id order by m.task_log_id) gid,
                       m.user_name uname,
                       m.processinstid
                  from bpm_rtm_log_task m
                 where m.process_id =
                       'cn.net.sinodata.sdbomc.itsm.workflow.genericservice.genericservice') tmp
         where tmp.gid = 2
           and a.processinstid = tmp.processinstid)
  from bpm_rtm_process_instance a,
       bpm_biz_itsm_genericservice b,
       itsm_dict_servicecatalog d,
       itsm_dict_gs_emergency e,
       eos_dict_entry ed,
       (select t.customeruserid,
               t.customerid,
               t.customerusername,
               t.customeruserorgan,
               t.customeruserphone2,
               t.customerusermail,
               t.customerusermsn,
               t.customeruserqq,
               t.createname,
               t.createtime,
               t.lastupdatename,
               t.lastupdatetime,
               t.helpdeskapplyrole,
               t.headername,
               t.headerid,
               b.customerorganname
        
          from itsm_dict_customer_user t, itsm_dict_customer_organ b
         where t.customeruserorgan = b.customerorganid(+)) t where a.processinstid = b.processinstid_
   and ed.dicttypeid = 'BPM_CFG_INST_STATUS'
   and a.instance_status = ed.dictid(+)
   and b.servicecatalog = d.id(+)
   and b.urgency_degree = e.id(+)
   and b.applicant_id = t.customeruserid(+)
   and b.applicant_com_id = t.customerid(+)
      
   and b.data_type_ = 'new' order by a.launch_time desc

Answer 1

把执行计划贴上来。PGA多少？你的IO物理读都多少？其实回答了这么几个问题基本上你的问题也就解决了。。

Answer 2

果然是大神。我都不知道有这个东西。PLSQL的怎么看执行计划。哪些是您需要的。

Answer 3

仅看SQL语句 ，需要优化的。
首先sql语句本身可以优化，将  b.data_type_ = 'new' 提到 bpm_biz_itsm_genericservice表中先过滤一次，可以导致后面的左连接的查询次数减少。 同理 ed.dicttypeid = 'BPM_CFG_INST_STATUS'这个过滤条件也可以先进行过滤 ，然后进行左连接其次 在所有的左连接的连接条件上做索引。没有执行计划就只能看到这么多了。

Answer 4

补充一下 执行计划查看  在oracle   sqlplusset  autotrace  on ;
关闭  执行计划   set autotrace off;plsql里面,点击一个命令窗口：
sql> explain  plan  for 
    ----sql语句   
    select  * from test;
sql> select * from table(dbms_xplain.display);
就可以查看到执行计划了。

Answer 5

好吧，话没说明白。
看看你独立的SQL的执行计划。

Answer 6

SELECT STATEMENT, GOAL = ALL_ROWS 375 1538 1056606
 VIEW SDFRAME65 156 4212 252720
  WINDOW SORT PUSHED RANK 156 4212 349596
   TABLE ACCESS FULL SDFRAME65 BPM_RTM_LOG_TASK 71 4212 349596
 SORT ORDER BY 375 1538 1056606
  HASH JOIN RIGHT OUTER 148 1538 1056606
   VIEW SDFRAME65 9 463 18057
    HASH JOIN OUTER 9 463 18057
     TABLE ACCESS FULL SDFRAME65 ITSM_DICT_CUSTOMER_USER 5 463 8334
     TABLE ACCESS FULL SDFRAME65 ITSM_DICT_CUSTOMER_ORGAN 3 43 903
   HASH JOIN RIGHT OUTER 139 1538 996624
    TABLE ACCESS FULL SDFRAME65 ITSM_DICT_SERVICECATALOG 3 99 2871
    HASH JOIN RIGHT OUTER 136 1538 952022
     TABLE ACCESS FULL SDFRAME65 ITSM_DICT_GS_EMERGENCY 3 3 18
     HASH JOIN 132 1538 942794
      TABLE ACCESS BY INDEX ROWID SDFRAME65 EOS_DICT_ENTRY 3 11 352
       INDEX RANGE SCAN SDFRAME65 EOS_DICT_ENTRYINDEX 2 11
      HASH JOIN 129 1604 931924
       TABLE ACCESS FULL SDFRAME65 BPM_RTM_PROCESS_INSTANCE 16 2267 394458
       TABLE ACCESS FULL SDFRAME65 BPM_BIZ_ITSM_GENERICSERVICE 112 1604 652828

Answer 7

就是上面的
 from bpm_rtm_process_instance a,
       bpm_biz_itsm_genericservice b,
       itsm_dict_servicecatalog d,
       itsm_dict_gs_emergency e,
       eos_dict_entry ed,
这个地方的b 写成 
from bpm_rtm_process_instance a,
       (select * from  bpm_biz_itsm_genericservice  where data_type_ = 'new' )b,
       itsm_dict_servicecatalog d,
       itsm_dict_gs_emergency e,
       (select * from eos_dict_entry where dicttypeid = 'BPM_CFG_INST_STATUS' )ed,
然后把最后的两个条件去掉      dicttypeid = 'BPM_CFG_INST_STATUS'     data_type_ = 'new' 估计就问题不大了。  

Answer 8

@babaerzi17 大神 调整了下顺序。执行时间从21.544降到了21.138.我加索引试试

Answer 9

这位大虾，我有几个疑问想请教下：
疑问的内容也大致跟这帖子相似，是一个关于先连接再过滤、还是先过滤再连接的问题，孰优孰劣我至今都没弄明白，上次我写了个sql，根据我自己的理解采取的是先过滤的写法，并且不是用select*，而是直接过滤出整个查询想要的字段，由于4、5张表的连接，每个都先过滤字段和条件，导致整个sql非常长。
针对这个问题还被我项目经理及客户批了，他们说我不会写sql，写的太复杂。先连接再过滤的写法更简洁，更清晰，说oracle会自动优化，我是非常的纳闷啊，但是又找不到合理的解释给他们。如果是先连接再过滤，sql长度将近可以缩短2/3,我也看了下执行计划，cost居然一样(也许我不会看)。
我真的很崩溃啊，大虾，能不能给点意见？？？？

Answer 10

关于这个问题。为什么要先过滤再连接。
打个比方来说吧  A 表  1000条数据  B 表1000条数据。 然后进行连接  最后进行的是过滤。
在oracle里面，最开始的A表与B表进行 A*B 为 1000*1000条数据进行匹配，需要10000000次。然后再进行过滤操作。
如果我们先对A表进行过滤了，A表可能过滤后就剩下300条了，那连接进行的是 3000000次了。给个极端点的，过滤后只剩下1条 ，那我们需要进行连接查询也就仅仅1000次匹配。
这个就是为什么需要先过滤再连接的。一般的话 主表作为数据较少的一端性能比较好。

Answer 11

所以先过滤后连接的性能上是比后过滤要好。就你刚才说的5张表的太繁琐的话，建议你使用with  b as 将过滤的表提出来 然后再看就简洁多了。
with  a  as (
select * from  AA where  col=''
),
b  as  (
select  * from  bb  where  col=''
)select   a.*,b.* from  a ,b  where  a.col=b.col

Answer 12

其实你这个查询慢的主要原因是在select * from table 的* 的位置又出现的子查询：  (select tmp.uname
          from (select row_number() over(partition by m.instance_id order by m.task_log_id) gid,
                       m.user_name uname,
                       m.processinstid
                  from bpm_rtm_log_task m
                 where m.process_id =
                       'cn.net.sinodata.sdbomc.itsm.workflow.genericservice.genericservice') tmp
         where tmp.gid = 2
           and a.processinstid = tmp.processinstid)这样效率是很低很低的，你可以试着先把这个子查询去掉，保证查询时间立马降到5s以内。
个人经验：所有的子查询都要尽量写到from 后面，不要出现在* 的位置

Answer 13

大神，被你说中了，就是这个问题。我小菜。请问具体怎么弄。就是将*位置的子查询挪到from 后面

Answer 14

SELECT A.*,
       B.*,
       TO_CHAR(A.LAUNCH_TIME, 'yyyy-MM-dd hh24:mi:ss') L_TIME,
       E.ENAME,
       ED.DICTNAME,
       D.PATHNAME SERVICECATALOGNAME,
       B.APPLICANT,
       B.APPLICANT_COM,
       T.CUSTOMERORGANNAME,
       F.UNAME
  FROM BPM_RTM_PROCESS_INSTANCE A,
       BPM_BIZ_ITSM_GENERICSERVICE B,
       ITSM_DICT_SERVICECATALOG D,
       ITSM_DICT_GS_EMERGENCY E,
       EOS_DICT_ENTRY ED,
       (SELECT TMP.UNAME,TMP.PROCESSINSTID
          FROM (SELECT ROW_NUMBER() OVER(PARTITION BY M.INSTANCE_ID ORDER BY M.TASK_LOG_ID) GID,
                       M.USER_NAME UNAME,
                       M.PROCESSINSTID
                  FROM BPM_RTM_LOG_TASK M
                 WHERE M.PROCESS_ID =
                       'cn.net.sinodata.sdbomc.itsm.workflow.genericservice.genericservice') TMP
         WHERE TMP.GID = 2) F,
       (SELECT T.CUSTOMERUSERID,
               T.CUSTOMERID,
               T.CUSTOMERUSERNAME,
               T.CUSTOMERUSERORGAN,
               T.CUSTOMERUSERPHONE2,
               T.CUSTOMERUSERMAIL,
               T.CUSTOMERUSERMSN,
               T.CUSTOMERUSERQQ,
               T.CREATENAME,
               T.CREATETIME,
               T.LASTUPDATENAME,
               T.LASTUPDATETIME,
               T.HELPDESKAPPLYROLE,
               T.HEADERNAME,
               T.HEADERID,
               B.CUSTOMERORGANNAME
          FROM ITSM_DICT_CUSTOMER_USER T, ITSM_DICT_CUSTOMER_ORGAN B
         WHERE T.CUSTOMERUSERORGAN = B.CUSTOMERORGANID(+)) T
 WHERE A.PROCESSINSTID = B.PROCESSINSTID_
   AND A.PROCESSINSTID = F.PROCESSINSTID
   AND ED.DICTTYPEID = 'BPM_CFG_INST_STATUS'
   AND A.INSTANCE_STATUS = ED.DICTID(+)
   AND B.SERVICECATALOG = D.ID(+)
   AND B.URGENCY_DEGREE = E.ID(+)
   AND B.APPLICANT_ID = T.CUSTOMERUSERID(+)
   AND B.APPLICANT_COM_ID = T.CUSTOMERID(+)
   AND B.DATA_TYPE_ = 'new'
 ORDER BY A.LAUNCH_TIME DESC

Answer 15

忘记结贴了。的确是子查询的问题。还有SQL没优化的情境下使用物化视图也使查询降到了毫秒级。可喜可贺。