优化问题

Question

对一个过程，要连续的调用，过程是这样的
CREATE OR REPLACE PROCEDURE XJ_DEVICE_VALUE_INSERT_AUTO
 (  p_DEVICE_ID IN XJ_USER.XJ_DEVICE_VALUE.DEVICE_ID%TYPE,
  p_XJ_DATE IN XJ_USER.XJ_DEVICE_VALUE.XJ_DATE%TYPE,
  p_IB_ID IN XJ_USER.XJ_DEVICE_VALUE.IB_ID%TYPE,
  p_ITEMID IN XJ_USER.XJ_DEVICE_VALUE.ITEMID%TYPE,
  p_XJ_VALUE IN XJ_USER.XJ_DEVICE_VALUE.XJ_VALUE%TYPE,
  p_USERID IN XJ_USER.XJ_DEVICE_VALUE.USERID%TYPE,
  p_STATE IN XJ_USER.XJ_DEVICE_VALUE.STATE%TYPE )
 iS
  ItemType  integer;
  ValueUp  VARCHAR2(10);
  ValueDown VARCHAR2(10);
  tempVALUETYPE integer;
  PlanID integer;
  notPatrolledPlanCount integer;
  PatrolledPlanCount integer;
--优化动态查询部分
type rc_idcount is ref cursor;
type rc_PatrolledPlanCount is ref cursor;
type rc_ItemType is ref cursor;
type rc_PlanID is ref cursor;
type rc_valueMaxMin is ref cursor;l_rc_idcount rc_idcount;
l_rc_PatrolledPlanCount rc_PatrolledPlanCount;
l_rc_ItemType rc_ItemType;
l_rc_PlanID rc_PlanID;
l_rc_valueMaxMin rc_valueMaxMin;
--优化更新插入部分　
l_stmt_UpdatePlan long;
l_stmt_InsertValue long;
l_stmt_UpdateValue long;
l_stmt_InsertValueIgnore long;l_cursor_UpdatePlan integer;
l_cursor_InsertValue integer;
l_cursor_UpdateValue integer;
l_cursor_InsertValueIgnore integer;l_status number;
begin
    tempVALUETYPE:=0;
    ValueDown:='111';
    ValueUp:='00';open l_rc_idcount for
     'select count(XJ_USER.XJ_PLAN.ID) FROM XJ_USER.XJ_PLAN WHERE (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=:p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=:p_ITEMID and XJ_USER.XJ_PLAN.ISSTATE=0'
             using p_XJ_DATE, p_XJ_DATE, p_DEVICE_ID, p_ITEMID;
open l_rc_PatrolledPlanCount for
     'SELECT  count(XJ_USER.XJ_PLAN.ID) FROM XJ_USER.XJ_PLAN WHERE (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=:p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=:p_ITEMID and XJ_USER.XJ_PLAN.ISSTATE=1'
              using p_XJ_DATE, p_XJ_DATE, p_DEVICE_ID, p_ITEMID;
open l_rc_ItemType for
     'select XJ_USER.XJ_ITEM_DICTIONARY.TYPE from XJ_USER.XJ_ITEM_DICTIONARY where XJ_USER.XJ_ITEM_DICTIONARY.ITEMID=:p_ITEMID'
              using p_ITEMID;
open l_rc_PlanID for
     'SELECT nvl(XJ_USER.XJ_PLAN.ID,0) FROM XJ_USER.XJ_PLAN WHERE (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=:p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=:p_ITEMID'
              using  p_XJ_DATE, p_XJ_DATE, p_DEVICE_ID, p_ITEMID;
open l_rc_valueMaxMin for
     'SELECT XJ_USER.XJ_PLAN.DOWNVALUE, XJ_USER.XJ_PLAN.UPVALUE FROM XJ_USER.XJ_PLAN WHERE (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=:p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=:p_ITEMID'
              using  p_XJ_DATE, p_XJ_DATE, p_DEVICE_ID, p_ITEMID;fetch l_rc_idcount into notPatrolledPlanCount;
fetch l_rc_PatrolledPlanCount into PatrolledPlanCount;
fetch l_rc_ItemType into ItemType;
fetch l_rc_PlanID into PlanID;
fetch l_rc_valueMaxMin into ValueDown, ValueUp;close l_rc_idcount;
close l_rc_PatrolledPlanCount;
close l_rc_ItemType;
close l_rc_PlanID;
close l_rc_valueMaxMin;

