1、捕捉运行很久的SQL

column username format a12
        column opname format a16
        column progress format a8
        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;

2、求DISK READ较多的SQL

SELECT St.Sql_Text
        FROM V$sql s, V$sqltext St
        WHERE s.Address = St.Address
        AND s.Hash_Value = St.Hash_Value
        AND s.Disk_Reads > 300;

 3、求DISK SORT严重的SQL

SELECT Sess.Username, SQL.Sql_Text, Sort1.Blocks
        FROM V$session Sess, V$sqlarea SQL, V$sort_Usage Sort1
        WHERE Sess.Serial# = Sort1.Session_Num
        AND Sort1.Sqladdr = SQL.Address
        AND Sort1.Sqlhash = SQL.Hash_Value
        AND Sort1.Blocks > 200;

4、监控索引是否使用

alter index &index_name monitoring usage;
        alter index &index_name nomonitoring usage;
        *** * from v$object_usage where index_name = &index_name;

 5、求数据文件的I/O分布

SELECT Df.NAME, Phyrds, Phywrts, Phyblkrd, Phyblkwrt, Singleblkrds, Readtim,
        Writetim
        FROM V$filestat Fs, V$dbfile Df
        WHERE Fs.File# = Df.File#
        ORDER BY Df.NAME;

   6、查看还没提交的事务

*** * from v$locked_object;
        *** * from v$transaction;

 7、回滚段查看

SELECT Rownum, Sys.Dba_Rollback_Segs.Segment_Name NAME,
        V$rollstat.Extents Extents, V$rollstat.Rssize Size_In_Bytes,
        V$rollstat.Xacts Xacts, V$rollstat.Gets Gets, V$rollstat.Waits Waits,
        V$rollstat.Writes Writes, Sys.Dba_Rollback_Segs.Status Status
        FROM V$rollstat, Sys.Dba_Rollback_Segs, V$rollname
        WHERE V$rollname.NAME(+) = Sys.Dba_Rollback_Segs.Segment_Name
        AND V$rollstat.Usn(+) = V$rollname.Usn
        ORDER BY Rownum

 8、查看系统请求情况

SELECT Decode(NAME, 'summed dirty write queue length', VALUE) /
        Decode(NAME, 'write requests', VALUE) "Write Request Length"
        FROM V$sysstat
        WHERE NAME IN ('summed dirty queue length', 'write requests')
        AND VALUE > 0;

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(+);

17、求归档日志的切换频率(生产系统可能时间会很长)

SELECT Start_Recid, Start_Time, End_Recid, End_Time, Minutes
        FROM (SELECT Test.*, Rownum AS Rn
        FROM (SELECT b.Recid Start_Recid,
        To_Char(b.First_Time, 'yyyy-mm-dd hh24:mi:ss') Start_Time,
        a.Recid End_Recid,
        To_Char(a.First_Time, 'yyyy-mm-dd hh24:mi:ss') End_Time,
        Round(((a.First_Time - b.First_Time) * 24) * 60, 2) Minutes
        FROM V$log_History a, V$log_History b
        WHERE a.Recid = b.Recid + 1
        AND b.First_Time > SYSDATE - 1
        ORDER BY a.First_Time DESC) Test) y
        WHERE y.Rn < 30

18、求回滚段正在处理的事务

SELECT a.NAME, b.Xacts, c.Sid, c.Serial#, d.Sql_Text
        FROM V$rollname a, V$rollstat b, V$session c, V$sqltext d, V$transaction e
        WHERE a.Usn = b.Usn
        AND b.Usn = e.Xidusn
        AND c.Taddr = e.Addr
        AND c.Sql_Address = d.Address
        AND c.Sql_Hash_Value = d.Hash_Value
        ORDER BY a.NAME, c.Sid, d.Piece;