求高手分析此执行计划

orcle  优化

解决方案 »

  1.   

    请附上以下信息：
    1）sql语句
    2）索引分布
    3）表记录数以及表结构
    4）表是否分析过
    5）db版本
      

  2.   

    比你这个复杂多的执行计划我见的多了，，但sql速度依然会很快。你的问题是什么？
      

  3.   

    1、sql语句
    SELECT 
            HDAD.CLIENT_ID AS CLIENT_ID,
            HDAD.ADVICE_ID AS ADVICE_ID,
            HDAD.SOAP_SEQID AS SOAP_SEQID,          
            HDAD.BED_NUM AS BED_NUM,          
            HDAD.CLIENT_NAME AS CLIENT_NAME,  
            HDAD.MED_NAME AS MED_NAME,  
            HDAD.DOC_ID AS DOC_ID,  
            HDAD.NURSE_ID AS NURSE_ID,        
            HDAD.START_TIME AS START_TIME,  
            HDAD.SUM_ADVICE AS SUM_ADVICE,     
            HDAD.EXECUT_ID AS EXECUT_ID ,        
            HDAD.ORG_NAME AS ORG_NAME ,  
            HDAD.ADVICE_PROPERTY AS ADVICE_PROPERTY,  
            HDAD.APP_DETID AS APP_DETID,        
            HDAD.ADVICE_CATEGORY AS ADVICE_CATEGORY,  
            HDAD.MEDBACK_ID  AS MEDBACK_ID,
            HDAD.EXECUT_TIME AS EXECUT_TIME,
            HDAD.TIME AS TIME,
            HDAD.FREQUENCY AS FREQUENCY,
            HDAD.FREQUENCY_NAME AS FREQUENCY_NAME,
            '' AS ACCOUNTS_ID,
            HAC.STATUS AS STATUS,
            HAC.CANCEL_ID AS CANCEL_ID
    FROM HOS_DOCTOR_ADVICE_DONE HDAD,HOS_ADVICE_CANCEL HAC 
    WHERE not exists
           (select 1 from HOS_SETTLE_ACCOUNTS where hos_patient_id=hdad.hos_patient_id)
                       AND  HDAD.EXECUT_ID = HAC.EXECUT_ID(+)
                       AND HDAD.ORG_ID ='CGZWSYJC000'
    2、所有关联字段都建立了索引，包括视图内部的连接
    3、view（有些许改动，将select嵌套查询改为了连接查询）
    CREATE OR REPLACE VIEW HOS_DOCTOR_ADVICE_DONE
    (client_id, advice_id, soap_seqid, bed_num, client_name, med_name, doc_id, nurse_id, start_time, sum_advice, execut_id, org_id, org_name, advice_property, app_detid, advice_category, medback_id, execut_time, frequency, time, execut_id_nottoday, frequency_name, hos_patient_id)
    AS
    SELECT E.CLIENT_ID AS CLIENT_ID,
           D.ADVICE_ID AS ADVICE_ID,
           D.ADVICE_ID AS soap_seqid,
           HB.BED_NUM AS BED_NUM,
           E.CLIENT_NAME AS CLIENT_NAME,
           D.MED_NAME AS MED_NAME,
           p.user_name AS DOC_ID,
           pp.user_name AS NURSE_ID,
           D.START_TIME AS START_TIME,
           ' ' AS SUM_ADVICE,
           ND.EXECUT_ID,
           d.org_id AS ORG_ID,
           c.depart_name AS ORG_NAME,
           D.advice_property AS ADVICE_PROPERTY,
           ' ' AS app_detid,
           D.ADVICE_CATEGORY AS ADVICE_CATEGORY,
           ' ' AS MEDBACK_ID,
           nd.EXECUT_TIME,
           TO_NUMBER(b.DD_DESC) AS FREQUENCY,
           ' ',
           ND.EXECUT_ID AS EXECUT_ID,
           a.DD_DESC AS DD_DESC,
           HP.HOS_PATIENT_ID
      FROM HOS_DOCTOR_ADVICE    D,
           HOS_BED              HB,
           HOS_WARD_MANAGE      HW,
           HOS_PATIENT          HP,
           EHR_CLI_REGISTRY     E,
           pub_manage_user      p,
           pub_manage_user      pp,
           pub_depart_info      c,
           HOS_DD_FREQUENCY     b,
           CLC_DD_MED_FREQUENCY a,
           HOS_NURSE_ADVICE     ND --护士执行表
     WHERE ND.advice_id = D.Advice_Id
       AND ND.Status = '1'
       AND D.ADVICE_STOP = '0'
       AND E.CLIENT_ID = D.CLIENT_ID
       AND D.doc_id = p.user_idcode(+)
       AND nd.execut_opr_id = pp.user_idcode(+)
       AND d.org_id = c.depart_code(+)
       and NVL(D.FREQUENCY, 1) = TO_NUMBER(b.DD_CODE(+))
       and D.FREQUENCY = a.DD_CODE(+)
       AND HB.BED_ID = HW.BED_ID
       AND HW.WARD_ID = D.WARD_ID
       AND HP.CLIENT_ID = D.CLIENT_ID
       UNION all
       SELECT E.CLIENT_ID AS CLIENT_ID,
           DCN.ADVICECN_ID AS ADVICE_ID,
           DCN.ADVICECN_ID AS soap_seqid,
           HB.BED_NUM AS BED_NUM,
           E.CLIENT_NAME AS CLIENT_NAME,
           DCN.MED_NAME AS MED_NAME,
           p.user_name AS DOC_ID,
           pp.user_name AS NURSE_ID,
           DCN.START_TIME AS START_TIME,
           '' AS SUM_ADVICE,
           ND.EXECUT_ID,
           DCN.org_id AS ORG_ID,
           c.depart_name AS ORG_NAME,
           DCN.advice_property AS ADVICE_PROPERTY,
           '' AS app_detid,
           DCN.ADVICE_CATEGORY AS ADVICE_CATEGORY,
           '' AS MEDBACK_ID,
           nd.execut_time,
           TO_NUMBER(b.DD_DESC) AS FREQUENCY,
           '',
           nd.execut_id AS EXECUT_ID,
           a.dd_desc         AS FREQUENCY_NAME,
           HP.HOS_PATIENT_ID
      FROM HOS_DOCTOR_ADVICECN  DCN,
           HOS_BED              HB,
           HOS_WARD_MANAGE      HW,
           HOS_PATIENT          HP,
           EHR_CLI_REGISTRY     E,
           pub_manage_user      p,
           pub_manage_user      pp,
           pub_depart_info      c,
           HOS_DD_FREQUENCY     b,
           CLC_DD_MED_FREQUENCY a,
           HOS_NURSE_ADVICE     ND --护士执行表
     WHERE ND.advice_id = DCN.ADVICECN_ID
       AND ND.Status = '1'
       AND DCN.ADVICE_STOP = '0'
       AND E.CLIENT_ID = DCN.CLIENT_ID
       AND p.user_idcode(+) = DCN.doc_id
       AND pp.user_idcode(+) = nd.execut_opr_id
       and c.depart_code(+) = DCN.org_id
       and TO_NUMBER(b.DD_CODE(+)) = NVL(Dcn.FREQUENCY, 1)
       and Dcn.FREQUENCY = a.DD_CODE(+)
       AND HB.BED_ID = HW.BED_ID
       AND HW.WARD_ID = DCN.WARD_ID
       AND HP.CLIENT_ID = DCN.CLIENT_ID
       UNION all
       SELECT E.CLIENT_ID AS CLIENT_ID,
           DEP.ADVICEP_ID AS ADVICE_ID,
           DEP.ADVICEP_ID AS soap_seqid,
           HB.BED_NUM AS BED_NUM,
           E.CLIENT_NAME AS CLIENT_NAME,
           DEP.ITEM_NAME AS MED_NAME,
           p.user_name AS DOC_ID,
           pp.user_name AS NURSE_ID,
           DEP.START_TIME AS START_TIME,
           '' AS SUM_ADVICE,
           ND.EXECUT_ID,
           dep.org_id AS ORG_ID,
           c.depart_name AS ORG_NAME,
           DEP.advice_property AS ADVICE_PROPERTY,
           '' AS app_detid,
           DEP.ADVICE_CATEGORY AS ADVICE_CATEGORY,
           '' AS MEDBACK_ID,
           ND.EXECUT_TIME,
           TO_NUMBER(b.DD_DESC) AS FREQUENCY,
           '',
           ND.EXECUT_ID AS EXECUT_ID,
           a.dd_desc AS FREQUENCY_NAME,
           HP.HOS_PATIENT_ID
      FROM HOS_DOCTOR_ADVICEP   DEP,
           HOS_BED              HB,
           HOS_WARD_MANAGE      HW,
           HOS_PATIENT          HP,
           EHR_CLI_REGISTRY     E,
           pub_manage_user      p,
           pub_manage_user      pp,
           pub_depart_info      c,
           HOS_DD_FREQUENCY     b,
           CLC_DD_MED_FREQUENCY a,
           HOS_NURSE_ADVICE     ND --护士执行表
     WHERE ND.advice_id = DEP.ADVICEP_ID
       AND ND.Status = '1'
       AND DEP.ADVICE_STOP = '0'
       AND E.CLIENT_ID = DEP.CLIENT_ID
       AND p.user_idcode(+) = DEP.doc_id
       AND pp.user_idcode(+) = ND.EXECUT_OPR_ID
       AND c.depart_code(+) = dep.org_id
       and TO_NUMBER(b.DD_CODE(+)) = NVL(DEP.FREQUENCY, 1)
       and Dep.FREQUENCY = a.DD_CODE(+)
       AND HB.BED_ID = HW.BED_ID
       AND HW.WARD_ID = DEP.WARD_ID
       AND HP.CLIENT_ID = DEP.CLIENT_ID
       UNION all
         SELECT E.CLIENT_ID AS CLIENT_ID,
           DEP.ADVICEP_ID AS ADVICE_ID,
           DEP.ADVICEP_ID AS soap_seqid,
           HB.BED_NUM AS BED_NUM,
           E.CLIENT_NAME AS CLIENT_NAME,
           DEP.ITEM_NAME AS MED_NAME,
           p.user_name AS DOC_ID,
           pp.user_name AS NURSE_ID,
           DEP.START_TIME AS START_TIME,
           '' AS SUM_ADVICE,
           ND.EXECUT_ID,
           dep.org_id AS ORG_ID,
           c.depart_name AS ORG_NAME,
           DEP.advice_property AS ADVICE_PROPERTY,
           '' AS app_detid,
           DEP.ADVICE_CATEGORY AS ADVICE_CATEGORY,
           '' AS MEDBACK_ID,
           ND.EXECUT_TIME,
           TO_NUMBER(b.DD_DESC) AS FREQUENCY,
           '',
           ND.EXECUT_ID AS EXECUT_ID,
           a.dd_desc AS FREQUENCY_NAME,
           HP.HOS_PATIENT_ID
      FROM HOS_DOCTOR_ADVICEP   DEP,
           HOS_BED              HB,
           HOS_WARD_MANAGE      HW,
           HOS_PATIENT          HP,
           EHR_CLI_REGISTRY     E,
           pub_manage_user      p,
           pub_manage_user      pp,
           pub_depart_info      c,
           HOS_DD_FREQUENCY     b,
           CLC_DD_MED_FREQUENCY a,
           HOS_NURSE_ADVICE     ND --护士执行表
     WHERE ND.advice_id = DEP.ADVICEP_ID
       AND ND.Status = '1'
       AND DEP.ADVICE_STOP = '0'
       AND E.CLIENT_ID = DEP.CLIENT_ID
       AND p.user_idcode(+) = DEP.doc_id
       AND pp.user_idcode(+) = ND.EXECUT_OPR_ID
       AND c.depart_code(+) = dep.org_id
       and TO_NUMBER(b.DD_CODE(+)) = NVL(DEP.FREQUENCY, 1)
       and Dep.FREQUENCY = a.DD_CODE(+)
       AND HB.BED_ID = HW.BED_ID
       AND HW.WARD_ID = DEP.WARD_ID
       AND HP.CLIENT_ID = DEP.CLIENT_ID
       UNION all
      SELECT E.CLIENT_ID   AS CLIENT_ID,
           o.SOAPO_SEQID AS ADVICE_ID,
           o.soap_seqid  AS soap_seqid,
           b.bed_num     AS BED_NUM,
           e.client_name AS CLIENT_NAME,
           r.item_name   AS MED_NAME,
           u.user_name AS DOC_ID,
           pp.user_name AS NURSE_ID,
           O.Add_Time AS START_TIME,
           '' AS SUM_ADVICE,
           ND.EXECUT_ID,
           o.org_id AS ORG_ID,
           dp.depart_name AS ORG_NAME,
           '' AS ADVICE_PROPERTY,
           '' AS app_detid,
           '4' AS ADVICE_CATEGORY,
           '' AS MEDBACK_ID,
           nd.execut_time,
           NVL(TO_NUMBER(O.QUANTITY), 1) AS FREQUENCY,
           '',
           nd.execut_id AS EXECUT_ID,
           '' AS FREQUENCY_NAME,
           P.HOS_PATIENT_ID
      FROM CLC_SOAP_MAIN    c,
           CLC_SOAP_O       o,
           ehr_cli_registry e,
           hos_ward_manage  t,
           HOS_PATIENT      p,
           hos_bed          b,
           CLC_PRICELIST    r,
           pub_manage_user  u,
           pub_manage_user  pp,
           pub_depart_info  dp,
           HOS_NURSE_ADVICE ND --护士执行表
     WHERE ND.ADVICE_ID = o.soapo_seqid
       AND ND.Status = '1'
       and u.user_idcode(+) = C.DUTY_ID
       and pp.user_idcode(+) = ND.EXECUT_OPR_ID
       and dp.depart_code(+) = o.org_id
       AND c.SOAP_SEQID = o.SOAP_SEQID
       AND c.client_id = e.client_id
       AND O.hos_patient_id = p.hos_patient_id
       AND T.WARD_ID = O.WARD_ID
       AND p.client_id = e.client_id
       AND t.bed_id = b.bed_id
       AND r.pricelist_seqid = o.pricelist_seqid;
    4、视图的数据量大概是二百多万，
    表HOS_ADVICE_CANCEL数据大概是 两万条，
    select count(*),count(distinct org_id) from HOS_DOCTOR_ADVICE;
    结果 355115 18
    select count(*),count(distinct org_id) from HOS_DOCTOR_ADVICECN;
    结果  6966 9
    select count(*),count(distinct org_id) from HOS_DOCTOR_ADVICEP;
    结果 144672 15
    select count(*),count(distinct org_id) from HOS_NURSE_STUFF;
    结果 224 8
    select count(*),count(distinct org_id) from CLC_SOAP_O;
    结果 201666 14
    5、oracle10g  版本10.2.0.1.0
    这是补充资料 请帮忙优化一下，这个sql语句执行很慢
      

  4.   

    这是后来又新增加的索引
    create index pub_manage_user_id11 on  pub_manage_user(user_idcode,user_name);create index pub_depart_info_id11 on  pub_depart_info(depart_code,depart_name);create index HOS_DD_FREQUENCY_id11 on  HOS_DD_FREQUENCY(DD_CODE,DD_DESC);create index CLC_DD_MED_FREQUENCY_id11 on  CLC_DD_MED_FREQUENCY(DD_CODE,DD_DESC);
      

  5.   

    1）你贴的sql语句和你的执行计划不匹配、贴错了？还是你贴的执行计划是执行这个view得出的？
    2）你在DD_CODE列上建立索引、但你在SQL语句里又用TO_NUMBER(b.DD_CODE(+))把该索引列给污染、导致无法走索引
    3）另外、从你的执行计划可以看出、你走全表扫的表有：
       ① HOS_BED HB
       ② EHR_CLI_REGISTRY E
       ③ HOS_DOCTOR_ADVICE D
       ④ HOS_WARD_MANAGE HW
       ⑤ HOS_PATIENT HP
       而你建立view的语句几乎每个union all 都有：
       ① HB.BED_ID=HW.BED_ID
       ② HW.WARD_ID=D.WARD_ID
       ③ HP.CLIENT_ID=D.CLIENT_ID
       楼主是否考虑先试试在表HB的BED_ID上建立索引、在HW上BED_ID上建立索引、在HW表上WARD_ID上建立索引、在D表上的WARD_ID上建立索引、在D表CLIENT_ID上建立索引、在HP表上的CLIENT_ID上建立索引呢？
      

  6.   

    sql没有贴错，不过我的发的视图是执行计划之后又修改了一下，我把视图里select ··· (select user_name form table where···) AS DOC_ID,··· from table 这种嵌套查询改成连接查询了 p.user_name AS DOC_ID, 然后，视图里面所有的关联查询字段都建立索引了，
    例如 ① HOS_BED HB  索引  PK_HOS_BED Unique BED_ID
    。
     David_Lin，估计在帖子上不好讲清楚，请问要是改为物化视图，
    create materialized view MV_HOS_DOCTOR_ADVICE_DONE
    refresh force on demand
    start with to_date('01-07-2013 16:50:00', 'dd-mm-yyyy hh24:mi:ss') next TRUNC(SYSDATE,'mi')+1/24/60*30 
    as
    select * from HOS_DOCTOR_ADVICE_DONE;
    有什么弊端吗，以前没有使用过物化视图，所以在这发帖子想优化下查询