Pro*C procedure call hangs indefinitely

Question

I have a multithreaded Pro*C program which calls anonymous stored procedures in each thread on separate connections and runtime contexts.

My anonymous procedure calls takes different time frames to return from the procedure and sometimes it even hangs indefinitely. My Oracle procedure takes just 0.05 seconds to return as it is shown in AWR logs but surprisingly the Pro*C call takes 5 seconds to return from procedure.

What is the processing activity involved between the Pro*C procedure call and actual Oracle procedure execution? Are any locks or other blocking issues?

Answer 1

If it's hanging indefinitely, then yes, there is some type of blocking involved (or polling, etc. ... something is happening in the function causing it not to return).

From your other question you posted on this matter, if you want to just kill a specific thread that has hanged, then on thing you can look at is to setup your thread ID's as a struct that has a "finished" flag in it.

#include <pthread.h>
#include <signal.h>

struct thread_id
{
    pthread_t thread;
    sig_atomic_t thread_flag;
};

void init_thread_id(struct thread_id* id)
{
    id->thread_flag = 0;
}

thread_id threads[NUMBER_OF_THREADS];

void* thread_function(void* arg)
{
    thread_id* my_id = (thread_id*)arg;

    //do something in your thread

    //when you finish, set the flag for that thread
    my_id->thread_flag = 1;
}

Now when you set your timeout alarm, simply scroll through the array of thread_id, and see which ones are finished. The ones that are finsihed, you can call pthread_join on, otherwise you can send a signal to the thread using pthread_kill or pthread_cancel to stop the thread.

Answer 2

You could try an Oracle trace (DBMS_MONITOR) and see what the last activity is in the trace file. The only thing I can think of that might properly account for the time is if the procedure returns a large value (BLOB, CLOB, XML) which is taking time to return back to the client over the network (or where the client chokes on the size of received data).