Re: SQL running very slow

Hi Amit,

From your first email, it looks like you just need to gather some stats - it thought it would be touching one row (or less) when really it was a lot more. Make sure your stats gathering philosophy is shared between your environments.

A profile will help to force individual cardinalities for specific SQL but you probably have several queries that aren’t running optimally.

Adaptive features is very much a personal decision on 12c, but again you should be using the same on dev as on prod (whether both on or both off).

Thanks,
Andy

On Tue, 26 Oct 2021 at 18:49, Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Thanks for the inputs. Mark. We have imported the profile from the lower
> environment into production and it works fine today.
>
> optimizer_adaptive_features is set to false in our database. Do you think
> enabling it can help avoid such issues?
>
>
> Best Regards,
> AMIT
>
>
> On Mon, Oct 25, 2021 at 2:49 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
>> in the html you sent all the estimated rows were 1. Since 1 doesn’t need
>> a hash, Oracle always skips building a hash and does the one loop of nested
>> loops for estimates of 1.
>>
>>
>>
>> I suggest you compare the statistics between your lower and upper
>> envionments.
>>
>>
>>
>> Good luck,
>>
>>
>>
>> mwf
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Amit Saroha
>> *Sent:* Monday, October 25, 2021 2:01 PM
>> *To:* Laurentiu Oprea
>> *Cc:* Andy Sayer; ORACLE-L (oracle-l_at_freelists.org)
>> *Subject:* 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