DBA_HIST_BUFFER_POOL_STAT,DBA_HIST_SYSMETRIC_SUMMARY

DBA_HIST_BUFFER_POOL_STAT

DBA_HIST_BUFFER_POOL_STAT displays historical statistics about all buffer pools available for the instance. This view contains snapshots ofV$BUFFER_POOL_STATISTICS.

Column	Datatype	NULL	Description
SNAP_ID	NUMBER	NOT NULL	Unique snapshot ID
DBID	NUMBER	NOT NULL	Database ID for the snapshot
INSTANCE_NUMBER	NUMBER	NOT NULL	Instance number for the snapshot
ID	NUMBER	NOT NULL	Buffer pool identifier number
NAME	VARCHAR2(20)		Name of the buffer pool
BLOCK_SIZE	NUMBER		Block Size
SET_MSIZE	NUMBER		Buffer pool maximum set size
CNUM_REPL	NUMBER		Number of buffers on the replacement list
CNUM_WRITE	NUMBER		Number of buffers on the write list
CNUM_SET	NUMBER		Number of buffers in the set
BUF_GOT	NUMBER		Number of buffers gotten by the set
SUM_WRITE	NUMBER		Number of buffers written by the set
SUM_SCAN	NUMBER		Number of buffers scanned in the set
FREE_BUFFER_WAIT	NUMBER		Free buffer wait statistic
WRITE_COMPLETE_WAIT	NUMBER		Write complete wait statistic
BUFFER_BUSY_WAIT	NUMBER		Buffer busy wait statistic
FREE_BUFFER_INSPECTED	NUMBER		Free buffer inspected statistic
DIRTY_BUFFERS_INSPECTED	NUMBER		Dirty buffers inspected statistic
DB_BLOCK_CHANGE	NUMBER		Database blocks changed statistic
DB_BLOCK_GETS	NUMBER		Database blocks gotten statistic
CONSISTENT_GETS	NUMBER		Consistent gets statistic
PHYSICAL_READS	NUMBER		Physical reads statistic
PHYSICAL_WRITES	NUMBER		Physical writes statistic

DBA_HIST_SYSMETRIC_SUMMARY

DBA_HIST_SYSMETRIC_SUMMARY displays a history of statistical summary of all metric values in the System Metrics Long Duration group. This view contains snapshots of V$SYSMETRIC_SUMMARY.

Column	Datatype	NULL	Description
SNAP_ID	NUMBER	NOT NULL	Unique snapshot ID
DBID	NUMBER	NOT NULL	Database ID for the snapshot
INSTANCE_NUMBER	NUMBER	NOT NULL	Instance number for the snapshot
BEGIN_TIME	DATE	NOT NULL	Begin time of the interval
END_TIME	DATE	NOT NULL	End time of the interval
INTSIZE	NUMBER	NOT NULL	Interval size (in hundredths of a second)
GROUP_ID	NUMBER	NOT NULL	Group ID
METRIC_ID	NUMBER	NOT NULL	Metric ID
METRIC_NAME	VARCHAR2(64)	NOT NULL	Metric name
METRIC_UNIT	VARCHAR2(64)	NOT NULL	Unit of measurement
NUM_INTERVAL	NUMBER	NOT NULL	Number of intervals observed
MINVAL	NUMBER	NOT NULL	Minimum value observed
MAXVAL	NUMBER	NOT NULL	Maximum value observed
AVERAGE	NUMBER	NOT NULL	Average over the period
STANDARD_DEVIATION	NUMBER	NOT NULL	One standard deviation

  

                 --CPU使用率  

