7.6 监控表空间的 I/O 比例
select df.tablespace_name name, df.file_name \ f.phyrds pyr, f.phyblkrd pbr, f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name;
7.7 监控文件系统的 I/O 比例
select substr(a.file#, 1, 2) \ substr(a.name, 1, 30) \ a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b where a.file# = b.file#;
7.8 监控 SGA 的命中率
select a.value + b.value \ c.value \ round(100 * ((a.value + b.value) - c.value) / (a.value + b.value)) \
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 -- physical read total multi block requests and b.statistic# = 39 -- physical read total bytes
and c.statistic# = 40; -- physical write total IO requests
7.9 监控 SGA 中字典缓冲区的命中率
select parameter, gets,
Getmisses,
getmisses / (gets + getmisses) * 100 \ (1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100 \ratio\
from v$rowcache
where gets + getmisses <> 0
group by parameter, gets, getmisses;
7.10 监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) \
sum(reloads) \
sum(reloads) / sum(pins) libcache from v$librarycache;
select sum(pinhits - reloads) / sum(pins) * 100 \ sum(reloads) / sum(pins) \ from v$librarycache;
7.11 临控 SGA 中重做日志缓存区的命中率，应该小于1%
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');
7.12 监控内存和硬盘的排序比率，最好使它小于 0.10，增加
sort_area_size
SELECT name, value FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
7.13 监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address = b.address order by address, piece;
7.14 监控字典缓冲区
SELECT SUM(PINS) \
SUM(RELOADS) \ (SUM(PINS - RELOADS)) / SUM(PINS) \ FROM V$LIBRARYCACHE;
SELECT SUM(GETS) \
SUM(GETMISSES) \
(SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) \CACHE\ FROM V$ROWCACHE;
“LIB CACHE“与“ROW CACHE”越接近1.00超好，不要低于0.90。否则需要调大SGA的空间。
7.15 查看Lock
select s.osuser, l.sid,
s.serial#, s.username, s.terminal, decode(l.type, 'TM',
'TM - DML Enqueue', 'TX',
'TX - Trans Enqueue', 'UL',
'UL - User',
l.type || ' - Other Type') LOCKTYPE, substr(t.name, 1, 10) OBJECT, u.name owner, l.id1, l.id2,
decode(l.lmode, 1,
'No Lock', 2,
'Row Share', 3,
'Row Exclusive', 4,
'Share', 5,
'Shr Row Excl', 6,
'Exclusive', null) lmode, decode(l.request, 1,
'No Lock', 2,
'Row Share', 3,
'Row Excl', 4,
'Share', 5,
'Shr Row Excl', 6,
'Exclusive', null) request
from v$lock l, v$session s, sys.user$ u, sys.obj$ t where l.sid = s.sid
and s.type != 'BACKGROUND' and t.obj# = l.id1
and u.user# = t.owner#;
7.16 捕捉运行很久的SQL
select username, sid, opname,
round(sofar * 100 / totalwork, 0) || '%' as progress, time_remaining, sql_text
from v$session_longops, v$sql where time_remaining <> 0 and sql_address = address
and sql_hash_value = hash_value;
7.17 查看数据表的参数信息
SELECT partition_name, table_name, high_value,
high_value_length, tablespace_name, pct_free, pct_used, ini_trans, max_trans,
initial_extent, next_extent, min_extent, max_extent, pct_increase, FREELISTS,
freelist_groups, LOGGING,
BUFFER_POOL, num_rows, blocks,
empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size, last_analyzed
FROM sys.dba_tab_partitions
WHERE table_owner = 'HS_HIS' AND table_name = 'HISBANKTRADE' --WHERE table_name = :tname AND table_owner = :towner ORDER BY partition_position,partition_name;
7.18 查看还没提交的事务
select * from v$locked_object; select * from v$transaction;
7.19 查找object为哪些进程所用
select p.spid, s.sid,