Re: Query runtime is slow in view

Hi,

since it is rather late here the short answer: There seems to be an Index missing on  po_headers_all (ORG_ID,ATTRIBUTE1, TYPE_LOOKUP_CODE, CLOSED_CODE,CANCEL_FLAG) . There is more to be said about good indexing, but not now. I will return to my bed.😉

Regards

Lothar

Am 02.02.2021 um 23:21 schrieb Amit Saroha:
> Thank you for the clarification and as suggested I have enclosed the
> SQL monitoring report here. Kindly review if you have some time to
> help me.
>
>
> Best Regards,
> Amit
>
>
> On Mon, Feb 1, 2021 at 1:30 PM Lothar Flatz <l.flatz_at_bluewin.ch
> <mailto:l.flatz_at_bluewin.ch>> wrote:
>
> Hi,
>
> first of all Sql Devloper will only retrieve the first 50 (by
> default)
> rows. Timing in SQL Developer can not be trusted if the result is
> > 50 rows.
>  From your description I do not know if whatever you query in SQL
> Developer is really the equivalent of selecting from the (tuned) view.
> There is a good chance that you are dealing with two diffent queries.
> The best way simulating a not yet created view is a subquery in
> the from
> clause. Even than it is not sure that we really get the same
> execution
> plan when we query the stored view.
> We would need run time statistics to determine what is going on.
> Working without exact measurement is unscientific.
>
> Regards
>
> Lothar
>
>
>
>
>
> Am 01.02.2021 um 18:14 schrieb Amit Saroha:
> > Good Morning, Everyone,
> >
> > I am in need of your expert suggestions for a weird situation
> and any
> > pointers are appreciated.
> >
> > The situation is - There’s a view containing a UNION query, which I
> > tuned and results are coming in less than a second when running
> in SQL
> > developer. But, when I replaced the existing view query with the
> tuned
> > query and select it from the view then it started taking more
> than 2
> > minutes.
> >
> > I have never seen such a situation before when the query is running
> > within a second but when used inside view starts taking the time.
> >
> >
> > Best Regards,
> > Amit
>
> --
> http://www.freelists.org/webpage/oracle-l
> <http://www.freelists.org/webpage/oracle-l>
>
>

--
http://www.freelists.org/webpage/oracle-l