I need a query to retrieve the CPU usage percentage of the database at specific snapshot times. For example, my AWR snapshots are taken every 15 minutes, and I would like to know the CPU usage at each of these 15-minute intervals.

Could you provide a query that shows the CPU usage percentage at each snapshot interval?

Thank you!

I tried with this query, that give me the usage by service:

SELECT CC.SERVICE_NAME,
         CC.STAT_NAME
             AS STATISTIC_NAME,
         (CASE WHEN SUM_VALUE <> 0 THEN (VALUE / SUM_VALUE * 100) ELSE NULL END)
             AS USAGE_PERCENTAGE
    FROM ( -- 3. SUM_VALUE column is created by summing up the relevant statistical values found at SERVICE_NAME level.
          SELECT BB.SERVICE_NAME,
                 BB.STAT_NAME,
                 BB.VALUE,
                 SUM (BB.VALUE) OVER (PARTITION BY STAT_NAME)     AS SUM_VALUE
            FROM ( -- 2. Group by is done according to SERVICE_NAME and STAT_NAME. Thus, the values of the instances are collected at the SERVICE_NAME and STAT_NAME levels for the specified time period.
                    SELECT AA.SERVICE_NAME, AA.STAT_NAME, SUM (VALUE) VALUE
                      FROM ( -- 1. VALUE: The statistical value of the days in the time period specified at the instance level is found by subtracting the minimum value from the maximum value. Since VALUE is cumulative.
                             SELECT SRVC.INSTANCE_NUMBER,
                                    TRUNC (BEGIN_INTERVAL_TIME)             DAY,
                                    SRVC.SERVICE_NAME,
                                    SRVC.STAT_NAME,
                                    MAX (SRVC.VALUE) - MIN (SRVC.VALUE)     VALUE
                               FROM DBA_HIST_SERVICE_STAT SRVC,
                                    DBA_HIST_SNAPSHOT SNAP
                              WHERE     SRVC.SNAP_ID = SNAP.SNAP_ID
                                    AND SRVC.DBID = SNAP.DBID
                                    AND SRVC.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER
                                    --AND SNAP.BEGIN_INTERVAL_TIME >= TO_DATE ('08/10/2022', 'DD/MM/YYYY') AND SNAP.END_INTERVAL_TIME < TO_DATE ('09/10/2022', 'DD/MM/YYYY')
                                    AND SNAP.BEGIN_INTERVAL_TIME >= SYSDATE - 7 AND SNAP.END_INTERVAL_TIME <= SYSDATE
                                    AND SRVC.STAT_NAME IN ('DB CPU')
                           --AND SRVC.STAT_NAME IN ('DB CPU','execute count','sql execute elapsed time','session logical reads')
                           GROUP BY SRVC.INSTANCE_NUMBER,
                                    TRUNC (BEGIN_INTERVAL_TIME),
                                    SRVC.SERVICE_NAME,
                                    SRVC.STAT_NAME) AA
                  GROUP BY AA.SERVICE_NAME, AA.STAT_NAME) BB) CC
ORDER BY 2, 3 DESC