Oracle运维详细手册(4)

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,

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Oracle运维详细手册(4)在线全文阅读。