Which Oracle Databases use most CPU on my server?

Assumptions

  • You have many (hundreds) of instances and more than a couple of servers
  • One of your servers have high CPU Load
  • You have Enterprise Manager 12c but the Database Load does not filter by server
  • You want to have an historical representation of the user CPU utilization, per instance

Getting the data from the EM Repository

With the following query, connected to the SYSMAN schema of your EM repository, you can get the hourly max() and/or avg() of user CPU by instance and time.

Suppose you select just the max value: the result will be similar to this:

 

Putting it into excel

There are one million ways to do something more reusable than excel (like rrdtool scripts, gnuplot, R, name it), but Excel is just right for most people out there (including me when I feel lazy).

  • Configure an Oracle Client and add the ODBC data source to the EM repository:

odbc_emrep

  • Open Excel, go to “Data” – “Connections” and add a new connection:
    • Search…
    • New Source
    • DSN ODBC
  • Select your new ODBC data source, user, password
  • Uncheck “Connection to a specific table”
  • Give a name and click Finish
  • On the DSN -> Properties -> Definition, enter the SQL text I have provided previously

connection_properties_odbc_excel

The result should be something similar: ( but much longer :-))

first_step_excelPivoting the results

Create e new sheet and name it “pivot”, Click on “Create Pivot Table”, select your data and your dimensions:

pivotThe result:

pivotedCreating the Graph

Now that the data is correctly formatted, it’s easyy to add a graph:

just select the entire pivot table and create a new stacked area graph.

The result will be similar to this:

graph_cpu_load_excel

With such graph, it is easy to spot which databases consumed most CPU on the system in a defined period, and to track the progress if you start a “performance campaign”.

For example, you can see that the “green” and “red” databases were consuming constantly some CPU up to 17.05.2017 and then some magic solved the CPU problem for those instances.

It is also quite convenient for checking the results of new instance caging settings…

The resulting CPU will not necessarily be 100%: the SYS CPU time is not included, as well as the user CPU of all the other processes that are either not DB or not monitored with Enterprise Manager.

HTH

Ludovico

Be Sociable, Share!
The following two tabs change content below.

Ludovico

Oracle ACE and Senior Consultant at Trivadis SA
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Senior Database Specialist for Trivadis, Switzerland.

2 thoughts on “Which Oracle Databases use most CPU on my server?

  1. Pingback: Which Oracle Databases use most CPU on my server? - Ludovico Caldara - Blogs - triBLOG

  2. The last 7 days, included the current one, can be displayed by changing the query with this one… It’s still an aggregation by hour, Excel is not good at displaying different series with different collection times…

    SELECT entity_name,
    ROUND(collection_time,’HH’) AS colltime,
    round(sum(value)/count(*),2) AS avgv — same here
    FROM gc$metric_values mv
    JOIN em_targets t
    ON (t.target_name =mv.entity_name)
    WHERE t.host_name =’sli2577v’ — myserver1 is the server that has high CPU Usage
    AND mv.metric_column_name = ‘user_cpu_time_cnt’ — let’s get the user cpu time
    group by entity_name, ROUND(collection_time,’HH’)
    ORDER BY entity_name,
    ROUND(collection_time,’HH’);

Leave a Reply

Your email address will not be published. Required fields are marked *