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@xxxxxxxxxx <mailto:l.flatz@xxxxxxxxxx>> 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
--
//www.freelists.org/webpage/oracle-l
<//www.freelists.org/webpage/oracle-l>