关于oracle数据库性能监控指标

精心整理纯干货

便于使用，可以直接复制执行。

--1. 当前连接会话数以及当前并发连接个数
-- 当前活跃用户会话数
Select count(*) from v$session  where status='ACTIVE' and USERNAME is not null;
-- 当前活跃系统会话数
Select count(*) from v$session  where USER# = 0;
-- 当前非活跃会话数
Select count(*) from v$session  where status='INACTIVE'
---
--2. 表空间利用率(各个表空间的大小以及使用情况)
SELECT
 UPPER( F.TABLESPACE_NAME ) "表空间名",
 D.TOT_GROOTTE_MB "表空间大小(M)",
 D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
 TO_CHAR( ROUND(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2 ), '990.99' ) || '%' "使用比",
 F.TOTAL_BYTES "空闲空间(M)",
 F.MAX_BYTES "最大块(M)" 
FROM (SELECT
  TABLESPACE_NAME,
  ROUND( SUM( BYTES ) / ( 1024 * 1024 ), 2 ) TOTAL_BYTES,
  ROUND( MAX( BYTES ) / ( 1024 * 1024 ), 2 ) MAX_BYTES 
 FROM SYS.DBA_FREE_SPACE 
 GROUP BY TABLESPACE_NAME 
 ) F,
 (SELECT
  DD.TABLESPACE_NAME,ROUND( SUM( DD.BYTES ) / ( 1024 * 1024 ), 2 ) TOT_GROOTTE_MB 
 FROM SYS.DBA_DATA_FILES DD 
 GROUP BY DD.TABLESPACE_NAME 
 ) D 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 
ORDER BY 1;
---
--3. Oracle IOPS(每秒的IO)和吞吐量
--网上给出的答案是这样的
SELECT
  sum(decode( name, 'physical read IO requests', value, 'physical write IO requests', value, 0 )) AS iops,
  sum(decode( name, 'physical read bytes', value, 'physical write bytes', value, 0 )) / 1024 / 1024 AS mbps
FROM v$sysstat
WHERE name IN 
(
 'physical read IO requests',
 'physical write IO requests',
 'physical read bytes',
 'physical read total bytes',
 'physical write bytes',
 'physical write total bytes',
 'physical read total IO requests',
 'physical write total IO requests'
);
--我对这个答案有一些疑惑，如果是物理io请求，那么这样得到的是io的总数，而不是每秒的io啊，另一个方法请看第 18 条
---
4.碎片程度
SELECT tablespace_name,
 round( sqrt( max( blocks ) / sum( blocks )) * ( 100 / sqrt( sqrt( count( blocks )))), 2 ) FSFI 
FROM dba_free_space 
GROUP BY tablespace_name 
ORDER BY 1;
---
--5.高速缓存命中率
-- 高速缓存命中率
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
  1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS
WHERE NAME='DEFAULT';
-- 或者 
SELECT physical_reads,db_block_gets,consistent_gets,NAME,
100 * (1 - (physical_reads / ( consistent_gets + db_block_gets - physical_reads ))) "Data Buffer Hit Ratio"
FROM v$buffer_pool_statistics;
-- 共享池命中率
select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache;
-- 共享区字典缓冲区命中率
select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache;
-- 共享区缓存命中率
select sum(pins-reloads)/sum(pins) from v$librarycache;
-- 已经被包含在5里面
---
--6.库高速缓存命中率
SELECT TO_CHAR(
 ROUND(( 1 - SUM( getmisses ) / SUM( gets )) * 100, 1 )) || '%' "Dictionary Cache Hit Ratio" 
FROM v$rowcache;
---
--7.字典缓存命中率
SELECT sum( gets - getmisses - usage - fixed ) / sum( gets ) 　　 
FROM v$rowcache;
---
--8.空闲的共享池内存
SELECT * 
FROM V$SGASTAT 
WHERE NAME = 'free memory' 
 AND POOL = 'shared pool';
---
--9.共享池使用率
SELECT ( 1 - ROUND( BYTES / ( select sum(value) "total sga MB" from v$sga ), 2 )) * 100 || '%' 
FROM V$SGASTAT 
WHERE NAME = 'free memory' 
 AND POOL = 'shared pool';
---
--10.数据库服务启动时间
select * from sys.v_$instance
---
--11.共享缓冲池中重做日志命中率
SELECT name, gets, misses, immediate_gets, immediate_misses,
 Decode( gets, 0, 0, misses / gets * 100 ) ratio1,
 Decode( immediate_gets + immediate_misses, 0, 0, immediate_misses / ( immediate_gets + immediate_misses ) * 100 ) ratio2 
