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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Amit Saroha
Sent: Monday, October 25, 2021 2:01 PM
To: Laurentiu Oprea
Cc: Andy Sayer; ORACLE-L (oracle-l@xxxxxxxxxxxxx)
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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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
