select rstat.profile_id,
rstat.statement_id,
sta.statement_type,
sta.package_name,
sta.routine_name,
sta.parent_statement_id,
sta_parent.statement_type parent_statement_type,
sta_parent.routine_name parent_routine_name,
(select sql_text
from plg$prof_statements
where profile_id = rstat.profile_id and
statement_id = coalesce(sta.parent_statement_id, rstat.statement_id)
) sql_text,
rstat.cursor_id,
cur.name cursor_name,
cur.line_num cursor_line_num,
cur.column_num cursor_column_num,
rstat.record_source_id,
recsrc.parent_record_source_id,
recsrc.level,
recsrc.access_path,
cast(sum(rstat.open_counter) as bigint) open_counter,
min(rstat.open_min_elapsed_time) open_min_elapsed_time,
max(rstat.open_max_elapsed_time) open_max_elapsed_time,
cast(sum(rstat.open_total_elapsed_time) as bigint) open_total_elapsed_time,
cast(sum(rstat.open_total_elapsed_time) / nullif(sum(rstat.open_counter), 0) as bigint) open_avg_elapsed_time,
cast(sum(rstat.fetch_counter) as bigint) fetch_counter,
min(rstat.fetch_min_elapsed_time) fetch_min_elapsed_time,
max(rstat.fetch_max_elapsed_time) fetch_max_elapsed_time,
cast(sum(rstat.fetch_total_elapsed_time) as bigint) fetch_total_elapsed_time,
cast(sum(rstat.fetch_total_elapsed_time) / nullif(sum(rstat.fetch_counter), 0) as bigint) fetch_avg_elapsed_time,
cast(coalesce(sum(rstat.open_total_elapsed_time), 0) + coalesce(sum(rstat.fetch_total_elapsed_time), 0) as bigint) open_fetch_total_elapsed_time
from plg$prof_record_source_stats rstat
join plg$prof_cursors cur
on cur.profile_id = rstat.profile_id and
cur.statement_id = rstat.statement_id and
cur.cursor_id = rstat.cursor_id
join plg$prof_record_sources recsrc
on recsrc.profile_id = rstat.profile_id and
recsrc.statement_id = rstat.statement_id and
recsrc.cursor_id = rstat.cursor_id and
recsrc.record_source_id = rstat.record_source_id
join plg$prof_statements sta
on sta.profile_id = rstat.profile_id and
sta.statement_id = rstat.statement_id
left join plg$prof_statements sta_parent
on sta_parent.profile_id = sta.profile_id and
sta_parent.statement_id = sta.parent_statement_id
group by rstat.profile_id,
rstat.statement_id,
sta.statement_type,
sta.package_name,
sta.routine_name,
sta.parent_statement_id,
sta_parent.statement_type,
sta_parent.routine_name,
rstat.cursor_id,
cur.name,
cur.line_num,
cur.column_num,
rstat.record_source_id,
recsrc.parent_record_source_id,
recsrc.level,
recsrc.access_path
order by coalesce(sum(rstat.open_total_elapsed_time), 0) + coalesce(sum(rstat.fetch_total_elapsed_time), 0) desc