Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query gets hanged during first of month

User_DDYV1Mar 2 2022 — edited Mar 2 2022

Hello, I have a oracle query (merge statement) that runs every day. But during the first few days of the month , it gets hanged whereas other days it is fine.
I tried running manually and the execution time is done but this screen stay for a long time (more than an hour)
image.png
Can some one assist me on how do I find what's running on the background and how do I troubleshoot ?
Thanks in advance.

Comments

EdStevens

You don't give much to go on. Please copy and paste the text of the entire statement/procedure/script. Please explain in detail how it is being submitted.

User_DDYV1

Due to security issues, I may not be able to copy the entire scripts here. But I'm trying to run a merge statement which is something like below. Usually this statement takes a minute to run and merge the data into the dimension table, but every first of month it runs forever and hangs there.
merge into table d using
( select * from (
select key , id , id1 , sysdate , row_number() over (partition by key order by v.id) as row_num
from f_transaction f,
(select ID from f_import v where date = 'Today'sDate' ) v
where f.id = v.id
and f.date = 'today's date' )
where row_num = 1 ) x
on (d_key = x.key
when matched then update set
d.id = x.id
, d.dw_update_date = x.dw_update_date
when not matched then
insert ( id , Key, Date)
values ( x.id , x.key , x.date);

When running this merge statement in toad, it executes till 23 seconds, then it stays there and stops counting. Toad then get hanged. I checked in the session monitor for any locks and made sure no other jobs are running parallelly.
So need some help here to know how to troubleshoot this.

JohnWatson2

And if you run the statement from SQL*Plus?

tsangsir

And are the numbers of rows inf_transaction and f_import more or less the same throughout the month?

User_DDYV1

@johnwatson2 , The same issue with SQL plus as well.
@tsangsir , the rows gets added each day , so these two tables keeps growing. These two tables already have millions of records, we do have index on these tables as well.

JohnWatson2

JohnWatson2 , The same issue with SQL plus as well.
Do you mean that it runs to completion in 23 seconds? Or do you mean that it hangs forever?
A simple way to gather a bit more information, in SQL*Plus, would be to

set timing on
set autotrace on
merge into......

and copy/paste it all here.

EdStevens

and copy/paste it all here.
And please be sure to post it as code-formatted

User_DDYV1

@johnwatson2 , @edstevens1 Thanks , when I run the same query in SQL plus, it hangs there as well.
the thing is, I ran it today, the same merge statement and I got the result in 23 seconds. But I'm facing this issue only during the first of month (Every time).

1 - 8

Post Details

Added on Mar 2 2022
#general-database-discussions, #performance
8 comments
197 views
3 mentions