- - 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 ;
