全部答案

2018-04-03 07:39:18

•   性能相关内容
  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';。

  全部
  

  M***

  2018-04-03 07:39:18

  44 3 评论

  分享
  提交评论