Simple problem: I got a query (complex) which seems to run slow for specific parameters. I want to find out what these parameters are.
Is there any tracing I can enable in Oracle (12) which would allow me that. I am looking for some kind of list (for given sql_id) which would list executions + bound params + execution time.
Thanks!
The most effective way to debug the performance of a SQL statement is to obtain a SQL Monitor report. You can obtain this from Enterprise Manager (EM). If you do not have EM, then you can generate a SQL Monitor report via SQLPlus:
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool sqlmon_active.html
select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
spool off
You can augment this to also pass in the sqlid.
The resulting html file can them be viewed in a browser.
This html file will show you a huge amount of information. Execution plan, estimated and actual cardinalities, bind variable values, time breakdown, waits. etc.
For more information (though a little dated), take a look at: http://www.oracle.com/technetwork/database/manageability/sqlmonitor-084401.html
