关于oracle数据库性能监控指标
-- 当前活跃⽤户会话数线性排水沟尺寸
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;
-- ⾼速缓存命中率
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 "⽤户名",
14n
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;
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议