FROM v$latch 
WHERE name IN ( 'redo allocation', 'redo copy' );
---
--12.当前oracle所有进程执行进度
--当前进程的数量
select count(*) from v$process;
SELECT A.USERNAME,A.TARGET,A.SID,a.SERIAL#,A.OPNAME,
       ROUND(A.SOFAR * 100 / A.TOTALWORK, 0) || '%' AS "执行进度",
       A.TIME_REMAINING "剩余秒数",
       TRUNC(A.TIME_REMAINING / 60, 2) "剩余分钟",
       TRUNC(A.TIME_REMAINING / 60/60, 2) "剩余小时",
       B.SQL_TEXT "执行SQL",
       B.LAST_ACTIVE_TIME "开始时间"
  FROM V$SESSION_LONGOPS A, V$SQLAREA B
 WHERE A.TIME_REMAINING <> 0
   AND A.SQL_ADDRESS = B.ADDRESS
   AND A.SQL_HASH_VALUE = B.HASH_VALUE
---
--13.查询SGA和PGA的使用情况
select name,round(total,2),round(total - free, 2) used,round(free, 2) free,
       round((total - free) / total * 100, 2) pctused
from (select 'SGA' name,
		   (select sum(value / 1024 / 1024) from v$sga) total,
		   (select sum(bytes / 1024 / 1024)
			  from v$sgastat
			 where name = 'free memory') free
	  from dual)
union
select name,
       round(total,2),
       round(used, 2) used,
       round(total - used, 2) free,
       round(used / total * 100, 2) pctused
from (select 'PGA' name,
		   (select value / 1024 / 1024 total
			  from v$pgastat
			 where name = 'aggregate PGA target parameter') total,
		   (select value / 1024 / 1024 used
			  from v$pgastat
			 where name = 'total PGA allocated') used
	  from dual);
---
--13. 查看会话的内存占用情况
SELECT server "连接类型", s.username "用户名", OSUSEr "系统用户",NAME,
       round(VALUE / 1024 / 1024 ,2) "占用内存(M)",s.SID "会话ID",s.serial# 会话序列号,
       spid "系统进程ID",p.PGA_USED_MEM,p.PGA_ALLOC_MEM,p.PGA_FREEABLE_MEM,p.PGA_MAX_MEM
FROM v$session s, v$sesstat st, v$statname sn, v$process p
WHERE st.SID = s.SID
  AND st.statistic# = sn.statistic#
  AND sn.NAME LIKE 'session pga memory'
  AND p.addr = s.paddr
ORDER BY VALUE DESC
---
--14.查询耗时最长的10条sql
select *
from (select v.sql_id,
		   v.child_number,
		   v.sql_text,
		   v.elapsed_time,
		   v.cpu_time,
		   v.disk_reads,
		   rank() over(order by v.elapsed_time desc) elapsed_rank
	  from v$sql v) a
where elapsed_rank <= 10;
---
--15.MTS
select busy/(busy+idle) shared_servers_busy from v$dispatcher
---
--16.表级锁
select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode
from v$locked_object lo, dba_objects ao, v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
---
--17.正在等待锁的所有会话
SELECT * FROM DBA_WAITERS;
---
--18.TPS,QPS,MBPS
SELECT
 'Qps|' AS begin1,
 ( SELECT sum( value ) FROM gv$sysmetric WHERE metric_name = 'I/O Requests per Second' ) AS b1,
 '|' AS end1,
 'Tps|' AS begin2,
 ( SELECT sum( value ) FROM gv$sysmetric WHERE metric_name = 'User Transaction Per Sec' ) AS b2,
 '|' AS end2,
 'Mbps|' AS begin3,
 ( SELECT sum( value ) FROM gv$sysmetric WHERE metric_name = 'I/O Megabytes per Second' ) AS b3,
 '|' AS end3 
FROM dual;
---
--19.数据库容量大小（GB）以及数据库各个表空间大小
-- 数据库总大小
SELECT ROUND(SUM(BYTES)/1024/1024/1024,2)||'GB' 
FROM DBA_DATA_FILES;
-- 数据库各个表空间大小
select tablespace_name ,sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name;
---
--20.审计信息
-- 具体的审计信息
select * from dba_audit_trail
-- 是否打开审计功能 (默认是未开启，NONE表示未开启，其他的应该就是开启，比如 DB_EXTENDED)
SELECT * from v$parameter WHERE name = 'audit_trail'
---
--21.当前数据库的版本信息
SELECT * from v$version
---
--22. 获取不用用户不同操作的审计执行频率
SELECT username, action_name, count( * ) c 
FROM dba_audit_trail 
GROUP BY username, action_name 
ORDER BY c DESC
---
--23.查询不同用户的连接数
SELECT username, count( username ) 
FROM v$session 
WHERE username IS NOT NULL 
GROUP BY username;
---
--24.被锁定或者已经过期的用户
select username,lock_date from dba_users WHERE ACCOUNT_STATUS in ('LOCKED','EXPIRED & LOCKED')
https://blog.csdn.net/qq_23296327/article/details/104894975