Oracle性能监控SQL

-- 查询会话：
SELECT status, type, COUNT(*) as value FROM v$session GROUP BY status, type;

-- 查询资源使用情况：
SELECT resource_name,current_utilization,CASE WHEN TRIM(limit_value) LIKE 'UNLIMITED' THEN '-1' ELSE TRIM(limit_value) END as limit_value FROM v$resource_limit；

-- 查询asm_diskgroup：
SELECT name,total_mb10241024 as total,free_mb10241024 as free FROM v$asm_diskgroup_stat;

-- 查询v$sysstat:
SELECT name, value FROM v$sysstat WHERE name IN ('parse count (total)', 'execute count', 'user commits', 'user rollbacks');

-- 查询系统进程数：
SELECT COUNT(*) as count FROM v$process;

-- 查询系统等待：
SELECT n.wait_class as WAIT_CLASS,
round(m.time_waited / m.INTSIZE_CSEC, 3) as VALUE
FROM v$waitclassmetric m, v$system_wait_class n
WHERE m.wait_class_id = n.wait_class_id
AND n.wait_class != 'Idle';

-- 查询表空间：
SELECT
df.tablespace_name as tablespace,
df.type as type,
nvl(sum(df.bytes),0) as bytes,
nvl(sum(df.max_bytes),0) as max_bytes,
nvl(sum(f.free),0) as free
FROM
(
SELECT
ddf.file_id,
dt.contents as type,
ddf.file_name,
ddf.tablespace_name,
TRUNC(ddf.bytes) as bytes,
TRUNC(GREATEST(ddf.bytes,ddf.maxbytes)) as max_bytes
FROM
dba_data_files ddf,
dba_tablespaces dt
WHERE ddf.tablespace_name = dt.tablespace_name
) df,
(
SELECT
TRUNC(SUM(bytes)) AS free,
file_id
FROM dba_free_space
GROUP BY file_id
) f
WHERE df.file_id = f.file_id (+)
GROUP BY df.tablespace_name, df.type
UNION ALL
SELECT
Y.name as tablespace_name,
Y.type as type,
SUM(Y.bytes) as bytes,
SUM(Y.max_bytes) as max_bytes,
MAX(nvl(Y.free_bytes,0)) as free
FROM
(
SELECT
dtf.tablespace_name as name,
dt.contents as type,
dtf.status as status,
dtf.bytes as bytes,
(
SELECT
((f.total_blocks - s.tot_used_blocks)*vp.value)
FROM
(SELECT tablespace_name, sum(used_blocks) tot_used_blocks FROM gv$sort_segment WHERE tablespace_name!='DUMMY' GROUP BY tablespace_name) s,
(SELECT tablespace_name, sum(blocks) total_blocks FROM dba_temp_files where tablespace_name !='DUMMY' GROUP BY tablespace_name) f,
(SELECT value FROM v$parameter WHERE name = 'db_block_size') vp
WHERE f.tablespace_name=s.tablespace_name AND f.tablespace_name = dtf.tablespace_name
) as free_bytes,
CASE
WHEN dtf.maxbytes = 0 THEN dtf.bytes
ELSE dtf.maxbytes
END as max_bytes
FROM
sys.dba_temp_files dtf,
sys.dba_tablespaces dt
WHERE dtf.tablespace_name = dt.tablespace_name
) Y
GROUP BY Y.name, Y.type
ORDER BY tablespace