Re: SQL running very slow

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