Home » RDBMS Server » Performance Tuning » Percentage of CPU utilization per instance (Oracle 11g, 12c)
Percentage of CPU utilization per instance [message #684729] Thu, 05 August 2021 08:38 Go to next message
db_senior
Messages: 11
Registered: July 2021
Junior Member
Hi,
My Oracle version is: Oracle Database 11g and 12c EE on Oracle Linux 6.10 (Exadata Machine)

On DB Server there are 20 instances and our databases are configured with Oracle RAC option (2 nodes).

We are trying to do an analysis of CPU utilization (%) per Oracle database instance (not HOST).

I'm using the following query:
Quote:

select to_char(begin_time, 'yyyy/mm/dd')||' '||to_char(begin_time, 'hh24:mi') data_time,
to_char(begin_time, 'hh24:mi') begin_time,
to_char(end_time, 'hh24:mi') end_time,
round(average,2) avgval,
round(maxval,2) maxval, snap_id
From DBA_HIST_SYSMETRIC_SUMMARY
where metric_name='Host CPU Utilization (%)'
and instance_number = 1 ----and instance_number = 2
order by 1 desc,3
unfortunately isn't useful for providing information on CPU (%) per single instance.

Can you tell me How can provide information of the CPU usage (%) per database instance?

Thanks!
Re: Percentage of CPU utilization per instance [message #684731 is a reply to message #684729] Thu, 05 August 2021 12:25 Go to previous messageGo to next message
John Watson
Messages: 8644
Registered: January 2010
Location: Global Village
Senior Member
The AWR report for your old 11g has a section "Instance CPU", doesn't that give you what you need? Release 12.x reports have more info which will certainly answer your question. If you don't have the diag pack licence, try a statspack report - it has similar information.
Re: Percentage of CPU utilization per instance [message #684732 is a reply to message #684731] Thu, 05 August 2021 12:36 Go to previous messageGo to next message
db_senior
Messages: 11
Registered: July 2021
Junior Member
John Watson wrote on Thu, 05 August 2021 12:25
The AWR report for your old 11g has a section "Instance CPU", doesn't that give you what you need? Release 12.x reports have more info which will certainly answer your question. If you don't have the diag pack licence, try a statspack report - it has similar information.

"Instance CPU" in AWR report determine the proportion of CPU usage by instance between begin_snap_id and end_snap_id but I'm trying to obtain a trend of last 7 days.

Is there a query that provides a history of the last 7 days?
Re: Percentage of CPU utilization per instance [message #684734 is a reply to message #684732] Fri, 06 August 2021 01:00 Go to previous messageGo to next message
John Watson
Messages: 8644
Registered: January 2010
Location: Global Village
Senior Member
So generate seven reports, one for each day.
Re: Percentage of CPU utilization per instance [message #684735 is a reply to message #684734] Fri, 06 August 2021 01:12 Go to previous message
db_senior
Messages: 11
Registered: July 2021
Junior Member
John Watson wrote on Fri, 06 August 2021 01:00
So generate seven reports, one for each day.
I'd like to have a report with the cpu that provide a trend of last 7 day every hour, so I should create 7x24 = 168 AWR reports
Previous Topic: The predicate prevents the optimizer from using indices efficiently
Next Topic: Slow Archive Apply in Physical Standby
Goto Forum:
  


Current Time: Wed Dec 08 23:21:53 CST 2021