Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 cursor_sharing=similar  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7430
Приветствую Алл.

В приложении есть довольно много поисковых запросов, с динамически формируемым where clause. Чтобы как-то уменьшить засирание sql кеша и время парсинга решено было включить сабж. Как-то странно оно работает. В трейсе вижу, что литерал заменен системной бинд переменной, но втоже время вижу "Misses in library cache during parse: 1". Если повторить запрос, не меняя значение бинд переменной "Misses in library cache during parse: 0". Т.е. Оракл продолжает парсить при смене значения бинд переменной, что, в общем-то, пытались побороть :( Какие мысли господа по этому поводу?

Поменяли DC_CONFIRM_ID = :"SYS_B_16"

Misses in library cache during parse: 1

select /*+ FIRST_ROWS */ * from (
select distinct
    cl.DC_KEY,
    cl.DCP_KEY,
    cl.DC_CONFIRM_ID,
    cl.CREATED_ON,
    cl.DC_TOTAL_PAYABLE_AMT,
    cl.DC_TOTAL_DEDUCT_AMT,
    cs.CLAIM_STATUS_DESC,
    cl.DC_RELEASE_IND,
    ct.CLAIM_TYPE_CD,
    ct.CLAIM_TYPE_DESC,
    cr.CARD_NUM,
    ch.CARDHOLDER_KEY,
    pr.PERSON_LAST_NAME || :"SYS_B_00" || pr.PERSON_FIRST_NAME PatientName,
    upper(pr.PERSON_LAST_NAME) PERSON_LAST_NAME,
    upper(pr.PERSON_FIRST_NAME) PERSON_FIRST_NAME,
    t04.DENT_PROV_LICENSE_NUM,
    cl.RELEASED_ON,
    pt.PAYEE_TYPE_DESC,
    cl.RECEIVED_ON,
    bt.business_type_cd,
    bt.business_type_desc,
    cl.UPDATED_BY,
    :"SYS_B_01" Discipline
  from
    v_dental_claim cl,
    v_claim_status cs,
    v_claim_type ct,
    v_cardholder ch,
    v_person pr,
    v_card cr,
    v_company co,
    v_business_program_type bt,
    v_payee_type pt,
    v_dental_prov_billing_address t01,
    v_dental_provider t03,
    v_dent_prov_special_assgn t04
  where
    cl.CLAIM_STATUS_KEY = cs.CLAIM_STATUS_KEY
    and
    cl.CLAIM_TYPE_KEY = ct.CLAIM_TYPE_KEY
    and
    cl.CARDHOLDER_KEY = ch.CARDHOLDER_KEY
    and
    ch.CARD_KEY = cr.CARD_KEY
    and
    cr.co_key = co.co_key
    and
    co.business_type_key = bt.business_type_key
    and
    ch.PERSON_KEY = pr.PERSON_KEY
    and
    cl.PAYEE_TYPE_KEY = pt.PAYEE_TYPE_KEY
    and
    t01.DENT_PROV_KEY = t03.DENT_PROV_KEY
    and
    t03.DENT_PROV_KEY = t04.DENT_PROV_KEY
    and
    cl.RECEIVED_ON between t04.EFFECTIVE_ON and nvl(t04.EXPIRED_ON, cl.RECEIVED_ON)
    and
    cl.dent_prov_bill_add_key  = t01.dent_prov_bill_add_key
    and
    exists(
      select
        cl.dc_key
      from
        dual
      where
        :"SYS_B_02" = :param_0
      union all
      select
        dc_key
      from
        v_dental_claim_item ci,
        v_card_eligibility ce,
        v_unit u,
        v_division d,
        v_plans p
      where
        cl.dc_key = ci.dc_key
        and
        DC_ITEM_PROCEDURE_CD like nvl(:PROCEDURE_CD, DC_ITEM_PROCEDURE_CD)
        and
        dc_item_service_dt = nvl(to_date(:service_dt, :"SYS_B_03"), dc_item_service_dt)
        and
        ci.CD_ELIGIBILITY_KEY = ce.CD_ELIGIBILITY_KEY
        and
        ci.DC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(ce.EXPIRED_ON, ci.DC_ITEM_SERVICE_DT)
        and
        ce.UNIT_KEY = u.UNIT_KEY
        and
        ci.DC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(u.TERMINATED_ON, ci.DC_ITEM_SERVICE_DT)
        and
        d.DIVISION_KEY = u.DIVISION_KEY
        and
        ci.DC_ITEM_SERVICE_DT between d.EFFECTIVE_ON and nvl(d.TERMINATED_ON, ci.DC_ITEM_SERVICE_DT)
        and
        d.PLANS_KEY = p.PLANS_KEY
        and
        p.PLANS_ID = nvl(:PLANS_ID, p.PLANS_ID)
    )
union all
select distinct
    cl.HC_KEY,
    cl.HCP_KEY,
    cl.HC_CONFIRM_ID,
    cl.CREATED_ON,
    cl.HC_TOTAL_PAYABLE_AMT,
    cl.HC_TOTAL_DEDUCT_AMT,
    cs.CLAIM_STATUS_DESC,
    cl.HC_RELEASE_IND,
    ct.CLAIM_TYPE_CD,
    ct.CLAIM_TYPE_DESC,
    cr.CARD_NUM,
    ch.CARDHOLDER_KEY,
    pr.PERSON_LAST_NAME || :"SYS_B_04" || pr.PERSON_FIRST_NAME PatientName,
    upper(pr.PERSON_LAST_NAME) PERSON_LAST_NAME,
    upper(pr.PERSON_FIRST_NAME) PERSON_FIRST_NAME,
    t04.hlth_PROV_LICENSE_NUM,
    cl.RELEASED_ON,
    pt.PAYEE_TYPE_DESC,
    cl.RECEIVED_ON,
    bt.business_type_cd,
    bt.business_type_desc,
    cl.UPDATED_BY,
    :"SYS_B_05" Discipline
  from
    v_health_claim cl,
    v_claim_status cs,
    v_claim_type ct,
    v_cardholder ch,
    v_person pr,
    v_card cr,
    v_company co,
    v_business_program_type bt,
    v_payee_type pt,
    v_health_prov_billing_address t01,
    v_health_provider t03,
    v_health_prov_special_assgn t04
  where
    cl.CLAIM_STATUS_KEY = cs.CLAIM_STATUS_KEY
    and
    cl.CLAIM_TYPE_KEY = ct.CLAIM_TYPE_KEY
    and
    cl.CARDHOLDER_KEY = ch.CARDHOLDER_KEY
    and
    ch.CARD_KEY = cr.CARD_KEY
    and
    cr.co_key = co.co_key
    and
    co.business_type_key = bt.business_type_key
    and
    ch.PERSON_KEY = pr.PERSON_KEY
    and
    cl.PAYEE_TYPE_KEY = pt.PAYEE_TYPE_KEY
    and
    t01.HLTH_PROV_KEY = t03.HLTH_PROV_KEY
    and
    t03.HLTH_PROV_KEY = t04.HLTH_PROV_KEY
    and
    cl.RECEIVED_ON between t04.EFFECTIVE_ON and nvl(t04.EXPIRED_ON, cl.RECEIVED_ON)
    and
    cl.HLTH_prov_bill_add_key  = t01.HLTH_prov_bill_add_key
    and
    exists(
      select
        cl.HC_key
      from
        dual
      where
        :"SYS_B_06" = :param_0
      union all
      select
        HC_key
      from
        v_health_claim_item ci,
        v_card_eligibility ce,
        v_unit u,
        v_division d,
        v_plans p
      where
        cl.HC_key = ci.HC_key
        and
        HC_ITEM_PROCEDURE_CD like nvl(:PROCEDURE_CD, HC_ITEM_PROCEDURE_CD)
        and
        hc_item_service_dt = nvl(to_date(:service_dt, :"SYS_B_07"), hc_item_service_dt)
        and
        ci.CD_ELIGIBILITY_KEY = ce.CD_ELIGIBILITY_KEY
        and
        ci.HC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(ce.EXPIRED_ON, ci.hC_ITEM_SERVICE_DT)
        and
        ce.UNIT_KEY = u.UNIT_KEY
        and
        ci.HC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(u.TERMINATED_ON, ci.hC_ITEM_SERVICE_DT)
        and
        d.DIVISION_KEY = u.DIVISION_KEY
        and
        ci.HC_ITEM_SERVICE_DT between d.EFFECTIVE_ON and nvl(d.TERMINATED_ON, ci.hC_ITEM_SERVICE_DT)
        and
        d.PLANS_KEY = p.PLANS_KEY
        and
        p.PLANS_ID = nvl(:PLANS_ID, p.PLANS_ID)
    )
union all
select distinct
    cl.VC_KEY,
    cl.VCP_KEY,
    cl.VC_CONFIRM_ID,
    cl.CREATED_ON,
    cl.VC_TOTAL_PAYABLE_AMT,
    cl.VC_TOTAL_DEDUCT_AMT,
    cs.CLAIM_STATUS_DESC,
    cl.VC_RELEASE_IND,
    ct.CLAIM_TYPE_CD,
    ct.CLAIM_TYPE_DESC,
    cr.CARD_NUM,
    ch.CARDHOLDER_KEY,
    pr.PERSON_LAST_NAME || :"SYS_B_08" || pr.PERSON_FIRST_NAME PatientName,
    upper(pr.PERSON_LAST_NAME) PERSON_LAST_NAME,
    upper(pr.PERSON_FIRST_NAME) PERSON_FIRST_NAME,
    t04.vis_PROV_LICENSE_NUM,
    cl.RELEASED_ON,
    pt.PAYEE_TYPE_DESC,
    cl.RECEIVED_ON,
    bt.business_type_cd,
    bt.business_type_desc,
    cl.UPDATED_BY,
    :"SYS_B_09" Discipline
  from
    v_vision_claim cl,
    v_claim_status cs,
    v_claim_type ct,
    v_cardholder ch,
    v_person pr,
    v_card cr,
    v_company co,
    v_business_program_type bt,
    v_payee_type pt,
    v_vision_prov_billing_address t01,
    v_vision_provider t03,
    v_vis_prov_specialty_assgn t04
  where
    cl.CLAIM_STATUS_KEY = cs.CLAIM_STATUS_KEY
    and
    cl.CLAIM_TYPE_KEY = ct.CLAIM_TYPE_KEY
    and
    cl.CARDHOLDER_KEY = ch.CARDHOLDER_KEY
    and
    ch.CARD_KEY = cr.CARD_KEY
    and
    cr.co_key = co.co_key
    and
    co.business_type_key = bt.business_type_key
    and
    ch.PERSON_KEY = pr.PERSON_KEY
    and
    cl.PAYEE_TYPE_KEY = pt.PAYEE_TYPE_KEY
    and
    t01.VIS_PROV_KEY = t03.VIS_PROV_KEY
    and
    t03.VIS_PROV_KEY = t04.VIS_PROV_KEY
    and
    cl.RECEIVED_ON between t04.EFFECTIVE_ON and nvl(t04.EXPIRED_ON, cl.RECEIVED_ON)
    and
    cl.vis_prov_bill_add_key  = t01.vis_prov_bill_add_key
    and
    exists(
      select
        cl.VC_key
      from
        dual
      where
        :"SYS_B_10" = :param_0
      union all
      select
        VC_key
      from
        v_vision_claim_item ci,
        v_card_eligibility ce,
        v_unit u,
        v_division d,
        v_plans p
      where
        cl.VC_key = ci.VC_key
        and
        VC_ITEM_PROCEDURE_CD like nvl(:PROCEDURE_CD, VC_ITEM_PROCEDURE_CD)
        and
        vc_item_service_dt = nvl(to_date(:service_dt, :"SYS_B_11"), vc_item_service_dt)
        and
        ci.CD_ELIGIBILITY_KEY = ce.CD_ELIGIBILITY_KEY
        and
        ci.VC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(ce.EXPIRED_ON, ci.VC_ITEM_SERVICE_DT)
        and
        ce.UNIT_KEY = u.UNIT_KEY
        and
        ci.VC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(u.TERMINATED_ON, ci.VC_ITEM_SERVICE_DT)
        and
        d.DIVISION_KEY = u.DIVISION_KEY
        and
        ci.VC_ITEM_SERVICE_DT between d.EFFECTIVE_ON and nvl(d.TERMINATED_ON, ci.VC_ITEM_SERVICE_DT)
        and
        d.PLANS_KEY = p.PLANS_KEY
        and
        p.PLANS_ID = nvl(:PLANS_ID, p.PLANS_ID)
    )
union all
select distinct
    cl.HSAC_KEY,
    cl.HSA_CP_KEY,
    cl.HSAC_CONFIRM_ID,
    cl.CREATED_ON,
    cl.HSAC_TOTAL_PAYABLE_AMT,
    null HSAC_TOTAL_DEDUCT_AMT,
    cs.CLAIM_STATUS_DESC,
    cl.HSAC_RELEASE_IND,
    ct.CLAIM_TYPE_CD,
    ct.CLAIM_TYPE_DESC,
    cr.CARD_NUM,
    ch.CARDHOLDER_KEY,
    pr.PERSON_LAST_NAME || :"SYS_B_12" || pr.PERSON_FIRST_NAME PatientName,
    upper(pr.PERSON_LAST_NAME) "upper(PERSON_LAST_NAME)",
    upper(pr.PERSON_FIRST_NAME) "upper(PERSON_FIRST_NAME)",
    dpsa.DENT_PROV_LICENSE_NUM || vpsa.VIS_PROV_LICENSE_NUM || hpsa.HLTH_PROV_LICENSE_NUM || cl.HSAC_PROVIDER_NAME PROV_LICENSE_NUM,
    cl.RELEASED_ON,
    pt.PAYEE_TYPE_DESC,
    cl.RECEIVED_ON,
    bt.business_type_cd,
    bt.business_type_desc,
    cl.UPDATED_BY,
    :"SYS_B_13" Discipline
  from
    v_hsa_claim cl
    join v_claim_status cs on cl.CLAIM_STATUS_KEY = cs.CLAIM_STATUS_KEY
    join v_claim_type ct on cl.CLAIM_TYPE_KEY = ct.CLAIM_TYPE_KEY
    join v_cardholder ch on cl.CARDHOLDER_KEY = ch.CARDHOLDER_KEY
    join v_person pr on ch.PERSON_KEY = pr.PERSON_KEY
    join v_card cr on ch.CARD_KEY = cr.CARD_KEY
    join v_company co on cr.co_key = co.co_key
    join v_business_program_type bt on co.business_type_key = bt.business_type_key
    join v_payee_type pt on cl.PAYEE_TYPE_KEY = pt.PAYEE_TYPE_KEY
    left join v_dental_prov_billing_address dpa on cl.dent_prov_bill_add_key  = dpa.dent_prov_bill_add_key
    left join v_dental_provider dp on dpa.DENT_PROV_KEY = dp.DENT_PROV_KEY
    left join v_dent_prov_special_assgn dpsa on cl.DENT_PROV_SPEC_KEY = dpsa.DENT_PROV_SPEC_KEY
    left join V_VISION_PROV_BILLING_ADDRESS vpba on cl.VIS_PROV_BILL_ADD_KEY = vpba.VIS_PROV_BILL_ADD_KEY
    left join v_vision_provider vp on vpba.VIS_PROV_KEY = vp.VIS_PROV_KEY
    left join V_VIS_PROV_SPECIALTY_ASSGN vpsa on cl.VIS_PROV_SPEC_ASSGN_KEY = vpsa.VIS_PROV_SPEC_ASSGN_KEY
    left join V_HEALTH_PROV_BILLING_ADDRESS hpba on cl.HLTH_PROV_BILL_ADD_KEY = hpba.HLTH_PROV_BILL_ADD_KEY
    left join v_health_provider hp on hpba.HLTH_PROV_KEY = hp.HLTH_PROV_KEY
    left join V_HEALTH_PROV_SPECIAL_ASSGN hpsa on cl.HLTH_PROV_SPEC_ASSGN_KEY = hpsa.HLTH_PROV_SPEC_ASSGN_KEY
  where
    exists(
      select
        cl.hsac_key
      from
        dual
      where
        :"SYS_B_14" = :param_0
      union all
      select
        hsac_key
      from
        v_hsa_claim_item ci,
        v_card_eligibility ce,
        v_unit u,
        v_division d,
        v_plans p
      where
        cl.HSAC_key = ci.HSAC_key
        and
        HSAC_ITEM_PROCEDURE_CD like nvl(:PROCEDURE_CD, HSAC_ITEM_PROCEDURE_CD)
        and
        HSAC_item_service_dt = nvl(to_date(:service_dt, :"SYS_B_15"), HSAC_item_service_dt)
        and
        ci.CD_ELIGIBILITY_KEY = ce.CD_ELIGIBILITY_KEY
        and
        ci.HSAC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(ce.EXPIRED_ON, ci.HSAC_ITEM_SERVICE_DT)
        and
        ce.UNIT_KEY = u.UNIT_KEY
        and
        ci.HSAC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(u.TERMINATED_ON, ci.HSAC_ITEM_SERVICE_DT)
        and
        d.DIVISION_KEY = u.DIVISION_KEY
        and
        ci.HSAC_ITEM_SERVICE_DT between d.EFFECTIVE_ON and nvl(d.TERMINATED_ON, ci.HSAC_ITEM_SERVICE_DT)
        and
        d.PLANS_KEY = p.PLANS_KEY
        and
        p.PLANS_ID = nvl(:PLANS_ID, p.PLANS_ID)
    )
)
WHERE (DC_CONFIRM_ID = :"SYS_B_16") order by to_number(DC_CONFIRM_ID) desc

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      2.78       2.92          0        152          0           0
Execute      1      2.19       2.43          0        355          0           0
Fetch        1      0.00       0.00          0         42          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      4.97       5.36          0        549          0           1

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 61  

Повторяющееся значение DC_CONFIRM_ID = :"SYS_B_16"

Misses in library cache during parse: 0


select /*+ FIRST_ROWS */ * from (
select distinct
    cl.DC_KEY,
    cl.DCP_KEY,
    cl.DC_CONFIRM_ID,
    cl.CREATED_ON,
    cl.DC_TOTAL_PAYABLE_AMT,
    cl.DC_TOTAL_DEDUCT_AMT,
    cs.CLAIM_STATUS_DESC,
    cl.DC_RELEASE_IND,
    ct.CLAIM_TYPE_CD,
    ct.CLAIM_TYPE_DESC,
    cr.CARD_NUM,
    ch.CARDHOLDER_KEY,
    pr.PERSON_LAST_NAME || :"SYS_B_00" || pr.PERSON_FIRST_NAME PatientName,
    upper(pr.PERSON_LAST_NAME) PERSON_LAST_NAME,
    upper(pr.PERSON_FIRST_NAME) PERSON_FIRST_NAME,
    t04.DENT_PROV_LICENSE_NUM,
    cl.RELEASED_ON,
    pt.PAYEE_TYPE_DESC,
    cl.RECEIVED_ON,
    bt.business_type_cd,
    bt.business_type_desc,
    cl.UPDATED_BY,
    :"SYS_B_01" Discipline
  from
    v_dental_claim cl,
    v_claim_status cs,
    v_claim_type ct,
    v_cardholder ch,
    v_person pr,
    v_card cr,
    v_company co,
    v_business_program_type bt,
    v_payee_type pt,
    v_dental_prov_billing_address t01,
    v_dental_provider t03,
    v_dent_prov_special_assgn t04
  where
    cl.CLAIM_STATUS_KEY = cs.CLAIM_STATUS_KEY
    and
    cl.CLAIM_TYPE_KEY = ct.CLAIM_TYPE_KEY
    and
    cl.CARDHOLDER_KEY = ch.CARDHOLDER_KEY
    and
    ch.CARD_KEY = cr.CARD_KEY
    and
    cr.co_key = co.co_key
    and
    co.business_type_key = bt.business_type_key
    and
    ch.PERSON_KEY = pr.PERSON_KEY
    and
    cl.PAYEE_TYPE_KEY = pt.PAYEE_TYPE_KEY
    and
    t01.DENT_PROV_KEY = t03.DENT_PROV_KEY
    and
    t03.DENT_PROV_KEY = t04.DENT_PROV_KEY
    and
    cl.RECEIVED_ON between t04.EFFECTIVE_ON and nvl(t04.EXPIRED_ON, cl.RECEIVED_ON)
    and
    cl.dent_prov_bill_add_key  = t01.dent_prov_bill_add_key
    and
    exists(
      select
        cl.dc_key
      from
        dual
      where
        :"SYS_B_02" = :param_0
      union all
      select
        dc_key
      from
        v_dental_claim_item ci,
        v_card_eligibility ce,
        v_unit u,
        v_division d,
        v_plans p
      where
        cl.dc_key = ci.dc_key
        and
        DC_ITEM_PROCEDURE_CD like nvl(:PROCEDURE_CD, DC_ITEM_PROCEDURE_CD)
        and
        dc_item_service_dt = nvl(to_date(:service_dt, :"SYS_B_03"), dc_item_service_dt)
        and
        ci.CD_ELIGIBILITY_KEY = ce.CD_ELIGIBILITY_KEY
        and
        ci.DC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(ce.EXPIRED_ON, ci.DC_ITEM_SERVICE_DT)
        and
        ce.UNIT_KEY = u.UNIT_KEY
        and
        ci.DC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(u.TERMINATED_ON, ci.DC_ITEM_SERVICE_DT)
        and
        d.DIVISION_KEY = u.DIVISION_KEY
        and
        ci.DC_ITEM_SERVICE_DT between d.EFFECTIVE_ON and nvl(d.TERMINATED_ON, ci.DC_ITEM_SERVICE_DT)
        and
        d.PLANS_KEY = p.PLANS_KEY
        and
        p.PLANS_ID = nvl(:PLANS_ID, p.PLANS_ID)
    )
union all
select distinct
    cl.HC_KEY,
    cl.HCP_KEY,
    cl.HC_CONFIRM_ID,
    cl.CREATED_ON,
    cl.HC_TOTAL_PAYABLE_AMT,
    cl.HC_TOTAL_DEDUCT_AMT,
    cs.CLAIM_STATUS_DESC,
    cl.HC_RELEASE_IND,
    ct.CLAIM_TYPE_CD,
    ct.CLAIM_TYPE_DESC,
    cr.CARD_NUM,
    ch.CARDHOLDER_KEY,
    pr.PERSON_LAST_NAME || :"SYS_B_04" || pr.PERSON_FIRST_NAME PatientName,
    upper(pr.PERSON_LAST_NAME) PERSON_LAST_NAME,
    upper(pr.PERSON_FIRST_NAME) PERSON_FIRST_NAME,
    t04.hlth_PROV_LICENSE_NUM,
    cl.RELEASED_ON,
    pt.PAYEE_TYPE_DESC,
    cl.RECEIVED_ON,
    bt.business_type_cd,
    bt.business_type_desc,
    cl.UPDATED_BY,
    :"SYS_B_05" Discipline
  from
    v_health_claim cl,
    v_claim_status cs,
    v_claim_type ct,
    v_cardholder ch,
    v_person pr,
    v_card cr,
    v_company co,
    v_business_program_type bt,
    v_payee_type pt,
    v_health_prov_billing_address t01,
    v_health_provider t03,
    v_health_prov_special_assgn t04
  where
    cl.CLAIM_STATUS_KEY = cs.CLAIM_STATUS_KEY
    and
    cl.CLAIM_TYPE_KEY = ct.CLAIM_TYPE_KEY
    and
    cl.CARDHOLDER_KEY = ch.CARDHOLDER_KEY
    and
    ch.CARD_KEY = cr.CARD_KEY
    and
    cr.co_key = co.co_key
    and
    co.business_type_key = bt.business_type_key
    and
    ch.PERSON_KEY = pr.PERSON_KEY
    and
    cl.PAYEE_TYPE_KEY = pt.PAYEE_TYPE_KEY
    and
    t01.HLTH_PROV_KEY = t03.HLTH_PROV_KEY
    and
    t03.HLTH_PROV_KEY = t04.HLTH_PROV_KEY
    and
    cl.RECEIVED_ON between t04.EFFECTIVE_ON and nvl(t04.EXPIRED_ON, cl.RECEIVED_ON)
    and
    cl.HLTH_prov_bill_add_key  = t01.HLTH_prov_bill_add_key
    and
    exists(
      select
        cl.HC_key
      from
        dual
      where
        :"SYS_B_06" = :param_0
      union all
      select
        HC_key
      from
        v_health_claim_item ci,
        v_card_eligibility ce,
        v_unit u,
        v_division d,
        v_plans p
      where
        cl.HC_key = ci.HC_key
        and
        HC_ITEM_PROCEDURE_CD like nvl(:PROCEDURE_CD, HC_ITEM_PROCEDURE_CD)
        and
        hc_item_service_dt = nvl(to_date(:service_dt, :"SYS_B_07"), hc_item_service_dt)
        and
        ci.CD_ELIGIBILITY_KEY = ce.CD_ELIGIBILITY_KEY
        and
        ci.HC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(ce.EXPIRED_ON, ci.hC_ITEM_SERVICE_DT)
        and
        ce.UNIT_KEY = u.UNIT_KEY
        and
        ci.HC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(u.TERMINATED_ON, ci.hC_ITEM_SERVICE_DT)
        and
        d.DIVISION_KEY = u.DIVISION_KEY
        and
        ci.HC_ITEM_SERVICE_DT between d.EFFECTIVE_ON and nvl(d.TERMINATED_ON, ci.hC_ITEM_SERVICE_DT)
        and
        d.PLANS_KEY = p.PLANS_KEY
        and
        p.PLANS_ID = nvl(:PLANS_ID, p.PLANS_ID)
    )
union all
select distinct
    cl.VC_KEY,
    cl.VCP_KEY,
    cl.VC_CONFIRM_ID,
    cl.CREATED_ON,
    cl.VC_TOTAL_PAYABLE_AMT,
    cl.VC_TOTAL_DEDUCT_AMT,
    cs.CLAIM_STATUS_DESC,
    cl.VC_RELEASE_IND,
    ct.CLAIM_TYPE_CD,
    ct.CLAIM_TYPE_DESC,
    cr.CARD_NUM,
    ch.CARDHOLDER_KEY,
    pr.PERSON_LAST_NAME || :"SYS_B_08" || pr.PERSON_FIRST_NAME PatientName,
    upper(pr.PERSON_LAST_NAME) PERSON_LAST_NAME,
    upper(pr.PERSON_FIRST_NAME) PERSON_FIRST_NAME,
    t04.vis_PROV_LICENSE_NUM,
    cl.RELEASED_ON,
    pt.PAYEE_TYPE_DESC,
    cl.RECEIVED_ON,
    bt.business_type_cd,
    bt.business_type_desc,
    cl.UPDATED_BY,
    :"SYS_B_09" Discipline
  from
    v_vision_claim cl,
    v_claim_status cs,
    v_claim_type ct,
    v_cardholder ch,
    v_person pr,
    v_card cr,
    v_company co,
    v_business_program_type bt,
    v_payee_type pt,
    v_vision_prov_billing_address t01,
    v_vision_provider t03,
    v_vis_prov_specialty_assgn t04
  where
    cl.CLAIM_STATUS_KEY = cs.CLAIM_STATUS_KEY
    and
    cl.CLAIM_TYPE_KEY = ct.CLAIM_TYPE_KEY
    and
    cl.CARDHOLDER_KEY = ch.CARDHOLDER_KEY
    and
    ch.CARD_KEY = cr.CARD_KEY
    and
    cr.co_key = co.co_key
    and
    co.business_type_key = bt.business_type_key
    and
    ch.PERSON_KEY = pr.PERSON_KEY
    and
    cl.PAYEE_TYPE_KEY = pt.PAYEE_TYPE_KEY
    and
    t01.VIS_PROV_KEY = t03.VIS_PROV_KEY
    and
    t03.VIS_PROV_KEY = t04.VIS_PROV_KEY
    and
    cl.RECEIVED_ON between t04.EFFECTIVE_ON and nvl(t04.EXPIRED_ON, cl.RECEIVED_ON)
    and
    cl.vis_prov_bill_add_key  = t01.vis_prov_bill_add_key
    and
    exists(
      select
        cl.VC_key
      from
        dual
      where
        :"SYS_B_10" = :param_0
      union all
      select
        VC_key
      from
        v_vision_claim_item ci,
        v_card_eligibility ce,
        v_unit u,
        v_division d,
        v_plans p
      where
        cl.VC_key = ci.VC_key
        and
        VC_ITEM_PROCEDURE_CD like nvl(:PROCEDURE_CD, VC_ITEM_PROCEDURE_CD)
        and
        vc_item_service_dt = nvl(to_date(:service_dt, :"SYS_B_11"), vc_item_service_dt)
        and
        ci.CD_ELIGIBILITY_KEY = ce.CD_ELIGIBILITY_KEY
        and
        ci.VC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(ce.EXPIRED_ON, ci.VC_ITEM_SERVICE_DT)
        and
        ce.UNIT_KEY = u.UNIT_KEY
        and
        ci.VC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(u.TERMINATED_ON, ci.VC_ITEM_SERVICE_DT)
        and
        d.DIVISION_KEY = u.DIVISION_KEY
        and
        ci.VC_ITEM_SERVICE_DT between d.EFFECTIVE_ON and nvl(d.TERMINATED_ON, ci.VC_ITEM_SERVICE_DT)
        and
        d.PLANS_KEY = p.PLANS_KEY
        and
        p.PLANS_ID = nvl(:PLANS_ID, p.PLANS_ID)
    )
union all
select distinct
    cl.HSAC_KEY,
    cl.HSA_CP_KEY,
    cl.HSAC_CONFIRM_ID,
    cl.CREATED_ON,
    cl.HSAC_TOTAL_PAYABLE_AMT,
    null HSAC_TOTAL_DEDUCT_AMT,
    cs.CLAIM_STATUS_DESC,
    cl.HSAC_RELEASE_IND,
    ct.CLAIM_TYPE_CD,
    ct.CLAIM_TYPE_DESC,
    cr.CARD_NUM,
    ch.CARDHOLDER_KEY,
    pr.PERSON_LAST_NAME || :"SYS_B_12" || pr.PERSON_FIRST_NAME PatientName,
    upper(pr.PERSON_LAST_NAME) "upper(PERSON_LAST_NAME)",
    upper(pr.PERSON_FIRST_NAME) "upper(PERSON_FIRST_NAME)",
    dpsa.DENT_PROV_LICENSE_NUM || vpsa.VIS_PROV_LICENSE_NUM || hpsa.HLTH_PROV_LICENSE_NUM || cl.HSAC_PROVIDER_NAME PROV_LICENSE_NUM,
    cl.RELEASED_ON,
    pt.PAYEE_TYPE_DESC,
    cl.RECEIVED_ON,
    bt.business_type_cd,
    bt.business_type_desc,
    cl.UPDATED_BY,
    :"SYS_B_13" Discipline
  from
    v_hsa_claim cl
    join v_claim_status cs on cl.CLAIM_STATUS_KEY = cs.CLAIM_STATUS_KEY
    join v_claim_type ct on cl.CLAIM_TYPE_KEY = ct.CLAIM_TYPE_KEY
    join v_cardholder ch on cl.CARDHOLDER_KEY = ch.CARDHOLDER_KEY
    join v_person pr on ch.PERSON_KEY = pr.PERSON_KEY
    join v_card cr on ch.CARD_KEY = cr.CARD_KEY
    join v_company co on cr.co_key = co.co_key
    join v_business_program_type bt on co.business_type_key = bt.business_type_key
    join v_payee_type pt on cl.PAYEE_TYPE_KEY = pt.PAYEE_TYPE_KEY
    left join v_dental_prov_billing_address dpa on cl.dent_prov_bill_add_key  = dpa.dent_prov_bill_add_key
    left join v_dental_provider dp on dpa.DENT_PROV_KEY = dp.DENT_PROV_KEY
    left join v_dent_prov_special_assgn dpsa on cl.DENT_PROV_SPEC_KEY = dpsa.DENT_PROV_SPEC_KEY
    left join V_VISION_PROV_BILLING_ADDRESS vpba on cl.VIS_PROV_BILL_ADD_KEY = vpba.VIS_PROV_BILL_ADD_KEY
    left join v_vision_provider vp on vpba.VIS_PROV_KEY = vp.VIS_PROV_KEY
    left join V_VIS_PROV_SPECIALTY_ASSGN vpsa on cl.VIS_PROV_SPEC_ASSGN_KEY = vpsa.VIS_PROV_SPEC_ASSGN_KEY
    left join V_HEALTH_PROV_BILLING_ADDRESS hpba on cl.HLTH_PROV_BILL_ADD_KEY = hpba.HLTH_PROV_BILL_ADD_KEY
    left join v_health_provider hp on hpba.HLTH_PROV_KEY = hp.HLTH_PROV_KEY
    left join V_HEALTH_PROV_SPECIAL_ASSGN hpsa on cl.HLTH_PROV_SPEC_ASSGN_KEY = hpsa.HLTH_PROV_SPEC_ASSGN_KEY
  where
    exists(
      select
        cl.hsac_key
      from
        dual
      where
        :"SYS_B_14" = :param_0
      union all
      select
        hsac_key
      from
        v_hsa_claim_item ci,
        v_card_eligibility ce,
        v_unit u,
        v_division d,
        v_plans p
      where
        cl.HSAC_key = ci.HSAC_key
        and
        HSAC_ITEM_PROCEDURE_CD like nvl(:PROCEDURE_CD, HSAC_ITEM_PROCEDURE_CD)
        and
        HSAC_item_service_dt = nvl(to_date(:service_dt, :"SYS_B_15"), HSAC_item_service_dt)
        and
        ci.CD_ELIGIBILITY_KEY = ce.CD_ELIGIBILITY_KEY
        and
        ci.HSAC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(ce.EXPIRED_ON, ci.HSAC_ITEM_SERVICE_DT)
        and
        ce.UNIT_KEY = u.UNIT_KEY
        and
        ci.HSAC_ITEM_SERVICE_DT between ce.EFFECTIVE_ON and nvl(u.TERMINATED_ON, ci.HSAC_ITEM_SERVICE_DT)
        and
        d.DIVISION_KEY = u.DIVISION_KEY
        and
        ci.HSAC_ITEM_SERVICE_DT between d.EFFECTIVE_ON and nvl(d.TERMINATED_ON, ci.HSAC_ITEM_SERVICE_DT)
        and
        d.PLANS_KEY = p.PLANS_KEY
        and
        p.PLANS_ID = nvl(:PLANS_ID, p.PLANS_ID)
    )
)
WHERE (DC_CONFIRM_ID = :"SYS_B_16") order by to_number(DC_CONFIRM_ID) desc

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         42          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.01          0         42          0           1

Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 61  
5 фев 08, 01:10    [5243383]     Ответить | Цитировать Сообщить модератору
 Re: cursor_sharing=similar  [new]
Тынц.
Guest
При cursor_sharing = similar будет произвдён hp, если Oracle посчитает, что для нового литерала лучше построить более другой план.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams035.htm#sthref156

FORCE

Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

SIMILAR

Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

EXACT

Only allows statements with identical text to share the same cursor.


+ обсуждалось, наверное, не раз
+ статья: http://www.oracle.com/global/ru/oramag/dec2006/w_dev_asktom_o16.html
+ в Performance Tuning Guide пару слов есть
+ гляньте на Металинке количество багов, связанных с similar и force :)
5 фев 08, 04:09    [5243437]     Ответить | Цитировать Сообщить модератору
 Re: cursor_sharing=similar  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7430
Тынц.
При cursor_sharing = similar будет произвдён hp, если Oracle посчитает, что для нового литерала лучше построить более другой план.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams035.htm#sthref156

FORCE

Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

SIMILAR

Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

EXACT

Only allows statements with identical text to share the same cursor.


+ обсуждалось, наверное, не раз
+ статья: http://www.oracle.com/global/ru/oramag/dec2006/w_dev_asktom_o16.html
+ в Performance Tuning Guide пару слов есть
+ гляньте на Металинке количество багов, связанных с similar и force :)
Том, конечно, дядька великорозумный. Может обосновать любой кривовой дизайн, в частности разделяемого пула. Пусть лучше подумает на досуге, зачем загромождать пул запросами с литералами, которые могут расшарится, ну, наверно, только теоретически. И уж, конечно, выдумывать параметры типа similar, заставляющие задуматься над словол "разделяемый". Назовите его просто пул, вопросов хоть будет меньше ;))
6 фев 08, 17:58    [5253763]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить