Database Blog

To check the TOP SQL in last 2 minutes sample

select round(avg(max(cnt_tot)) over (order by sample_time RANGE BETWEEN INTERVAL ‘5’ minute PRECEDING AND current row)) as avg,max(cnt_tot) as tot, SAMPLE_time,max(cnt) as cnt, event,substr(sq.sql_text,1), ash.sql_id, ash.sql_child_number chd,/* plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id,…

To Check the TOP SQL in last 5 minutes sample

Top Session per User with CPU Status

select/* if sid not found in v$session then disconnected */decode(nvl(to_char(s.sid),-1),-1,’DISCONNECTED’,’CONNECTED’)“STATUS”,topsession.session_id “SESSION_ID”,u.name “NAME”,topsession.program “PROGRAM”,max(topsession.CPU) “CPU”,max(topsession.WAITING) “WAITING”,max(topsession.IO) “IO”,max(topsession.TOTAL) “TOTAL”from (selectash.session_id,ash.session_serial#,ash.user_id,ash.program,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(en.wait_class,’User I/O’,1, 0 ), 0)) “WAITING” ,sum(decode(ash.session_state,’WAITING’,decode(en.wait_class,’User I/O’,1, 0 ),…

To get the Top Session Userwise

To check the Top Sessions

select ash.session_id, ash.session_serial#, ash.user_id, ash.program, 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(en.wait_class,’User I/O’,1, 0 ), 0)) “WAITING” , sum(decode(ash.session_state,’WAITING’, decode(en.wait_class,’User I/O’,1, 0 ), 0)) “IO” , sum(decode(session_state,’ON CPU’,1,1)) “TOTAL” from…

To get the Top SQL from ASH

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(en.wait_class, ‘User I/O’,1,0),0)) “WAIT” , sum(decode(ash.session_state,’WAITING’, decode(en.wait_class, ‘User I/O’,1,0),0)) “IO” , sum(decode(ash.session_state,’ON CPU’,1,1)) “TOTAL” from v$active_session_history ash, v$event_name en where SQL_ID…

Top Waiting Session in last 5 minutes

Select session_id, count(*) from v$active_session_history where session_state=’WAITING’ and SAMPLE_TIME > SYSDATE – (60/(24*60)) and rownum<11 group by session_id order by count(*) desc;

To check Top CPU Session in last 5 minutes

Select session_id, count(*) from v$active_session_history where session_state= ‘ON CPU’ and SAMPLE_TIME > sysdate – (120/(24*60)) and rownum<11 group by session_id order by count(*) desc;

Scripts to check Rollback Segments information

Rollback segment Information SELECT segment_name, tablespace_name, status FROM sys.dba_rollback_segs; SELECT segment_name, tablespace_name, (bytes)/1024/1024, blocks, extents FROM sys.dba_segments WHERE segment_type = ‘ROLLBACK’; SELECT name, xacts “ACTIVE TRANSACTIONS” FROM…

Segments Unable to extend/Segments reached max extents

SET pages 2000 SET verify OFF SET heading off set lines 150 undef warning def Warning=5 spool alter_chunk.sql — Alter tables and table partitions in ts with a size of…

We take you from best practices to next practices of software implementation

Solutions that Ensure You Stay Ahead of Competition

Your Partner for Strategic Technology Solutions

Guiding Your Digital Transformation Journey