Check the real time database health using below SQL

 You can check the real time database health using below sql commands

— Top 10 CPU consumers in last 5 minutes

select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;

— Top 10 waiting sessions in last 5 minutes

select * from
(
select session_id, session_serial#,count(*)
from v$active_session_history
where session_state='WAITING'  and
sample_time >  sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;

— Who is that SID?

set lines 200
col username for a10
col osuser for a10
col machine for a10
col program for a10
col resource_consumer_group for a10
col client_info for a10

select  serial#,
username,
osuser,
machine,
program,
resource_consumer_group,
client_info
from v$session where sid='&sid';

— What did that SID do?

select distinct sql_id, session_serial# from v$active_session_history
where sample_time >  sysdate - interval '60' minute
and session_id=&sid;

— Retrieve the SQL from the Library Cache:

col sql_text for a80
select sql_text from v$sql where sql_id='&sqlid';
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s