OraFAQ Forum: Performance Tuning » Percentage of CPU utilization per instance

Home » RDBMS Server » Performance Tuning » Percentage of CPU utilization per instance (Oracle 11g, 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Percentage of CPU utilization per instance [message #684729]

Thu, 05 August 2021 08:38

db_senior
Messages: 13
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!

Report message to a moderator

Re: Percentage of CPU utilization per instance [message #684731 is a reply to message #684729]

Thu, 05 August 2021 12:25

John Watson
Messages: 8976
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.

Report message to a moderator

Re: Percentage of CPU utilization per instance [message #684732 is a reply to message #684731]

Thu, 05 August 2021 12:36

db_senior
Messages: 13
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?

Report message to a moderator

Re: Percentage of CPU utilization per instance [message #684734 is a reply to message #684732]

Fri, 06 August 2021 01:00

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

So generate seven reports, one for each day.

Report message to a moderator

Re: Percentage of CPU utilization per instance [message #684735 is a reply to message #684734]

Fri, 06 August 2021 01:12

db_senior
Messages: 13
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

Report message to a moderator

Re: Percentage of CPU utilization per instance [message #686308 is a reply to message #684735]

Sat, 23 July 2022 07:14

Andrey_R
Messages: 441
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 20:30:38 CDT 2025