to find queries ran in last one month accessing a particular object in oracle database

select tab.*,t.sql_text from (select * from dba_hist_sqltext where sql_text like '%MSTR.VW_MS_DSTR_DATAMART%')t,
(select trunc(b.begin_interval_time) DateRun,a.PARSING_SCHEMA_NAME,
a.Sql_id,a.plan_hash_value PLANHASH,sum(a.executions_delta) executions,max(px_servers_execs_delta) PXUsed,
sum(a.disk_reads_delta) disk_reads,sum(a.buffer_gets_delta) buffer_gets,sum(a.rows_processed_delta) rows_proced,
sum(a.elapsed_time_delta)/(1000000*60) ELAPSEDTIME_mIN,
sum(a.elapsed_time_delta)/(1000000*60*decode(sum(a.executions_delta),0,1,sum(a.executions_delta))) Per_EXEC_MINS
from DBA_HIST_SQLstat a, dba_hist_snapshot b
where a.snap_id = b.snap_id and trunc(b.begin_interval_time)> trunc(sysdate-30)
group by trunc(b.begin_interval_time),a.PARSING_SCHEMA_NAME,
a.Sql_id,a.plan_hash_value order by trunc(b.begin_interval_time) desc) tab
where tab.sql_id=t.sql_id;

Comments

Popular posts from this blog

upgrade database oracle 10g to oracle 11g

Installing DBMS_JAVA package in Oracle and calling UTL_DBWS web services through Oracle database

Starting background process GTX4 and GLOBAL_TXN_PROCESSES