Nice! So I could create a report that shows, for example, the % of DB CPU used by each service:

SELECT SERVICE_NAME, STAT_NAME, ROUND(AVG(PCT_USAGE), 3) AS PCT_USAGE
  FROM (SELECT X.*,
               (CASE
                 WHEN SUM_VALUE <> 0 THEN
                  (VALUE / SUM_VALUE * 100)
                 ELSE
                  NULL
               END) AS PCT_USAGE
          FROM (SELECT SNAP_ID,
                       INSTANCE_NUMBER,
                       SERVICE_NAME,
                       STAT_NAME,
                       VALUE,
                       SUM(VALUE) OVER(PARTITION BY SNAP_ID, INSTANCE_NUMBER, STAT_NAME) AS SUM_VALUE
                  FROM (SELECT DISTINCT SRVC.SNAP_ID,
                                        SRVC.INSTANCE_NUMBER,
                                        SRVC.SERVICE_NAME,
                                        SRVC.STAT_NAME,
                                        SRVC.VALUE
                          FROM DBA_HIST_SERVICE_STAT SRVC,
                               DBA_HIST_SNAPSHOT     SNAP
                         WHERE SRVC.SNAP_ID = SNAP.SNAP_ID
                           AND SNAP.BEGIN_INTERVAL_TIME >=
                               TO_DATE('01/12/2016', 'DD/MM/YYYY')
                           AND SNAP.END_INTERVAL_TIME <
                               TO_DATE('01/01/2017', 'DD/MM/YYYY')
                           AND SRVC.STAT_NAME = 'DB CPU')) X)
 GROUP BY SERVICE_NAME, STAT_NAME
 ORDER BY (CASE
            WHEN PCT_USAGE = 0 THEN
             1
            ELSE
             0
          END),
          UPPER(SERVICE_NAME),
          STAT_NAME;

Please correct me if I make any mistake.

Regards,
Vitor Campos

snap_id    stat    value
1          pga     1000
2          pga     2000
3          pga     3000
4          pga     4000

Hello All,

I have prepared below SQL.
Would you check it, please? Is there any logical error?
Thanks in advance.
Serhat CELIK.

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 (SELECT BB.SERVICE_NAME,
BB.STAT_NAME,
BB.VALUE,
SUM (BB.VALUE) OVER (PARTITION BY STAT_NAME) AS SUM_VALUE
FROM ( SELECT AA.SERVICE_NAME, AA.STAT_NAME, SUM (VALUE) VALUE
FROM ( SELECT SRVC.INSTANCE_NUMBER,
TO_DATE (
TO_CHAR (BEGIN_INTERVAL_TIME,
'DD.MM.YYYY'),
'DD.MM.YYYY')
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.STAT_NAME IN
('DB CPU',
'execute count',
'sql execute elapsed time',
'session logical reads')
GROUP BY SRVC.INSTANCE_NUMBER,
TO_DATE (
TO_CHAR (BEGIN_INTERVAL_TIME,
'DD.MM.YYYY'),
'DD.MM.YYYY'),
SRVC.SERVICE_NAME,
SRVC.STAT_NAME) AA
GROUP BY AA.SERVICE_NAME, AA.STAT_NAME) BB) CC
ORDER BY 2, 3 DESC

Hi Connor,

Thank you for valuable answers.
I am writing my answers below of yours.

1) to_date(to_char(begin_interval_time can just be "trunc" or "cast as date"
Answer: Thank you I will do it.

2) I assume you're going to work under the assumption that there was not database bounce anywhere here?
Answer: Yes you are right. I assume there was not db bounce.
We can also add below condition for specific dates.

/*AND SNAP.BEGIN_INTERVAL_TIME >=
                                     TO_DATE ('03/10/2022', 'DD/MM/YYYY')
                                 AND SNAP.END_INTERVAL_TIME <
                                     TO_DATE ('04/10/2022', 'DD/MM/YYYY')*/

3) as a "just in case" you should join on all appropriate columns to dba_hist_snapshot, ie, dbid, instance_number
Answer: Thank you. I will join that columns also.
Please note that I have 2-Node RAC and only a Non-CDB database.

--

I have only an extra question;

Is my SQL code giving the correct result? Is it logically correct?

Since the value is cumulative, I subtracted the minimum values from the maximum values on a day and instance basis and summed these values.

Please correct me if the logic is incorrect.

Thanks in advance.
Serhat CELIK.

Hi Connor,

Thanks for your confirmation.
Have a great work!

Serhat CELIK

You can find detailed information at the below address.

https://serhatcelik.wordpress.com/2022/10/11/how-to-find-historical-resource-usage-by-service-in-oracle-database/