OraFAQ Forum: Performance Tuning » Tuning an oracle view to increase the speed of the view

Home » RDBMS Server » Performance Tuning » Tuning an oracle view to increase the speed of the view

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning an oracle view to increase the speed of the view [message #213906]

Fri, 12 January 2007 13:54

suranjita_hajra_7
Messages: 20
Registered: November 2006
Location: Pittsburgh, USA

Junior Member

Hi,

I have a view with lots of joins and union all. It is taking 46 seconds to provide me with the output. It is required that I should get the output within 25 seconds.

I have created approprita indexes and also used index hints in my view. still it is taking a long time to fetch me the output.

Please help me out to tune the view so that I can get the desired result.

Thanks in advance.

Regards,
Suranjita Hajra.

My view is as written below:

create or replace view wfapp.joborder_blank_vw as

select /*+ INDEX(ADDRESS ADDRESS_PK), INDEX(ORG ORG_PK), INDEX(CUST_RELN CUST_RELN_PK), INDEX(COMMERCE COMMERCE_ACTOR_PK),
INDEX(VEND_RELN VEND_RELN_PK), INDEX(TRADE_RQMT TRADE_RQMT_PK), INDEX(STEELPROPS_RQMT STEELPROPS_RQMT_PK), INDEX(PART PART_PK),
INDEX(PART_REV PART_REV_PK), INDEX(PROCROUT_STEP PROCROUT_STEP_PK), INDEX(PROCROUT PROCROUT_PK),
INDEX(JOBORD$E JOBORD$E_PK)*/

ocp.handle profctr_org_handle,
ad.handle admin_domain_handle,
jo.skey joborder_skey,
jo.jobord_nbr joborder_no,
jo.jobord_recipe_sway_type_code process_type,
jo.jobord_note_1 job_order_comments_1,
jo.jobord_note_2 job_order_comments_2,
jo.jobord_note_3 job_order_comments_3,
jo.required_qty,
wfapp.get_qu_dsp_txt(jo.required_qty_uq) required_qty_uq,
joborder_date_committed.date_committed job_order_date_committed,
pjo.jobord_nbr parent_job_order_no,
org_pc.skey profctr_org_skey,
org_pc.name profit_center_name,
stlh.heat_nbr heat_no,
own_act.name owner,
own_act.duns owner_duns,
own_act.abbr owner_abbr,
loc_act.name location,
loc_act.abbr location_abbr,
mitm.material_id mill_coil_id,
supp_act.name steel_producer,
supp_act.abbr steel_producer_abbr,

sporg.name customer_name,
sporg.abbr customer_abbr,

enduser_org.name enduser_name,
enduser_org.abbr enduser_abbr,

stli.material_id charge_material_id,
prtc.steward_part_nbr part_no,
prtc.specifier_part_nbr cust_part_no,
prtrvc.grade_code grade,
prtrvc.steward_part_description_1 charge_part_description1,
prtrvc.steward_part_description_2 charge_part_description2,
prtrvc.steward_part_description_3 charge_part_description3,
prtrvc.spec_quality_code spec,
prtrvc.part_exposure_type_code part_type,
stlrqmt.coating_weight_spec_code coating,

stlrqmt.outside_diam est_dim_od,
wfapp.get_mu_dsp_txt(stlrqmt.outside_diam_um) est_dim_od_uom,

stlrqmt.weight est_dim_wt,
wfapp.get_mu_dsp_txt(stlrqmt.weight_um) est_dim_wt_uom,

stlrqmt.gauge gauge,
(stlrqmt.gauge - stlrqmt.gauge_tolr_minus) gauge_min,
(stlrqmt.gauge + stlrqmt.gauge_tolr_plus) gauge_max,
wfapp.get_mu_dsp_txt(stlrqmt.gauge_um) gauge_uom,

stlrqmt.width width,
(stlrqmt.width - stlrqmt.width_tolr_minus) width_min,
(stlrqmt.width + stlrqmt.width_tolr_plus) width_max,
wfapp.get_mu_dsp_txt(stlrqmt.width_um) width_uom,

stlrqmt.outside_diam_tolr_minus od_tol_min,
wfapp.get_mu_dsp_txt(stlrqmt.outside_diam_um) od_tol_min_uom,
stlrqmt.outside_diam_tolr_plus od_tol_max,
wfapp.get_mu_dsp_txt(stlrqmt.outside_diam_um) od_tol_max_uom,
stlrqmt.pin_pallet_type_skey pallet_type, --~*~ code
dl.name detailed_location_name,
stli.width_min min_width,
wfapp.get_mu_dsp_txt(stli.width_um) min_width_uom,
stli.width_max max_width,
wfapp.get_mu_dsp_txt(stli.width_um) max_width_uom,

--recipe
ccdt.display_text die_no,
v_jorcp.yield_factor projected_yield,
v_jorcp.recipe_name process_master_name,
v_jorcp.recipe_description process_master_description,
v_jorcp.recipe_note_1 process_master_comments_1,
v_jorcp.recipe_note_2 process_master_comments_2,
v_jorcp.recipe_note_3 process_master_comments_3,
pu1.name preferred_prod_unit_name,

--prod processes
(
select /*INDEX(RECIPE_PRODPROC RCPPRDPRC_LK), INDEX(RECIPE_PRODPROC RECIPE_PRODPROC_PK)*/
rpp_.prodproc_code
from otis.recipe_prodproc rpp_
where rpp_.recipe_skey = v_jorcp.recipe_skey
and ordinal = 1
) process1,
(
select /*INDEX(RECIPE_PRODPROC RCPPRDPRC_LK), INDEX(RECIPE_PRODPROC RECIPE_PRODPROC_PK)*/
rpp_.prodproc_code
from otis.recipe_prodproc rpp_
where rpp_.recipe_skey = v_jorcp.recipe_skey
and ordinal = 2
) process2,
(
select /*INDEX(RECIPE_PRODPROC RCPPRDPRC_LK), INDEX(RECIPE_PRODPROC RECIPE_PRODPROC_PK)*/
rpp_.prodproc_code
from otis.recipe_prodproc rpp_
where rpp_.recipe_skey = v_jorcp.recipe_skey
and ordinal = 3
) process3,
(
select /*INDEX(RECIPE_PRODPROC RCPPRDPRC_LK), INDEX(RECIPE_PRODPROC RECIPE_PRODPROC_PK)*/
rpp_.prodproc_code
from otis.recipe_prodproc rpp_
where rpp_.recipe_skey = v_jorcp.recipe_skey
and ordinal = 4
) process4,
(
select /*INDEX(RECIPE_PRODPROC RCPPRDPRC_LK), INDEX(RECIPE_PRODPROC RECIPE_PRODPROC_PK)*/
rpp_.prodproc_code
from otis.recipe_prodproc rpp_
where rpp_.recipe_skey = v_jorcp.recipe_skey
and ordinal = 5
) process5,

--dischg
rd.ordinal || '' setup_no,
prtd.steward_part_nbr discharge_part_no,

rqmtd.gauge discharge_gauge,
(rqmtd.gauge - rqmtd.gauge_tolr_minus) discharge_gauge_min,
(rqmtd.gauge + rqmtd.gauge_tolr_plus) discharge_gauge_max,
wfapp.get_mu_dsp_txt(rqmtd.gauge_um) discharge_guage_uom,

rqmtd.width discharge_width,
(rqmtd.width - rqmtd.width_tolr_minus) discharge_width_min,
(rqmtd.width + rqmtd.width_tolr_plus) discharge_width_max,
wfapp.get_mu_dsp_txt(rqmtd.width_um) discharge_width_uom,

rqmtd.pitch discharge_pitch,
wfapp.get_mu_dsp_txt(rqmtd.pitch_um) discharge_pitch_uom,

rqmtd.piece_weight,
wfapp.get_mu_dsp_txt(rqmtd.piece_weight_um) pieces_weight_uom,
rqmtd.pieces_per_lift,
rqmtd.lifts_per_pallet,
ppt.label pin_pallet_type_label,
(rqmtd.piece_weight * rqmtd.pieces_per_lift * rqmtd.lifts_per_pallet) total_max_pallet,

(select /*INDEX(EVENT_BASE EB_FK_STEELITEM$E), INDEX(STEELITEM$E STEELITEM$E_PK), INDEX(STEELITEM$E STEELITEM$E_LK), INDEX(STEELITEM$E STEELITEM$E_ETC),
INDEX(STEELITEM STEELITEM_PK)*/
max( b.when$ms ) keep ( dense_rank last order by e.steelitem_skey, e.serial )
from otis.event_base b
join otis.steelitem$e e on b.steelitem$e = e.serial
where e.event_type_code = 'RECEIVE'
and e.steelitem_skey = stli.skey
)receive_date,

(case
when stli.weight_um = 'POUND' then
otis.measurement_conversion_pkg.converted_mg( stli.weight, 'POUND', 'KILOGRAM', 0 )
when stli.weight_um = 'KILOGRAM' then
stli.weight
end) weight_metric,
'KG' weight_metric_uom,

(case
when stli.weight_um = 'POUND' then
stli.weight
when stli.weight_um = 'KILOGRAM' then
otis.measurement_conversion_pkg.converted_mg( stli.weight, 'KILOGRAM', 'POUND', 0 )
end) weight_english,
'LB' weight_english_uom,

(case
when stli.gauge_um = 'INCH' then
otis.measurement_conversion_pkg.converted_mg(stli.gauge, 'INCH', 'MILLIMETER', 2 )
when stli.gauge_um = 'MILLIMETER' then
stli.gauge
end) gauge_metric,
'MM' gauge_metric_uom,

(case
when stli.gauge_um = 'INCH' then
stli.gauge
when stli.gauge_um = 'MILLIMETER' then
otis.measurement_conversion_pkg.converted_mg(stli.gauge, 'MILLIMETER', 'INCH', 2 )
end) gauge_english,
'IN' gauge_english_uom,

(case
when stli.width_um = 'INCH' then
otis.measurement_conversion_pkg.converted_mg(stli.width, 'INCH', 'MILLIMETER', 2 )
when stli.width_um = 'MILLIMETER' then
stli.width
end) width_metric,
'MM' width_metric_uom,

(case
when stli.width_um = 'INCH' then
stli.width
when stli.width_um = 'MILLIMETER' then
otis.measurement_conversion_pkg.converted_mg(stli.width, 'MILLIMETER', 'INCH', 2 )
end) width_english,
'IN' width_english_uom

/********~*~n/a
--'610' discharge_coil_id,
--'4' stacks_per_pallet,
'1' number_of_pallets,
'250' sheets_per_stack,
'1.9697' sheet_wgt,
'5' total_max_pallet,
'BL1' blanking_line_no,
'9729' min_charge_coil_wgt,
'10331' max_charge_coil_wgt,
'53713/4-0C010' recipe_no,
'02/14/2006' workorder_created_date,
'1' loc_building,
'Bay C' loc_area,
'Row 32' loc_detail,
'234' min_discharge_pitch,
'235.5' max_discharge_pitch,
********/

from otis.jobord jo
join (
select /*INDEX(JOBORD$$RCPFULL JOBORD$RCPF_FK_RECIPE), INDEX(RECIPE RECIPE_PK),
INDEX(RECIPE$$BLANK RECIPE$$BLANK_PK)*/
jrf.jobord_skey,
rb.die_type_code,
rb.yield_factor,
rb.charge_procrout_step_skey,
rb.recipe_skey,
r.name recipe_name,
r.description recipe_description,
r.recipe_note_1,
r.recipe_note_2,
r.recipe_note_3
from otis.jobord$$rcpfull jrf
join otis.recipe r on jrf.recipe_skey = r.skey
join otis.recipe$$blank rb on r.skey = rb.recipe_skey

union all

select /*INDEX(JOBORD$$RCPSOME JOBORD$RCPP_FK_RECIPE), INDEX(JOBORD$$RCPSOME JOBORD$$RCPSOME_PK),
INDEX(JOBORD$$RCPSOME$$BLNK JOBORD$$RCPSOME$$BLNK_PK), INDEX(RECIPE RECIPE_PK), INDEX(RECIPE$$BLANK RECIPE$$BLANK_PK)*/

jrs.jobord_skey,
rb.die_type_code,
rb.yield_factor,
rb.charge_procrout_step_skey,
rb.recipe_skey,
r.name recipe_name,
r.description recipe_description,
jrs.recipe_note_1,
jrs.recipe_note_2,
jrs.recipe_note_3
from otis.jobord$$rcpsome jrs
join otis.jobord$$rcpsome$$blnk jrsb on jrs.jobord_skey = jrsb.jobord_skey
join otis.recipe r on jrs.recipe_skey = r.skey
join otis.recipe$$blank rb on r.skey = rb.recipe_skey

union all

select /*INDEX(JOBORD$$RCPNONE JOBORD$$RCPNONE_PK), INDEX(JOBORD$$RCPNONE$BLNK JOBORD$$RCPNONE$$BLNK_PK),
INDEX(JOBORD JOBORD_PK), INDEX(JOBORD I_PRED_JOBORDER_SKEY), INDEX(RECIPE_PRODUNIT_1 RECIPE_PRODUNIT_1_PK),
INDEX(PRODUNIT PRODUNIT_PK), INDEX(RECIPE_PRODUNIT_1 RCPPRODUNIT1_FK_PRODUNIT, INDEX(JOBORD I_CHARGE_STEELITEM_SKEY),
INDEX(STEELITEM STEELITEM_PK)*/
jrnb.jobord_skey,
jrnb.die_type_code,
jrnb.yield_factor,
jrnb.charge_procrout_step_skey,
null recipe_name,
null recipe_description,
null recipe_skey,
null recipe_note_1,
null recipe_note_2,
null recipe_note_3
from otis.jobord$$rcpnone jrn
join otis.jobord$$rcpnone$$blnk jrnb on jrn.jobord_skey = jrnb.jobord_skey

) v_jorcp on jo.skey = v_jorcp.jobord_skey

left join (
select /*INDEX(JOBORD JOBORD_PK)*/
max( jobord_skey ) keep (dense_rank last order by serial ) as jobord_skey,
to_char(max( when$ms - 0 ) keep (dense_rank last order by serial ), 'MM/DD/YYYY') as date_committed
from (
select /*INDEX(JOBORD$E JOBORD$E_PK), INDEX(JOBORD$E JOBORD$E_PK), INDEX(EVENT_BASE INDEX(EVENT_BASE EB_FK_STEELITEM$E),
INDEX(JOBORD$E JOBORD$E_FK_JOBORD$E$T)*/
joe.serial,
joe.jobord_skey,
eb.when$ms
from otis.jobord$e joe
join otis.event_base eb
on eb.jobord$e = joe.serial
where joe.event_type_code = 'COMMIT'
)
group by jobord_skey
) joborder_date_committed
on jo.skey = joborder_date_committed.jobord_skey

left join otis.jobord pjo on jo.pred_joborder_skey = pjo.skey
left join otis.recipe_produnit_1 rp1
on rp1.recipe_skey = v_jorcp.recipe_skey
left join otis.produnit pu1
on pu1.skey = rp1.produnit_skey

--~*~put back: join corehistory.steelitem$h stli on jo.charge_steelitem_skey = stli.steelitem_skey
join otis.steelitem stli on jo.charge_steelitem_skey = stli.skey

join otis.procrout_step_props_rqmt_cfvw stlrqmt on v_jorcp.charge_procrout_step_skey = stlrqmt.procrout_step_skey

left join (
select stli2_a.skey steelitem_skey,
soh.soldto_cust_reln_skey
from otis.steelitem stli2_a
join otis.salesord_ln$r solr on stli2_a.salesord_ln$r_skey = solr.skey
join otis.salesord_ln sol on solr.salesord_hd_skey = sol.salesord_hd_skey
and solr.line_number = sol.line_number
join otis.salesord_hd soh on sol.salesord_hd_skey = soh.skey

union all

select stli2_b.skey steelitem_skey,
bso.soldto_cust_reln_skey
from otis.steelitem stli2_b
join otis.blanket_salesord$r bsorv on stli2_b.blanket_salesord$r_skey = bsorv.skey
join otis.blanket_salesord bso on bsorv.blanket_salesord_skey = bso.skey
) v_so on stli.skey = v_so.steelitem_skey

left join otis.detailed_location dl on dl.skey = stli.detailed_location_skey

join otis.procrout_step prsc on stli.next_procrout_step_skey = prsc.skey --join otis.procrout_step prsc on v_jorcp.charge_procrout_step_skey = prsc.skey
join otis.procrout prc on prsc.procrout_skey = prc.skey
join otis.part_rev prtrvc on prc.part_rev_skey = prtrvc.skey
join otis.part prtc on prtrvc.part_skey = prtc.skey

join otis.trade_rqmt sptr_rqmt on sptr_rqmt.procrout_skey = prc.skey
left join otis.commerce_actor_vw supp_act on supp_act.skey = sptr_rqmt.steel_supplier_actor_skey

join otis.commerce_actor_vw own_act on stli.owner_actor_skey = own_act.skey
join otis.org org_pc on jo.profctr_org_skey = org_pc.skey

left join otis.millitem mitm on stli.millitem_skey = mitm.skey
left join otis.steelheat stlh on mitm.steelheat_skey = stlh.skey
join otis.commerce_actor_vw loc_act on stli.locat_actor_skey = loc_act.skey

--dschg
join otis.recipe_dischg rd on v_jorcp.recipe_skey = rd.recipe_skey
join otis.recipe_dischg$$blank rdb on rd.skey = rdb.recipe_dischg_skey
join otis.procrout_step prsd on rd.dischg_procrout_step_skey = prsd.skey
join otis.procrout_step_props_rqmt_cfvw rqmtd on rd.dischg_procrout_step_skey = rqmtd.procrout_step_skey
left join otis.pin_pallet_type ppt on ppt.skey = rqmtd.pin_pallet_type_skey
join otis.procrout prd on prsd.procrout_skey = prd.skey
join otis.part_rev prtrvd on prd.part_rev_skey = prtrvd.skey
join otis.part prtd on prtrvd.part_skey = prtd.skey

left join otis.enduser_reln eur on eur.skey = prtd.enduser_reln_skey
left join otis.org enduser_org on enduser_org.skey = eur.skey

left join otis.cust_reln spcr on prtd.specifier_cust_reln_skey = spcr.skey
and prtd.steward_client_org_skey = spcr.seller_org_skey
left join otis.org sporg on sporg.skey = spcr.buyer_org_skey

--codes
left join otis.coating_weight_spec_cfvw cccws on stlrqmt.coating_weight_spec_code = cccws.code
and cccws.profctr_org_skey = org_pc.skey
left join otis.grade_cfvw ccg on prtrvc.grade_code = ccg.code
and ccg.profctr_org_skey = org_pc.skey
left join otis.spec_quality_cfvw ccsq on prtrvc.spec_quality_code = ccsq.code
and ccsq.profctr_org_skey = org_pc.skey
left join otis.die_type_cfvw ccdt on v_jorcp.die_type_code = ccdt.code
and ccdt.profctr_org_skey = org_pc.skey
join otis.org$_client$_profctr ocp
on ocp.profctr_org_skey = jo.profctr_org_skey
join otis.admin_domain ad
on ad.skey = ocp.admin_domain_skey

Report message to a moderator