5 Replies Latest reply: Oct 6, 2009 9:17 PM by Charles Hooper RSS

    Query/View Optimisation for Oracle8i

    725270
      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
          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.
            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
              Charles Hooper
              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
                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
                  Charles Hooper
                  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.