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
