关于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
