Oracle 如何跟踪PL/SQL过程以进行调优

嗨，汤姆，

有一个过程正在运行很长/需要时间，因此，我如何跟踪该过程以检查问题出在哪里。

像-tkprod或跟踪会话

您可以使用SQL跟踪捕获过程的执行统计信息。但这只会收集SQL执行细节。

如果您想了解PL/SQL执行的详细信息，可以使用分层探查器。

create or replace function f ( p int ) 
  return number as
begin
  return sqrt (p);
end f;
/

create or replace procedure p  as
  val number;
begin
  for i in 1 .. 10000 loop
    val := val + f ( i );
  end loop;
  
  dbms_output.put_line ( val );
  
end p;
/

var run number;

begin
  dbms_hprof.start_profiling (
    location => 'PLSHPROF_DIR',
    filename => 'profile.txt');
   
  p();

  dbms_hprof.stop_profiling;

  :run := DBMS_HPROF.analyze (
     location    => 'PLSHPROF_DIR',
     filename    => 'profile.txt',
     run_comment => 'PL/SQL profile'
  );

end;
/

select rpad(' ', (level-1)*2, ' ') || a.name as name,
       a.subtree_elapsed_time,
       a.function_elapsed_time,
       a.calls
from   (select fi.symbolid,
               pci.parentsymid,
               rtrim(fi.owner || '.' || fi.module || '.' || nullif(fi.function,fi.module), '.') as name,
               nvl(pci.subtree_elapsed_time, fi.subtree_elapsed_time) as subtree_elapsed_time,
               nvl(pci.function_elapsed_time, fi.function_elapsed_time) as function_elapsed_time,
               nvl(pci.calls, fi.calls) as calls
        from   dbmshp_function_info fi
        left join dbmshp_parent_child_info pci 
        on     fi.runid = pci.runid and fi.symbolid = pci.childsymid
        where  fi.runid = :run
        and    fi.module != 'DBMS_HPROF') a
connect by a.parentsymid = prior a.symbolid
start with a.parentsymid is null;

NAME                           SUBTREE_ELAPSED_TIME   FUNCTION_ELAPSED_TIME   CALLS   
CHRIS.P                                         29880                   12981       1 
  CHRIS.F                                       16832                   16832   10000 
  SYS.DBMS_OUTPUT.PUT_LINE                         67                       4       1 
    SYS.DBMS_OUTPUT.NEW_LINE                        2                       2       1 
    SYS.DBMS_OUTPUT.PUT                            61                      61       1

HT到Tim Hall进行上述查询。您可以从他那里了解有关如何配置探查器的更多信息，请参阅:

https://oracle-base.com/articles/11g/plsql-hierarchical-profiler-11gr1

或者在SQL Developer中运行它。Barry McGillin对此有说明:

https://barrymcgillin.blogspot.co.uk/2012/04/using-hierarchical-profiler-in-sql.html