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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/2bc90a204347926c56d9c671c2ad24bd?s=64&d=mm&r=g) |
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 #621582 is a reply to message #621581] |
Thu, 14 August 2014 11:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/2bc90a204347926c56d9c671c2ad24bd?s=64&d=mm&r=g) |
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 #621607 is a reply to message #621606] |
Thu, 14 August 2014 15:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
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!
|
|
|
Goto Forum:
Current Time: Sat Sep 07 20:09:39 CDT 2024
|