数据库管理员日常SQL查询：监控数据库运行状态

监控重做日志切换频率

通过执行以下SQL语句，可以获取过去11天内每小时的重做日志切换次数，有助于评估数据库的日志文件大小是否合适以及日志切换对系统性能的影响：

SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 5) AS "Day",
       SUM(CASE WHEN SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2) = '00' THEN 1 ELSE 0 END) AS "H00",
       SUM(CASE WHEN SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2) = '01' THEN 1 ELSE 0 END) AS "H01",
       ...
       SUM(CASE WHEN SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2) = '23' THEN 1 ELSE 0 END) AS "H23",
       COUNT(*) AS "TOTAL"
FROM v$log_history
WHERE first_time >= TO_CHAR(SYSDATE - 11)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 5) DESC;

查询PGA与SGA内存使用情况

下面的SQL语句可以帮助数据库管理员了解当前系统中PGA和SGA的分配情况，这对于调整数据库配置以优化性能至关重要：

SELECT 'SGA' AS NAME, ROUND(SUM(value) / 1024 / 1024, 2) || 'M' AS "SIZE(M)" FROM v$sga
UNION
SELECT 'PGA' AS NAME, ROUND(value / 1024 / 1024, 2) || 'M' AS "SIZE(M)" FROM v$pgastat WHERE name = 'total PGA allocated'
UNION
SELECT 'TOTAL' AS NAME, (SELECT ROUND(SUM(value) / 1024 / 1024, 2) FROM v$sga) + (SELECT ROUND(value / 1024 / 1024, 2) FROM v$pgastat WHERE name = 'total PGA allocated') || 'M' AS "SIZE(M)" FROM DUAL
UNION
SELECT name, TO_CHAR(value) FROM v$pgastat WHERE name = 'process count';

此外，还可以通过以下查询来查看不同客户端机器上会话的状态分布，这有助于识别潜在的连接问题或资源消耗过高的会话：

SELECT machine, status, COUNT(*) FROM v$session GROUP BY machine, status ORDER BY status;