Skip to Main Content
  • Questions
  • Pro*C procedure call taking longer time

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sudhanshu.

Asked: September 11, 2017 - 6:33 am UTC

Last updated: September 12, 2017 - 2:15 am UTC

Version: 11 g

Viewed 1000+ times

You Asked

I have below code in pro*c

EXEC SQL CALL SP_INTL_BATCH(:l_wrapper, :l_func, :p_env_data, :l_paramstr, :l_user,:l_error);

This simple code taking 12 to 13 second to execute and move to next line , while if i normally execute store procedure from oracle sql developer it executes within max 1/2 seconds,Is this anything related to call to oracle from front end taking time, Please suggest any solution can be done with this.

and Connor said...

Things to check

1) Make sure you are testing like for like, ie, using binds in both cases, using the same bind variable values and datatypes

2) Do you see the same behaviour with EXECUTE ie

EXEC SQL EXECUTE 
   BEGIN 
      SP_INTL_BATCH(:l_wrapper, :l_func, :p_env_data, :l_paramstr, :l_user,:l_error);
   END; 
   END-EXEC; 


3) Enable tracing in Pro*c just before the call, eg

EXEC SQL EXECUTE 
   BEGIN 
      dbms_monitor.session_trace_enable(waits=>true,binds=>true);
   END; 
   END-EXEC; 


and disable it after the call and examine the trace file to see if the slow down is *in* the plsql code, or to do with the call between pro*c and plsql

4) Is it *all* procedure calls, or just this one ?



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database