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: 12
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: 8738
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: 12
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: 8738
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 messageGo to next message
db_senior
Messages: 12
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
Re: Percentage of CPU utilization per instance [message #686308 is a reply to message #684735] Sat, 23 July 2022 07:14 Go to previous message
Andrey_R
Messages: 404
Registered: January 2012
Location: Israel
Senior Member
db_senior wrote on Fri, 06 August 2021 09:12
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
Lets say you want to take 1st augugst 08:00 AM to 8th of August 08:00 AM

You make sure you have enough retention for the AWR to retain snapshots of 1 week

Then you produce a SINGLE AWR from BEGIN Snapshot of 1st of August and END Snapshot of 8th of August.

Andrey
Previous Topic: query tunning
Next Topic: Time metrics in V$SQL don't add up
Goto Forum:
  


Current Time: Tue Aug 16 07:38:25 CDT 2022