Re: Query runtime is slow in view

Hello Amit,

To make sure you receive better advice in the execution with sql developer where you mention is running fast include /*+ monitor */ hint and attach that sql monitor as well.

Thanks.

În mie., 3 feb. 2021 la 00:21, Amit Saroha <eramitsaroha_at_gmail.com> a scris:

> 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> 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