oracle 运行普通方式及调试debug方式存储过程性能区别

　调试某一存储过程时，在plsql developer　debug调试执行时，20多分钟都执行不完，

后分析如下：

 

1,查询调试会话运行存储过程的对应sql

select sid,serial#,event,status,sql_id,prev_sql_id,action,module from v$session where suser='123' and sid not in(9,31,151)

2,查询上述sql的内容文本

select sql_text,sql_id from v$sqlarea where sql_id in('g0unxwd406yu0','du7nyv586fquh','4n6y6p4sm7j9r','08vznc16ycuag')

3,经查询一直伴随运行如下sql

SELECT SYS_GUID() FROM SYS.DUAL

declare   runtime_info sys.dbms_debug.runtime_info;

declare ret binary_integer; begin ret := PBSDE.DEBUG_LOOP; end;

INSERT /*+ append */ INTO DEAL_PAY_INFO_TMP ( PAY_ID, ORDER_ID, USER_ID, INVOICE_TYPE, INVOICE_TOP, INVOICE_TOP_NAME, INVOICE_PRICE , INVOICE_CONTENT , INVOICE_CODE , INVOICE_DATE , PAY_TYPE , PAY_BANK_CODE , SEND_TYPE , SEND_TYPE_TIME , IF_CALL , SEND_PROVINCE , SEND_CITY , SEND_COUNTY , SEND_ADDRESS , SEND_POSTCODE , SEND_CUSNAME , SEND_CUSMOBILE , SEND_CUSTELNUM , SEND_CUSEMAIL , CREATE_DATE , CREATE_OPID , DISABLE_DATE , DISABLE_OPID , REC_STATUS , REMARK ) SELECT F_GETNID(), PO.EXT_CUST_ORDER_ID, DU.CUST_ID_Y2, PI.INVOICE_TYPE, PI.INVOICE_TYPE, PI.INVOICE_ADDRESS, PI.INVOICE_PRICE, DECODE(PI.INVOICE_CONTENTS,NULL,1,'企业',2,'个人',1,'商品明细',1,'通信器材',4,'通讯器材',4,'天翼飞Young19元号卡',1), PO.INVOICE_CODE, PI.INVOICE_DATE, PO.REMARK3, PO.REMARK7, PO.REMARK15, (CASE WHEN PO.DELIVER_DATE LIKE '%双休日%' THEN '3' WHEN PO.DELIVER_DATE LIKE '%不限时间%' THEN '2' WHEN PO.DELIVER_DATE LIKE '%工作日%' OR PO.DELIVER_DATE LIKE '%周一至周五%' THEN

 

最后一条sql是存储过程正常运行的sql,而其它的sql皆是与debug模式相关的sql

 

然后:我在命令模式直接运行上述的存储过程

80s执行完

且没有出现上述debug模式的对应sql

 

小结:1,debug模式运行存储过程很慢,在命令模式运行即可

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-750864/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-750864/