Forum Stats

  • 3,759,183 Users
  • 2,251,510 Discussions
  • 7,870,526 Comments

Discussions

Categories

How do i find out which query is hanging the stored procedure?

gg
gg Member Posts: 120 Blue Ribbon
edited Nov 5, 2019 6:03AM in SQL & PL/SQL

Hi All,

    I have a stored procedure which is running forever. The particular stored procedure reads a file and performs validation and inserts into some transaction table.

    There are many stored procedures that are called within the main procedure. There might be locks or blocks or some query is taking long time to execute

    How do i find out which query in which procedure is taking long time to execute? But sometimes the same procedures completes successfully within few seconds inserting

    all the records (10000 recs).

Thanks

Gautam S

Tagged:
gg
· Share on TwitterShare on Facebook

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 4, 2019 9:25PM 
    580988 wrote:Hi All, I have a stored procedure which is running forever. The particular stored procedure reads a file and performs validation and inserts into some transaction table. There are many stored procedures that are called within the main procedure. There might be locks or blocks or some query is taking long time to execute How do i find out which query in which procedure is taking long time to execute? But sometimes the same procedures completes successfully within few seconds inserting all the records (10000 recs).ThanksGautam S

    Consider to issue SQL below just prior to starting the rogue procedure.

    ALTER SESSION SET SQL_TRACE=TRUE;  -- ensure the USER has necessary privilege to successfully run this SQL statement

    process resultant trace file using TKPROF

    gggg
    · Share on TwitterShare on Facebook
  • gg
    gg Member Posts: 120 Blue Ribbon
    edited Nov 4, 2019 10:39PM

    I just ran the main procedure now and it ran successfully within few seconds. I don't know why it was not running earlier 1 hour ago. Can someone throw some light on this?

    Thanks

    Gautam

    · Share on TwitterShare on Facebook
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 4, 2019 10:50PM 
    580988 wrote:I just ran the main procedure now and it ran successfully within few seconds. I don't know why it was not running earlier 1 hour ago. Can someone throw some light on this?ThanksGautam

    Something changed, but we don't know what you have.

    Does any of the SQL in your mystery procedure use bind variables?

    gggg
    · Share on TwitterShare on Facebook
  • gg
    gg Member Posts: 120 Blue Ribbon
    edited Nov 4, 2019 11:11PM

    No, none of the SQL use bind variables. My procedure calls the innermost procedure which is a PRAGMA AUTONOMOUS procedure.

    FYI : The innermost procedure has a FOR UPDATE in a select clause. I have issued proper COMMIT/ROLLBACK also in that procedure. Does it have something to do with that? Also, the total record count i am processing is 10000.

    Thanks

    Gautam

    · Share on TwitterShare on Facebook
  • gg
    gg Member Posts: 120 Blue Ribbon
    edited Nov 4, 2019 11:39PM

    @John Thorton

    However the records are processed successfully and inserted into relevant tables. Only the session keeps hanging in SQL Developer.

    Let me know if you need anything else. If the session keeps hanging probably the application would also keep hanging in Production.

    Can you someone kindly help on this.

    Thanks

    Gautam

    · Share on TwitterShare on Facebook
  • gg
    gg Member Posts: 120 Blue Ribbon
    edited Nov 5, 2019 1:17AM

    Hi all,

    It's solved. The hanging was due to the excessive dbms_output messages being populated for each of the 10000 records.

    · Share on TwitterShare on Facebook
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited Nov 5, 2019 5:47AM 
    580988 wrote:No, none of the SQL use bind variables.

    Are you using dynamic SQL? Because unless you are, all your SQL will definitely be using bind variables. PL/SQL sorts that out automatically.

    · Share on TwitterShare on Facebook
  • BEDE
    BEDE Oracle Developer Member Posts: 2,283 Gold Trophy
    edited Nov 5, 2019 6:00AM

    The most simple thing is to use a log table and log there when the code gets to some place using a store procedure with pragma autonomous_transaction.

    Or, you may see what shows in the sys.gv_$open_cursor and sys.gv_$sql for that session of yours, like below:

    select sq.*

    from sys.gv_$open_cursor oc

    join sys.gv_$sql sq on oc.sql_id=sq.sql_id and oc.inst_id=sq.inst_id

    where oc.sid=&sid and oc.inst_id=&inst_id

    and sq.users_executing>0

    ;

    If it's quite difficult to know which your session is, you may mark that session using the facilities of package dbms_application_info.

    · Share on TwitterShare on Facebook
  • BEDE
    BEDE Oracle Developer Member Posts: 2,283 Gold Trophy
    edited Nov 5, 2019 6:03AM

    If you suspect there may be some session blocking:

    Select * from sys.gv_$session_blockers

    ;

    And see what you get.

    · Share on TwitterShare on Facebook