Hello All,
I have a maintenance SP to run but only when there's no load on the DB or relatively less load on the DB. The maintenance SP either drops partitions from the table if it is partitioned or does a row by row delete if not partitioned based on retention time set. That's that. But I must run this SP in the lull time.
I'm thinking to ascertain it by below parameters:
CPU usage
Memory usage
Just a few assumptions/notes
The DB instance is dedicated and has only one DB user that servers the application requests.
For CPU I've written following SQL and please let me know if that's a right path to progress:
-- CPU Utilization
select busy.stat_name busy_time, busy.value busy_val, idle.stat_name idle_time, idle.value idle_val, (busy.value+idle.value) capacity, round((busy.value/(busy.value+idle.value))*100, 2) utilization
from v$osstat busy, v$osstat idle
where busy.stat_name = 'BUSY_TIME'
and idle.stat_name = 'IDLE_TIME';
-- Rolled up CPU utilization by DB User
select nvl(s.username, 'Oracle Internal Process'), sum((st.value/1000000))
from v$sess_time_model st
join v$Session s
on st.sid = s.sid
where s.status = 'ACTIVE'
group by nvl(s.username, 'Oracle Internal Process')
order by 2 desc;
Kindly let me know if I'm on right track. Also for memory utilization, any pointers that experts can share to proceed with?
Thanks for your help.
Best,
Girish