The stored procedure performs horribly - increases the timeout or fixes the problem

Question

I inherited a third-party interface. This interface interacts with Oracle through procedures written by another third party. It takes 2 minutes and 36 seconds to save the search results in order to return the search results when they are performed manually. I do not see this procedure, and this team suggested increasing the timeout in the web application (hosted on a shared server).

In my world, for anything more than 30 seconds, a performance fix will be required before being deployed to production with a few exceptions (legacy code, crazy reports, etc.). The option that was offered to me was to increase the timeout from 30 seconds (explicitly added by the interface designer) to 180 seconds.

My question to you: What are the risks with an easy approach and an increase in timeout? If possible, include links to articles that support your views so I can link to them.

Also feel free to call if you think this is not a problem.

Answer 1

, :

• .
• .
• .

-, , , , , , . , , , , . , , , .

, , , . , , . - , , , , , .

Answer 2

, . - , , .

:

SQL .

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

, .

DBMS_PROFILER .

, , , , , :

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;

, :

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 ( profiler.sql), , , / PL/SQL . DBMS_PROFILER 10g.

Answer 3

, , ( script) , AJAX .

script, , script, Oracle , , .

, ( , ..)

Answer 4

, - 180 - . 2 . , . 1 , 30 , 2 3 . 2 , - , proc . , 180 , , 360 , 720 2 . , . , , , .

Answer 5

, . ? / , , :

• .

, - . (, , ), . , , , SALES GUY, . ​​ , . , , , , , , , , , . : "... ...", "... ...", "... , ...", "... ...". , - - - - , - . ( , ...). ...

If, on the other hand, you can change the database, which may serve you well, as @Adam Munsch suggested, and find out which SQL statements work so slowly. Perhaps you can significantly improve the situation by adding an index or two.

Good luck.