Oracle/PLSQL 性能提示

Question

我在查询中遇到了问题。当我添加ROUND和解码查询需要很长时间时，但当我删除时直接返回值。当我搜索sql建议时，没有任何建议。我如何修复这 2 种语法？

SELECT I.*, 
Q.INVOICE_DATE, 
Q.SERIES_ID IFS_SERIES_ID, 
Q.INVOICE_NO, 
Q.IDENTITY, 
Q.IDENTITY_NAME, 
Q.ASSOCIATION_NO, 
Q.NET_CURR_AMOUNT, 
Q.VAT_CURR_AMOUNT, 
Q.TOTAL_CURR_AMOUNT, 
Q.CURRENCY_CODE, 
ROUND(Q.CURR_RATE,:"SYS_B_0") CURR_RATE, 
Q.PROFILE_ID DEFAULT_PROFILE_ID, 
X.XML_CONTENT, 
DECODE(NVL(DBMS_LOB.INSTR(X.XML_CONTENT,:"SYS_B_1",:"SYS_B_2",:"SYS_B_3"), :"SYS_B_4"), :"SYS_B_5",:"SYS_B_6", :"SYS_B_7") SINGED_UBL, 
X.SCHEMATRON_RESULT, 
q.SHIPMENT_ID, 
APP.TBN_UTILITY_API.GET_NUMBER(I.COMPANY, I.INVOICE_ID) DESPATCH_REFERENCE, 
X.OBJID XML_OBJID, 
X.OBJVERSION XML_OBJVERSION, 
APP.API_MODULE.GET_DESC(I.MODULE_ID) MODULE_NAME 
FROM APP.TREF_INVOICE I, 
APP.TREF_INVOICE_INFO_QRY Q, 
APP.TREF_XML_ARCHIVE X 
WHERE Q.COMPANY = I.COMPANY 
AND Q.INVOICE_ID = I.INVOICE_ID 
AND X.XML_ARCHIVE_ID(+) = I.XML_ARCHIVE_ID 
AND I.COMPANY = :COMPANY 
AND I.INVOICE_ID = :INVOICE_ID

Answer 1

您应该跟踪每个语句的一个或多个执行，以准确查看它的作用。分析跟踪数据时，您将知道该怎么做。

SQL> select value from v$diag_info where name = 'Default Trace File'/* name of the trace file */;
SQL> exec dbms_monitor.session_trace_enable(null, null, true, false, 'all_executions')
SQL> your query executed under normal circumstances
SQL> exec dbms_monitor.session_trace_disable(null, null)