Home » RDBMS Server » Performance Tuning » What should i do for tuning this query (Oracle 10g)
What should i do for tuning this query [message #655786] Mon, 12 September 2016 23:58 Go to next message
rayrevan
Messages: 21
Registered: August 2016
Junior Member
Sql Queries
CREATE OR REPLACE VIEW STD_V_SALES_DISTRIBUTOR
(SLS_SALES_DISTRIBUTOR_ID, C_PERIOD_ID, STD_VERSION_ID, STD_TYPE_ID, TAHUN, 
 BULAN, TANGGAL, REMAIN, DISTRIBUTOR, KODE_PELANGGAN, 
 NAMA_PELANGGAN, ALAMAT_PELANGGAN, ALAMAT_LENGKAP, CABANG, SEKTOR, 
 KOTA, TEAM_ID, M_PRODUCT_ID, PRODUCT, PRODUCT_REF_CODE, 
 PRODUCT_REF_NAME, CODE, PRICE_NET, PRICE_GROSS, PRICE, 
 QTY, SALES, VALUE, DISCOUNT, DISCOUNT_VALUE, 
 NETTO, PANEL, HIDE)
AS 
select 
    a.sls_sales_distributor_id, a.c_period_id, b.std_version_id, c.std_type_id, a.tahun, a.bulan, a.tanggal, a.remain, a.distributor, a.kode_pelanggan, a.nama_pelanggan, 
    a.alamat_pelanggan, a.alamat_lengkap, a.cabang, a.sektor, a.kota, a.team_id, 
    a.m_product_id, a.product, a.product_ref_code, a.product_ref_name, 
    case when a.panel = 'Y' then a.code_member
         when a.m_product_id = dp.m_product_id then dp.mapp_code
         else dp.code end as code, 
    a.price_net, a.price_gross, a.price, a.qty, 
    --a.sales, 
    case when panel = 'Y' and hide = 'N' then a.value else a.sales end sales,
    a.value, a.discount,
    case when a.discount > 0 then ((a.price*a.discount)/100)*qty
         else 0 end as discount_value,
    case when a.discount > 0 then a.value - ((a.price*a.discount)/100)*qty
         else a.value end as netto, a.panel, a.hide
from (
    select 
        distinct 
        a.sls_sales_distributor_id,
        e.c_period_id, a.tahun_periode as tahun, a.bulan_periode as bulan, a.tanggal, d.remain,
        a.kode_distributor as distributor, 
        case when a.kode_distributor = 'AMS' then a.area_ref_code||'-'||a.kode_pelanggan
             else a.kode_pelanggan end as kode_pelanggan,
        a.nama_pelanggan,
        coalesce(b.alamat_pelanggan,a.alamat_pelanggan) as alamat_pelanggan, 
        coalesce(b.alamat_lengkap,a.alamat_lengkap) as alamat_lengkap,
        b.cabang, b.sektor, b.kota,
        a.m_product_id, c.value as productcode, c.name as product, a.product_ref_code, a.product_ref_name,
        coalesce(coalesce(g.mapp_team_id, g.team_id),(
            case when a.m_product_id = 1006385 then 1000673    
                 when f.team_id in (1000677, 1000678, 1000684, 1000673) then 1000673
            else f.team_id end
        )) as team_id,
        pp.pricenet as price_net, 
        pp.pricelist as price_gross,
        pp.pricelist as price,
        coalesce(a.sales_qty,0) as qty,
        --coalesce(pp.pricelist * a.sales_qty, 0) as sales,
        --coalesce(pp.pricelist*a.sales_qty,0)-(((pp.pricelist*a.discount_all)/100)*a.sales_qty) as sales,
        case when a.discount_all > 0 then coalesce(pp.pricelist*a.sales_qty,0)-(((pp.pricelist*a.discount_all)/100)*a.sales_qty)
             else coalesce(pp.pricelist*a.sales_qty,0) end as sales,
        --coalesce(pp.pricelist*a.sales_qty,0) as value,
        case when a.panelprocess='Y' and a.hide = 'N' then a.value else coalesce(pp.pricelist*a.sales_qty,0) end as value,
        coalesce(a.discount_all,0) as discount, a.code_member, a.panelprocess as panel, a.hide
    from sls_sales_distributor a
    left join mstr_distributor_outlet b on (
        (case when a.kode_distributor = 'AMS' then a.area_ref_code||'-'||a.kode_pelanggan
             else a.kode_pelanggan end) = b.kode_pelanggan_full
    )
    left join m_product c on a.m_product_id = c.m_product_id
    left join sls_sales_remain d on a.tahun_periode = d.tahun and a.bulan_periode = d.bulan and a.tanggal = d.tanggal
    left join detail_period e on 1=1 and a.tahun_periode = e.tahun and a.bulan_periode = e.bulan
    left join std_team_product f on e.c_period_id = f.c_period_id and a.m_product_id = f.m_product_id
    left join std_v_distmapp_productcode g on (
        (case when a.kode_distributor = 'AMS' then a.area_ref_code||'-'||a.kode_pelanggan
             else a.kode_pelanggan end) = g.kode_pelanggan and a.m_product_id = g.m_product_id
    )
    inner join m_pricelist pl on (
        pl.m_pricelist_id = case when regexp_like((select name from m_product where m_product_id = a.m_product_id),'ASKES|BPJS') then 1000004 else 1000000 end
    )
    left join (
        select b.c_period_id, b.name, a.year as tahun, b.periodno as bulan
        from c_year a
        left join c_period b on a.c_year_id = b.c_year_id
    ) p on (p.tahun = a.tahun_periode and p.bulan = a.bulan_periode)
    left join m_productprice pp on (
        a.m_product_id = pp.m_product_id 
        and pp.m_pricelist_version_id = getpricelist_version_id4(pl.m_pricelist_id,p.c_period_id)        
    )
    where a.hide = 'N' and a.tahun_periode > 2015 
) a
left join std_version b on a.c_period_id = b.c_period_id
left join std_type c on b.std_version_id = c.std_version_id and upper(c.value) = 'SALES'
left join std_v_distmapp_productcode dp on a.kode_pelanggan = dp.kode_pelanggan and a.team_id = dp.team_id
/


Explain Plan (Toad)
- <ExplainPlan>
- <PlanElement object_ID="0" id="0" operation="SELECT STATEMENT" optimizer="ALL_ROWS" cost="4 M" cardinality="58 K" bytes="73 M" cpu_cost="271 G" io_cost="4 M" time="49 K">
- <PlanElements>
- <PlanElement object_ID="0" id="1" operation="HASH JOIN" option="RIGHT OUTER" remarks="<remark><info type='plan_hash'>1530210552</info></remark>" cost="4 M" cardinality="58 K" bytes="73 M" cpu_cost="271 G" io_cost="4 M" access_predicates=""B"."STD_VERSION_ID"="C"."STD_VERSION_ID"(+)" time="49 K">
- <PlanElements>
  <PlanElement object_ID="1" id="2" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_TYPE" object_type="TABLE" object_instance="27" cost="3" cardinality="1" bytes="19" cpu_cost="53 K" io_cost="3" filter_predicates="UPPER("C"."VALUE"(+))='SALES'" time="1" /> 
- <PlanElement object_ID="0" id="3" operation="NESTED LOOPS" option="OUTER" cost="4 M" cardinality="58 K" bytes="71 M" cpu_cost="271 G" io_cost="4 M" time="49 K">
- <PlanElements>
- <PlanElement object_ID="0" id="4" operation="HASH JOIN" option="RIGHT OUTER" cost="4 M" cardinality="58 K" bytes="71 M" cpu_cost="270 G" io_cost="4 M" temp_space="2 M" access_predicates=""A"."TEAM_ID"="DP"."TEAM_ID"(+) AND "A"."KODE_PELANGGAN"="DP"."KODE_PELANGGAN"(+)" time="49 K">
- <PlanElements>
- <PlanElement object_ID="2" id="5" operation="VIEW" object_owner="COMPIERE" object_name="STD_V_DISTMAPP_PRODUCTCODE" object_type="VIEW" object_instance="29" cost="130" cardinality="15 K" bytes="2 M" cpu_cost="254 M" io_cost="71" time="2">
- <PlanElements>
- <PlanElement object_ID="0" id="6" operation="NESTED LOOPS" option="OUTER" cost="130" cardinality="15 K" bytes="1 M" cpu_cost="254 M" io_cost="71" time="2">
- <PlanElements>
- <PlanElement object_ID="0" id="7" operation="NESTED LOOPS" option="OUTER" cost="102" cardinality="15 K" bytes="1 M" cpu_cost="135 M" io_cost="71" time="2">
- <PlanElements>
- <PlanElement object_ID="0" id="8" operation="HASH JOIN" option="RIGHT OUTER" cost="75" cardinality="15 K" bytes="1 M" cpu_cost="17 M" io_cost="71" access_predicates=""B"."STD_DISTMAPP_OUTLET_ID"="C"."STD_DISTMAPP_OUTLET_ID"(+)" time="1">
- <PlanElements>
  <PlanElement object_ID="3" id="9" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_DISTMAPP_CODE" object_type="TABLE" object_instance="98" cost="42" cardinality="15 K" bytes="312 K" cpu_cost="3 M" io_cost="41" time="1" /> 
- <PlanElement object_ID="0" id="10" operation="HASH JOIN" option="RIGHT OUTER" cost="32" cardinality="8 K" bytes="417 K" cpu_cost="9 M" io_cost="30" access_predicates=""B"."STD_DISTMAPP_OUTLET_ID"="D"."STD_DISTMAPP_OUTLET_ID"(+)" time="1">
- <PlanElements>
  <PlanElement object_ID="4" id="11" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_DISTMAPP_PRODUCT" object_type="TABLE" object_instance="100" cost="8" cardinality="3 K" bytes="68 K" cpu_cost="534 K" io_cost="8" time="1" /> 
- <PlanElement object_ID="0" id="12" operation="HASH JOIN" option="OUTER" cost="23" cardinality="8 K" bytes="200 K" cpu_cost="5 M" io_cost="22" access_predicates=""A"."STD_DISTMAPP_ID"="B"."STD_DISTMAPP_ID"(+)" time="1">
- <PlanElements>
  <PlanElement object_ID="5" id="13" operation="INDEX" option="FULL SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_DIST_PK" object_type="INDEX (UNIQUE)" cardinality="5" bytes="30" /> 
  <PlanElement object_ID="6" id="14" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_DISTMAPP_OUTLET" object_type="TABLE" object_instance="96" cost="22" cardinality="8 K" bytes="152 K" cpu_cost="2 M" io_cost="22" time="1" /> 
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  <PlanElement object_ID="7" id="15" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_ELEMENTVALUE_KEY" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1" bytes="6" cpu_cost="8 K" io_cost="0" access_predicates=""B"."C_ELEMENTVALUE_ID"(+)=CASE "C"."TEAM_ID" WHEN 1000677 THEN 1000673 WHEN 1000678 THEN 1000673 WHEN 1000684 THEN 1000673 ELSE "C"."TEAM_ID" END" time="1" /> 
  </PlanElements>
  </PlanElement>
  <PlanElement object_ID="7" id="16" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_ELEMENTVALUE_KEY" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1" bytes="6" cpu_cost="8 K" io_cost="0" access_predicates=""C"."C_ELEMENTVALUE_ID"(+)=CASE "D"."TEAM_ID" WHEN 1000677 THEN 1000673 WHEN 1000678 THEN 1000673 WHEN 1000684 THEN 1000673 ELSE "D"."TEAM_ID" END" time="1" /> 
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
- <PlanElement object_ID="8" id="17" operation="VIEW" object_owner="COMPIERE" object_instance="1" cost="4 M" cardinality="58 K" bytes="62 M" cpu_cost="270 G" io_cost="4 M" time="49 K">
- <PlanElements>
- <PlanElement object_ID="0" id="18" operation="SORT" option="UNIQUE" cost="4 M" cardinality="58 K" bytes="19 M" cpu_cost="270 G" io_cost="4 M" temp_space="43 M" time="49 K">
- <PlanElements>
- <PlanElement object_ID="0" id="19" operation="HASH JOIN" option="RIGHT OUTER" cost="4 M" cardinality="58 K" bytes="19 M" cpu_cost="270 G" io_cost="4 M" temp_space="8 M" access_predicates=""PP"."M_PRICELIST_VERSION_ID"(+)="GETPRICELIST_VERSION_ID4"("PL"."M_PRICELIST_ID","P"."C_PERIOD_ID") AND "A"."M_PRODUCT_ID"="PP"."M_PRODUCT_ID"(+)" time="49 K">
- <PlanElements>
  <PlanElement object_ID="9" id="20" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="M_PRODUCTPRICE" object_type="TABLE" object_instance="23" cost="373" cardinality="196 K" bytes="5 M" cpu_cost="37 M" io_cost="364" time="5" /> 
- <PlanElement object_ID="10" id="21" operation="VIEW" object_owner="SYS" object_instance="22" cost="4 M" cardinality="57 K" bytes="17 M" cpu_cost="270 G" io_cost="4 M" time="49 K">
- <PlanElements>
- <PlanElement object_ID="0" id="22" operation="HASH JOIN" option="RIGHT OUTER" cost="4 M" cardinality="7 M" bytes="9 G" cpu_cost="270 G" io_cost="4 M" temp_space="11 M" access_predicates=""A"."M_PRODUCT_ID"="F"."M_PRODUCT_ID"(+) AND "E"."C_PERIOD_ID"="F"."C_PERIOD_ID"(+)" time="49 K">
- <PlanElements>
- <PlanElement object_ID="11" id="23" operation="VIEW" object_owner="COMPIERE" object_name="STD_TEAM_PRODUCT" object_type="VIEW" object_instance="11" cost="4 M" cardinality="222 K" bytes="8 M" cpu_cost="266 G" io_cost="4 M" time="46 K">
- <PlanElements>
- <PlanElement object_ID="0" id="24" operation="SORT" option="UNIQUE" cost="4 M" cardinality="222 K" bytes="41 M" cpu_cost="266 G" io_cost="4 M" temp_space="104 M" time="46 K">
- <PlanElements>
- <PlanElement object_ID="0" id="25" operation="HASH JOIN" cost="4 M" cardinality="222 K" bytes="41 M" cpu_cost="266 G" io_cost="4 M" access_predicates=""H"."M_PRODUCT_ID"=COALESCE("G"."M_PRODUCT_ID","F"."M_PRODUCT_ID")" time="46 K">
- <PlanElements>
  <PlanElement object_ID="12" id="26" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="M_PRODUCT" object_type="TABLE" object_instance="56" cost="65" cardinality="1 K" bytes="113 K" cpu_cost="7 M" io_cost="63" filter_predicates=""H"."ISACTIVE"='Y'" time="1" /> 
- <PlanElement object_ID="10" id="27" operation="VIEW" object_owner="SYS" object_instance="55" cost="4 M" cardinality="945 K" bytes="105 M" cpu_cost="266 G" io_cost="4 M" time="46 K">
- <PlanElements>
- <PlanElement object_ID="0" id="28" operation="NESTED LOOPS" option="OUTER" cost="4 M" cardinality="945 K" bytes="131 M" cpu_cost="266 G" io_cost="4 M" time="46 K">
- <PlanElements>
- <PlanElement object_ID="0" id="29" operation="NESTED LOOPS" option="OUTER" cost="19 K" cardinality="945 K" bytes="119 M" cpu_cost="9 G" io_cost="17 K" time="226">
- <PlanElements>
- <PlanElement object_ID="0" id="30" operation="HASH JOIN" cost="36" cardinality="4 K" bytes="453 K" cpu_cost="14 M" io_cost="33" access_predicates=""D"."STD_TEAM_ID"="F"."STD_TEAM_ID"" time="1">
- <PlanElements>
- <PlanElement object_ID="0" id="31" operation="HASH JOIN" option="OUTER" cost="17" cardinality="96" bytes="9 K" cpu_cost="7 M" io_cost="15" access_predicates=""D"."C_ELEMENTVALUE_ID"="E"."C_ELEMENTVALUE_ID"(+)" time="1">
- <PlanElements>
- <PlanElement object_ID="0" id="32" operation="HASH JOIN" cost="10" cardinality="96" bytes="5 K" cpu_cost="5 M" io_cost="9" access_predicates=""C"."STD_TYPE_ID"="D"."STD_TYPE_ID"" time="1">
- <PlanElements>
- <PlanElement object_ID="0" id="33" operation="HASH JOIN" option="OUTER" cost="7" cardinality="25" bytes="775" cpu_cost="2 M" io_cost="6" access_predicates=""A"."STD_VERSION_ID"="C"."STD_VERSION_ID"(+)" time="1">
- <PlanElements>
  <PlanElement object_ID="13" id="34" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_VERSION" object_type="TABLE" object_instance="40" cost="3" cardinality="25" bytes="300" cpu_cost="39 K" io_cost="3" time="1" /> 
  <PlanElement object_ID="1" id="35" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_TYPE" object_type="TABLE" object_instance="43" cost="3" cardinality="1" bytes="19" cpu_cost="53 K" io_cost="3" filter_predicates="UPPER("C"."VALUE"(+))='SALES'" time="1" /> 
  </PlanElements>
  </PlanElement>
  <PlanElement object_ID="14" id="36" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_TEAM" object_type="TABLE" object_instance="45" cost="3" cardinality="96" bytes="2 K" cpu_cost="97 K" io_cost="3" filter_predicates=""D"."ISACTIVE"='Y'" time="1" /> 
  </PlanElements>
  </PlanElement>
  <PlanElement object_ID="15" id="37" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_ELEMENTVALUE" object_type="TABLE" object_instance="47" cost="6" cardinality="916" bytes="38 K" cpu_cost="262 K" io_cost="6" time="1" /> 
  </PlanElements>
  </PlanElement>
  <PlanElement object_ID="16" id="38" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_PRODUCT" object_type="TABLE" object_instance="49" cost="19" cardinality="4 K" bytes="54 K" cpu_cost="5 M" io_cost="18" filter_predicates=""F"."ISACTIVE"='Y'" time="1" /> 
  </PlanElements>
  </PlanElement>
- <PlanElement object_ID="10" id="39" operation="VIEW" object_owner="SYS" object_instance="62" cost="5" cardinality="228" bytes="4 K" cpu_cost="2 M" io_cost="4" time="1">
- <PlanElements>
- <PlanElement object_ID="0" id="40" operation="FILTER" filter_predicates=""A"."C_PERIOD_ID"="B"."C_PERIOD_ID"">
- <PlanElements>
- <PlanElement object_ID="0" id="41" operation="HASH JOIN" option="OUTER" cost="5" cardinality="228" bytes="6 K" cpu_cost="2 M" io_cost="4" access_predicates=""A"."C_YEAR_ID"="B"."C_YEAR_ID"(+)" time="1">
- <PlanElements>
  <PlanElement object_ID="17" id="42" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_YEAR" object_type="TABLE" object_instance="58" cost="2" cardinality="19" bytes="209" cpu_cost="10 K" io_cost="2" time="1" /> 
  <PlanElement object_ID="18" id="43" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_PERIOD" object_type="TABLE" object_instance="59" cost="2" cardinality="228" bytes="3 K" cpu_cost="51 K" io_cost="2" time="1" /> 
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
- <PlanElement object_ID="10" id="44" operation="VIEW" object_owner="SYS" object_instance="68" cost="4" cardinality="1" bytes="13" cpu_cost="271 K" io_cost="4" time="1">
- <PlanElements>
- <PlanElement object_ID="0" id="45" operation="NESTED LOOPS" option="OUTER" cost="4" cardinality="1" bytes="28" cpu_cost="271 K" io_cost="4" time="1">
- <PlanElements>
- <PlanElement object_ID="19" id="46" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="COMPIERE" object_name="CRM_GROUPPRODUCT" object_type="TABLE" object_instance="52" cost="1" cardinality="1" bytes="14" cpu_cost="15 K" io_cost="1" filter_predicates=""A"."ISACTIVE"='Y'" time="1">
- <PlanElements>
  <PlanElement object_ID="20" id="47" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="SYS_C00372625" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1" cpu_cost="7 K" io_cost="0" access_predicates=""F"."M_PRODUCT_ID"="A"."PRODUCTSUBTITUTE_ID"" time="1" /> 
  </PlanElements>
  </PlanElement>
  <PlanElement object_ID="21" id="48" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="CRM_GROUPPRODUCTLINE" object_type="TABLE" object_instance="53" cost="3" cardinality="1" bytes="14" cpu_cost="256 K" io_cost="3" filter_predicates=""B"."ISACTIVE"(+)='Y' AND "A"."CRM_GROUPPRODUCT_ID"="B"."CRM_GROUPPRODUCT_ID"(+)" time="1" /> 
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
- <PlanElement object_ID="0" id="49" operation="HASH JOIN" option="RIGHT OUTER" cost="191 K" cardinality="90 K" bytes="122 M" cpu_cost="4 G" io_cost="190 K" access_predicates=""P"."BULAN"(+)="from$_subquery$_010"."BULAN_PERIODE" AND "from$_subquery$_010"."TAHUN_PERIODE"=TO_NUMBER("P"."TAHUN"(+))" time="2 K">
- <PlanElements>
- <PlanElement object_ID="8" id="50" operation="VIEW" object_owner="COMPIERE" object_instance="18" cost="5" cardinality="228" bytes="7 K" cpu_cost="2 M" io_cost="4" time="1">
- <PlanElements>
- <PlanElement object_ID="0" id="51" operation="HASH JOIN" option="OUTER" cost="5" cardinality="228" bytes="6 K" cpu_cost="2 M" io_cost="4" access_predicates=""A"."C_YEAR_ID"="B"."C_YEAR_ID"(+)" time="1">
- <PlanElements>
  <PlanElement object_ID="17" id="52" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_YEAR" object_type="TABLE" object_instance="19" cost="2" cardinality="19" bytes="209" cpu_cost="10 K" io_cost="2" time="1" /> 
  <PlanElement object_ID="18" id="53" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_PERIOD" object_type="TABLE" object_instance="20" cost="2" cardinality="228" bytes="3 K" cpu_cost="51 K" io_cost="2" time="1" /> 
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
- <PlanElement object_ID="0" id="54" operation="NESTED LOOPS" cost="191 K" cardinality="90 K" bytes="119 M" cpu_cost="4 G" io_cost="190 K" time="2 K">
- <PlanElements>
- <PlanElement object_ID="0" id="55" operation="HASH JOIN" option="RIGHT OUTER" cost="191 K" cardinality="90 K" bytes="119 M" cpu_cost="3 G" io_cost="190 K" temp_space="2 M" access_predicates=""from$_subquery$_010"."QCSJ_C000000000600000"="G"."M_PRODUCT_ID"(+) AND "G"."KODE_PELANGGAN"(+)=CASE "from$_subquery$_010"."QCSJ_C000000000400010" WHEN 'AMS' THEN "from$_subquery$_010"."AREA_REF_CODE"||'-'||"from$_subquery$_010"."QCSJ_C000000000400012" ELSE "from$_subquery$_010"."QCSJ_C000000000400012" END" time="2 K">
- <PlanElements>
- <PlanElement object_ID="2" id="56" operation="VIEW" object_owner="COMPIERE" object_name="STD_V_DISTMAPP_PRODUCTCODE" object_type="VIEW" object_instance="13" cost="103" cardinality="15 K" bytes="1 M" cpu_cost="251 M" io_cost="45" time="2">
- <PlanElements>
- <PlanElement object_ID="0" id="57" operation="NESTED LOOPS" option="OUTER" cost="103" cardinality="15 K" bytes="949 K" cpu_cost="251 M" io_cost="45" time="2">
- <PlanElements>
- <PlanElement object_ID="0" id="58" operation="NESTED LOOPS" option="OUTER" cost="76" cardinality="15 K" bytes="864 K" cpu_cost="133 M" io_cost="45" time="1">
- <PlanElements>
- <PlanElement object_ID="0" id="59" operation="HASH JOIN" option="RIGHT OUTER" cost="48" cardinality="15 K" bytes="779 K" cpu_cost="14 M" io_cost="45" access_predicates=""B"."STD_DISTMAPP_OUTLET_ID"="C"."STD_DISTMAPP_OUTLET_ID"(+)" time="1">
- <PlanElements>
  <PlanElement object_ID="22" id="60" operation="INDEX" option="FAST FULL SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_DISTMAPP_OUTLET_CODE" object_type="INDEX (UNIQUE)" cost="15" cardinality="15 K" bytes="170 K" cpu_cost="420 K" io_cost="15" time="1" /> 
- <PlanElement object_ID="0" id="61" operation="HASH JOIN" option="RIGHT OUTER" cost="32" cardinality="8 K" bytes="344 K" cpu_cost="9 M" io_cost="30" access_predicates=""B"."STD_DISTMAPP_OUTLET_ID"="D"."STD_DISTMAPP_OUTLET_ID"(+)" time="1">
- <PlanElements>
  <PlanElement object_ID="4" id="62" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_DISTMAPP_PRODUCT" object_type="TABLE" object_instance="77" cost="8" cardinality="3 K" bytes="45 K" cpu_cost="534 K" io_cost="8" time="1" /> 
- <PlanElement object_ID="0" id="63" operation="HASH JOIN" option="OUTER" cost="23" cardinality="8 K" bytes="200 K" cpu_cost="5 M" io_cost="22" access_predicates=""A"."STD_DISTMAPP_ID"="B"."STD_DISTMAPP_ID"(+)" time="1">
- <PlanElements>
  <PlanElement object_ID="5" id="64" operation="INDEX" option="FULL SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_DIST_PK" object_type="INDEX (UNIQUE)" cardinality="5" bytes="30" /> 
  <PlanElement object_ID="6" id="65" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_DISTMAPP_OUTLET" object_type="TABLE" object_instance="73" cost="22" cardinality="8 K" bytes="152 K" cpu_cost="2 M" io_cost="22" time="1" /> 
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  <PlanElement object_ID="7" id="66" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_ELEMENTVALUE_KEY" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1" bytes="6" cpu_cost="8 K" io_cost="0" access_predicates=""B"."C_ELEMENTVALUE_ID"(+)=CASE "C"."TEAM_ID" WHEN 1000677 THEN 1000673 WHEN 1000678 THEN 1000673 WHEN 1000684 THEN 1000673 ELSE "C"."TEAM_ID" END" time="1" /> 
  </PlanElements>
  </PlanElement>
  <PlanElement object_ID="7" id="67" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_ELEMENTVALUE_KEY" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1" bytes="6" cpu_cost="8 K" io_cost="0" access_predicates=""C"."C_ELEMENTVALUE_ID"(+)=CASE "D"."TEAM_ID" WHEN 1000677 THEN 1000673 WHEN 1000678 THEN 1000673 WHEN 1000684 THEN 1000673 ELSE "D"."TEAM_ID" END" time="1" /> 
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
- <PlanElement object_ID="10" id="68" operation="VIEW" object_owner="SYS" object_instance="10" cost="185 K" cardinality="90 K" bytes="110 M" cpu_cost="3 G" io_cost="185 K" time="2 K">
- <PlanElements>
- <PlanElement object_ID="0" id="69" operation="NESTED LOOPS" option="OUTER" cost="185 K" cardinality="90 K" bytes="24 M" cpu_cost="3 G" io_cost="185 K" time="2 K">
- <PlanElements>
- <PlanElement object_ID="0" id="70" operation="HASH JOIN" option="RIGHT OUTER" cost="5 K" cardinality="45 K" bytes="12 M" cpu_cost="806 M" io_cost="5 K" access_predicates=""A"."M_PRODUCT_ID"="C"."M_PRODUCT_ID"(+)" time="58">
- <PlanElements>
  <PlanElement object_ID="12" id="71" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="M_PRODUCT" object_type="TABLE" object_instance="5" cost="64" cardinality="6 K" bytes="469 K" cpu_cost="3 M" io_cost="63" time="1" /> 
- <PlanElement object_ID="0" id="72" operation="HASH JOIN" option="RIGHT OUTER" cost="5 K" cardinality="45 K" bytes="8 M" cpu_cost="795 M" io_cost="5 K" access_predicates=""A"."TANGGAL"="D"."TANGGAL"(+) AND "A"."BULAN_PERIODE"="D"."BULAN"(+) AND "A"."TAHUN_PERIODE"="D"."TAHUN"(+)" time="57">
- <PlanElements>
  <PlanElement object_ID="23" id="73" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="SLS_SALES_REMAIN" object_type="TABLE" object_instance="7" cost="5" cardinality="366" bytes="5 K" cpu_cost="363 K" io_cost="5" filter_predicates=""D"."TAHUN"(+)>2015" time="1" /> 
- <PlanElement object_ID="0" id="74" operation="HASH JOIN" option="RIGHT OUTER" cost="5 K" cardinality="45 K" bytes="8 M" cpu_cost="788 M" io_cost="5 K" temp_space="3 M" access_predicates=""B"."KODE_PELANGGAN_FULL"(+)=CASE "A"."KODE_DISTRIBUTOR" WHEN 'AMS' THEN "A"."AREA_REF_CODE"||'-'||"A"."KODE_PELANGGAN" ELSE "A"."KODE_PELANGGAN" END" time="57">
- <PlanElements>
  <PlanElement object_ID="24" id="75" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="MSTR_DISTRIBUTOR_OUTLET" object_type="TABLE" object_instance="3" cost="153" cardinality="41 K" bytes="2 M" cpu_cost="10 M" io_cost="151" time="2" /> 
  <PlanElement object_ID="25" id="76" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="SLS_SALES_DISTRIBUTOR" object_type="TABLE" object_instance="2" cost="4 K" cardinality="45 K" bytes="5 M" cpu_cost="756 M" io_cost="4 K" filter_predicates=""A"."TAHUN_PERIODE">2015 AND "A"."HIDE"='N'" time="50" /> 
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
- <PlanElement object_ID="10" id="77" operation="VIEW" object_owner="SYS" object_instance="38" cost="4" cardinality="2" bytes="26" cpu_cost="38 K" io_cost="4" time="1">
- <PlanElements>
- <PlanElement object_ID="0" id="78" operation="FILTER" filter_predicates=""A"."BULAN_PERIODE"="B"."PERIODNO"">
- <PlanElements>
- <PlanElement object_ID="0" id="79" operation="NESTED LOOPS" option="OUTER" cost="4" cardinality="2" bytes="52" cpu_cost="38 K" io_cost="4" time="1">
- <PlanElements>
  <PlanElement object_ID="17" id="80" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_YEAR" object_type="TABLE" object_instance="34" cost="2" cardinality="1" bytes="11" cpu_cost="16 K" io_cost="2" filter_predicates=""A"."TAHUN_PERIODE"=TO_NUMBER("A"."YEAR")" time="1" /> 
- <PlanElement object_ID="18" id="81" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_PERIOD" object_type="TABLE" object_instance="35" cost="2" cardinality="12" bytes="180" cpu_cost="23 K" io_cost="2" time="1">
- <PlanElements>
  <PlanElement object_ID="26" id="82" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="C_PERIOD_NOUNIQUE" object_type="INDEX (UNIQUE)" search_columns="1" cost="1" cardinality="12" cpu_cost="10 K" io_cost="1" access_predicates=""A"."C_YEAR_ID"="B"."C_YEAR_ID"(+)" time="1" /> 
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
- <PlanElement object_ID="27" id="83" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="M_PRICELIST_KEY" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1" bytes="6" cpu_cost="7 K" io_cost="0" access_predicates=""PL"."M_PRICELIST_ID"=CASE WHEN REGEXP_LIKE ( (SELECT "NAME" FROM "M_PRODUCT" "M_PRODUCT" WHERE "M_PRODUCT_ID"=:B1),N'ASKES|BPJS') THEN 1000004 ELSE 1000000 END" time="1">
- <PlanElements>
- <PlanElement object_ID="12" id="84" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="COMPIERE" object_name="M_PRODUCT" object_type="TABLE" object_instance="17" cost="2" cardinality="1" bytes="61" cpu_cost="23 K" io_cost="2" time="1">
- <PlanElements>
  <PlanElement object_ID="28" id="85" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="M_PRODUCT_KEY" object_type="INDEX (UNIQUE)" search_columns="1" cost="1" cardinality="1" cpu_cost="14 K" io_cost="1" access_predicates=""M_PRODUCT_ID"=:B1" time="1" /> 
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
- <PlanElement object_ID="13" id="86" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_VERSION" object_type="TABLE" object_instance="25" cost="1" cardinality="1" bytes="12" cpu_cost="9 K" io_cost="1" time="1">
- <PlanElements>
  <PlanElement object_ID="29" id="87" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="COMPIERE" object_name="STD_VERSION_PERIOD" object_type="INDEX" search_columns="1" cost="0" cardinality="1" cpu_cost="1 K" io_cost="0" access_predicates=""A"."C_PERIOD_ID"="B"."C_PERIOD_ID"(+)" time="1" /> 
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </PlanElements>
  </PlanElement>
  </ExplainPlan>

Can anyone help? (since im new in oracle database)
should i change distinct clause to another queries like group by?
Re: What should i do for tuning this query [message #655789 is a reply to message #655786] Tue, 13 September 2016 01:01 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your EXPLAIN PLAN is unreadable. Please do it like this:
orclz>
orclz> explain plan for
  2  select empno from emp;

Explained.

orclz> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 179099197

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    14 |    56 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

8 rows selected.

orclz>
Re: What should i do for tuning this query [message #655791 is a reply to message #655789] Tue, 13 September 2016 01:32 Go to previous messageGo to next message
rayrevan
Messages: 21
Registered: August 2016
Junior Member
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1530210552

--------------------------------------------------------------------------------
--------------------------------------------------

| Id  | Operation                                   | Name
 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
--------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |
 | 57948 |    72M|       |  4081K  (2)| 13:36:16 |

|*  1 |  HASH JOIN RIGHT OUTER                      |
 | 57948 |    72M|       |  4081K  (2)| 13:36:16 |

|*  2 |   TABLE ACCESS FULL                         | STD_TYPE
 |     1 |    19 |       |     3   (0)| 00:00:01 |

|   3 |   NESTED LOOPS OUTER                        |
 | 57948 |    71M|       |  4081K  (2)| 13:36:16 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  4 |    HASH JOIN RIGHT OUTER                    |
 | 57948 |    70M|  2440K|  4074K  (2)| 13:34:59 |

|   5 |     VIEW                                    | STD_V_DISTMAPP_PRODUCTCODE
 | 14511 |  2267K|       |   130  (46)| 00:00:02 |

|   6 |      NESTED LOOPS OUTER                     |
 | 14511 |  1218K|       |   130  (46)| 00:00:02 |

|   7 |       NESTED LOOPS OUTER                    |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 | 14511 |  1133K|       |   102  (31)| 00:00:02 |

|*  8 |        HASH JOIN RIGHT OUTER                |
 | 14511 |  1048K|       |    75   (6)| 00:00:01 |

|   9 |         TABLE ACCESS FULL                   | STD_DISTMAPP_CODE
 | 14516 |   311K|       |    42   (3)| 00:00:01 |

|* 10 |         HASH JOIN RIGHT OUTER               |
 |  8202 |   416K|       |    32   (7)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  11 |          TABLE ACCESS FULL                  | STD_DISTMAPP_PRODUCT
 |  2574 | 69498 |       |     8   (0)| 00:00:01 |

|* 12 |          HASH JOIN OUTER                    |
 |  8202 |   200K|       |    23   (5)| 00:00:01 |

|  13 |           INDEX FULL SCAN                   | STD_DIST_PK
 |     5 |    30 |       |            |          |

|  14 |           TABLE ACCESS FULL                 | STD_DISTMAPP_OUTLET
 |  8202 |   152K|       |    22   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 15 |        INDEX UNIQUE SCAN                    | C_ELEMENTVALUE_KEY
 |     1 |     6 |       |     0   (0)| 00:00:01 |

|* 16 |       INDEX UNIQUE SCAN                     | C_ELEMENTVALUE_KEY
 |     1 |     6 |       |     0   (0)| 00:00:01 |

|  17 |     VIEW                                    |
 | 57948 |    61M|       |  4071K  (2)| 13:34:19 |

|  18 |      SORT UNIQUE                            |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 | 57948 |    18M|    41M|  4071K  (2)| 13:34:19 |

|* 19 |       HASH JOIN RIGHT OUTER                 |
 | 57948 |    18M|  7840K|  4067K  (2)| 13:33:28 |

|  20 |        TABLE ACCESS FULL                    | M_PRODUCTPRICE
 |   195K|  5545K|       |   373   (3)| 00:00:05 |

|  21 |        VIEW                                 |
 | 57493 |    17M|       |  4065K  (2)| 13:33:08 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 22 |         HASH JOIN RIGHT OUTER               |
 |  6784K|  9465M|    10M|  4065K  (2)| 13:33:08 |

|  23 |          VIEW                               | STD_TEAM_PRODUCT
 |   222K|  8473K|       |  3867K  (2)| 12:53:36 |

|  24 |           SORT UNIQUE                       |
 |   222K|    41M|    99M|  3867K  (2)| 12:53:36 |

|* 25 |            HASH JOIN                        |
 |   222K|    41M|       |  3858K  (2)| 12:51:42 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 26 |             TABLE ACCESS FULL               | M_PRODUCT
 |  1488 |   113K|       |    65   (4)| 00:00:01 |

|  27 |             VIEW                            |
 |   944K|   105M|       |  3858K  (2)| 12:51:41 |

|  28 |              NESTED LOOPS OUTER             |
 |   944K|   130M|       |  3858K  (2)| 12:51:41 |

|  29 |               NESTED LOOPS OUTER            |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 |   944K|   118M|       | 18772  (12)| 00:03:46 |

|* 30 |                HASH JOIN                    |
 |  4145 |   453K|       |    36   (9)| 00:00:01 |

|* 31 |                 HASH JOIN OUTER             |
 |    96 |  9408 |       |    17  (12)| 00:00:01 |

|* 32 |                  HASH JOIN                  |
 |    96 |  5280 |       |    10  (10)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 33 |                   HASH JOIN OUTER           |
 |    25 |   775 |       |     7  (15)| 00:00:01 |

|  34 |                    TABLE ACCESS FULL        | STD_VERSION
 |    25 |   300 |       |     3   (0)| 00:00:01 |

|* 35 |                    TABLE ACCESS FULL        | STD_TYPE
 |     1 |    19 |       |     3   (0)| 00:00:01 |

|* 36 |                   TABLE ACCESS FULL         | STD_TEAM
 |    96 |  2304 |       |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|  37 |                  TABLE ACCESS FULL          | C_ELEMENTVALUE
 |   916 | 39388 |       |     6   (0)| 00:00:01 |

|* 38 |                 TABLE ACCESS FULL           | STD_PRODUCT
 |  3972 | 55608 |       |    19   (6)| 00:00:01 |

|  39 |                VIEW                         |
 |   228 |  4560 |       |     5  (20)| 00:00:01 |

|* 40 |                 FILTER                      |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 |       |       |       |            |          |

|* 41 |                  HASH JOIN OUTER            |
 |   228 |  5928 |       |     5  (20)| 00:00:01 |

|  42 |                   TABLE ACCESS FULL         | C_YEAR
 |    19 |   209 |       |     2   (0)| 00:00:01 |

|  43 |                   TABLE ACCESS FULL         | C_PERIOD
 |   228 |  3420 |       |     2   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  44 |               VIEW                          |
 |     1 |    13 |       |     4   (0)| 00:00:01 |

|  45 |                NESTED LOOPS OUTER           |
 |     1 |    28 |       |     4   (0)| 00:00:01 |

|* 46 |                 TABLE ACCESS BY INDEX ROWID | CRM_GROUPPRODUCT
 |     1 |    14 |       |     1   (0)| 00:00:01 |

|* 47 |                  INDEX UNIQUE SCAN          | SYS_C00372625
 |     1 |       |       |     0   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 48 |                 TABLE ACCESS FULL           | CRM_GROUPPRODUCTLINE
 |     1 |    14 |       |     3   (0)| 00:00:01 |

|* 49 |          HASH JOIN RIGHT OUTER              |
 | 89950 |   122M|       |   190K  (1)| 00:38:12 |

|  50 |           VIEW                              |
 |   228 |  7524 |       |     5  (20)| 00:00:01 |

|* 51 |            HASH JOIN OUTER                  |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 |   228 |  5928 |       |     5  (20)| 00:00:01 |

|  52 |             TABLE ACCESS FULL               | C_YEAR
 |    19 |   209 |       |     2   (0)| 00:00:01 |

|  53 |             TABLE ACCESS FULL               | C_PERIOD
 |   228 |  3420 |       |     2   (0)| 00:00:01 |

|  54 |           NESTED LOOPS                      |
 | 89950 |   119M|       |   190K  (1)| 00:38:12 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 55 |            HASH JOIN RIGHT OUTER            |
 | 89950 |   118M|  1664K|   190K  (1)| 00:38:10 |

|  56 |             VIEW                            | STD_V_DISTMAPP_PRODUCTCODE
 | 14511 |  1487K|       |   103  (57)| 00:00:02 |

|  57 |              NESTED LOOPS OUTER             |
 | 14511 |   949K|       |   103  (57)| 00:00:02 |

|  58 |               NESTED LOOPS OUTER            |
 | 14511 |   864K|       |    76  (41)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 59 |                HASH JOIN RIGHT OUTER        |
 | 14511 |   779K|       |    48   (7)| 00:00:01 |

|  60 |                 INDEX FAST FULL SCAN        | STD_DISTMAPP_OUTLET_CODE
 | 14516 |   170K|       |    15   (0)| 00:00:01 |

|* 61 |                 HASH JOIN RIGHT OUTER       |
 |  8202 |   344K|       |    32   (7)| 00:00:01 |

|  62 |                  TABLE ACCESS FULL          | STD_DISTMAPP_PRODUCT

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 |  2574 | 46332 |       |     8   (0)| 00:00:01 |

|* 63 |                  HASH JOIN OUTER            |
 |  8202 |   200K|       |    23   (5)| 00:00:01 |

|  64 |                   INDEX FULL SCAN           | STD_DIST_PK
 |     5 |    30 |       |            |          |

|  65 |                   TABLE ACCESS FULL         | STD_DISTMAPP_OUTLET
 |  8202 |   152K|       |    22   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 66 |                INDEX UNIQUE SCAN            | C_ELEMENTVALUE_KEY
 |     1 |     6 |       |     0   (0)| 00:00:01 |

|* 67 |               INDEX UNIQUE SCAN             | C_ELEMENTVALUE_KEY
 |     1 |     6 |       |     0   (0)| 00:00:01 |

|  68 |             VIEW                            |
 | 89950 |   109M|       |   185K  (1)| 00:37:02 |

|  69 |              NESTED LOOPS OUTER             |
 | 89950 |    24M|       |   185K  (1)| 00:37:02 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 70 |               HASH JOIN RIGHT OUTER         |
 | 44975 |    11M|       |  4794   (4)| 00:00:58 |

|  71 |                TABLE ACCESS FULL            | M_PRODUCT
 |  6320 |   469K|       |    64   (2)| 00:00:01 |

|* 72 |                HASH JOIN RIGHT OUTER        |
 | 44975 |  8564K|       |  4729   (4)| 00:00:57 |

|* 73 |                 TABLE ACCESS FULL           | SLS_SALES_REMAIN

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 |   366 |  4758 |       |     5   (0)| 00:00:01 |

|* 74 |                 HASH JOIN RIGHT OUTER       |
 | 44975 |  7993K|  2744K|  4722   (4)| 00:00:57 |

|  75 |                  TABLE ACCESS FULL          | MSTR_DISTRIBUTOR_OUTLET
 | 40628 |  2261K|       |   153   (2)| 00:00:02 |

|* 76 |                  TABLE ACCESS FULL          | SLS_SALES_DISTRIBUTOR
 | 44975 |  5490K|       |  4139   (5)| 00:00:50 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  77 |               VIEW                          |
 |     2 |    26 |       |     4   (0)| 00:00:01 |

|* 78 |                FILTER                       |
 |       |       |       |            |          |

|  79 |                 NESTED LOOPS OUTER          |
 |     2 |    52 |       |     4   (0)| 00:00:01 |

|* 80 |                  TABLE ACCESS FULL          | C_YEAR
 |     1 |    11 |       |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|  81 |                  TABLE ACCESS BY INDEX ROWID| C_PERIOD
 |    12 |   180 |       |     2   (0)| 00:00:01 |

|* 82 |                   INDEX RANGE SCAN          | C_PERIOD_NOUNIQUE
 |    12 |       |       |     1   (0)| 00:00:01 |

|* 83 |            INDEX UNIQUE SCAN                | M_PRICELIST_KEY
 |     1 |     6 |       |     0   (0)| 00:00:01 |

|  84 |             TABLE ACCESS BY INDEX ROWID     | M_PRODUCT

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 |     1 |    61 |       |     2   (0)| 00:00:01 |

|* 85 |              INDEX UNIQUE SCAN              | M_PRODUCT_KEY
 |     1 |       |       |     1   (0)| 00:00:01 |

|  86 |    TABLE ACCESS BY INDEX ROWID              | STD_VERSION
 |     1 |    12 |       |     1   (0)| 00:00:01 |

|* 87 |     INDEX RANGE SCAN                        | STD_VERSION_PERIOD
 |     1 |       |       |     0   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."STD_VERSION_ID"="C"."STD_VERSION_ID"(+))
   2 - filter(UPPER("C"."VALUE"(+))='SALES')
   4 - access("A"."TEAM_ID"="DP"."TEAM_ID"(+) AND "A"."KODE_PELANGGAN"="DP"."KOD
E_PELANGGAN"(+))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   8 - access("B"."STD_DISTMAPP_OUTLET_ID"="C"."STD_DISTMAPP_OUTLET_ID"(+))
  10 - access("B"."STD_DISTMAPP_OUTLET_ID"="D"."STD_DISTMAPP_OUTLET_ID"(+))
  12 - access("A"."STD_DISTMAPP_ID"="B"."STD_DISTMAPP_ID"(+))
  15 - access("B"."C_ELEMENTVALUE_ID"(+)=CASE "C"."TEAM_ID" WHEN 1000677 THEN 10
00673 WHEN 1000678 THEN 1000673 WHEN

              1000684 THEN 1000673 ELSE "C"."TEAM_ID" END )
  16 - access("C"."C_ELEMENTVALUE_ID"(+)=CASE "D"."TEAM_ID" WHEN 1000677 THEN 10
00673 WHEN 1000678 THEN 1000673 WHEN


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
              1000684 THEN 1000673 ELSE "D"."TEAM_ID" END )
  19 - access("PP"."M_PRICELIST_VERSION_ID"(+)="GETPRICELIST_VERSION_ID4"("PL"."
M_PRICELIST_ID","P"."C_PERIOD_ID") AND

              "A"."M_PRODUCT_ID"="PP"."M_PRODUCT_ID"(+))
  22 - access("A"."M_PRODUCT_ID"="F"."M_PRODUCT_ID"(+) AND "E"."C_PERIOD_ID"="F"
."C_PERIOD_ID"(+))

  25 - access("H"."M_PRODUCT_ID"=COALESCE("G"."M_PRODUCT_ID","F"."M_PRODUCT_ID")
)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  26 - filter("H"."ISACTIVE"='Y')
  30 - access("D"."STD_TEAM_ID"="F"."STD_TEAM_ID")
  31 - access("D"."C_ELEMENTVALUE_ID"="E"."C_ELEMENTVALUE_ID"(+))
  32 - access("C"."STD_TYPE_ID"="D"."STD_TYPE_ID")
  33 - access("A"."STD_VERSION_ID"="C"."STD_VERSION_ID"(+))
  35 - filter(UPPER("C"."VALUE"(+))='SALES')
  36 - filter("D"."ISACTIVE"='Y')
  38 - filter("F"."ISACTIVE"='Y')
  40 - filter("A"."C_PERIOD_ID"="B"."C_PERIOD_ID")
  41 - access("A"."C_YEAR_ID"="B"."C_YEAR_ID"(+))
  46 - filter("A"."ISACTIVE"='Y')

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  47 - access("F"."M_PRODUCT_ID"="A"."PRODUCTSUBTITUTE_ID")
  48 - filter("B"."ISACTIVE"(+)='Y' AND "A"."CRM_GROUPPRODUCT_ID"="B"."CRM_GROUP
PRODUCT_ID"(+))

  49 - access("P"."BULAN"(+)="from$_subquery$_010"."BULAN_PERIODE" AND
              "from$_subquery$_010"."TAHUN_PERIODE"=TO_NUMBER("P"."TAHUN"(+)))
  51 - access("A"."C_YEAR_ID"="B"."C_YEAR_ID"(+))
  55 - access("from$_subquery$_010"."QCSJ_C000000000600000"="G"."M_PRODUCT_ID"(+
) AND "G"."KODE_PELANGGAN"(+)=CASE

              "from$_subquery$_010"."QCSJ_C000000000400010" WHEN 'AMS' THEN

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
              "from$_subquery$_010"."AREA_REF_CODE"||'-'||"from$_subquery$_010".
"QCSJ_C000000000400012" ELSE

              "from$_subquery$_010"."QCSJ_C000000000400012" END )
  59 - access("B"."STD_DISTMAPP_OUTLET_ID"="C"."STD_DISTMAPP_OUTLET_ID"(+))
  61 - access("B"."STD_DISTMAPP_OUTLET_ID"="D"."STD_DISTMAPP_OUTLET_ID"(+))
  63 - access("A"."STD_DISTMAPP_ID"="B"."STD_DISTMAPP_ID"(+))
  66 - access("B"."C_ELEMENTVALUE_ID"(+)=CASE "C"."TEAM_ID" WHEN 1000677 THEN 10
00673 WHEN 1000678 THEN 1000673 WHEN

              1000684 THEN 1000673 ELSE "C"."TEAM_ID" END )

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  67 - access("C"."C_ELEMENTVALUE_ID"(+)=CASE "D"."TEAM_ID" WHEN 1000677 THEN 10
00673 WHEN 1000678 THEN 1000673 WHEN

              1000684 THEN 1000673 ELSE "D"."TEAM_ID" END )
  70 - access("A"."M_PRODUCT_ID"="C"."M_PRODUCT_ID"(+))
  72 - access("A"."TANGGAL"="D"."TANGGAL"(+) AND "A"."BULAN_PERIODE"="D"."BULAN"
(+) AND

              "A"."TAHUN_PERIODE"="D"."TAHUN"(+))
  73 - filter("D"."TAHUN"(+)>2015)
  74 - access("B"."KODE_PELANGGAN_FULL"(+)=CASE "A"."KODE_DISTRIBUTOR" WHEN 'AMS

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
' THEN

              "A"."AREA_REF_CODE"||'-'||"A"."KODE_PELANGGAN" ELSE "A"."KODE_PELA
NGGAN" END )

  76 - filter("A"."TAHUN_PERIODE">2015 AND "A"."HIDE"='N')
  78 - filter("A"."BULAN_PERIODE"="B"."PERIODNO")
  80 - filter("A"."TAHUN_PERIODE"=TO_NUMBER("A"."YEAR"))
  82 - access("A"."C_YEAR_ID"="B"."C_YEAR_ID"(+))
  83 - access("PL"."M_PRICELIST_ID"=CASE  WHEN  REGEXP_LIKE ( (SELECT "NAME" FRO
M "M_PRODUCT" "M_PRODUCT" WHERE

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

              "M_PRODUCT_ID"=:B1),N'ASKES|BPJS') THEN 1000004 ELSE 1000000 END )
  85 - access("M_PRODUCT_ID"=:B1)
  87 - access("A"."C_PERIOD_ID"="B"."C_PERIOD_ID"(+))

153 rows selected.
Re: What should i do for tuning this query [message #655792 is a reply to message #655791] Tue, 13 September 2016 01:34 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Come on, man. Do you think that is any better? Set your linesize to at least 200 and your pagesize also. THen post something readable.
Re: What should i do for tuning this query [message #655795 is a reply to message #655792] Tue, 13 September 2016 02:03 Go to previous messageGo to next message
rayrevan
Messages: 21
Registered: August 2016
Junior Member
Sorry for bad code

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1530210552

--------------------------------------------------------------------------------
--------------------------------------------------

| Id  | Operation                                   | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            | 	                        | 57948 |    72M|       |  4081K  (2)| 13:36:16 |

|*  1 |  HASH JOIN RIGHT OUTER                      |                           | 57948 |    72M|       |  4081K  (2)| 13:36:16 |

|*  2 |   TABLE ACCESS FULL                         | STD_TYPE                  |     1 |    19 |       |     3   (0)| 00:00:01 |

|   3 |   NESTED LOOPS OUTER                        |                           | 57948 |    71M|       |  4081K  (2)| 13:36:16 |

|*  4 |    HASH JOIN RIGHT OUTER                    |                           | 57948 |    70M|  2440K|  4074K  (2)| 13:34:59 |

|   5 |     VIEW                                    | STD_V_DISTMAPP_PRODUCTCODE| 14511 |  2267K|       |   130  (46)| 00:00:02 |

|   6 |      NESTED LOOPS OUTER                     |                           | 14511 |  1218K|       |   130  (46)| 00:00:02 |

|   7 |       NESTED LOOPS OUTER                    |                           | 14511 |  1133K|       |   102  (31)| 00:00:02 |

|*  8 |        HASH JOIN RIGHT OUTER                |                           | 14511 |  1048K|       |    75   (6)| 00:00:01 |

|   9 |         TABLE ACCESS FULL                   | STD_DISTMAPP_CODE         | 14516 |   311K|       |    42   (3)| 00:00:01 |

|* 10 |         HASH JOIN RIGHT OUTER               |                           |  8202 |   416K|       |    32   (7)| 00:00:01 |

|  11 |          TABLE ACCESS FULL                  | STD_DISTMAPP_PRODUCT      |  2574 | 69498 |       |     8   (0)| 00:00:01 |

|* 12 |          HASH JOIN OUTER                    |                           |  8202 |   200K|       |    23   (5)| 00:00:01 |

|  13 |           INDEX FULL SCAN                   | STD_DIST_PK         	|     5 |    30 |       |            |          |

|  14 |           TABLE ACCESS FULL                 | STD_DISTMAPP_OUTLET 	|  8202 |   152K|       |    22   (0)| 00:00:01 |

|* 15 |        INDEX UNIQUE SCAN                    | C_ELEMENTVALUE_KEY        |     1 |     6 |       |     0   (0)| 00:00:01 |

|* 16 |       INDEX UNIQUE SCAN                     | C_ELEMENTVALUE_KEY        |     1 |     6 |       |     0   (0)| 00:00:01 |

|  17 |     VIEW                                    |                           | 57948 |    61M|       |  4071K  (2)| 13:34:19 |

|  18 |      SORT UNIQUE                            |                           | 57948 |    18M|    41M|  4071K  (2)| 13:34:19 |

|* 19 |       HASH JOIN RIGHT OUTER                 |                           | 57948 |    18M|  7840K|  4067K  (2)| 13:33:28 |

|  20 |        TABLE ACCESS FULL                    | M_PRODUCTPRICE            |   195K|  5545K|       |   373   (3)| 00:00:05 |

|  21 |        VIEW                                 |                           | 57493 |    17M|       |  4065K  (2)| 13:33:08 |

|* 22 |         HASH JOIN RIGHT OUTER               |                           |  6784K|  9465M|    10M|  4065K  (2)| 13:33:08 |

|  23 |          VIEW                               | STD_TEAM_PRODUCT          |   222K|  8473K|       |  3867K  (2)| 12:53:36 |
 
|  24 |           SORT UNIQUE                       |                           |   222K|    41M|    99M|  3867K  (2)| 12:53:36 |
 
|* 25 |            HASH JOIN                        |                           |   222K|    41M|       |  3858K  (2)| 12:51:42 |

|* 26 |             TABLE ACCESS FULL               | M_PRODUCT                 |  1488 |   113K|       |    65   (4)| 00:00:01 |

|  27 |             VIEW                            |                           |   944K|   105M|       |  3858K  (2)| 12:51:41 |

|  28 |              NESTED LOOPS OUTER             |                           |   944K|   130M|       |  3858K  (2)| 12:51:41 |

|  29 |               NESTED LOOPS OUTER            |                           |   944K|   118M|       | 18772  (12)| 00:03:46 |

|* 30 |                HASH JOIN                    |                           |  4145 |   453K|       |    36   (9)| 00:00:01 |

|* 31 |                 HASH JOIN OUTER             |                           |    96 |  9408 |       |    17  (12)| 00:00:01 |

|* 32 |                  HASH JOIN                  |                           |    96 |  5280 |       |    10  (10)| 00:00:01 |

|* 33 |                   HASH JOIN OUTER           |                           |    25 |   775 |       |     7  (15)| 00:00:01 |

|  34 |                    TABLE ACCESS FULL        | STD_VERSION               |    25 |   300 |       |     3   (0)| 00:00:01 |

|* 35 |                    TABLE ACCESS FULL        | STD_TYPE                  |     1 |    19 |       |     3   (0)| 00:00:01 |

|* 36 |                   TABLE ACCESS FULL         | STD_TEAM                  |    96 |  2304 |       |     3   (0)| 00:00:01 |

|  37 |                  TABLE ACCESS FULL          | C_ELEMENTVALUE            |   916 | 39388 |       |     6   (0)| 00:00:01 |

|* 38 |                 TABLE ACCESS FULL           | STD_PRODUCT               |  3972 | 55608 |       |    19   (6)| 00:00:01 |

|  39 |                VIEW                         |                           |   228 |  4560 |       |     5  (20)| 00:00:01 |

|* 40 |                 FILTER                      |                           |       |       |       |            |          |

|* 41 |                  HASH JOIN OUTER            |                           |   228 |  5928 |       |     5  (20)| 00:00:01 |

|  42 |                   TABLE ACCESS FULL         | C_YEAR                    |    19 |   209 |       |     2   (0)| 00:00:01 |

|  43 |                   TABLE ACCESS FULL         | C_PERIOD                  |   228 |  3420 |       |     2   (0)| 00:00:01 |

|  44 |               VIEW                          |                           |     1 |    13 |       |     4   (0)| 00:00:01 |

|  45 |                NESTED LOOPS OUTER           |                           |     1 |    28 |       |     4   (0)| 00:00:01 |

|* 46 |                 TABLE ACCESS BY INDEX ROWID | CRM_GROUPPRODUCT          |     1 |    14 |       |     1   (0)| 00:00:01 |

|* 47 |                  INDEX UNIQUE SCAN          | SYS_C00372625             |     1 |       |       |     0   (0)| 00:00:01 |

|* 48 |                 TABLE ACCESS FULL           | CRM_GROUPPRODUCTLINE      |     1 |    14 |       |     3   (0)| 00:00:01 |

|* 49 |          HASH JOIN RIGHT OUTER              |                           | 89950 |   122M|       |   190K  (1)| 00:38:12 |

|  50 |           VIEW                              |                           |   228 |  7524 |       |     5  (20)| 00:00:01 |

|* 51 |            HASH JOIN OUTER                  |                           |   228 |  5928 |       |     5  (20)| 00:00:01 |

|  52 |             TABLE ACCESS FULL               | C_YEAR                    |    19 |   209 |       |     2   (0)| 00:00:01 |

|  53 |             TABLE ACCESS FULL               | C_PERIOD                  |   228 |  3420 |       |     2   (0)| 00:00:01 |

|  54 |           NESTED LOOPS                      |                           | 89950 |   119M|       |   190K  (1)| 00:38:12 |

|* 55 |            HASH JOIN RIGHT OUTER            |                           | 89950 |   118M|  1664K|   190K  (1)| 00:38:10 |

|  56 |             VIEW                            | STD_V_DISTMAPP_PRODUCTCODE| 14511 |  1487K|       |   103  (57)| 00:00:02 |

|  57 |              NESTED LOOPS OUTER             |                           | 14511 |   949K|       |   103  (57)| 00:00:02 |

|  58 |               NESTED LOOPS OUTER            |                           | 14511 |   864K|       |    76  (41)| 00:00:01 |

|* 59 |                HASH JOIN RIGHT OUTER        |                           | 14511 |   779K|       |    48   (7)| 00:00:01 |

|  60 |                 INDEX FAST FULL SCAN        | STD_DISTMAPP_OUTLET_CODE  | 14516 |   170K|       |    15   (0)| 00:00:01 |

|* 61 |                 HASH JOIN RIGHT OUTER       |                           |  8202 |   344K|       |    32   (7)| 00:00:01 |

|  62 |                  TABLE ACCESS FULL          | STD_DISTMAPP_PRODUCT      |  2574 | 46332 |       |     8   (0)| 00:00:01 |

|* 63 |                  HASH JOIN OUTER            |                           |  8202 |   200K|       |    23   (5)| 00:00:01 |

|  64 |                   INDEX FULL SCAN           | STD_DIST_PK               |     5 |    30 |       |            |          |

|  65 |                   TABLE ACCESS FULL         | STD_DISTMAPP_OUTLET       |  8202 |   152K|       |    22   (0)| 00:00:01 |

|* 66 |                INDEX UNIQUE SCAN            | C_ELEMENTVALUE_KEY        |     1 |     6 |       |     0   (0)| 00:00:01 |

|* 67 |               INDEX UNIQUE SCAN             | C_ELEMENTVALUE_KEY        |     1 |     6 |       |     0   (0)| 00:00:01 |

|  68 |             VIEW                            |                           | 89950 |   109M|       |   185K  (1)| 00:37:02 |

|  69 |              NESTED LOOPS OUTER             |                           | 89950 |    24M|       |   185K  (1)| 00:37:02 |

|* 70 |               HASH JOIN RIGHT OUTER         |                           | 44975 |    11M|       |  4794   (4)| 00:00:58 |

|  71 |                TABLE ACCESS FULL            | M_PRODUCT                 |  6320 |   469K|       |    64   (2)| 00:00:01 |

|* 72 |                HASH JOIN RIGHT OUTER        |                           | 44975 |  8564K|       |  4729   (4)| 00:00:57 |

|* 73 |                 TABLE ACCESS FULL           | SLS_SALES_REMAIN          |   366 |  4758 |       |     5   (0)| 00:00:01 |

|* 74 |                 HASH JOIN RIGHT OUTER       |                           | 44975 |  7993K|  2744K|  4722   (4)| 00:00:57 |

|  75 |                  TABLE ACCESS FULL          | MSTR_DISTRIBUTOR_OUTLET   | 40628 |  2261K|       |   153   (2)| 00:00:02 |

|* 76 |                  TABLE ACCESS FULL          | SLS_SALES_DISTRIBUTOR     | 44975 |  5490K|       |  4139   (5)| 00:00:50 |

|  77 |               VIEW                          |                           |     2 |    26 |       |     4   (0)| 00:00:01 |

|* 78 |                FILTER                       |                           |       |       |       |            |          |
    
|  79 |                 NESTED LOOPS OUTER          |                           |     2 |    52 |       |     4   (0)| 00:00:01 |

|* 80 |                  TABLE ACCESS FULL          | C_YEAR                    |     1 |    11 |       |     2   (0)| 00:00:01 |

|  81 |                  TABLE ACCESS BY INDEX ROWID| C_PERIOD                  |    12 |   180 |       |     2   (0)| 00:00:01 |

|* 82 |                   INDEX RANGE SCAN          | C_PERIOD_NOUNIQUE         |    12 |       |       |     1   (0)| 00:00:01 |

|* 83 |            INDEX UNIQUE SCAN                | M_PRICELIST_KEY           |     1 |     6 |       |     0   (0)| 00:00:01 |

|  84 |             TABLE ACCESS BY INDEX ROWID     | M_PRODUCT                 |     1 |    61 |       |     2   (0)| 00:00:01 |

|* 85 |              INDEX UNIQUE SCAN              | M_PRODUCT_KEY             |     1 |       |       |     1   (0)| 00:00:01 |

|  86 |    TABLE ACCESS BY INDEX ROWID              | STD_VERSION               |     1 |    12 |       |     1   (0)| 00:00:01 |

|* 87 |     INDEX RANGE SCAN                        | STD_VERSION_PERIOD        |     1 |       |       |     0   (0)| 00:00:01 |
Re: What should i do for tuning this query [message #655796 is a reply to message #655795] Tue, 13 September 2016 02:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This is hopeless. Your linesize is still too narrow. And you have chopped off the predicate information.
Re: What should i do for tuning this query [message #655799 is a reply to message #655796] Tue, 13 September 2016 02:21 Go to previous messageGo to next message
rayrevan
Messages: 21
Registered: August 2016
Junior Member
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1530210552

----------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                            | 57948 |    72M|       |  4081K  (2)| 13:36:16 |
|*  1 |  HASH JOIN RIGHT OUTER                      |                            | 57948 |    72M|       |  4081K  (2)| 13:36:16 |
|*  2 |   TABLE ACCESS FULL                         | STD_TYPE                   |     1 |    19 |       |     3   (0)| 00:00:01 |
|   3 |   NESTED LOOPS OUTER                        |                            | 57948 |    71M|       |  4081K  (2)| 13:36:16 |
|*  4 |    HASH JOIN RIGHT OUTER                    |                            | 57948 |    70M|  2440K|  4074K  (2)| 13:34:59 |
|   5 |     VIEW                                    | STD_V_DISTMAPP_PRODUCTCODE | 14511 |  2267K|       |   130  (46)| 00:00:02 |
|   6 |      NESTED LOOPS OUTER                     |                            | 14511 |  1218K|       |   130  (46)| 00:00:02 |
|   7 |       NESTED LOOPS OUTER                    |                            | 14511 |  1133K|       |   102  (31)| 00:00:02 |
|*  8 |        HASH JOIN RIGHT OUTER                |                            | 14511 |  1048K|       |    75   (6)| 00:00:01 |
|   9 |         TABLE ACCESS FULL                   | STD_DISTMAPP_CODE          | 14516 |   311K|       |    42   (3)| 00:00:01 |
|* 10 |         HASH JOIN RIGHT OUTER               |                            |  8202 |   416K|       |    32   (7)| 00:00:01 |
|  11 |          TABLE ACCESS FULL                  | STD_DISTMAPP_PRODUCT       |  2574 | 69498 |       |     8   (0)| 00:00:01 
|* 12 |          HASH JOIN OUTER                    |                            |  8202 |   200K|       |    23   (5)| 00:00:01 |
|  13 |           INDEX FULL SCAN                   | STD_DIST_PK                |     5 |    30 |       |            |          |
|  14 |           TABLE ACCESS FULL                 | STD_DISTMAPP_OUTLET        |  8202 |   152K|       |    22   (0)| 00:00:01 
|* 15 |        INDEX UNIQUE SCAN                    | C_ELEMENTVALUE_KEY         |     1 |     6 |       |     0   (0)| 00:00:
|* 16 |       INDEX UNIQUE SCAN                     | C_ELEMENTVALUE_KEY         |     1 |     6 |       |     0   (0)| 00:00:
|  17 |     VIEW                                    |                            | 57948 |    61M|       |  4071K  (2)| 13:34:19 |
|  18 |      SORT UNIQUE                            |                            | 57948 |    18M|    41M|  4071K  (2)| 13:34:19 |
|* 19 |       HASH JOIN RIGHT OUTER                 |                            | 57948 |    18M|  7840K|  4067K  (2)| 13:33:28 |
|  20 |        TABLE ACCESS FULL                    | M_PRODUCTPRICE             |   195K|  5545K|       |   373   (3)| 00:00:05 
|  21 |        VIEW                                 |                            | 57493 |    17M|       |  4065K  (2)| 13:33:08 |
|* 22 |         HASH JOIN RIGHT OUTER               |                            |  6784K|  9465M|    10M|  4065K  (2)| 13:33:08 |
|  23 |          VIEW                               | STD_TEAM_PRODUCT           |   222K|  8473K|       |  3867K  (2)| 12:53:36 |
|  24 |           SORT UNIQUE                       |                            |   222K|    41M|    99M|  3867K  (2)| 12:53:36 |
|* 25 |            HASH JOIN                        |                            |   222K|    41M|       |  3858K  (2)| 12:51:42 |
|* 26 |             TABLE ACCESS FULL               | M_PRODUCT                  |  1488 |   113K|       |    65   (4)| 00:00:01 |
|  27 |             VIEW                            |                            |   944K|   105M|       |  3858K  (2)| 12:51:41 |
|  28 |              NESTED LOOPS OUTER             |                            |   944K|   130M|       |  3858K  (2)| 12:51:41 |
|  29 |               NESTED LOOPS OUTER            |                            |   944K|   118M|       | 18772  (12)| 00:03:46 |
|* 30 |                HASH JOIN                    |                            |  4145 |   453K|       |    36   (9)| 00:00:01 |
|* 31 |                 HASH JOIN OUTER             |                            |    96 |  9408 |       |    17  (12)| 00:00:01 |
|* 32 |                  HASH JOIN                  |                            |    96 |  5280 |       |    10  (10)| 00:00:01 |
|* 33 |                   HASH JOIN OUTER           |                            |    25 |   775 |       |     7  (15)| 00:00:01 |
|  34 |                    TABLE ACCESS FULL        | STD_VERSION                |    25 |   300 |       |     3   (0)| 00:00:01 |
|* 35 |                    TABLE ACCESS FULL        | STD_TYPE                   |     1 |    19 |       |     3   (0)| 00:00:01 |
|* 36 |                   TABLE ACCESS FULL         | STD_TEAM                   |    96 |  2304 |       |     3   (0)| 00:00:01 |
|  37 |                  TABLE ACCESS FULL          | C_ELEMENTVALUE             |   916 | 39388 |       |     6   (0)| 00:00:01 |
|* 38 |                 TABLE ACCESS FULL           | STD_PRODUCT                |  3972 | 55608 |       |    19   (6)| 00:00:01 |
|  39 |                VIEW                         |                            |   228 |  4560 |       |     5  (20)| 00:00:01 |
|* 40 |                 FILTER                      |                            |       |       |       |            |          |
|* 41 |                  HASH JOIN OUTER            |                            |   228 |  5928 |       |     5  (20)| 00:00:01 |
|  42 |                   TABLE ACCESS FULL         | C_YEAR                     |    19 |   209 |       |     2   (0)| 00:00:01 |
|  43 |                   TABLE ACCESS FULL         | C_PERIOD                   |   228 |  3420 |       |     2   (0)| 00:00:01 |
|  44 |               VIEW                          |                            |     1 |    13 |       |     4   (0)| 00:00:01 |
|  45 |                NESTED LOOPS OUTER           |                            |     1 |    28 |       |     4   (0)| 00:00:01 |
|* 46 |                 TABLE ACCESS BY INDEX ROWID | CRM_GROUPPRODUCT           |     1 |    14 |       |     1   (0)| 00:00:0
|* 47 |                  INDEX UNIQUE SCAN          | SYS_C00372625              |     1 |       |       |     0   (0)| 00:00:01 |
|* 48 |                 TABLE ACCESS FULL           | CRM_GROUPPRODUCTLINE       |     1 |    14 |       |     3   (0)| 00:00:01 |
|* 49 |          HASH JOIN RIGHT OUTER              |                            | 89950 |   122M|       |   190K  (1)| 00:38:12 |
|  50 |           VIEW                              |                            |   228 |  7524 |       |     5  (20)| 00:00:01 |
|* 51 |            HASH JOIN OUTER                  |                            |   228 |  5928 |       |     5  (20)| 00:00:01 |
|  52 |             TABLE ACCESS FULL               | C_YEAR                     |    19 |   209 |       |     2   (0)| 00:00:01 |
|  53 |             TABLE ACCESS FULL               | C_PERIOD                   |   228 |  3420 |       |     2   (0)| 00:00:01 |
|  54 |           NESTED LOOPS                      |                            | 89950 |   119M|       |   190K  (1)| 00:38:12 |
|* 55 |            HASH JOIN RIGHT OUTER            |                            | 89950 |   118M|  1664K|   190K  (1)| 00:38:10 |
|  56 |             VIEW                            | STD_V_DISTMAPP_PRODUCTCODE | 14511 |  1487K|       |   103  (57)| 00:00:02 |
|  57 |              NESTED LOOPS OUTER             |                            | 14511 |   949K|       |   103  (57)| 00:00:02 |
|  58 |               NESTED LOOPS OUTER            |                            | 14511 |   864K|       |    76  (41)| 00:00:01 |
|* 59 |                HASH JOIN RIGHT OUTER        |                            | 14511 |   779K|       |    48   (7)| 00:00:01 |
|  60 |                 INDEX FAST FULL SCAN        | STD_DISTMAPP_OUTLET_CODE   | 14516 |   170K|       |    15   (0)| 00:
|* 61 |                 HASH JOIN RIGHT OUTER       |                            |  8202 |   344K|       |    32   (7)| 00:00:01 |
|  62 |                  TABLE ACCESS FULL          | STD_DISTMAPP_PRODUCT       |  2574 | 46332 |       |     8   (0)| 00:00:01 
|* 63 |                  HASH JOIN OUTER            |                            |  8202 |   200K|       |    23   (5)| 00:00:01 |
|  64 |                   INDEX FULL SCAN           | STD_DIST_PK                |     5 |    30 |       |            |          |
|  65 |                   TABLE ACCESS FULL         | STD_DISTMAPP_OUTLET        |  8202 |   152K|       |    22   (0)| 00:00:01 
|* 66 |                INDEX UNIQUE SCAN            | C_ELEMENTVALUE_KEY         |     1 |     6 |       |     0   (0)| 00:00:
|* 67 |               INDEX UNIQUE SCAN             | C_ELEMENTVALUE_KEY         |     1 |     6 |       |     0   (0)| 00:00:
|  68 |             VIEW                            |                            | 89950 |   109M|       |   185K  (1)| 00:37:02 |
|  69 |              NESTED LOOPS OUTER             |                            | 89950 |    24M|       |   185K  (1)| 00:37:02 |
|* 70 |               HASH JOIN RIGHT OUTER         |                            | 44975 |    11M|       |  4794   (4)| 00:00:58 |
|  71 |                TABLE ACCESS FULL            | M_PRODUCT                  |  6320 |   469K|       |    64   (2)| 00:00:01 |
|* 72 |                HASH JOIN RIGHT OUTER        |                            | 44975 |  8564K|       |  4729   (4)| 00:00:57 |
|* 73 |                 TABLE ACCESS FULL           | SLS_SALES_REMAIN           |   366 |  4758 |       |     5   (0)| 00:00:01 |
|* 74 |                 HASH JOIN RIGHT OUTER       |                            | 44975 |  7993K|  2744K|  4722   (4)| 00:00:57 |
|  75 |                  TABLE ACCESS FULL          | MSTR_DISTRIBUTOR_OUTLET    | 40628 |  2261K|       |   153   (2)| 00:00:
|* 76 |                  TABLE ACCESS FULL          | SLS_SALES_DISTRIBUTOR      | 44975 |  5490K|       |  4139   (5)| 00:00:50
|  77 |               VIEW                          |                            |     2 |    26 |       |     4   (0)| 00:00:01 |
|* 78 |                FILTER                       |                            |       |       |       |            |          |
|  79 |                 NESTED LOOPS OUTER          |                            |     2 |    52 |       |     4   (0)| 00:00:01 |
|* 80 |                  TABLE ACCESS FULL          | C_YEAR                     |     1 |    11 |       |     2   (0)| 00:00:01 |
|  81 |                  TABLE ACCESS BY INDEX ROWID| C_PERIOD                   |    12 |   180 |       |     2   (0)| 00:00:01 |
|* 82 |                   INDEX RANGE SCAN          | C_PERIOD_NOUNIQUE          |    12 |       |       |     1   (0)| 00:00:01 |
|* 83 |            INDEX UNIQUE SCAN                | M_PRICELIST_KEY            |     1 |     6 |       |     0   (0)| 00:00:01 |
|  84 |             TABLE ACCESS BY INDEX ROWID     | M_PRODUCT                  |     1 |    61 |       |     2   (0)| 00:00:
|* 85 |              INDEX UNIQUE SCAN              | M_PRODUCT_KEY              |     1 |       |       |     1   (0)| 00:00:01 |
|  86 |    TABLE ACCESS BY INDEX ROWID              | STD_VERSION                |     1 |    12 |       |     1   (0)| 00:00:
|* 87 |     INDEX RANGE SCAN                        | STD_VERSION_PERIOD         |     1 |       |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."STD_VERSION_ID"="C"."STD_VERSION_ID"(+))
   2 - filter(UPPER("C"."VALUE"(+))='SALES')
   4 - access("A"."TEAM_ID"="DP"."TEAM_ID"(+) AND "A"."KODE_PELANGGAN"="DP"."KODE_PELANGGAN"(+))
   8 - access("B"."STD_DISTMAPP_OUTLET_ID"="C"."STD_DISTMAPP_OUTLET_ID"(+))
  10 - access("B"."STD_DISTMAPP_OUTLET_ID"="D"."STD_DISTMAPP_OUTLET_ID"(+))
  12 - access("A"."STD_DISTMAPP_ID"="B"."STD_DISTMAPP_ID"(+))
  15 - access("B"."C_ELEMENTVALUE_ID"(+)=CASE "C"."TEAM_ID" WHEN 1000677 THEN 1000673 WHEN 1000678 T
              1000684 THEN 1000673 ELSE "C"."TEAM_ID" END )
  16 - access("C"."C_ELEMENTVALUE_ID"(+)=CASE "D"."TEAM_ID" WHEN 1000677 THEN 1000673 WHEN 1000678 T
              1000684 THEN 1000673 ELSE "D"."TEAM_ID" END )
  19 - access("PP"."M_PRICELIST_VERSION_ID"(+)="GETPRICELIST_VERSION_ID4"("PL"."M_PRICELIST_ID","P".
              "A"."M_PRODUCT_ID"="PP"."M_PRODUCT_ID"(+))
  22 - access("A"."M_PRODUCT_ID"="F"."M_PRODUCT_ID"(+) AND "E"."C_PERIOD_ID"="F"."C_PERIOD_ID"(+))
  25 - access("H"."M_PRODUCT_ID"=COALESCE("G"."M_PRODUCT_ID","F"."M_PRODUCT_ID"))
  26 - filter("H"."ISACTIVE"='Y')
  30 - access("D"."STD_TEAM_ID"="F"."STD_TEAM_ID")
  31 - access("D"."C_ELEMENTVALUE_ID"="E"."C_ELEMENTVALUE_ID"(+))
  32 - access("C"."STD_TYPE_ID"="D"."STD_TYPE_ID")
  33 - access("A"."STD_VERSION_ID"="C"."STD_VERSION_ID"(+))
  35 - filter(UPPER("C"."VALUE"(+))='SALES')
  36 - filter("D"."ISACTIVE"='Y')
  38 - filter("F"."ISACTIVE"='Y')
  40 - filter("A"."C_PERIOD_ID"="B"."C_PERIOD_ID")
  41 - access("A"."C_YEAR_ID"="B"."C_YEAR_ID"(+))
  46 - filter("A"."ISACTIVE"='Y')
  47 - access("F"."M_PRODUCT_ID"="A"."PRODUCTSUBTITUTE_ID")
  48 - filter("B"."ISACTIVE"(+)='Y' AND "A"."CRM_GROUPPRODUCT_ID"="B"."CRM_GROUPPRODUCT_ID"(+))
  49 - access("P"."BULAN"(+)="from$_subquery$_010"."BULAN_PERIODE" AND
              "from$_subquery$_010"."TAHUN_PERIODE"=TO_NUMBER("P"."TAHUN"(+)))
  51 - access("A"."C_YEAR_ID"="B"."C_YEAR_ID"(+))
  55 - access("from$_subquery$_010"."QCSJ_C000000000600000"="G"."M_PRODUCT_ID"(+) AND "G"."KODE_PELA
              "from$_subquery$_010"."QCSJ_C000000000400010" WHEN 'AMS' THEN
              "from$_subquery$_010"."AREA_REF_CODE"||'-'||"from$_subquery$_010"."QCSJ_C000000000400012" ELS
              "from$_subquery$_010"."QCSJ_C000000000400012" END )
  59 - access("B"."STD_DISTMAPP_OUTLET_ID"="C"."STD_DISTMAPP_OUTLET_ID"(+))
  61 - access("B"."STD_DISTMAPP_OUTLET_ID"="D"."STD_DISTMAPP_OUTLET_ID"(+))
  63 - access("A"."STD_DISTMAPP_ID"="B"."STD_DISTMAPP_ID"(+))
  66 - access("B"."C_ELEMENTVALUE_ID"(+)=CASE "C"."TEAM_ID" WHEN 1000677 THEN 1000673 WHEN 1000678 T
              1000684 THEN 1000673 ELSE "C"."TEAM_ID" END )
  67 - access("C"."C_ELEMENTVALUE_ID"(+)=CASE "D"."TEAM_ID" WHEN 1000677 THEN 1000673 WHEN 1000678 T
              1000684 THEN 1000673 ELSE "D"."TEAM_ID" END )
  70 - access("A"."M_PRODUCT_ID"="C"."M_PRODUCT_ID"(+))
  72 - access("A"."TANGGAL"="D"."TANGGAL"(+) AND "A"."BULAN_PERIODE"="D"."BULAN"(+) AND
              "A"."TAHUN_PERIODE"="D"."TAHUN"(+))
  73 - filter("D"."TAHUN"(+)>2015)
  74 - access("B"."KODE_PELANGGAN_FULL"(+)=CASE "A"."KODE_DISTRIBUTOR" WHEN 'AMS' THEN
              "A"."AREA_REF_CODE"||'-'||"A"."KODE_PELANGGAN" ELSE "A"."KODE_PELANGGAN" END )
  76 - filter("A"."TAHUN_PERIODE">2015 AND "A"."HIDE"='N')
  78 - filter("A"."BULAN_PERIODE"="B"."PERIODNO")
  80 - filter("A"."TAHUN_PERIODE"=TO_NUMBER("A"."YEAR"))
  82 - access("A"."C_YEAR_ID"="B"."C_YEAR_ID"(+))
  83 - access("PL"."M_PRICELIST_ID"=CASE  WHEN  REGEXP_LIKE ( (SELECT "NAME" FROM "M_PRODUCT" "M_PROD
              "M_PRODUCT_ID"=:B1),N'ASKES|BPJS') THEN 1000004 ELSE 1000000 END )
  85 - access("M_PRODUCT_ID"=:B1)
  87 - access("A"."C_PERIOD_ID"="B"."C_PERIOD_ID"(+))

153 rows selected.


RC: Tags added

[Updated on: Tue, 13 September 2016 02:24] by Moderator

Report message to a moderator

Re: What should i do for tuning this query [message #655801 is a reply to message #655799] Tue, 13 September 2016 02:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
That's better (thanks to RC).
Please can you post the code for the views STD_V_DISTMAPP_PRODUCTCODE and STD_TEAM_PRODUCT,
I'ld like to see why they are not being merged.
Re: What should i do for tuning this query [message #655865 is a reply to message #655801] Wed, 14 September 2016 20:55 Go to previous messageGo to next message
rayrevan
Messages: 21
Registered: August 2016
Junior Member
STD_V_DISTMAPP_PRODUCTCODE
SELECT 
    A.SLS_DISTRIBUTOR_ID, A.DISTRIBUTOR, A.NAMA_DISTRIBUTOR,
    A.KODE_PELANGGAN, A.NAMA_PELANGGAN,
    A.TEAM_ID, B.NAME AS TEAM, A.CODE,
    A.M_PRODUCT_ID, A.MAPP_TEAM_ID, C.NAME AS MAPP_TEAM, A.MAPP_CODE
FROM (
    SELECT 
        A.SLS_DISTRIBUTOR_ID, A.KODE_DISTRIBUTOR AS DISTRIBUTOR, A.NAMA_DISTRIBUTOR,
        B.KODE_PELANGGAN, B.NAMA_PELANGGAN,
        CASE --WHEN D.TEAM_ID IS NOT NULL THEN D.TEAM_ID
             WHEN C.TEAM_ID IN (1000677,1000678,1000684) THEN 1000673
             ELSE C.TEAM_ID END AS TEAM_ID,
        C.CODE,
        D.M_PRODUCT_ID,
        CASE WHEN D.TEAM_ID IN (1000677,1000678,1000684) THEN 1000673
             ELSE D.TEAM_ID END AS MAPP_TEAM_ID, 
        D.CODE AS MAPP_CODE
    FROM STD_DISTMAPP A
    LEFT JOIN STD_DISTMAPP_OUTLET B ON A.STD_DISTMAPP_ID = B.STD_DISTMAPP_ID
    LEFT JOIN STD_DISTMAPP_CODE C ON B.STD_DISTMAPP_OUTLET_ID = C.STD_DISTMAPP_OUTLET_ID
    LEFT JOIN STD_DISTMAPP_PRODUCT D ON B.STD_DISTMAPP_OUTLET_ID = D.STD_DISTMAPP_OUTLET_ID
) A
LEFT JOIN C_ELEMENTVALUE B ON A.TEAM_ID = B.C_ELEMENTVALUE_ID  
LEFT JOIN C_ELEMENTVALUE C ON A.MAPP_TEAM_ID = C.C_ELEMENTVALUE_ID
/

STD_TEAM_PRODUCT
select  
    distinct a.std_version_id, a.std_type_id, a.c_period_id, a.tahun, a.bulan, a.team_id, a.team, a.m_product_id, a.product_code, a.product, a.REFF_CODE
from (
    select 
        a.std_version_id, c.std_type_id, a.c_period_id, b.tahun, b.bulan,
        d.c_elementvalue_id as team_id, e.name as team, 
        coalesce(g.m_product_id, f.m_product_id) as m_product_id, h.value as product_code, h.name as product, d.REFF_CODE 
    from std_version a
    left join detail_period b on a.c_period_id = b.c_period_id
    left join std_type c on a.std_version_id = c.std_version_id and upper(c.value) = 'SALES'
    left join std_team d on c.std_type_id = d.std_type_id
    left join c_elementvalue e on d.c_elementvalue_id = e.c_elementvalue_id
    left join std_product f on d.std_team_id = f.std_team_id
    left join (
        select distinct a.productsubtitute_id, b.m_product_id 
        from crm_groupproduct a
        left join crm_groupproductline b on a.crm_groupproduct_id = b.crm_groupproduct_id and b.isactive = 'Y'
        where a.isactive = 'Y'
    ) g on f.m_product_id = g.productsubtitute_id
    left join m_product h on coalesce(g.m_product_id, f.m_product_id) = h.m_product_id
    where d.isactive = 'Y' and h.isactive = 'Y' and f.isactive = 'Y'  
) a
/
Re: What should i do for tuning this query [message #655878 is a reply to message #655865] Thu, 15 September 2016 02:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Materializing the view STD_TEAM_PRODUCT is the most expensive part of the query, 95% of the total cost. It cannot be merged because it has a DISTINCT. I think you need to get rid of the DISTINCT. Note that the CBO does not expect it to remove any rows, is it in fact possible for there to be any dupliactes? You need to test that. If there are some duplicates, either do not select them or remove them later on in the query.
Re: What should i do for tuning this query [message #655879 is a reply to message #655865] Thu, 15 September 2016 02:38 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This is another section you need to rewrite,
        select distinct a.productsubtitute_id, b.m_product_id 
        from crm_groupproduct a
        left join crm_groupproductline b on a.crm_groupproduct_id = b.crm_groupproduct_id and b.isactive = 'Y'
        where a.isactive = 'Y'
Again, the DISTINCT is preventing merging. But more obvious, you have a useless outer join. It cannot preserve any rows, because the predicate will then remove them. Convert it to an inner join.

In general:
1. You need to question any use of aggregations such as DISTINCT: programmers often throw them in without thinking.
2. You need to check whether any outer joins are really needed. Outer joins force a join order and therefore cripple the optimizer. Again, programmers often throw them in for no purpose.
Re: What should i do for tuning this query [message #655882 is a reply to message #655879] Thu, 15 September 2016 03:25 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That isn't functionally equivalent to an inner join. The where clause is on a and b is the optional table.
Re: What should i do for tuning this query [message #655883 is a reply to message #655882] Thu, 15 September 2016 03:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Though I agree that the amount of outer joins is suspicious and they should all be checked too see if they're necessary.
Re: What should i do for tuning this query [message #655990 is a reply to message #655879] Mon, 19 September 2016 09:51 Go to previous messageGo to next message
rayrevan
Messages: 21
Registered: August 2016
Junior Member
what if i replace distinct clause with delete duplicate function? can it work? or any suggestion to delete any duplicate row without using distinct clause?
Re: What should i do for tuning this query [message #655993 is a reply to message #655990] Mon, 19 September 2016 10:32 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What do you mean by delete duplicate function?
Re: What should i do for tuning this query [message #656007 is a reply to message #655993] Mon, 19 September 2016 20:32 Go to previous messageGo to next message
rayrevan
Messages: 21
Registered: August 2016
Junior Member
i mean, i create new function to delete duplicate row, and delete distinct clause in query.
so i can use that function like this code
left join m_productprice pp on (
        a.m_product_id = pp.m_product_id 
        and pp.m_pricelist_version_id =[b]getpricelist_version_id4[/b](pl.m_pricelist_id,p.c_period_id)   
Re: What should i do for tuning this query [message #656012 is a reply to message #656007] Tue, 20 September 2016 03:24 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Functions in where clause usually make performance worse not better. Why can't you do whatever the function is going to do directly in the where clause of your select?
Previous Topic: MR (Metadata Repository) SQL caused poor performance
Next Topic: Application is running slow time to time
Goto Forum:
  


Current Time: Thu Mar 28 08:00:58 CDT 2024