query to check what query running into specific user at given time

SET HEADING ON

SET LINESIZE 300

SET PAGESIZE 60

COLUMN Sample_Time FOR A12

COLUMN username FOR A20

COLUMN sql_text FOR A40

COLUMN program FOR A25

COLUMN module FOR A25

SELECT

   to_char(sample_time,'DD Mon HH24:MI') as Sample_Time,

   u.username,

   h.program,

   h.module,

   s.sql_text

FROM

   DBA_HIST_ACTIVE_SESS_HISTORY h,

   DBA_USERS u,

   DBA_HIST_SQLTEXT s

WHERE  sample_time between '11-JUL-13 05.45.03.793 PM' and '11-JUL-13 06.45.04.455 PM'

   AND h.user_id=u.user_id

   AND h.sql_id = s.sql_iD

   AND u.username='SCOTT'

order by 1;

To check given query performance for any specific table:-

select a. snap_id, begin_interval_time,c.sql_text,

executions_delta,(elapsed_time_delta /executions_delta )/1000000,

plan_hash_value,(cpu_time_delta /executions_delta )/1000000,

rows_processed_delta/executions_delta ,(iowait_delta /executions_delta )/1000000

,(fetches_delta/ executions_delta)

,(disk_reads_delta/ executions_delta)

,(buffer_gets_delta/ executions_delta)

from dba_hist_sqlstat a, dba_hist_snapshot b ,

dba_hist_sqltext c where sql_text like '%table_name%' and a. snap_id= b. snap_id  and executions_delta ! =0 and a.sql_id=c.sql_id order by snap_id desc