Oracle数据库维护常用SQL语句集合

现在的位置: 首页 > 综合 > 正文

Oracle数据库维护常用SQL语句集合

2013年09月09日 ⁄ 综合 ⁄ 共 8337字 ⁄ 字号小中大 ⁄ 评论关闭

性能相关内容

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;
        select * 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、查看还没提交的事务

select * from v$locked_object;
select * 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;

19、求某个事务的重做信息(bytes)

         SELECT s.NAME, m.VALUE
        FROM V$mystat m, V$statname s
        WHERE m.Statistic# = s.Statistic#
        AND s.NAME LIKE '%redo size%';

20、求cache中缓存超过其5%的对象

         SELECT o.Owner, o.Object_Type, o.Object_Name, COUNT(b.Objd)
        FROM V$bh b, Dba_Objects o
        WHERE b.Objd = o.Object_Id
        GROUP BY o.Owner, o.Object_Type, o.Object_Name
        HAVING COUNT(b.Objd) > (SELECT To_Number(VALUE) * 0.05
        FROM V$parameter
        WHERE NAME = 'db_block_buffers');

21、求buffer cache中的块信息

         SELECT o.Object_Type, Substr(o.Object_Name, 1, 10) Objname, b.Objd, b.Status,
        COUNT(b.Objd)
        FROM V$bh b, Dba_Objects o
        WHERE b.Objd = o.Data_Object_Id
        AND o.Owner = '&owner'
        GROUP BY o.Object_Type, o.Object_Name, b.Objd, b.Status;

22、求日志文件的空间使用

         SELECT Le.Leseq Current_Log_Sequence#,
        100 * Cp.Cpodr_Bno / Le.Lesiz Percentage_Full
        FROM X$kcccp Cp, X$kccle Le
        WHERE Le.Leseq = Cp.Cpodr_Seq;

23、求等待中的对象

         SELECT /*+rule */
        s.Sid, s.Username, w.Event, o.Owner, o.Segment_Name, o.Segment_Type,
        o.Partition_Name, w.Seconds_In_Wait Seconds, w.State
        FROM V$session_Wait w, V$session s, Dba_Extents o
        WHERE w.Event IN (SELECT NAME
        FROM V$event_Name
        WHERE Parameter1 = 'file#'
        AND Parameter2 = 'block#'
        AND NAME NOT LIKE 'control%')
        AND o.Owner <> 'sys'
        AND w.Sid = s.Sid
        AND w.P1 = o.File_Id
        AND w.P2 >= o.Block_Id
        AND w.P2 < o.Block_Id + o.Blocks

24、求当前事务的重做尺寸

         SELECT V$statname.NAME,VALUE
        FROM V$mystat, V$statname
        WHERE V$mystat.Statistic# = V$statname.Statistic#
        AND V$statname.NAME = 'redo size';

25、唤醒smon去清除临时段

         column pid new_value Smon
        set termout off
        SELECT p.Pid
        FROM Sys.v_$bgprocess b, Sys.v_$process p
        WHERE b.NAME = 'SMON'
        AND p.Addr = b.Paddr;
        SET Termout ON Oradebug Wakeup &Smon Undefine Smon

26、求回退率

         SELECT b.VALUE / (a.VALUE + b.VALUE), a.VALUE, b.VALUE
        FROM V$sysstat a, V$sysstat b
        WHERE a.Statistic# = 4
        AND b.Statistic# = 5;

27、求free memory

         SELECT *
        FROM V$sgastat
        WHERE NAME = 'free memory';
        SELECT a.NAME, SUM(b.VALUE)
        FROM V$statname a, V$sesstat b
        WHERE a.Statistic# = b.Statistic#
        GROUP BY a.NAME;

查看一下谁在使用那个可以得回滚段，或者查看一下某个可以得用户在使用回滚段，

找出领回滚段不断增长的事务，再看看如何处理它，是否可以将它commit，再不行

就看看能否kill它，等等, 查看当前正在使用的回滚段的用户信息和回滚段信息:

         set linesize 121
        SELECT r.NAME "ROLLBACK SEGMENT NAME ", l.Sid "ORACLE PID",
        p.Spid "SYSTEM PID ", s.Username "ORACLE USERNAME"
        FROM V$lock l, V$process p, V$rollname r, V$session s
        WHERE l.Sid = p.Pid(+)
        AND s.Sid = l.Sid
        AND Trunc(l.Id1(+) / 65536) = r.Usn
        AND l.TYPE(+) = 'TX'
        AND l.Lmode(+) = 6
        ORDER BY r.NAME;

28、查看用户的回滚段的信息

         SELECT s.Username, Rn.NAME
        FROM V$session s, V$transaction t, V$rollstat r, V$rollname Rn
        WHERE s.Saddr = t.Ses_Addr
        AND t.Xidusn = r.Usn
        AND r.Usn = Rn.Usn

29、查看内存中存的使用

        SELECT Decode(Greatest(CLASS, 10),
        10,
        Decode(CLASS, 1, 'Data', 2, 'Sort', 4, 'Header', To_Char(CLASS)), 'Rollback') "Class",
        SUM(Decode(Bitand(Flag, 1), 1, 0, 1)) "Not Dirty",
        SUM(Decode(Bitand(Flag, 1), 1, 1, 0)) "Dirty",
        SUM(Dirty_Queue) "On Dirty", COUNT(*) "Total"
        FROM X$bh
        GROUP BY Decode(Greatest(CLASS, 10),
        10,
        Decode(CLASS, 1, 'Data', 2, 'Sort', 4, 'Header', To_Char(CLASS)), 'Rollback');

【上篇】深入探讨如何在 C# 中使用 Win32 和其他现有库
【下篇】linux shell 对两个文件的处理

作者: fractured

该日志由 fractured 于11年前发表在综合分类下，最后更新于 2013年09月09日.
转载请注明: Oracle数据库维护常用SQL语句集合 | 学步园 +复制链接

抱歉!评论已关闭.

学步园

Oracle数据库维护常用SQL语句集合

作者: fractured

书签

最新文章New

本站推荐

返回首页