Stored procedure performs terribly - increase timeout or fix the problem

Question

I've inherited a front end written by a third-party. That front end interacts with Oracle through procedures written by a different third-party. The stored procedure in question requires 2 minutes and 36 seconds to return search results when it is manually executed. I can't see the procedure and that team has suggested that I increase the timeout in the Web application (hosted on a shared server).

In my world, anything over 30 seconds would require a performance fix before being deployed to production with a few exceptions (legacy code, crazy reports, etc.). The option that was suggested to me was to increase the timeout from 30 seconds (explicitly added by the front end developer) to 180 seconds.

My question to you: What are the risks with taking the easy approach and increasing the timeout? If possible, please provide links to articles that support your views so I can reference them.

Also feel free to chime in if you believe this is a non-issue.

Answer 1

You should not increase the timeout to hide the problem. You've positively identified a performance problem -- it's something that should be fixed, not swept under the rug.

Two things to do:

Get a SQL trace of the stored procedure.

exec dbms_monitor.session_trace_enable(binds => false, waits => true);
exec poor_performing_procedure();
exec dbms_monitor.session_trace_disable();

Look to see what statements are being run how often, and how much time is spent running them.

Add hooks into DBMS_PROFILER in the code for the stored procedure.

I've got code like this in all my packages so that I can determine whether or not to profile them by setting a package variable:

PROCEDURE profiler_control(p_start_stop IN VARCHAR2, p_run_comm IN VARCHAR2, p_ret OUT BOOLEAN) AS
  l_ret_code INTEGER;
BEGIN
  l_ret_code:=dbms_profiler.internal_version_check;
  IF l_ret_code !=0 THEN
    p_ret:=FALSE;
  ELSIF p_start_stop NOT IN ('START','STOP') THEN
    p_ret:=FALSE;
  ELSIF p_start_stop = 'START' THEN
    l_ret_code:=DBMS_PROFILER.START_PROFILER(run_comment1 => p_run_comm);
    IF l_ret_code=0 THEN
      p_ret:=TRUE;
    ELSE
      p_ret:=FALSE;
    END IF;
  ELSIF p_start_stop = 'STOP' THEN
    l_ret_code:=DBMS_PROFILER.FLUSH_DATA;
    l_ret_code:=DBMS_PROFILER.STOP_PROFILER;
    IF l_ret_code=0 THEN
      p_ret:=TRUE;
    ELSE
      p_ret:=FALSE;
    END IF;
  END IF;
END profiler_control;

Inside the procedures, then, there's code like:

create or replace procedure poorly_performing_procedure() 
begin
  if run_profiler then
    profiler_control('START', 'poorly_performing_procedure', g_retval);
  end if;
...
  if run_profiler then
    profiler_control('STOP', 'poorly_performing_procedure', g_retval);
  end if;
end poorly_performing_procedure;
/

Oracle provides scripts (one named profiler.sql) you can use to get pretty reports to show how many times each PL/SQL statement/operation was executed during a run. Here's a link to the DBMS_PROFILER documentation for 10g.