Answer 1

     if ItemType =2 then
        select case when to_number(ValueDown)<= to_number(p_XJ_VALUE) and to_number(ValueUp)>= to_number(p_XJ_VALUE) then 3
               when to_number(ValueUp)< to_number(p_XJ_VALUE) then 1
               when to_number(ValueDown)> to_number(p_XJ_VALUE) then 2 end into tempVALUETYPE from dual;     else
        tempVALUETYPE:=0;
     end if;
if notPatrolledPlanCount>0 and PatrolledPlanCount=0 then
     l_stmt_UpdatePlan := 'Update XJ_USER.XJ_PLAN Set XJ_USER.XJ_PLAN.ISSTATE=1 Where (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=:p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=:p_ITEMID' ;
     l_cursor_updateplan := dbms_sql.open_cursor;
     dbms_sql.parse(l_cursor_updatePlan, l_stmt_UpdatePlan, dbms_sql.native);
     dbms_sql.bind_variable(l_cursor_UpdatePlan, ':p_XJ_DATE', p_XJ_DATE);
     dbms_sql.bind_variable(l_cursor_UpdatePlan, ':p_DEVICE_ID', p_DEVICE_ID);
     dbms_sql.bind_variable(l_cursor_UpdatePlan, ':p_ITEMID', p_ITEMID);
     l_status:=dbms_sql.execute(l_cursor_UpdatePlan);     l_stmt_insertvalue := 'Insert into XJ_USER.XJ_DEVICE_VALUE( DEVICE_ID,XJ_DATE,IB_ID,ITEMID,XJ_VALUE,USERID,STATE,VALUETYPE,PLANID) Values(:p_DEVICE_ID,:p_XJ_DATE,:p_IB_ID,:p_ITEMID,:p_XJ_VALUE,:p_USERID,:p_STATE,:tempVALUETYPE,:PlanID)';
     l_cursor_insertvalue := dbms_sql.open_cursor;
     dbms_sql.parse(l_cursor_InsertValue, l_stmt_InsertValue, dbms_sql.native);
     dbms_sql.bind_variable(l_cursor_InsertValue, ':p_XJ_DATE', p_XJ_DATE);
     dbms_sql.bind_variable(l_cursor_InsertValue, ':p_DEVICE_ID', p_DEVICE_ID);
     dbms_sql.bind_variable(l_cursor_InsertValue, ':p_ITEMID', p_ITEMID);
     dbms_sql.bind_variable(l_cursor_InsertValue, ':p_IB_ID', p_IB_ID);
     dbms_sql.bind_variable(l_cursor_InsertValue, ':p_XJ_VALUE', p_XJ_VALUE);
     dbms_sql.bind_variable(l_cursor_InsertValue, ':p_USERID', p_USERID);
     dbms_sql.bind_variable(l_cursor_InsertValue, ':p_STATE', p_STATE);
     dbms_sql.bind_variable(l_cursor_InsertValue, ':tempVALUETYPE', tempVALUETYPE);
     dbms_sql.bind_variable(l_cursor_InsertValue, ':PlanID', PlanID);
     l_status:= dbms_sql.execute(l_cursor_InsertValue);     dbms_sql.close_cursor(l_cursor_UpdatePlan);
     dbms_sql.close_cursor(l_cursor_InsertValue);
   --Update XJ_USER.XJ_PLAN Set XJ_USER.XJ_PLAN.ISSTATE=1 Where (XJ_USER.XJ_PLAN.DATETIME-p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=p_ITEMID;
   --Insert into XJ_USER.XJ_DEVICE_VALUE( DEVICE_ID,XJ_DATE,IB_ID,ITEMID,XJ_VALUE,USERID,STATE,VALUETYPE,PLANID)
     --Values(p_DEVICE_ID,p_XJ_DATE,p_IB_ID,p_ITEMID,p_XJ_VALUE,p_USERID,p_STATE,tempVALUETYPE,PlanID) ;
elsif PatrolledPlanCount > 0 then
     l_stmt_UpdateValue := 'Update  XJ_USER.XJ_DEVICE_VALUE Set XJ_DATE=:p_XJ_DATE , IB_ID=:p_IB_ID, XJ_VALUE=:p_XJ_VALUE, USERID=:p_USERID, STATE=:p_STATE, VALUETYPE=:tempVALUETYPE Where DEVICE_ID=:p_DEVICE_ID And ITEMID=:p_ITEMID And PLANID=:PlanID';
     l_cursor_UpdateValue := dbms_sql.open_cursor;
     dbms_sql.parse(l_cursor_UpdateValue, l_stmt_UpdateValue, dbms_sql.native);
     dbms_sql.bind_variable(l_cursor_UpdateValue, ':p_XJ_DATE', p_XJ_DATE);
     dbms_sql.bind_variable(l_cursor_UpdateValue, ':p_DEVICE_ID', p_DEVICE_ID);
     dbms_sql.bind_variable(l_cursor_UpdateValue, ':p_ITEMID', p_ITEMID);
     dbms_sql.bind_variable(l_cursor_UpdateValue, ':p_IB_ID', p_IB_ID);
     dbms_sql.bind_variable(l_cursor_UpdateValue, ':p_XJ_VALUE', p_XJ_VALUE);
     dbms_sql.bind_variable(l_cursor_UpdateValue, ':p_USERID', p_USERID);
     dbms_sql.bind_variable(l_cursor_UpdateValue, ':p_STATE', p_STATE);
     dbms_sql.bind_variable(l_cursor_UpdateValue, ':tempVALUETYPE', tempVALUETYPE);
     dbms_sql.bind_variable(l_cursor_UpdateValue, ':PlanID', PlanID);
     l_status:= dbms_sql.execute(l_cursor_UpdateValue);     dbms_sql.close_cursor(l_cursor_UpdateValue);
  else
     l_stmt_insertvalueIgnore := 'Insert into XJ_USER.XJ_DEVICE_VALUE_IGNORE( DEVICE_ID,XJ_DATE,IB_ID,ITEMID,XJ_VALUE,USERID,STATE,VALUETYPE,PLANID) Values(:p_DEVICE_ID,:p_XJ_DATE,:p_IB_ID,:p_ITEMID,:p_XJ_VALUE,:p_USERID,:p_STATE,:tempVALUETYPE,:PlanID)';
     l_cursor_InsertValueIgnore := dbms_sql.open_cursor;
     dbms_sql.parse(l_cursor_InsertValueIgnore, l_stmt_insertvalueIgnore, dbms_sql.native);
     dbms_sql.bind_variable(l_cursor_InsertValueIgnore, ':p_XJ_DATE', p_XJ_DATE);
     dbms_sql.bind_variable(l_cursor_InsertValueIgnore, ':p_DEVICE_ID', p_DEVICE_ID);
     dbms_sql.bind_variable(l_cursor_InsertValueIgnore, ':p_ITEMID', p_ITEMID);
     dbms_sql.bind_variable(l_cursor_InsertValueIgnore, ':p_IB_ID', p_IB_ID);
     dbms_sql.bind_variable(l_cursor_InsertValueIgnore, ':p_XJ_VALUE', p_XJ_VALUE);
     dbms_sql.bind_variable(l_cursor_InsertValueIgnore, ':p_USERID', p_USERID);
     dbms_sql.bind_variable(l_cursor_InsertValueIgnore, ':p_STATE', p_STATE);
     dbms_sql.bind_variable(l_cursor_InsertValueIgnore, ':tempVALUETYPE', tempVALUETYPE);
     dbms_sql.bind_variable(l_cursor_InsertValueIgnore, ':PlanID', PlanID);
     l_status:= dbms_sql.execute(l_cursor_InsertValueIgnore);     dbms_sql.close_cursor(l_cursor_InsertValueIgnore);
   --Insert into XJ_USER.XJ_DEVICE_VALUE_IGNORE( DEVICE_ID,XJ_DATE,IB_ID,ITEMID,XJ_VALUE,USERID,STATE,VALUETYPE,PLANID) Values(p_DEVICE_ID,p_XJ_DATE,p_IB_ID,p_ITEMID,p_XJ_VALUE,p_USERID,p_STATE,0,0) ;
end if;Commit;Exception
  When Others Then
       dbms_output.put_line('出错');
       Rollback;
end;
其中表XJ_DEVICE_VALUE有一个触发器，用于创建主键ID。
在连续调用1000次时，就要用近9分钟，请高手给指点一下　，如何优化，能使在1－2分钟之内完成。
因为一次不能帖的太长，故分成了两个部分

Answer 2

我想知道，如果不使用游标，直接在sql中使用变量，还是不是使用绑定变量的方式运行？
如何才能优化的更好呢？

Answer 3

没有必要用游标。还有就是有些SQL可以合并，如前两个求count的SQL，就可以合并为一个，用case或decode来实现对ISSTATE为0和1的分别求count。

Answer 4

这个我已经试过了，但对优化的效果不是很明显，起不到大的作用，我想要的方案是能得到数量级上的性能提高，现在的过程是这样的：
CREATE OR REPLACE PROCEDURE XJ_DEVICE_VALUE_INSERT_AUTO
 (  p_DEVICE_ID IN XJ_USER.XJ_DEVICE_VALUE.DEVICE_ID%TYPE,
  p_XJ_DATE IN XJ_USER.XJ_DEVICE_VALUE.XJ_DATE%TYPE,
  p_IB_ID IN XJ_USER.XJ_DEVICE_VALUE.IB_ID%TYPE,
  p_ITEMID IN XJ_USER.XJ_DEVICE_VALUE.ITEMID%TYPE,
  p_XJ_VALUE IN XJ_USER.XJ_DEVICE_VALUE.XJ_VALUE%TYPE,
  p_USERID IN XJ_USER.XJ_DEVICE_VALUE.USERID%TYPE,
  p_STATE IN XJ_USER.XJ_DEVICE_VALUE.STATE%TYPE )
 iS
  ItemType  integer;
  ValueUp  VARCHAR2(10);
  ValueDown VARCHAR2(10);
  tempVALUETYPE integer;
  PlanID integer;
  notPatrolledPlanCount integer;
  PatrolledPlanCount integer;begin
    tempVALUETYPE:=0;
    ValueDown:='111';
    ValueUp:='00';     select count(case when ISSTATE=0 then XJ_USER.XJ_PLAN.ID end) ,  count(case when ISSTATE=1 then XJ_USER.XJ_PLAN.ID end) into notPatrolledPlanCount, PatrolledPlanCount FROM XJ_USER.XJ_PLAN WHERE (XJ_USER.XJ_PLAN.DATETIME-p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=p_ITEMID and XJ_USER.XJ_PLAN.ISSTATE=0;
     select XJ_USER.XJ_ITEM_DICTIONARY.TYPE into ItemType from XJ_USER.XJ_ITEM_DICTIONARY where XJ_USER.XJ_ITEM_DICTIONARY.ITEMID=p_ITEMID;
     SELECT nvl(XJ_USER.XJ_PLAN.ID,0) into PlanID FROM XJ_USER.XJ_PLAN WHERE (XJ_USER.XJ_PLAN.DATETIME-p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=p_ITEMID;
     SELECT XJ_USER.XJ_PLAN.DOWNVALUE, XJ_USER.XJ_PLAN.UPVALUE into ValueDown, ValueUp FROM XJ_USER.XJ_PLAN WHERE (XJ_USER.XJ_PLAN.DATETIME-p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=p_ITEMID;     if ItemType =2 then
        select case when to_number(ValueDown)<= to_number(p_XJ_VALUE) and to_number(ValueUp)>= to_number(p_XJ_VALUE) then 3
               when to_number(ValueUp)< to_number(p_XJ_VALUE) then 1
               when to_number(ValueDown)> to_number(p_XJ_VALUE) then 2 end into tempVALUETYPE from dual;
     else
        tempVALUETYPE:=0;
     end if;
if notPatrolledPlanCount>0 and PatrolledPlanCount=0 then
   Update XJ_USER.XJ_PLAN Set XJ_USER.XJ_PLAN.ISSTATE=1 Where (XJ_USER.XJ_PLAN.DATETIME-p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=p_ITEMID;
   Insert into XJ_USER.XJ_DEVICE_VALUE(ID, DEVICE_ID,XJ_DATE,IB_ID,ITEMID,XJ_VALUE,USERID,STATE,VALUETYPE,PLANID, EXAMINE)
     Values(DEVICEVALUE.nextval, p_DEVICE_ID,p_XJ_DATE,p_IB_ID,p_ITEMID,p_XJ_VALUE,p_USERID,p_STATE,tempVALUETYPE,PlanID, 0) ;
elsif PatrolledPlanCount > 0 then
     Update  XJ_USER.XJ_DEVICE_VALUE Set XJ_DATE=p_XJ_DATE , IB_ID=p_IB_ID, XJ_VALUE=p_XJ_VALUE, USERID=p_USERID, STATE=p_STATE, VALUETYPE=tempVALUETYPE Where DEVICE_ID=p_DEVICE_ID And ITEMID=p_ITEMID And PLANID=PlanID;
  else
      Insert into XJ_USER.XJ_DEVICE_VALUE_IGNORE(ID, DEVICE_ID,XJ_DATE,IB_ID,ITEMID,XJ_VALUE,USERID,STATE,VALUETYPE,PLANID, EXAMINE) 
            Values(DEVICEVALUE.nextval, p_DEVICE_ID,p_XJ_DATE,p_IB_ID,p_ITEMID,p_XJ_VALUE,p_USERID,p_STATE,0,0, 0) ;
end if;Commit;Exception
  When Others Then
       Rollback;
end;