I had a SQL_ID that i was checking in v$sql a few days ago, ga93ryqxj7mcs and bfzp2dt9prncs.
select * from v$sql where sql_id in ('ga93ryqxj7mcs', 'bfzp2dt9prncs') However, when i queried it today, it's gone. i checked dba_hist_sqltext and it's still there:
select * from dba_hist_sqltext where sql_id in ('ga93ryqxj7mcs', 'bfzp2dt9prncs'); It's also in dba_hist_sqlstat. But i'd like to know the PROGRAM_ID of those 2 SQL_IDs, any other views I can use?
2 Answers
Assuming you’ve purchased the diagnostics pack licence (which you should have if you’re querying dba_hist).
Have a look at dba_hist_active_sess_history, if the execution was sampled (which is likely if it’s in dba_hist_sql%) then the PLSQL_OBJECT_ID and PLSQL_SUBPROGRAM_ID columns will be useful, you can turn the subprogram_id into a procedure within a package using dba_procedures. The dba_hist_active_sess_history view can have a lot of data in it so expect a query to take a while. If you know when the executions happened, then lookup the snapshot rows on dba_hist_snapshot and use the snap_id and dbid values as filters on your dba_hist_active_sess_history queries - this will enable partition pruning on the underlying tables.
If you are working with Oracle EBS, then you can use the DBA AWR SQL Performance Summary blitz report query for this purpose. It shows the top sql_ids, their originating package names, code lines, EBS concurrent program names and much more.
