Technorama

One of our sql procedure executions took a lot of time. But it was not being monitored when it was executing. So we need a way to figure out the execution plan for the query so we can pinpoint the problem. That is where the oracle history tables helped us

select * from dba_hist_sqltext
where sql_text like '%TABLE_NAME%'

We knew the table name which was being accessed and so got the sql_id for the execution.

Next step was to get the execution plan:


select * from DBA_HIST_SQL_PLAN
where sql_id = 'ABCDEF';


This showed us that the plan was using NESTED loops for execution.

The respective tables for run time monitoring are gv$sqltext and gv$sql_plan