Oracle数据库性能监控常用Sql

监控sga内存分配信息

select * from v$sgainfo;

监控每个用户的磁盘io及io命中率

select v$sess_io.*,(block_gets+consistent_gets) reads,
((block_gets+consistent_gets-physical_reads)/(block_gets+consistent_gets)) ratio
from v$sess_io
where (block_gets+consistent_gets)>=1000
order by (block_gets+consistent_gets) desc;

查询最近执行的sql语句

select last_load_time,
disk_reads,
sorts,
fetches,
buffer_gets,
optimizer_cost,
cpu_time,
sql_fulltext,
sql_text
from v$sql
where to_char(last_load_time)> '2011-05-10/14:00:00' 
order by last_load_time desc;

查询会话信息和会话io

select a.sid,
block_gets,
consistent_gets,
physical_reads,
block_changes,
consistent_changes,
b.serial#,
b.username,
b.command,
b.server,
b.machine,
b.terminal,
b.program
from v$sess_io a,
v$session b
where a.sid=b.sid
order by 5 desc;

查询当前正在执行的sql及io信息

select a.sql_text,
a.sql_fulltext,
a.cpu_time,
b.sid,
b.serial#,
b.username,
b.machine,
b.terminal,
b.program,
c.block_gets,
c.consistent_gets,
c.physical_reads,
c.block_changes,
c.consistent_changes
from v$sql a,
v$session b,
v$sess_io c
where a.address=b.sql_address
and b.sid=c.sid
order by c.block_changes desc;

查询刚执行过的sql和io信息

select a.sql_text,
a.sql_fulltext,
a.cpu_time,
b.sid,
b.serial#,
b.username,
b.machine,
b.terminal,
b.program,
c.block_gets,
c.consistent_gets,
c.physical_reads,
c.block_changes,
c.consistent_changes
from v$sql a,
v$session b,
v$sess_io c
where a.address=b.prev_sql_addr
and b.sid=c.sid
order by c.block_changes desc;

查询表空间每个文件的空闲空间

select a.tablespace_name,b.file_name,sum(a.bytes/1024/1024) free_mb,b.bytes/1024/1024
size_mb,b.autoextensible,b.maxbytes/1024/1024 max_mb
from dba_free_space a,dba_data_files b where a.file_id=b.file_id
group by a.tablespace_name,b.file_name,b.bytes,b.autoextensible,b.maxbytes order by
a.tablespace_name,b.file_name;