Thanks Anton, I will check it
On Tuesday, May 5, 2015 9:58 PM, Anton <djeday84@xxxxxxxxx> wrote:
ash:
col text format a150
select
ash.sql_id,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User
I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User
I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL",
dbms_lob.substr( s.sql_text , 400, 1 ) text
from v$ACTIVE_SESSion_HISTORY ash
left join dba_hist_sqltext s on s.sql_id=ash.sql_id
join dba_users du on du.USER_ID=ash.user_id
where du.username like upper ('&USERNAME')
and ash.sample_time > sysdate -&MINUTES/(24*60)
group by ash.sql_id,dbms_lob.substr( s.sql_text , 400, 1 )
order by sum(decode(ash.session_state,'ON CPU',1,1)) asc
/
awr:
select
ash.sql_id,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User
I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User
I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL",
substr ( to_char(s.sql_text),1,4000) text
from DBA_HIST_ACTIVE_SESS_HISTORY ash
left join dba_hist_sqltext s on s.sql_id=ash.sql_id
join dba_users du on du.USER_ID=ash.user_id
where du.username=upper ('&USERNAME')
and ash.sample_time > sysdate -&HOURS/24
group by ash.sql_id , substr ( to_char(s.sql_text),1,4000)
order by sum(decode(ash.session_state,'ON CPU',1,1)) asc;
On 06.05.2015 04:43, Sanjay Mishra (Redacted sender smishra_97@xxxxxxxxx for
DMARC) wrote:
Hi
Can someone help as how to find all SQL from AWR for a specific user ?
Thanks for the help
Sanjay
