Re: SQL running very slow

Thanks Amit.

I wanted to check if there are any undo records applied (that would have suggested changes of that index during this query execution) but this is not the case.

The plan is using the NLs (and push predicate into the view) most probably because of the underestimate of the full table scan XXIM01T_SPP_PLAN_EXTRACT_TAB (1 row)

În lun., 25 oct. 2021 la 21:01, Amit Saroha <eramitsaroha_at_gmail.com> a scris:

> Thanks, Laurentiu,
>
> Enclosed are the details of snapper and plan details from the test
> environment. I can see it's all hash joins in the lower environment plan.
>
> Why would Oracle change the plan in production is a mystery to me now.
>
>
> Best Regards,
> AMIT
>
>
> On Mon, Oct 25, 2021 at 1:48 PM Laurentiu Oprea <
> laurentiu.oprea06_at_gmail.com> wrote:
>
>> Hello Amit,
>>
>> Most probable you cannot do anything to speed this query up while it is
>> running.
>>
>> Is highly probable that Andy is correct, but just for completeness can
>> you download Tanel`s snapper from below link and run: SQL> snapper all 60 1
>> sid_of_you_session
>> and attach it.
>>
>> https://github.com/tanelpoder/tpt-oracle/blob/master/snapper.sql
>>
>> În lun., 25 oct. 2021 la 20:38, Amit Saroha <eramitsaroha_at_gmail.com> a
>> scris:
>>
>>> Thanks, Andy.
>>>
>>> I can't cancel the query or kill the session to inject hints
>>> because it's part of a batch and more processes has to complete after this
>>> query. Is it possible to do some workaround?
>>>
>>>
>>> Best Regards,
>>> AMIT
>>>
>>>
>>> On Mon, Oct 25, 2021 at 1:30 PM Andy Sayer <andysayer_at_gmail.com> wrote:
>>>
>>>> Hi Amit,
>>>>
>>>> You are doing a nested loop full index scan. Either get it to do that
>>>> as a hash join or find it a better index which can be accessed with the
>>>> join predicates.
>>>>
>>>> Thanks,
>>>> Andy
>>>>
>>>> On Mon, 25 Oct 2021 at 18:15, Amit Saroha <eramitsaroha_at_gmail.com>
>>>> wrote:
>>>>
>>>>> Hi Listers,
>>>>>
>>>>> Database version - 12.0.1
>>>>>
>>>>> I have a query that is running from long but couldn't understand the
>>>>> reason for the long run. I have enclosed the SQL monitoring and SQL plan
>>>>> for reference.
>>>>>
>>>>> In LongOps it shows its reading blocks but doesn't know why reading is
>>>>> so slow. Could you please suggest what could cause such an issue?
>>>>>
>>>>>
>>>>> Best Regards,
>>>>> AMIT
>>>>>
>>>>

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