一些查看性能的查询语句

--CPU使用率
SELECT BEGIN_TIME,
       END_TIME           INTSIZE,
       NUM_INTERVAL,
       MINVAL,
       MAXVAL,
       AVERAGE,
       STANDARD_DEVIATION SD
FROM   DBA_HIST_SYSMETRIC_SUMMARY
WHERE  METRIC_ID = 2075
ORDER  BY BEGIN_TIME DESC;
    
--可以查看的统计信息
SELECT * FROM V$STATNAME;
--相关统计信息的值
SELECT * FROM V$SYSSTAT A WHERE A.STATISTIC# = 19;
--AWR（Automatic Workload Repository）的体系结构之后有如下系统表做支撑
--属于SYS用户但是放在SYSAUX表空间
SELECT * FROM sys.Wri$_Alert_History;
SYS.WRI$_;    (INTERNAL)
SYS.WRM$_;    (META_DATA)
SYS.WRH$_;    (HISTORY)
SELECT * FROM sys.Wrm$_Snapshot;
--AWR的数据由MMON进程每小时收集一次
SELECT * FROM V$BGPROCESS;
SELECT * FROM DBA_HIST_WR_CONTROL;
--oracle内存命中率
SELECT A.SNAP_ID,
       B.BEGIN_INTERVAL_TIME,
       B.END_INTERVAL_TIME,
       (A.DB_BLOCK_GETS + A.CONSISTENT_GETS) /
       (A.DB_BLOCK_GETS + A.CONSISTENT_GETS + A.PHYSICAL_READS) * 100 内存的命中率
FROM   DBA_HIST_BUFFER_POOL_STAT A
LEFT   JOIN DBA_HIST_SNAPSHOT B ON A.SNAP_ID = B.SNAP_ID
ORDER  BY A.SNAP_ID;
--快照
SELECT * FROM DBA_HIST_SNAPSHOT;
--收集表的统计信息
ANALYZE TABLE TABLENAME COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL COLUMNS;
--查看执行时间比较长的SQL
SELECT B.SQL_TEXT,
       (A.LAST_UPDATE_TIME - A.START_TIME) * 24 * 60 "total_time(s)",
       A.*
FROM   V$SESSION_LONGOPS A
LEFT   JOIN V$SQLAREA B ON A.SQL_ADDRESS = B.ADDRESS
WHERE  A.SID = '131'
ORDER  BY A.START_TIME DESC;
--SQL完成比率
SELECT SID,
       OPNAME,
       TARGET_DESC,
       SOFAR,
       TOTALWORK,
       TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PERWORK
FROM   V$SESSION_LONGOPS
WHERE  SOFAR != TOTALWORK;
--调整滥用磁盘读操作的主要语句
--我发现在没有作调整的情况下，在绝大多数的系统中，访问量占前25位的语句的磁盘读操作将占用整个系统所有磁盘和/或内存读操作的75%。
SELECT DISK_READS, A.*
FROM   V$SQLAREA A
ORDER  BY A.DISK_READS DESC;