Re: SQL running very slow

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




text/plain attachment: snapper_and_lower_plan.txt