1. 1. SELECT BEGIN_TIME,  
   2.        END_TIME           INTSIZE,  
   3.        NUM_INTERVAL,  
   4.        MINVAL,  
   5.        MAXVAL,  
   6.        AVERAGE,  
   7.        STANDARD_DEVIATION SD  
   8. FROM   DBA_HIST_SYSMETRIC_SUMMARY  
   9. WHERE  METRIC_ID = 2075  
   10. ORDER  BY BEGIN_TIME DESC;  
   11.       
   12. --可以查看的统计信息  
   13. SELECT * FROM V$STATNAME;  
   14. --相关统计信息的值  
   15. SELECT * FROM V$SYSSTAT A WHERE A.STATISTIC# = 19;  
   16. --AWR（Automatic Workload Repository）的体系结构之后有如下系统表做支撑  
   17. --属于SYS用户但是放在SYSAUX表空间  
   18. SELECT * FROM sys.Wri$_Alert_History;  
   19. SYS.WRI$_;    (INTERNAL)  
   20. SYS.WRM$_;    (META_DATA)  
   21. SYS.WRH$_;    (HISTORY)  
   22. SELECT * FROM sys.Wrm$_Snapshot;  
   23. --AWR的数据由MMON进程每小时收集一次  
   24. SELECT * FROM V$BGPROCESS;  
   25. SELECT * FROM DBA_HIST_WR_CONTROL;  
   26. --oracle内存命中率  
   27. SELECT A.SNAP_ID,  
   28.        B.BEGIN_INTERVAL_TIME,  
   29.        B.END_INTERVAL_TIME,  
   30.        (A.DB_BLOCK_GETS + A.CONSISTENT_GETS) /  
   31.        (A.DB_BLOCK_GETS + A.CONSISTENT_GETS + A.PHYSICAL_READS) * 100 内存的命中率  
   32. FROM   DBA_HIST_BUFFER_POOL_STAT A  
   33. LEFT   JOIN DBA_HIST_SNAPSHOT B ON A.SNAP_ID = B.SNAP_ID  
   34. ORDER  BY A.SNAP_ID;  
   35. --快照  
   36. SELECT * FROM DBA_HIST_SNAPSHOT;  
   37. --收集表的统计信息  
   38. ANALYZE TABLE TABLENAME COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL COLUMNS;  
   39. --查看执行时间比较长的SQL  
   40. SELECT B.SQL_TEXT,  
   41.        (A.LAST_UPDATE_TIME - A.START_TIME) * 24 * 60 "total_time(s)",  
   42.        A.*  
   43. FROM   V$SESSION_LONGOPS A  
   44. LEFT   JOIN V$SQLAREA B ON A.SQL_ADDRESS = B.ADDRESS  
   45. WHERE  A.SID = '131'  
   46. ORDER  BY A.START_TIME DESC;  
   47. --SQL完成比率  
   48. SELECT SID,  
   49.        OPNAME,  
   50.        TARGET_DESC,  
   51.        SOFAR,  
   52.        TOTALWORK,  
   53.        TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PERWORK  
   54. FROM   V$SESSION_LONGOPS  
   55. WHERE  SOFAR != TOTALWORK;  
   56. --调整滥用磁盘读操作的主要语句  
   57. --我发现在没有作调整的情况下，在绝大多数的系统中，访问量占前25位的语句的磁盘读操作将占用整个系统所有磁盘和/或内存读操作的75%。  
   58. SELECT DISK_READS, A.*  
   59. FROM   V$SQLAREA A  
   60. ORDER  BY A.DISK_READS DESC; 
       

select instance_number,
       snap_id,
       min(begin_time) as 快照开始时间,
       max(end_time) as 快照停止时间,
       round(sum(case metric_name
                   when 'Host CPU Utilization (%)' then
                    average
                 end)) as CPU使用率,
       sum(case metric_name
             when 'Current OS Load' then
              MAXVAL
           end) as 最大OS负载,
       sum(case metric_name
             when 'Current OS Load' then
              average
           end) as 平均OS负载,
       round(sum(case metric_name
                   when 'User Transaction Per Sec' then
                    average
                 end)) as 每秒事务数,
       round(sum(case metric_name
                   when 'Executions Per Sec' then
                    average
                 end)) as 每秒SQL执行次数,
       round(sum(case metric_name
                   when 'Logical Reads Per Sec' then
                    average
                 end)) as 每秒逻辑读总量,
       round(sum(case metric_name
                   when 'Physical Read Total IO Requests Per Sec' then
                    average
                 end)) as 物理读IOPS,
       round(sum(case metric_name
                   when 'Physical Write Total IO Requests Per Sec' then
                    average
                 end)) as 物理写IOPS,
       round(sum(case metric_name
                   when 'Redo Writes Per Sec' then
                    average
                 end)) as REDO_IOPS,
       round(sum(case metric_name
                   when 'Physical Read Total Bytes Per Sec' then
                    average / 1024 / 1024
                 end)) as 物理读MBPS,
       round(sum(case metric_name
                   when 'Physical Write Total Bytes Per Sec' then
                    average / 1024 / 1024
                 end)) as 物理写MBPS,
       round(sum(case metric_name
                   when 'Redo Generated Per Sec' then
                    average / 1024 / 1024
                 end)) as REDO_MBPS,
       round(sum(case metric_name
                   when 'Network Traffic Volume Per Sec' then
                    average / 1024 / 1024
                 end)) as 每秒网络流量_MB
  from dba_hist_sysmetric_summary t
 where t.instance_number in (1 /*, 2*/)
   and begin_time >= trunc(sysdate)
 group by instance_number, snap_id
 order by snap_id desc, instance_number;