Home » RDBMS Server » Performance Tuning » view slowness.. (11.2.0.2, WIndows 2008 R2)
view slowness.. [message #621579] Thu, 14 August 2014 10:39 Go to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Hi Friends,

We are troubleshooting a bunch of performance issues on the application, notice that several queries that is slow(most time spent in parsing the SQL) involve the below view. Please give me your thoughts if it can be tuned or change the definition for better performance..

create or replace view st_view as
select sst.s_id,
       sst.mdr,
       sst.goc,
       sst.s_item,
       sst.b_item,
       sst.s_ityp,
       je.jkey || '/' || je.ed_no o_cde,
       sst.i_code,
       je.j_tle title,	
       je. js_tle sub,
       je.jsh_tle short,
       je.j_pbs pubsr,
       sst.created_user,
       sst.created_date,
       sst.mod_user,
       sst.mod_date,
       myp_func('pubs', je.p_stat, sst.mdr, '', 'trm') status,
       je.s_date as s_date,
       sst.o_id o_id,
       je.med med,
       je.med2 med2,
       je.rkey as rkey,
       null as ibn,
       null as ibn13,
       null as o_no,
       null as pn_al,
       to_number(null) as im_no
  from si_tble sst, j_edi je
where sst.s_id = je.s_id
   and sst.s_ityp = 'je'
union
select distinct sst.s_id,
                sst.mdr,
                sst.goc,
                sst.s_item,
                sst.b_item,
                sst.s_ityp,
                decode(pi.ibn,
                       null,
                       pi.o_no,
                       pi.ibn || '-' || pi.ibn_ctrl) o_cde,
                sst.i_cde,
                pe.tle title,
                pe.subtle sub,
                pe.sh_tle short,
                pe.p_hse pubsr,
                sst.create_user,
                sst.create_date,
                sst.mod_user,
                sst.mod_date,
                myp_stat.status,
                null as s_date,
                sst.o_id o_id,
                pe.med med,
                pe.med2 med2,
                null as rkey,
                decode(who_func.al_cd1,
                       'y',
                       decode(pi.ibn,
                              null,
                              null,
                              pi.ibn || '-' || pi.ibn_ctrl),
                       decode(pi.ibn_old,
                              null,
                              null,
                              pi.ibn_old || '-' || pi.ibn_ctrl_old)) as ibn,
                decode(pi.ibn,
                       null,
                       null,
                       pi.ibn || '-' || pi.ibn_ctrl) as ibn13,
                pi.o_no as o_no,
                pi.pn_al as pn_al,
                pi.im_no as im_no
  from si_tble sst,
       p_imp pi,
       p_edit pe,
       (select myp.mdr, myp.code, myp.trm as status
          from myp mdt
         where myp.type = 'stat') myp_stat,
       (select myp.mdr, myp.al_cd1
          from myp mdt
         where myp.type = 'who'
           and myp.code = 'ibn13') who_func
where pi.im_id = sst.o_id
   and sst.s_ityp in ('prd', 'osp')
   and pi.ed_id = pe.ed_id
   and (myp_stat.mdr is null or myp_stat.mdr = sst.mdr)
   and myp_stat.code(+) = pi.status
   and (who_func.mdr is null or who_func.mdr = sst.mdr);


Really appreciate all your help.. THank you so much...

[Updated on: Thu, 14 August 2014 10:56]

Report message to a moderator

Re: view slowness.. [message #621581 is a reply to message #621579] Thu, 14 August 2014 11:00 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
sant_new1 wrote on Thu, 14 August 2014 16:39
(most time spent in parsing the SQL)



Are you sure?
Re: view slowness.. [message #621582 is a reply to message #621581] Thu, 14 August 2014 11:12 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Yes.. Most of the SQL's involving this view spends more time in parsing.. Below is the tkprof output of one of the SQL query (involving the above view)..
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.18       0.19          0          8          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.18       0.19          0         11          0           0

Rows     Row Source Operation
-------  ---------------------------------------------------
....................
...................
....................
   1   VIEW  ST_VIEW (cr=9 pr=0 pw=0 time=454 us cost=9 size=666 card=1)
      1    SORT UNIQUE (cr=9 pr=0 pw=0 time=452 us cost=9 size=2538 card=2)
...........
..............
............

Above is the row source operation of the SQL query, I just pasted the View portion of the operation.. It appears that the view does take more time in the execution of the SQL.. Appreciate all your suggestions...

Thanks much


[EDITED by LF: applied [code] tags]

[Updated on: Fri, 15 August 2014 12:10] by Moderator

Report message to a moderator

Re: view slowness.. [message #621606 is a reply to message #621582] Thu, 14 August 2014 15:38 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Could you please provide a tkprof output for one of your problematic sqls.
Re: view slowness.. [message #621607 is a reply to message #621606] Thu, 14 August 2014 15:52 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
if you reduced total elapsed time to ZERO, do you think anyone would actually notice.

trying to tune a query that starts with sub-second response time is proof positive that you suffer from Compulsive Tuning Disorder!
Previous Topic: cache issue
Next Topic: oracle performance metrics
Goto Forum:
  


Current Time: Thu Mar 28 07:19:09 CDT 2024