Oracle数据库维护常用SQL语句集合指的是什么？

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