Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Determine current load on DB

girishkaleAug 19 2020 — edited Aug 19 2020

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

Comments
Post Details
Added on Aug 19 2020
5 comments
7,167 views