9、计算data buffer 命中率

SELECT a.VALUE + b.VALUE "logical_reads", c.VALUE "phys_reads",
Round(100 * ((a.VALUE + b.VALUE) - c.VALUE) / (a.VALUE + b.VALUE)) "BUFFER HIT RATIO"
FROM V$sysstat a, V$sysstat b, V$sysstat c
WHERE a.Statistic# = 40
AND b.Statistic# = 41
AND c.Statistic# = 42;

SELECT NAME,
(1 - (Physical_Reads / (Db_Block_Gets + Consistent_Gets))) * 100 h_Ratio
FROM V$buffer_Pool_Statistics;

10、查看内存使用情况

SELECT Least(MAX(b.VALUE) / (1024 * 1024), SUM(a.Bytes) / (1024 * 1024)) Shared_Pool_Used,
MAX(b.VALUE) / (1024 * 1024) Shared_Pool_Size,
Greatest(MAX(b.VALUE) / (1024 * 1024), SUM(a.Bytes) / (1024 * 1024)) -
(SUM(a.Bytes) / (1024 * 1024)) Shared_Pool_Avail,
((SUM(a.Bytes) / (1024 * 1024)) / (MAX(b.VALUE) / (1024 * 1024))) * 100 Avail_Pool_Pct
FROM V$sgastat a, V$parameter b
WHERE (a.Pool = 'shared pool' AND a.NAME NOT IN ('free memory'))
AND b.NAME = 'shared_pool_size';

11、查看用户使用内存情况

SELECT Username, SUM(Sharable_Mem), SUM(Persistent_Mem), SUM(Runtime_Mem)
FROM Sys.v_$sqlarea a, Dba_Users b
WHERE a.Parsing_User_Id = b.User_Id
GROUP BY Username;

12、查看对象的缓存情况

SELECT Owner, Namespace, TYPE, NAME, Sharable_Mem, Loads, Executions, Locks,
Pins, Kept
FROM V$db_Object_Cache
WHERE TYPE NOT IN
('NOT LOADED', 'NON-EXISTENT', 'VIEW', 'TABLE', 'SEQUENCE')
AND Executions > 0
AND Loads > 1
AND Kept = 'NO'
ORDER BY Owner, Namespace, TYPE, Executions DESC;
SELECT TYPE, COUNT(*)
FROM V$db_Object_Cache
GROUP BY TYPE;

13、查看库缓存命中率

SELECT Namespace, Gets, Gethitratio * 100 Gethitratio, Pins,
Pinhitratio * 100 Pinhitratio, Reloads, Invalidations
FROM V$librarycache

14、查看某些用户的hash

SELECT a.Username, COUNT(b.Hash_Value) Total_Hash,
COUNT(b.Hash_Value) - COUNT(UNIQUE(b.Hash_Value)) Same_Hash,
(COUNT(UNIQUE(b.Hash_Value)) / COUNT(b.Hash_Value)) * 100 u_Hash_Ratio
FROM Dba_Users a, V$sqlarea b
WHERE a.User_Id = b.Parsing_User_Id
GROUP BY a.Username;

15、查看字典命中率

SELECT (SUM(Getmisses) / SUM(Gets)) Ratio
FROM V$rowcache;

16、查看undo段的使用情况

SELECT d.Segment_Name, Extents, Optsize, Shrinks, Aveshrink, Aveactive,
d.Status
FROM V$rollname n, V$rollstat s, Dba_Rollback_Segs d
WHERE d.Segment_Id = n.Usn(+)
AND d.Segment_Id = s.Usn(+);