oracle数据库性能监控常用sql
因执行时间较长建议使用plsql等第三方工具执行
--1.监控sga内存分配信息
select * from v$sgainfo;
--2.监控每个用户的磁盘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;
--3.查询最近执行的语句
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)>'2022-08-23/14:00:00' --某时间以后执行的sql
order by last_load_time desc;
--4.查询会话信息和会话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;
----5.查询当前正在执行的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;
---6.查询刚执行过的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;
---7.查询表空间每个文件的空闲空间
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;
本文来自博客园,作者: 三生有幸格格,转载请注明原文链接:https://www.cnblogs.com/mylive/p/16619599.html
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 百万级群聊的设计实践
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 全网最简单!3分钟用满血DeepSeek R1开发一款AI智能客服,零代码轻松接入微信、公众号、小程
· .NET 10 首个预览版发布,跨平台开发与性能全面提升
· 《HelloGitHub》第 107 期