Which Oracle Databases use most CPU on my server?

Posted on by

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

The following two tabs change content below.
My Twitter profileMy LinkedIn profile

Ludovico

Principal Product Manager at Oracle
Ludovico is a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Oracle Data Guard, Flashback technologies, and Cloud MAA.

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

  1. Ludovico,

    I have a quick question. Active Sessions Using CPU – ‘user_cpu_time_cnt’ captures only the CPU count used by the user, what can be done about CPU used by the system for the individual instance.
    Also is there a similar metric of Memory ?

    Thanks in advance

    Dali

    Reply ↓

  2. Thank you Ludovico. This is exactly what I need. I have an Exadata-full rack with CPU and Memory over-provisioned for DBs when compared to their actual usage. I would like to see the cpu usage history to better allocate CPU to each DB.

    Appreciate your post.
    Keep up the good work.

    Cheers

    Reply ↓

  3. 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’);

    Reply ↓

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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.