Forum Stats

  • 3,837,625 Users
  • 2,262,276 Discussions
  • 7,900,331 Comments

Discussions

Categories

Query gets hanged during first of month

User_DDYV1
User_DDYV1 Member Posts: 4 Green Ribbon
edited Mar 2, 2022 7:21PM in General Database Discussions

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.

Tagged:
· Share on TwitterShare on Facebook

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    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.

    · Share on TwitterShare on Facebook
  • User_DDYV1
    User_DDYV1 Member Posts: 4 Green Ribbon

    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.

    · Share on TwitterShare on Facebook
  • JohnWatson2
    JohnWatson2 Member Posts: 4,469 Silver Crown

    And if you run the statement from SQL*Plus?

    · Share on TwitterShare on Facebook
  • tsangsir
    tsangsir Member Posts: 486 Silver Badge
    edited Mar 3, 2022 7:55AM

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

    · Share on TwitterShare on Facebook
  • User_DDYV1
    User_DDYV1 Member Posts: 4 Green Ribbon

    @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.

    · Share on TwitterShare on Facebook
  • JohnWatson2
    JohnWatson2 Member Posts: 4,469 Silver Crown

    @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.

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    and copy/paste it all here.

    And please be sure to post it as code-formatted

    · Share on TwitterShare on Facebook
  • User_DDYV1
    User_DDYV1 Member Posts: 4 Green Ribbon

    @JohnWatson2 , @EdStevens 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).

    · Share on TwitterShare on Facebook