一些查看性能的查询语句

--CPU使用率SELECT BEGIN_TIME,       END_TIME           INTSIZE,       NUM_INTERVAL,       MINVAL,       MAXVAL,       AVERAGE,       STANDARD_DEVIATION SDFROM   DBA_HIST_SYSMETRIC_SUMMARYWHERE  METRIC_ID = 2075ORDER  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 ALEFT   JOIN DBA_HIST_SNAPSHOT B ON A.SNAP_ID = B.SNAP_IDORDER  BY A.SNAP_ID;--快照SELECT * FROM DBA_HIST_SNAPSHOT;--收集表的统计信息ANALYZE TABLE TABLENAME COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL COLUMNS;--查看执行时间比较长的SQLSELECT B.SQL_TEXT,       (A.LAST_UPDATE_TIME - A.START_TIME) * 24 * 60 "total_time(s)",       A.*FROM   V$SESSION_LONGOPS ALEFT   JOIN V$SQLAREA B ON A.SQL_ADDRESS = B.ADDRESSWHERE  A.SID = '131'ORDER  BY A.START_TIME DESC;--SQL完成比率SELECT SID,       OPNAME,       TARGET_DESC,       SOFAR,       TOTALWORK,       TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PERWORKFROM   V$SESSION_LONGOPSWHERE  SOFAR != TOTALWORK;--调整滥用磁盘读操作的主要语句--我发现在没有作调整的情况下，在绝大多数的系统中，访问量占前25位的语句的磁盘读操作将占用整个系统所有磁盘和/或内存读操作的75%。SELECT DISK_READS, A.*FROM   V$SQLAREA AORDER  BY A.DISK_READS DESC;