This discussion is archived
5 Replies Latest reply: Oct 6, 2009 7:17 PM by CharlesHooper RSS

Query/View Optimisation for Oracle8i

725270 Newbie
Currently Being Moderated
Hi,
I am hoping somebody here can help.

I currently have an Oracle 8i Database View/Query that is quite large and ugly and very time consuming to run and pulls information from multiple tables. I am after some tips to help me streamline it.

Would I be better to split this query up into multiple views? Is there any code I can change to help it run better.

Any help is appreciated :)



select "LINE_GOR","GROUPNAME","SUB","HUB","LINECODE","FROM_","TO_","LENGTH (m)","DINIS_GRP_CURRENT","% DINIS_GRP_CURRENT",
"DINIS_SEGMENT_CURRENT","GRP_SD50","GRP_SN50","GRP_WD50","GRP_WN50","SEG_SD","SEG_SN","SEG_WD","SEG_WN","SD_CONT_RAT",
"SN_CONT_RAT","WD_CONT_RAT","WN_CONT_RAT","SDLF","WDLF","SCRF","WCRF","SD_RATING","SN_RATING","WD_RATING","WN_RATING","SD_UTILISATION",
"SN_UTILISATION","WD_UTILISATION","WN_UTILISATION","WORST_UTILISATION","TMU","SD_GRP_RATING","SN_GRP_RATING","WD_GRP_RATING","WN_GRP_RATING",
"SD_GRP_VOLT_REG","SN_GRP_VOLT_REG","WD_GRP_VOLT_REG","WN_GRP_VOLT_REG","WORST_VOLT_REG","DINIS Power Loss (kW)","LOADFLOW_DATE",
"LOADFLOW_NOTES","PROJECT_NO","PROJECT_TYPE","POW","COMMENTS","COMMENTS_DATE","GRP_SD10","GRP_SN10","GRP_WD10","GRP_WN10"



from

(--------------------------------------------------------------------------------
-- CALCULATIONS

select groupname, sub, hub, line_gor, linecode, from_, to_, "LENGTH (m)", dinis_grp_current,
round("% DINIS_GRP_CURRENT",2) "% DINIS_GRP_CURRENT", dinis_segment_current,
grp_sd grp_sd50, grp_sn grp_sn50, grp_wd grp_wd50, grp_wn grp_wn50, seg_sd, seg_sn, seg_wd, seg_wn,
sd_cont_rat, sn_cont_rat, wd_cont_rat, wn_cont_rat,
sdlf, wdlf, scrf, wcrf,


round(sd_cont_rat*scrf,0) sd_rating, -- Segment Cyclic Ratings ie. Continuous Rating * Group Cyclic Rating Factor
round(sn_cont_rat*scrf,0) sn_rating,
round(wd_cont_rat*wcrf,0) wd_rating,
round(wn_cont_rat*wcrf,0) wn_rating,



round ((case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then 0 -- Segment Utilisation
else seg_sd/(sd_cont_rat*scrf)*100 end) ,0) sd_utilisation, -- If SD Forecast for a feeder/group is 0, a DIINIS Loadflow is not run.
-- The % DINIS Group Current is Null and the Group Current is 0, and
round ((case when "% DINIS_GRP_CURRENT" is null and grp_sn = 0 then 0 -- only Cnductor and Node data has been imported.
else seg_sn/(sn_cont_rat*scrf)*100 end) ,0) sn_utilisation, -- In this case the Utilisations are set to 0.

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wd = 0 then 0 -- Utilisations are Segment Current / Segment Cyclic Rating
else seg_wd/(wd_cont_rat*wcrf)*100 end) ,0) wd_utilisation,

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wn = 0 then 0
else seg_wn/(wn_cont_rat*wcrf)*100 end) ,0) wn_utilisation,



greatest(
round ((case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then 0 -- Segment Worst Utilisation
else seg_sd/(sd_cont_rat*scrf)*100 end) ,0),

round ((case when "% DINIS_GRP_CURRENT" is null and grp_sn = 0 then 0
else seg_sn/(sn_cont_rat*scrf)*100 end) ,0),

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wd = 0 then 0
else seg_wd/(wd_cont_rat*wcrf)*100 end) ,0),

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wn = 0 then 0
else seg_wn/(wn_cont_rat*wcrf)*100 end) ,0) ) worst_utilisation,

tmu,


