oracle - How to Execute these statements given beolw as a script in SQL*Plus, not as stored procedure -
procedure sp_process_rate_plan var_cursor integer; prv_rp_update_sql varchar2(4000); prv_rp_insert_sql varchar2(4000); v_dummy integer; sql_statement varchar2(400); /* massage data rate_plan table */ begin /*trigger dropped , recreated table priceview_rate_plan_proc */ execute immediate 'drop trigger rate_plan_id_trigger'; execute immediate 'create or replace trigger rate_plan_id_trigger before insert on priceview_rate_plan_proc each row begin select rate_plan_id_seq.nextval :new.rate_plan_id dual; end;'; /* update rate_period same value rate_periods*/ prv_rp_update_sql:= 'update rate_usage_overrides set a.rate_period = ''0'' a.seqnum not in (select /*+ use_hash(rate_usage_overrides) */ a.seqnum rate_usage_overrides b a.type_id_usg = b.type_id_usg , a.element_id = b.element_id , a.jurisdiction = b.jurisdiction , a.provider_class = b.provider_class , a.corridor_plan_id = b.corridor_plan_id , ( a.fixed_charge_amt <> b.fixed_charge_amt or a.add_fixed_amt <> b.add_fixed_amt or a.add_unit_rate <> b.add_unit_rate ) , decode(a.inactive_dt,null,''00/00/0000'',a.inactive_dt) = decode(b.inactive_dt,null,''00/00/0000'',b.inactive_dt) , a.active_dt = b.active_dt ) , (select count(*) rate_usage_overrides c a.type_id_usg = c.type_id_usg , a.element_id = c.element_id , a.jurisdiction = c.jurisdiction , a.provider_class = c.provider_class , a.corridor_plan_id = c.corridor_plan_id , a.fixed_charge_amt = c.fixed_charge_amt , a.add_fixed_amt = c.add_fixed_amt , decode(a.inactive_dt,null,''00/00/0000'',a.inactive_dt) = decode(c.inactive_dt,null,''00/00/0000'',c.inactive_dt) , a.active_dt = c.active_dt , a.add_unit_rate = c.add_unit_rate) > 1'; var_cursor := dbms_sql.open_cursor; dbms_sql.parse(var_cursor,prv_rp_update_sql,dbms_sql.v7); v_dummy:= dbms_sql.execute(var_cursor); dbms_sql.close_cursor(var_cursor); commit; lock table priceview_rate_plan_proc in exclusive mode; /* insert rate_plan data */ prv_rp_insert_sql := 'insert priceview_rate_plan_proc (ssr_code, corridor_plan_id, corridor_plan_description, usage_type, product, jurisdiction, provider, rate_period, flagfall, rate, rateband, numsecs, band_rate, active_dt, inactive_dt) select /*+ use_hash(rate_usage_overrides,corridor_plan_id_values,product_elements,descriptions,jurisdictions,rate_usage_bands_overrides) */ distinct decode(a.corridor_plan_id,0, '''', (select c.short_display corridor_plan_id_values c a.corridor_plan_id = c.corridor_plan_id)) ssr_code, a.corridor_plan_id corridor_plan_id, decode(a.corridor_plan_id,0, '''', (select d.display_value corridor_plan_id_values d a.corridor_plan_id = d.corridor_plan_id)) corridor_plan_description, decode(a.type_id_usg,0, '''', (select f.description_text usage_types e, descriptions f a.type_id_usg = e.type_id_usg , e.description_code = f.description_code)) usage_type, decode(a.element_id,0, '''', (select h.description_text product_elements g,descriptions h a.element_id = g.element_id , g.description_code = h.description_code)) product, decode(a.jurisdiction,0, '''', (select j.description_text jurisdictions i,descriptions j a.jurisdiction = i.jurisdiction , j.description_code = i.description_code)) jurisdiction, decode(a.provider_class,0, '''', (select k.display_value provider_class_values k a.provider_class = k.provider_class)) provider, decode(a.rate_period,''0'', '''',(select l.display_value rate_period_values l a.rate_period = l.rate_period)) rate_period, (a.fixed_charge_amt/100) + (a.add_fixed_amt/10000000) flagfall, (a.add_unit_rate/10000000) * 60 rate, b.rateband rateband, b.num_units numsecs, (b.unit_rate/10000000) * 60 band_rate, a.active_dt active_dt, a.inactive_dt inactive_dt rate_usage_overrides a, rate_usage_bands_overrides b a.seqnum = b.seqnum(+)'; var_cursor := dbms_sql.open_cursor; dbms_sql.parse(var_cursor,prv_rp_insert_sql,dbms_sql.v7); v_dummy:= dbms_sql.execute(var_cursor); dbms_sql.close_cursor(var_cursor); commit; /* create indexes */ execute immediate 'drop index rrp_rate_plan_id'; execute immediate 'drop index rrp_usage_type'; execute immediate 'drop index rrp_corridor_plan_id'; execute immediate 'drop index rrp_ssr_code'; execute immediate 'drop index rrp_ut_prod'; execute immediate 'drop index rrp_ut_juris'; execute immediate 'drop index rrp_ut_juris_prov'; execute immediate 'drop index rrp_ut_pd_jr_pv'; execute immediate 'drop index rrp_ut_prod_juris'; execute immediate 'drop index rrp_ut_prod_prov'; execute immediate 'drop index rrp_ut_prov'; execute immediate 'create index rrp_corridor_plan_id on priceview_rate_plan_proc(corridor_plan_id, usage_type) tablespace prv_index storage( pctincrease 0 )'; execute immediate 'create unique index rrp_rate_plan_id on priceview_rate_plan_proc(rate_plan_id) tablespace prv_index storage( pctincrease 0 )'; execute immediate 'create index rrp_ssr_code on priceview_rate_plan_proc(ssr_code) tablespace prv_index storage( pctincrease 0 )'; execute immediate 'create index rrp_usage_type on priceview_rate_plan_proc(usage_type) tablespace prv_index storage( pctincrease 0 )'; execute immediate 'create index rrp_ut_prod on priceview_rate_plan_proc(usage_type, product) tablespace prv_index storage( pctincrease 0 )'; execute immediate 'create index rrp_ut_juris on priceview_rate_plan_proc(usage_type, jurisdiction) tablespace prv_index storage( pctincrease 0 )'; execute immediate 'create index rrp_ut_juris_prov on priceview_rate_plan_proc(usage_type, jurisdiction, provider) tablespace prv_index storage( pctincrease 0 )'; execute immediate 'create index rrp_ut_pd_jr_pv on priceview_rate_plan_proc(usage_type, product, jurisdiction, provider) tablespace prv_index storage( pctincrease 0 )'; execute immediate 'create index rrp_ut_prod_juris on priceview_rate_plan_proc(usage_type, product, jurisdiction) tablespace prv_index storage( pctincrease 0 )'; execute immediate 'create index rrp_ut_prod_prov on priceview_rate_plan_proc(usage_type, product, provider) tablespace prv_index storage( pctincrease 0 )'; execute immediate 'create index rrp_ut_prov on priceview_rate_plan_proc(usage_type, provider) tablespace prv_index storage( pctincrease 0 )'; /* cut on new tables */ lock table priceview_rate_plan in exclusive mode ; execute immediate 'alter table priceview_rate_plan rename priceview_rate_plan_x'; execute immediate 'alter table priceview_rate_plan_proc rename priceview_rate_plan'; execute immediate 'alter table priceview_rate_plan_x rename priceview_rate_plan_proc'; /* remove old data */ execute immediate 'truncate table priceview_rate_plan_proc'; end sp_process_rate_plan; end priceview_process_extract; i new sql , no idea above code need in converting procedure sql script
there many factors, affects query performance :
- network traffic
- table joins
- number of records in each tables.
- indexing , many more...
i can see inside procedure, executing lot of ddland dml queries, impact procedure performance.
what can is, can execute each ddl , dml query individually , see, query taking longer time. if able find out query, impacting procedure performance. then, can analyze query , related tables better performance. also, remove unnecessary code dropping/creating trigger (it's not idea use trigger temporary objects, since creating , dropping trigger in each procedure execution).
Comments
Post a Comment