我的存储过程运行时间太慢了，是什么原因？

Question

今天写了一条存储过程，运行起来非常慢（准确的说，我没有耐心等下去了）
我怀疑存储过程写错了，请高手们帮我看看吧。create sequence nk_seq_nod6_l3
increment by 1
start with 1;
--prg
create or replace procedure usp_initialize(
       p_i_nodeid number,
       p_s_nodename varchar2,
       p_i_parentnodeid number)
as
  v_level number(3):=1;
  v_i_parentnodeid number(20);
  v_i_rootid number(20);
  v_i_isleaf number(5);
  v_i_nodestatus number(5) := 1;
  v_i_nodetype number(5) := 0;
  v_i_datatype number(5) := 0;
  v_i_datalen number(5) := 0;
  v_i_datapre number(5) := 0;
  v_i_issystem number(5) := 1;
  v_i_systemid number(20);
  v_i_sortid number(5);
  v_i_enableduserid number(20) := 0;
  v_dt_enableddate date := sysdate;
  v_s_searchcode1 varchar2(1000) := '';
  v_s_searchcode2 varchar2(1000) := '';
  v_s_searchcode3 varchar2(1000) := '';
  v_s_searchcode4 varchar2(1000) := '';
  v_s_searchcode5 varchar2(1000) := '';
  v_i_var1 number(20) := 0;
  v_i_var2 number(20) := 0;
  v_i_var3 number(20) := 0;
  v_i_var4 number(20) := 0;
  v_i_var5 number(20) := 0;
  v_i_sourcetype number(5) := 0;
  v_i_sourcenodeid number(20) := 0;
  v_i_templateid number(20) := 0;
  v_dt_xgrq timestamp(6) := current_timestamp;
  v_i_xgrnodeid number(20) := 0;
  
begin
  dbms_output.put_line(p_i_nodeid||p_s_nodename||p_i_parentnodeid);
  
  --得到该项节点为第本属性树下的第几层
  v_i_parentnodeid := p_i_parentnodeid;  
  while v_i_parentnodeid not in(-1,2,4,6,7,10,23,99) loop
     v_level := v_level +1;
     select t.i_parentnodeid into v_i_parentnodeid
       from test.tb_stb001 t
       where t.i_nodeid = v_i_parentnodeid;     
  end loop;
  
  dbms_output.put_line(v_level); --测试层次关系是否正确
  
  --如果为节点为4号树下的第一层,则各字段赋值
  if(v_i_parentnodeid = 4) then
        v_i_isleaf := 2;
        v_i_var1 :=1;
        v_i_systemid := p_i_nodeid;
        
     if(v_level = 1) then
        v_i_rootid := p_i_nodeid;               
        select nk_seq_nod4_l1.nextval-1 into v_i_sortid
          from dual;               
     elsif(v_level = 2) then
        v_i_rootid := p_i_parentnodeid;
        select nk_seq_nod4_l2.nextval-1 into v_i_sortid
          from dual;
     else
        v_i_rootid := p_i_parentnodeid;
        v_i_issystem := 2;
        select nk_seq_nod4_l3.nextval-1 into v_i_sortid
          from dual;
     end if;   
  elsif(v_i_parentnodeid = 6) then
        v_i_rootid := 6;  --1\2\3层的rootid都为6
        v_i_isleaf := 2;  --1\2层的isleaf都为2
        v_i_systemid := p_i_nodeid; --1\2\3都为指定的系统编号
     if(v_level = 1) then
        select nk_seq_nod6_l1.nextval-1 into v_i_sortid
          from dual;        
     elsif(v_level = 2) then
        v_i_issystem := 2;
        select decode(p_s_nodename,'功能权限类角色',1,2) into v_i_sortid
          from dual;
        select decode(p_s_nodename,'功能权限类角色',1,2) into v_i_var1
          from dual;
     else
        v_i_systemid := p_i_parentnodeid;
        select nk_seq_nod6_l3.nextval-1 into v_i_sortid
          from dual;
        select i_var1 into v_i_var1 
          from tb_stb001 
          where p_i_parentnodeid =i_nodeid;        
     end if;
  end if;
   
  
  --初始化语句
  insert into tb_stb001(         i_nodeid, s_nodename,  i_parentnodeid,  i_rootid,i_isleaf,
                                 i_nodestatus,i_nodetype,i_datatype,i_datalen,
                                 i_datapre,i_issystem,i_systemid,i_sortid,
                                 i_enableduserid,dt_enableddate,s_searchcode1,
                                 s_searchcode2,s_searchcode3,s_searchcode4,s_searchcode5,
                                 i_var1,i_var2,i_var3,i_var4,i_var5,i_sourcetype,
                                 i_sourcenodeid,i_templateid,dt_xgrq,i_xgrnodeid)
                      select  p_i_nodeid,p_s_nodename,p_i_parentnodeid,v_i_rootid,v_i_isleaf,
                              v_i_nodestatus,v_i_nodetype,v_i_datatype,v_i_datalen,
                              v_i_datapre,v_i_issystem,v_i_systemid,v_i_sortid,
                              v_i_enableduserid,v_dt_enableddate,v_s_searchcode1,
                              v_s_searchcode2,v_s_searchcode3,v_s_searchcode4,v_s_searchcode5,
                              v_i_var1,v_i_var2,v_i_var3,v_i_var4,v_i_var5,v_i_sourcetype
                              v_i_sourcenodeid,v_i_templateid,v_dt_xgrq,v_i_xgrnodeid
                        from dual
                        where not exists(
                            select 1 
                              from tb_stb001  
                              where i_nodeid = p_i_nodeid );
end;

Answer 1

一个看看是不是LOOP消耗时间
另外是个看看是不是insert里的tb_stb001里慢
你可以在这两个地方用DBMS_OUTPUT.put_line输出时间,来跟踪执行的情况

Answer 2

select i_var1 into v_i_var1 
          from tb_stb001 
          where p_i_parentnodeid =i_nodeid;   还有这句,看看是不是耗时长
也可以DEBUG单步执行看

Answer 3

dbms_profiler这个包是专门调试PL\SQL
的，使用后可以看到没一句执行使用的时间，很容易找到出问题的地方，直接百度一下吧！

Answer 4

我觉得性能差在哪里不是乱猜的。加一些时间log，看看哪些时间花得多。
    p_date := current_timestamp at time zone 'GMT';
    DBMS_OUTPUT.Put_Line(Time milestone 1: ' || p_date);
    --ur code
    DBMS_OUTPUT.Put_Line(Time milestone 2: ' ||
                         (current_timestamp at time zone 'GMT'));
    DBMS_OUTPUT.Put_Line(ur code cost: ' ||
                         (current_timestamp at time zone 'GMT' - p_date));

Answer 5

俺也正在优化学习了
俺的优化是简化逻辑，优化SQL创建索引！