Oracle 11g Active Sessions

Oracle 11g Sessions currently active

select   A.inst_id “Instance”,A.sid “Sid”,A.serial# “Serial No.”,p.spid “ospid”,a.schemaname “schemaname”,
        A.machine “Machine”,A.terminal “Terminal”,  A.program “Program”,
      a.event “event”,a.seconds_in_wait “seconds_in_wait”,
       a.blocking_session “blocking_session”, A.osuser ,    
       sw.p1, sw.p2, sw.p3,sw.event,
       B.sql_id “SQL ID”,
       B.sql_text “Query”,
       B.users_executing “Users” ,C.opname “Operation”,C.target “Target”,
       C.sofar “Till Now”,C.totalwork “Total Work”,C.Time_Remaining “Remaining Time”,
       C.elapsed_seconds “Time Taken”,
       C.Message “Message”,A.username “User”,A.status “Status” , sysdate “Time”,
       su.tablespace, su.contents, su.extents, su.blocks
       from   gv$session A,gv$sql B,gv$session_longops C,gv$process P ,gv$session_wait sw,v$sort_usage su
where  A.sql_address = B.address
and    a.PADDR = p.addr
and    B.users_executing > 0
and    C.inst_id(+) = A.inst_id
and    C.sid(+) = A.sid
and    C.serial#(+) = A.serial#
and    C.sql_address(+) = A.sql_address
and     sw.sid(+)= a.sid
and    a.saddr=su.session_addr
–and C.Time_Remaining >0 —-
–and a.schemaname = ‘XXX’
–and a.sid=3835;

select * from dba_hist_active_sess_history
where sample_time >sysdate-5/24;

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘0txzuvqj2ujux’));
select * from table(dbms_xplan.display_awr(‘3dd7k4gars656′));

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(‘3dd7k4gars656′,null,null,’ADVANCED’));
select * from table( DBMS_XPLAN.display_cursor(‘3dd7k4gars656′, NULL,’ADVANCED ROWS ALLSTATS’));

 select
count(1),to_char(sample_time, ‘YYYY-MM-DD HH24′) sample_time,
sql_id, sql_plan_hash_value
from dba_hist_active_sess_history
where
sql_id = ‘0txzuvqj2ujux’
group by
to_char(sample_time, ‘YYYY-MM-DD HH24′),
sql_id, sql_plan_hash_value
order by sample_time;

SELECT sql_id,plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total)/1000000 avg_elapsedt_secs
FROM dba_hist_sqlstat
WHERE sql_id = ‘0txzuvqj2ujux’
GROUP BY sql_id,plan_hash_value ;

——-
——- TOP SQL from dba_hist_active_sess_history no v$active_session_history   filter by DBID
——-
select * from (select     ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,    
 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(wait_class, ‘User I/O’,1,0),0))    “WAIT” ,     sum(decode(ash.session_state,’WAITING’, 
   decode(wait_class, ‘User I/O’,1,0),0))    “IO” ,     sum(decode(ash.session_state,’ON CPU’,1,1))     “TOTAL”
   from dba_hist_active_sess_history ash,     audit_actions aud where SQL_ID is not NULL   — and ash.dbid=&DBID   
and ash.sql_opcode=aud.action   — and ash.sample_time > sysdate – &minutes /( 60*24)
group by sql_id, SQL_PLAN_HASH_VALUE   , aud.name order by sum(decode(session_state,’ON CPU’,1,1))   desc
) where  rownum < 10