监控告警阈值load 大于10
SQL如下,太好用了
SELECT A.SQL_ID, A.SESS_COUNT, A.CPU_LOAD, B.SQL_TEXT
FROM (SELECT SQL_ID,
COUNT(*) SESS_COUNT,
ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) CPU_LOAD
FROM V$ACTIVE_SESSION_HISTORY
WHERE SAMPLE_TIME > SYSDATE - 1 / (24 * 6)
AND SESSION_TYPE <> 'BACKGROUD'
AND SESSION_STATE = 'ON CPU'
GROUP BY SQL_ID
ORDER BY COUNT(*) DESC) A,
V$SQLAREA B
WHERE A.SQL_ID = B.SQL_ID;
执行次数
SELECT SQL_ID, SQL_TEXT,FIRST_LOAD_TIME, EXECUTIONS
FROM V$SQLAREA
WHERE SQL_id='2zajkda8mzvka';
SELECT COUNT(*) TOTAL_COUNT, GROUP_ID
FROM IMC_GROUP_USER
WHERE JOINED = 1
AND GROUP_ID IN (:1, :2, :3)
GROUP BY GROUP_ID
group_id列分布 太不均衡了,代入不同值,有时候走全表,有时候走index range scan,
CREATE INDEX IM_CORE_BASE.I_IMC_GROUP_USER_gid_joined ON IM_CORE_BASE.IMC_GROUP_USER(GROUP_ID,JOINED) TABLESPACE
IM_CORE_BASE ONLINE;
代入不同值逻辑读分别从11127降到9 ,48 降到3
系统 load average 降到2.83
再查ayht结尾的sql id消失了