What is the Least busiest RAC

While working on the latest project, I realized I needed away to know what was the least busiest RAC. Also I needed to exclude certain RACS as well as setting the maximum allowed CPU Utilization. This query is based on a call to the RAC via a database link. We needed to know not only the last stat but also the last five minutes of CPU Utilization.


SELECT tmpA.INST_ID
,tmpA.avgpast5mins
,tmpB.laststat
,tmpC.INSTANCE_NUMBER
,tmpC.INSTANCE_NAME
,tmpC.HOST_NAME
,tmpD.N_MAX_CPU_PER_SERVER
FROM
(select INST_ID ,AVG(VALUE) avgpast5mins
from GV$SYSMETRIC_HISTORY@DLK_RAC
where metric_name='Host CPU Utilization (%)'
AND BEGIN_TIME > sysdate - 5*(1/60)/24
GROUP BY INST_ID ) tmpA
INNER JOIN
(select A.INST_ID
,a.BEGIN_TIME
,VALUE laststat
from GV$SYSMETRIC_HISTORY@DLK_RAC A
INNER JOIN
(select INST_ID
, MAX(BEGIN_TIME) BEGIN_TIME
from GV$SYSMETRIC_HISTORY@DLK_RAC
where metric_name='Host CPU Utilization (%)'
GROUP BY INST_ID) B
ON A.INST_ID = B.INST_ID
and A.BEGIN_TIME = B.BEGIN_TIME
where A.metric_name='Host CPU Utilization (%)' ) tmpB
ON tmpA.INST_ID = tmpB.INST_ID
INNER JOIN GV$INSTANCE@DLK_RAC tmpC
ON tmpA.INST_ID = tmpC.INST_ID
INNER JOIN GEAP_PROJECT.TBL_PRG_CONFIGURATION tmpD
ON 1=1
WHERE tmpC.INSTANCE_NAME NOT IN
(SELECT INSTANCE_NAME
FROM "GEAP_PROJECT"."TBL_EXTRACT_RAC_EXCLUSION")
AND AVGPAST5MINS < N_MAX_CPU_PER_SERVER
AND LASTSTAT < N_MAX_CPU_PER_SERVER
ORDER BY LASTSTAT