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  |
 |
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  |
John Watson
Messages: 8964
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 #655799 is a reply to message #655796] |
Tue, 13 September 2016 02:21 |
|
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 #655865 is a reply to message #655801] |
Wed, 14 September 2016 20:55 |
|
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 |
John Watson
Messages: 8964
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 |
John Watson
Messages: 8964
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'
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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Feb 02 18:00:35 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
