7 Replies Latest reply: Aug 7, 2013 10:01 PM by 1014077 RSS

    Hanging Procedure

    1014077

      Hello,

       

      We recently moved to Oracle 11g and I am using Toad 10. I am currently working on a procedure which seemed to work fine before but have recently

      started hanging, not sure if it's something related to the environment or it could be that the same issues were coming up earlier but we never noticed them.

       

      To figure out the issue, this is what I am doing:

       

      I compile the procedure in debug mode in Toad, set the break-point at the very top of the procedure,

      then start tracing the code, surprisingly, the control moves very fast through the entire code, never hanging anywhere and finally when it reaches the following 'end' statement of the procedure,

      it gets stuck there for about a minute or so -

       

      procedure ...

      end; --Control gets stuck here at the very end and then after sometime comes back with the results

      /

       

      There are no errors or exception thrown, can someone please tell me, how to know what's causing it to get stuck there ?.

       

      Thanks,

        • 1. Re: Hanging Procedure
          Billy~Verreynne

          The very first step in diagnosing a problem is identifying the problem.

           

          The "procedure is hanging" is not the the problem. It hangs because of the problem.

           

          So you need to look pass the symptoms at what the actual problem is.

           

          In this case, let the hang happen, open another session and via it, determine what the first session's wait state is for that hung procedure. Is it waiting for a library lock? A row lock? Etc.

           

          Alternative post the source code for us to look at, and comment on. (but there is no guarantee that the problem is in the code)

          • 2. Re: Hanging Procedure
            1014077

            Thank you Billy for the quick reply, I did do what you had suggested in your post, but the wait state had nothing in there when the process was executing and waiting to return back the results, can you please let me know what queries you would execute to accomplish the same ?.

             

            At the very end of the debugging session, I am displaying all the ref_cursors, and that's where the control waits, when we display the ref-cursor, is that when the select related to that cursor gets called/run ?, I thought, when we open that cursor with the select, at that very moment the results are being stored in a memory location and when the ref_cursor is being displayed, it just has to fetch the results from the memory and display it ?. Is that how it is supposed to work ?.


            Any information on this would be highly appreciated.


            Thanks,

            • 3. Re: Hanging Procedure
              9423755

              Have you tried tracing the code using DBMS_TRACE, DBMS_PROFILER, or DBMS_HPROF? Seems looking at locks etc. is going down to a very low level when a higher-level view of where your code is bottlenecking (hmm is that even a word?) might be more useful.

              But hey, I'm just a humble student

              • 4. Re: Hanging Procedure
                Ishan

                This could be because of STALE stats. Did you do DBMS_STATS.GATHER_TABLE_STATS on the table which is being used in REF CURSOR.

                 

                I guess you need to try the run the query used in REF CURSOR as a stand alone query. Check if it's taking more time or giving the result in expected time.

                 

                It would be better if you could post the actual code, else all the answers that you'll get will be based on experience and guess

                 

                Thanks,

                Ishan

                • 5. Re: Hanging Procedure
                  Billy~Verreynne

                  2a000fab-b238-42a9-b154-f9e2877fa698 wrote:

                   

                  I did do what you had suggested in your post, but the wait state had nothing in there when the process was executing and waiting to return back the results

                  Make sure that you identify the SID of the Oracle session running the PL/SQL code, correctly. A session will always have a wait state. Even an idle session.

                   

                  The only times I have seen less than accurate wait states is when a database call is followed by a kernel call. If the session is waiting on the kernel call, it may report the prior wait state as this was not updated to reflect the kernel call.

                   

                  At the very end of the debugging session, I am displaying all the ref_cursors, and that's where the control waits, when we display the ref-cursor, is that when the select related to that cursor gets called/run ?, I thought, when we open that cursor with the select, at that very moment the results are being stored in a memory location and when the ref_cursor is being displayed, it just has to fetch the results from the memory and display it ?. Is that how it is supposed to work ?.

                  No. A cursor is NOT a result set.

                   

                  Imagine for a moment a busy OLTP session with 100's of user sessions doing queries - if each of these cursors cached data in memory, just how long will memory last? Just how would you be able to scale the server to service that many user sessions?

                   

                  A cursor is a program. A cursor fetch is an execution iteration of that program, upon which the program finds the next relevant set of rows and output these. After which the program is in a paused state until the next fetch call is received from the client.

                   

                  You need to provide more details as to what your PL/SQL code does, how it creates ref cursors, and how the client calls this PL/SQL code.

                  • 6. Re: Hanging Procedure
                    9423755

                    Tom Kyte usefully says this in his book (Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions: Thomas Kyte: 9781590595305: Amazon…):

                     

                    ops$tkyte%ORA11GR2> create table t

                    2 as

                    3 select *

                    4 from all_users

                    5 /

                    Table created.

                     

                    ops$tkyte%ORA11GR2> set autoprint off

                    ops$tkyte%ORA11GR2> variable x refcursor;

                    ops$tkyte%ORA11GR2> begin

                    2 open :c for select * from t;

                    3 end;

                    4 /

                    PL/SQL procedure successfully completed.

                     

                    ops$tkyte%ORA11GR2> print c

                     

                    "Oracle does not copy the data anywhere when you open a cursor—imagine how long it would take to open a cursor on a one-billion row table if it did. The cursor opens instantly and it answers the query as it goes along. In other words, the cursor just reads data from the table as you fetch from it."

                    • 7. Re: Hanging Procedure
                      1014077

                      Thank you everyone for all the useful suggestions, I finally found the issue: It's one of the selects related to a cursor, once I fixed that select, everything started running smoothly. My earlier assumption that Oracle caches the values when a cursor is opened made be blind to the fact that there could be something wrong with the selects itself. But after going through your posts and many different ones over the net, I have come to understand a little more about how a cursor actually works. If anybody is interested, here is a link from AskTom which I found very useful:Ask Tom "Where does the Cursor stores the rows ?"

                       

                      Thanks again,