round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then sd_cont_rat*scrf -- Group Rating relative to Segemnt Utilisation
else (case when "% DINIS_GRP_CURRENT" = 0 then 9999 -- When the % DINIS Group Current is null and the Group Summer Day Load = 0
else (case when (sd_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999 -- ie. No Loadflow has been run, the Segment Cyclic Rating becomes the Group Rating
else (sd_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) sd_grp_rating, -- When the % DINIS Group Current = 0, the Group Rating = 9999
-- The Group Rating is capped at 9999

-- Group Ratings are Cyclic Rating * 100 / % DINIS Group Current


round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then sn_cont_rat*scrf
else (case when "% DINIS_GRP_CURRENT" = 0 then 9999
else (case when (sn_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999
else (sn_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) sn_grp_rating,




round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then wd_cont_rat*wcrf
else (case when "% DINIS_GRP_CURRENT" = 0 then 9999
else (case when (wd_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999
else (wd_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) wd_grp_rating,




round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then wn_cont_rat*wcrf
else (case when "% DINIS_GRP_CURRENT" = 0 then 9999
else (case when (wn_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999
else (wn_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) wn_grp_rating,


round(dinis_grp_volt_reg/dinis_grp_current*grp_sd,1) sd_grp_volt_reg,
round(dinis_grp_volt_reg/dinis_grp_current*grp_sn,1) sn_grp_volt_reg,
round(dinis_grp_volt_reg/dinis_grp_current*grp_wd,1) wd_grp_volt_reg,
round(dinis_grp_volt_reg/dinis_grp_current*grp_wn,1) wn_grp_volt_reg,

greatest ( round(dinis_grp_volt_reg/dinis_grp_current*grp_sd,1), round(dinis_grp_volt_reg/dinis_grp_current*grp_sn,1),
round(dinis_grp_volt_reg/dinis_grp_current*grp_wd,1), round(dinis_grp_volt_reg/dinis_grp_current*grp_wn,1) ) worst_volt_reg,

dinis_real_power_loss "DINIS Power Loss (kW)", daterun loadflow_date, errornotes loadflow_notes,

project_no, project_type, pow,comments, last_update_time comments_date,

grp_sd10, grp_sn10, grp_wd10, grp_wn10

from



(--------------------------------------------------------------------------------
-- DATA


-- Tables Structure
-- t1 -
-- t2 -
-- t3 - = t6 -
-- t4 -
-- t5 -
-- t7 - = DATA
-- t8 -
-- t9 -
-- t10 -
-- t11 -

select distinct t6.groupname, t7.sub, t7.hub, t6.line_gor, t6.linecode, t6.from_, t6.to_, t6."LENGTH (m)",
dinis_grp_current, t6."% DINIS_GRP_CURRENT", t6.dinis_segment_current,
t6.grp_sd, t6.grp_sn, t6.grp_wd, t6.grp_wn,t6.grp_sd10, t6.grp_sn10, t6.grp_wd10, t6.grp_wn10,
t6.seg_sd, t6.seg_sn, t6.seg_wd, t6.seg_wn,
t6.sd_cont_rat, t6.sn_cont_rat, t6.wd_cont_rat, t6.wn_cont_rat,
t6.sdlf, t6.wdlf, t6.scrf, t6.wcrf, t7.tmu, t8.errornotes, t8.daterun, t9.dinis_grp_volt_reg,
t10.dinis_real_power_loss, t11.project_no, t11.project_type, t11.pow, t11.comments, t11.last_update_time



from -- Tables 6, 7, 8, 9 , 10 & 11


( -- Table 6, Join of Tables 1, 2, 3, 4 & 5

select distinct t1.groupname, t1.line_gor, t1.linecode, t1.userreference1 from_, t2.userreference1 to_, t1.nodetext3 project_s, t2.nodetext3 project_r, t1.length "LENGTH (m)",

t3.group_current dinis_grp_current,

(case when (t3.group_current is null or t3.group_current = 0) then null -- See notes in Table 3
else t1.current_in/t3.group_current*100 end)"% DINIS_GRP_CURRENT",

t1.current_in dinis_segment_current,

t4.grp_sd, t4.grp_sn, t4.grp_wd, t4.grp_wn, grp_sd10, grp_sn10, grp_wd10,grp_wn10,

(case when (t3.group_current is null or t3.group_current = 0) then null
else round(t1.current_in/t3.group_current*t4.grp_sd,0) end) seg_sd,

(case when (t3.group_current is null or t3.group_current = 0) then null
else round(t1.current_in/t3.group_current*t4.grp_sn,0) end) seg_sn,

(case when (t3.group_current is null or t3.group_current = 0) then null
else round(t1.current_in/t3.group_current*t4.grp_wd,0) end) seg_wd,

(case when (t3.group_current is null or t3.group_current = 0) then null
else round(t1.current_in/t3.group_current*t4.grp_wn,0) end) seg_wn,

t5.sd_cont_rat, t5.sn_cont_rat, t5.wd_cont_rat, t5.wn_cont_rat,

t4.sdlf sdlf, t4.wdlf wdlf,

(case when (t1.linecode like '%UG%' or t1.linecode like '%CBD%') then -- OH cable Cyclis Rating Factor = 1
(select crf
from dlf_to_crf
where min_dlf <= t4.sdlf and max_dlf > t4.sdlf)
else 1 end) scrf,

(case when (t1.linecode like '%UG%' or t1.linecode like '%CBD%') then -- UG cable Cyclic Rating Factor Look Up
(select crf -- from DLF_TO_CRT table
from dlf_to_crf
where min_dlf <= t4.wdlf and max_dlf > t4.wdlf) -- DLF determined in Table 4
else 1 end) wcrf


from


( -- Table 1
-- Line Results converted to kVar, kW and Amps
-- Line Results joined to Node Results on "Sendingnodegor"

select distinct
lr.groupname, lr.gor line_gor, round(lr.userspecifiedlinelength,0) length, lr.linecode,
round((lr.reactivepowerin*power(10,6))/1000,2) Reactive_Power_in_kVar,
round((lr.reactivepowerout*power(10,6))/1000,2) Reactive_Power_out_kVar,
round((lr.reactivepowerloss*power(10,6))/1000,2) Reactive_Power_Loss_kVar,
round((lr.realpowerin*power(10,6))/1000,2)Real_Power_in_kW,
round((lr.realpowerout*power(10,6))/1000,2) Real_Power_Out_kW,
round((lr.realpowerloss*power(10,6))/1000,2) Real_Power_Loss_kW,
round (greatest (lr.currentin, lr.currentout) * 5248.64, 2) current_in, -- Selects highest current as "Current In"
round (least (lr.currentin, lr.currentout) * 5248.64, 2) current_out, -- Selects lowest current as "Current Out"
lr.sendingnodegor,nr.userreference1, nr.nodetext3,
lr.receivingnodegor

from autoloadflow_lineresults lr,
autoloadflow_noderesults nr

where lr.sendingnodegor = nr.gor (+) and
not lr.linecode = '11kV_BUSBAR' and -- Excludes Busbar inside Substation sites
lr.groupname like '%' ) t1, -- Table 1



( -- Table 2
-- Line Results GOR and Receiving Node Gor only
-- Line Results joined to Node Results on "Receivingnodegor"

select distinct
lr.groupname, lr.gor line_gor,
lr.receivingnodegor, nr.userreference1, nr.nodetext3

from autoloadflow_lineresults lr,
autoloadflow_noderesults nr

where lr.receivingnodegor = nr.gor (+) and
not lr.linecode = '11kV_BUSBAR' and -- Excludes Busbar inside Substation sites
lr.groupname like '%' ) t2, -- Table 2



( -- Table 3
-- Group Current in DINIS Loadflow
-- For radial feeders, selects the highest current found in the loadflow Line Results
-- For group/mesh feeders, selects the sum of Group Load values from the AUTOLOADFLOW_GROUPING table
-- Note, the results rely the AUTOLOADFLOW_GROUPING table being up-to-date


select groupname, max (round (greatest (lr.currentin, lr.currentout) * 5248.64, 2)) group_current
from distplan.autoloadflow_lineresults lr
where not groupname like '___G__' and not groupname like '______G__%' -- radial feeders -- Excluding Groups
and not groupname in ('APLLYT6','APLLYT10', 'APLLYT13', 'CTXLYT7', 'CTXLYT16', 'BDB6', 'BDBBRW3', -- Excluding parallel cables
'BDBBRW8', 'VSP1')
group by groupname -- If a feeder has "0" load, the Line Results currents will be Null ie. Tie feeders

union

select groupname, sum(load) load -- Sums multiple loads for groups/mesh feeders
from autoloadflow_grouping
where groupname like '___G__' or groupname like '______G__%' -- Groups/Mesh feeders
or groupname in ('APLLYT6','APLLYT10', 'APLLYT13', 'CTXLYT7', 'CTXLYT16', 'BDB6', 'BDBBRW3', -- Parallel cables
'BDBBRW8', 'VSP1')
group by groupname
order by groupname ) t3, -- Table 3



( --Table 4
-- Groupname GRP and SD, SN, WD, WN Group Forecast Loads, Group Summer DLF, Group Wiinter DLF
-- from the Transaction Viewer for the most recent transactions before taday excluding GROWTH RATE transactions

select t4b.grp,
sum(t4a.grp_sd) grp_sd,
sum(t4a.grp_sn) grp_sn,
sum(t4a.grp_wd) grp_wd,
sum(t4a.grp_wn) grp_wn,
sum(t4a.grp_sd10) grp_sd10,
sum(t4a.grp_sn10) grp_sn10,
sum(t4a.grp_wd10) grp_wd10,
sum(t4a.grp_wn10) grp_wn10,




round( avg(t4b.SDLF) ,4) SDLF,
round( avg(t4b.WDLF) ,4) WDLF
from

( -- Table 4a, All Forecast Events from the Forecast, Partitioned and Sorted with most recent RN = 1
select lmp,
event,
dt,
round(load_50poe_sd,0) grp_sd, -- Rounds loads
round(load_50poe_sn,0) grp_sn,
round(load_50poe_wd,0) grp_wd,
round(load_50poe_wn,0) grp_wn,
round(load_10poe_sd,0) grp_sd10, -- Rounds loads
round(load_10poe_sn,0) grp_sn10,
round(load_10poe_wd,0) grp_wd10,
round(load_10poe_wn,0) grp_wn10,





row_number() over (partition by lmp order by dt desc, load_50poe_sd) rn
-- Partition data by LMP, Order by DT descending and 50POE
-- Insert row numbers for each partition

from forecast

where dt < sysdate and -- Where the transaction date is before today's date
not event = 'GROWTH RATE' and -- Ignore Growth Rate Transactions in the Forecast
not event = 'THERMAL RATING' and -- Ignore Thermal rating Transactions in the Forecast
committed = 'YES' ) t4a, -- Table 4a -- Only committed transactions


( -- Tabale 4b, LMP, GRP, Summer DLF and Winter DLF from Feeder table
select lmp,
grp,
daily_load_factor_summer sdlf,
daily_load_factor_winter wdlf

from feeders_new ) t4b -- Table 4b


where t4a.lmp = t4b.lmp and
t4a.rn = 1

group by t4b.grp ) t4, -- Table 4



( -- Table 5
-- Continuous Cable Ratings

select linecode, round(sd,0) sd_cont_rat, round(sn,0) sn_cont_rat, round(wd,0) wd_cont_rat, round(wn,0) wn_cont_rat
from ratings_oh_cyclic

union

select linecode, summer sd_cont_rat, summer sn_cont_rat, winter wd_cont_rat, winter wn_cont_rat
from ratings_ug_continuous

order by linecode ) t5 -- Table 5



where t1.line_gor = t2.line_gor and -- Join Tables 1, 2, 3, 4 & 5
t1.groupname = t3.groupname (+) and
t1.groupname = t4.grp (+) and
t1.linecode = t5.linecode




order by groupname


) t6, -- Table 6, (Tables 1, 2, 3, 4 & 5 )




( -- Table 7
-- Grp, Sub, Hub and TMU Data

select t7a.grp, t7b.sub_id, t7c.alpha sub, t7b.hub, t7a.tmu

from

(select distinct grp, avg(target_maximum_utilisation)*100 tmu
from feeders_new
where grp in (select groupname from autoloadflow_lineresults) group by grp) t7a, -- Table 7a

(select distinct grp, hub, sub_id
from feeders_new where grp in (select groupname from autoloadflow_lineresults) ) t7b, -- Table 7b

(select id, alpha from substations ) t7c -- Table 7c

where t7a.grp = t7b.grp and
t7b.sub_id = t7c.id ) t7, -- Table 7




( -- Table 8
-- DINIS Loadflow Summary Results

select groupname, errornotes, daterun
from autoloadflow_summaryresults ) t8, -- Table 8



( -- Table 9
-- DINIS Loadflow Voltage Regulation

select groupname, ( max(voltagemagnitude)-min(voltagemagnitude) ) *100 dinis_grp_volt_reg
from autoloadflow_noderesults
group by groupname ) t9, -- Table 9



( -- Table 10
-- DINIS Group Power Loss

select groupname, round (sum((realpowerloss*100*power(10,6))/1000),2) dinis_real_power_loss
from autoloadflow_lineresults
group by groupname ) t10, -- Table 10



( -- Table 11
-- Autoloadflow_Limits_Projects

select project_no, group_name,

(case when (rating_limit = 'YES' and voltage_limit is null) then 'RATING'
when (rating_limit is null and voltage_limit = 'YES') then 'VOLTAGE'
when (rating_limit = 'YES' and voltage_limit = 'YES') then 'RATING and VOLTAGE'
when (rating_limit is null and voltage_limit is null) then null
else 'XXXXXXXX' end) project_type,

from_, to_, dinis_line_gor, comments, pow, last_update_time, date_completed

from autoloadflow_limits_projects
order by group_name ) t11 -- Table 11




where t6.groupname = t7.grp and -- Join Tables 6, 7, 8, 9, 10 & 11
t6.groupname = t8.groupname and
t6.groupname = t9.groupname and
t6.groupname = t10.groupname and
t6.line_gor = t11.dinis_line_gor (+)

)

)


where groupname like '%' and
hub like '%'

order by groupname, worst_utilisation desc
  • 1. Re: Query/View Optimisation for Oracle8i
    damorgan Oracle ACE Director
    Currently Being Moderated
    The first step would be to upgrade to a version of the product supported during the current millennium. That would be 10.2.0.4 or above.

    Your version of Oracle is so old I, to be honest, I haven't seen a copy in 10 years. But of equal importance is that no one is going to help you
    tune this unformatted verbose disaster when you didn't include a version number (no 8i is not a version number), any DDL for tables or indexes,
    or what I would call the bare minimum ... an explain plan.

    Upgrade to something real, run an explain plan, then come back and perhaps we can help you.
  • 2. Re: Query/View Optimisation for Oracle8i
    Sven W. Guru
    Currently Being Moderated
    Find a way to remove all the DISTINCT keywords. There is a good chance that this is slowing your query down a lot and prevents oracle to use use sensible optimizing approches.
  • 3. Re: Query/View Optimisation for Oracle8i
    CharlesHooper Expert
    Currently Being Moderated
    In general, when posting code to the forum, please use a { code } tag (without spaces) before and after the code.

    That is a long SQL statement. Splitting the SQL statement up into multiple views likely will not help performance, but could hurt performance.

    A couple comments about the SQL statement:
    * "SELECT DISTINCT" repeated in many places, including in an inline view using a GROUP BY clause. Are all of the DISTINCT clauses necessary?

    * ORDER BY is frequently used in the inline views: "order by group_name" in the T11 inline view, "order by groupname" in the T6 inline view, "order by groupname" in the T3 inline view, "order by linecode" in the T5 inline view. Remove those ORDER BY clauses.

    * "UNION" is used in T3 inline view - is it possible to replace that with a "UNION ALL"?

    * IN clauses may be inefficient in Oracle 8i, consider switching to an EXISTS clause or transforming the IN clause to an inline view.

    * Odd LIKE syntax in several places: where "groupname like '%' and hub like '%'", "lr.groupname like '%'" in T1 inline view, "lr.groupname like '%'" in T2 inline view.

    * The table autoloadflow_lineresults is accessed many times - is that a large table? Is it possible to consolidate the queries accessing that table?

    * The WHERE clauses seem to limit the usefulness of any indexes which may exist.

    * It appears that you want the results from the T6 inline view (which join the T1, T2, T3, T4, and T5 inline views) to drive into the T7, T8, T9, T10, and T11 inline views - does the execution plan show that is happening?

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 4. Re: Query/View Optimisation for Oracle8i
    725270 Newbie
    Currently Being Moderated
    Thankyou

    Simply removing the order by and like clauses not required and removing the distinct selection from the select statment where tables 6, 7, 8, 9, 10 & 11 are joined turned this from a 6min query to a 1min query.
  • 5. Re: Query/View Optimisation for Oracle8i
    CharlesHooper Expert
    Currently Being Moderated
    user8770951 wrote:
    Thankyou

    Simply removing the order by and like clauses not required and removing the distinct selection from the select statment where tables 6, 7, 8, 9, 10 & 11 are joined turned this from a 6min query to a 1min query.
    Thanks for the update regarding the performance improvement.

    Please also take a look at the other suggestions I provided to you. I would start with converting the IN clause(s) to either an EXISTS clause or to a join.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points