WITH cputime AS (
    SELECT
        trunc(times, 'dd') timesd,
        instance_number,
        'CPU' wait_class,
        'CPU' event_name,
        0 total_wait,
        round((SUM(value - prev_value)) / 1000000, 2) time_waited_sec,
        0 avg_time_wait
    FROM
        (
            SELECT
                trunc(begin_interval_time, 'mi') times,
                sn.instance_number,
                'CPU',
                'CPU',
                0,
                0,
                value,
                LAG(value) OVER(
                    PARTITION BY sn.instance_number
                    ORDER BY
                        sn.snap_id
                ) prev_value
            FROM
                dba_hist_sys_time_model   se
                INNER JOIN dba_hist_snapshot         sn ON ( sn.snap_id = se.snap_id
                                                     AND sn.instance_number = se.instance_number )
            WHERE
                begin_interval_time > trunc(SYSDATE) - 7
                AND stat_name = 'DB CPU'
        )
    GROUP BY
        trunc(times, 'dd'),
        instance_number
), dbtime AS (
    SELECT
        trunc(times, 'dd') timesd,
        instance_number,
        'DBTIME' wait_class,
        'DBTIME' event_name,
        0 total_wait,
        round((SUM(value - prev_value)) / 1000000, 2) time_waited_sec,
        0 avg_time_wait
    FROM
        (
            SELECT
                trunc(begin_interval_time, 'mi') times,
                sn.instance_number,
                'DBTIME',
                'DBTIME',
                0,
                0,
                value,
                LAG(value) OVER(
                    PARTITION BY sn.instance_number
                    ORDER BY
                        sn.snap_id
                ) prev_value
            FROM
                dba_hist_sys_time_model   se
                INNER JOIN dba_hist_snapshot         sn ON ( sn.snap_id = se.snap_id
                                                     AND sn.instance_number = se.instance_number )
            WHERE
                begin_interval_time > trunc(SYSDATE) - 7
                AND stat_name = 'DB time'
        )
    GROUP BY
        trunc(times, 'dd'),
        instance_number
), eventtime AS (
    SELECT
        trunc(times, 'dd') timesd,
        instance_number,
        wait_class,
        event_name,
        SUM(total_waits - prev_total_wait) total_wait,
        round((SUM(time_waited_micro - prev_time_waited_micro)) / 1000000, 2) time_waited_sec,
        round((SUM(time_waited_micro - prev_time_waited_micro) / SUM(total_waits - prev_total_wait)) / 1000, 2) avg_time_wait
    FROM
        (
            SELECT
                trunc(begin_interval_time, 'mi') times,
                sn.instance_number,
                wait_class,
                event_name,
                total_waits,
                LAG(total_waits) OVER(
                    PARTITION BY sn.instance_number, wait_class, event_name
                    ORDER BY
                        sn.snap_id
                ) prev_total_wait,
                time_waited_micro,
                LAG(time_waited_micro) OVER(
                    PARTITION BY sn.instance_number, wait_class, event_name
                    ORDER BY
                        sn.snap_id
                ) prev_time_waited_micro
            FROM
                dba_hist_system_event   se
                INNER JOIN dba_hist_snapshot       sn ON ( sn.snap_id = se.snap_id
                                                     AND sn.instance_number = se.instance_number )
            WHERE
                begin_interval_time > trunc(SYSDATE) - 7
        )
    WHERE
        ( total_waits - prev_total_wait ) > 0
    GROUP BY
        trunc(times, 'dd'),
        instance_number,
        wait_class,
        event_name
)
SELECT
    e.timesd,
    e.instance_number,
    e.wait_class,
    e.event_name,
    e.total_wait,
    e.time_waited_sec,
    e.avg_time_wait,
    round(e.time_waited_sec / d.time_waited_sec, 2) perc
FROM
    eventtime   e
    INNER JOIN dbtime      d ON e.instance_number = d.instance_number
                           AND e.timesd = d.timesd
UNION ALL
SELECT
    c.timesd,
    c.instance_number,
    c.wait_class,
    c.event_name,
    c.total_wait,
    c.time_waited_sec,
    c.avg_time_wait,
    round(c.time_waited_sec / d.time_waited_sec, 2) perc
FROM
    cputime   c
    INNER JOIN dbtime    d ON c.instance_number = d.instance_number
                           AND c.timesd = d.timesd;