Lutz Haake | Freelance Oracle Consultant

Find the proble­matic SQL ID to inves­tigate, then execute:

col report_id new_v report_id
select * from 
 (
 select * from DBA_HIST_REPORTS
 where key1 = '&&sql_id'
 and COMPONENT_NAME = 'sqlmonitor'
 order by generation_time desc
)
where rownum = 1;

This will give you the most recent execution, but you may want to use different criteria. Next, generate a plan statistics report in text format:

SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(
RID => &&report_id
, TYPE => 'text')
FROM dual;

This report contains all sorts of useful infor­mation to help pin down the perfor­mance bottleneck.