Oracle/PLSQL performance tips

Question

I living problem in my query. When i add ROUND and DECODE query takes too long time but when i delete directly return value. When i search for sql advice no any advice. How i can fix this 2 syntax ?

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, 
IFSAPP.TREF_UTILITY_API.GET_DESPATCH_NUMBER(I.COMPANY, I.INVOICE_ID) DESPATCH_REFERENCE, 
X.OBJID XML_OBJID, 
X.OBJVERSION XML_OBJVERSION, 
IFSAPP.TREF_MODULE_API.GET_DESCRIPTION(I.MODULE_ID) MODULE_NAME 
FROM IFSAPP.TREF_INVOICE I, 
IFSAPP.TREF_INVOICE_INFO_QRY Q, 
IFSAPP.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

You should trace one or more executions of each statement to see exactly what it does. When you profile the trace data you will know what to do.

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)