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模式執行儲存過程很慢,在命令模式執行即可