v$视图相关

V$OSSTAT：

V$OSSTAT view gives information about the operating system on which the Oracle database resides.

V$OSSTAT view has the following columns:

STAT_NAME – Name of the statistic
VALUE – Instantaneous statistic value
OSSTAT_ID – Statistic ID

List of Statistics that this view keeps track of

Statistic Name

NUM_CPUS
Number of CPUs or processors available

IDLE_TIME
Number of hundredths of a second that a processor has been idle, totalled over all processors

BUSY_TIME
Number of hundredths of a second that a processor has been busy executing user or kernel code, totalled over all processors

USER_TIME
Number of hundredths of a second that a processor has been busy executing user code, totalled over all processors

SYS_TIME
Number of hundredths of a second that a processor has been busy executing kernel code, totalled over all processors

IOWAIT_TIME
Number of hundredths of a second that a processor has been waiting for I/O to complete, totalled over all processors

NICE_TIME
Number of hundredths of a second that a processor has been busy executing low-priority user code, totalled over all processors

AVG_IDLE_TIME
Number of hundredths of a second that a processor has been idle, averaged over all processors

AVG_BUSY_TIME
Number of hundredths of a second that a processor has been busy executing user or kernel code, averaged over all processors

AVG_USER_TIME
Number of hundredths of a second that a processor has been busy executing user code, averaged over all processors

AVG_SYS_TIME
Number of hundredths of a second that a processor has been busy executing kernel code, averaged over all processors

AVG_IOWAIT_TIME
Number of hundredths of a second that a processor has been waiting for I/O to complete, averaged over all processors

AVG_NICE_TIME
Number of hundredths of a second that a processor has been busy executing low-priority user code, averaged over all processors

OS_CPU_WAIT_TIME
Total number of hundredths of a second that processes have been in a ready state, waiting to be selected by the operating system scheduler to run

RSRC_MGR_CPU_WAIT_TIME
Total number of hundredths of a second that Oracle processes have been in a ready state, waiting for CPU to be available for their consumer group in the currently active resource plan

VM_PAGE_IN_BYTES
Total number of bytes of data that have been paged in due to virtual memory paging

VM_PAGE_OUT_BYTES
Total number of bytes of data that have been paged out due to virtual memory paging

PHYSICAL_MEMORY_BYTES
Total number of bytes of physical memory

LOAD
Current number of processes that are either running or in the ready state, waiting to be selected by the operating-system scheduler to run. On many platforms, this statistic reflects the average load over the past minute.

NUM_CPU_CORES
Number of CPU cores available (includes subcores of multicore CPUs as well as single-core CPUs)

NUM_CPU_SOCKETS
Number of CPU sockets available (represents an absolute count of CPU chips on the system, regardless of multithreading or multi-core architectures)

All historical values of V$OSSTAT view are stored in DBA_HIST_OSSTAT

SQL> SELECT snap_id, dbid, stat_name, value
FROM dba_hist_osstat
WHERE stat_name = ‘LOAD’
ORDER BY snap_id;

相关查询:

select
 to_char(hs.begin_interval_time,'YYYYMMDD HH24:MI:SS') begin_interval_time,
 ho.snap_id,
 ho.dbid,
 ho.instance_number,
 round(max(Case When ho.stat_name = 'LOAD' Then Value End),2) As "LOAD",
 round(max(Case When ho.stat_name = 'IOWAIT_TIME' Then Value end)/100,2) As "IOWAIT_TIME",
 round(max(Case When ho.stat_name = 'NUM_CPUS' Then Value end),2) As NUM_CPUS,
 round(max(Case When ho.stat_name = 'IDLE_TIME' Then Value end)/100,2) As "IDLE_TIME(S)",
 round(max(Case When ho.stat_name = 'BUSY_TIME' Then Value end)/100,2) As "BUSY_TIME(S)",
 round(max(Case When ho.stat_name = 'USER_TIME' Then Value end)/100,2) As "USER_TIME(S)",
 round(max(Case When ho.stat_name = 'SYS_TIME' Then Value end)/100,2) As "SYS_TIME(S)",
 round(max(Case When ho.stat_name = 'AVG_IDLE_TIME' Then Value end)/100,2) As "AVG_IDLE_TIME(S)",
 round(max(Case When ho.stat_name = 'AVG_BUSY_TIME' Then Value end)/100,2) As "AVG_BUSY_TIME(S)",
 round(max(Case When ho.stat_name = 'AVG_USER_TIME' Then Value end)/100,2) As "AVG_USER_TIME(S)",
 round(max(Case When ho.stat_name = 'AVG_SYS_TIME' Then Value end)/100,2) As "AVG_SYS_TIME(S)",
 round(max(Case When ho.stat_name = 'RSRC_MGR_CPU_WAIT_TIME' Then Value end)/100,2) As "RSRC_MGR_CPU_WAIT_TIME(S)",
 round(max(Case When ho.stat_name = 'NUM_CPU_CORES' Then Value end),2) As "NUM_CPU_CORES",               
 round(max(Case When ho.stat_name = 'NUM_CPU_SOCKETS' Then Value end),2) As "NUM_CPU_SOCKETS",
 round(max(Case When ho.stat_name = 'PHYSICAL_MEMORY_BYTES' Then Value end)/1024/1004,2) As "PHYSICAL_MEMORY_BYTES(MB)",
 round(max(Case When ho.stat_name = 'VM_IN_BYTES' Then Value end)/1024/1024,2) As "VM_IN_BYTES(MB)",
 round(max(Case When ho.stat_name = 'VM_OUT_BYTES' Then Value end)/1024/1024,2) As "VM_OUT_BYTES(MB)",
 round(max(Case When ho.stat_name = 'TCP_RECEIVE_SIZE_MAX' Then Value End),2) As TCP_RECEIVE_SIZE_MAX
from dba_hist_osstat ho,
     dba_hist_snapshot hs
Where ho.snap_id = hs.snap_id
  And ho.dbid = hs.dbid
  And ho.instance_number = hs.instance_number
  And hs.begin_interval_time>=Trunc(Sysdate)-3
  And hs.instance_number =1
Group By
 hs.begin_interval_time,
 ho.snap_id,
 ho.dbid,
 ho.instance_number
Order By hs.begin_interval_time Desc