Hourly SGA and PGA Memory Adbisary / Pressure Report - Html

 - - Required diag and Tuning Pack to be enabled

SELECT

        SYS_CONTEXT('USERENV','DB_NAME') DB_NAME ,

    TO_CHAR(report_date, 'mm dd yyyy DY') AS report_date,param_name,

    current_target_gb existing,

--      max(A_TAR_GB) MAX_MEM,

--      max(A_PCT) MAX_PCT,

        --max(A_TAR_GB) || '<br><br>('|| max(regexp_substr(A_PCT,'[0-9]+'))|| ')' MAX_ADV ,

        max(A_TAR_GB) || '<br><br><span style="font-weight: bold;color: blue;">('|| max(A_PCT2)|| '%)</span>' MAX_ADV ,

--max(A_PCT2) max2,

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '00' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "00",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '01' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "01",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '02' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "02",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '03' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "03",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '04' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "04",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '05' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "05",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '06' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "06",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '07' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "07",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '08' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "08",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '09' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "09",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '10' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "10",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '11' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "11",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '12' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "12",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '13' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "13",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '14' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "14",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '15' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "15",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '16' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "16",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '17' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "17",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '18' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "18",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '19' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "19",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '20' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "20",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '21' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "21",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '22' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "22",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '23' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "23"

FROM (

    SELECT

        al.execution_start AS report_date,aa.ATTR1 param_name,

        ROUND(aa.num_attr1 / 1073741824) AS current_target_gb,

        ROUND(aa.num_attr2 / 1073741824) AS A_TAR_GB,

        CASE

        WHEN ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) >= 75 THEN '<span style="font-weight: bold;color: red;">'    || ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) || '%</span>'

        WHEN ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) >= 50 THEN '<span style="font-weight: bold;color: maroon;">' || ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) || '%</span>'

        WHEN ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) >= 25 THEN '<span style="font-weight: bold;color: purple;">' || ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) || '%</span>'

        WHEN ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) >= 1  THEN '<span style="font-weight: bold;color: orange;">' || ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) || '%</span>'

        ELSE

            '<span style="font-weight: bold;color: green;">' || ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) || '</span>'

        END AS A_PCT ,

                '<span style="font-weight: bold;color: green;">'||'0'||'</span>' sero,

ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) A_PCT2

    FROM

        dba_advisor_actions aa

    JOIN   dba_advisor_findings af ON aa.owner = af.owner AND aa.task_name = af.task_name AND aa.execution_name = af.execution_name

    JOIN   dba_advisor_log al      ON al.owner = af.owner AND al.task_name = af.task_name

    WHERE

        af.finding_name in ( 'Undersized SGA','Undersized PGA')

        AND aa.attr1 in('sga_target','pga_aggregate_target')

        AND TRUNC(al.execution_start) >= TRUNC(SYSDATE) - 7

)

GROUP BY

    TO_CHAR(report_date, 'mm dd yyyy DY'), current_target_gb,param_name ,sero

ORDER BY

  --  report_date DESC

to_date(report_date,'mm dd yyyy DY') DESC ;