Oracle 23c New Features for DBA's
The situation is, and you just logged 🙂
1. DB Response is slow
2. Top/OEM/Graphs show your database host CPU is pegged to 100%, and (no other things running apart from db)
3. Unfortunately you do not have Active Session History, to fire a magical script , 5 minutes back, 10 minutes back,
4. You just need to check current statements quickly which just pegging CPU
5. And the Weird things, if you are in Cloud or kind of DBAAS, you just cant do SSH (really) , so you cant find top process ID from TOP
So with all conditions above, the following script really really saving my life here 🙂
select
se.SID,
ss.serial#,
ss.username,
ss.machine,
ss.sql_id,
to_char(s.last_active_time,'DD-MON-YY HH:MI:SS'),
s.last_load_time,
VALUE/100 cpu_usage_seconds ,
substr(s.sql_text,1,100)
from
v$session ss,
v$sesstat se,
v$statname sn,
v$sql s
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
--and
--ss.status='ACTIVE'
--and
-- ss.username is not null and ss.username!='DBMANAGER'
and
ss.sql_id=s.sql_id
order by VALUE desc;
And the output, As you see two processes pegging CPU 129 and 40 of about more than 100% (thats actually calculation issue) and what next kill it and save your database.
And another
Thank You.