Oracle性能监控的SQL语句大集合

    由于工作的需要，需要写对Oracle进行监控的功能，自己也不知道需要监控哪些信息，就在网上下载了一个spotlight on Oracle 软件的试用版，连接上了Oracle后，对照其官方提供的使用文档，以及Oracle官方提供的性能视图，当然还有网上查询的其它的资料。得出此软件中的监控Oracle的信息的部分SQL(限于水平有限，如有错误，请指正)。如下。

--获取总用户数

select count(*) from v$session where username is not null

--获取活动用户数
select count(*) from v$session where STATUS='ACTIVE' and username is not null;

--

查看专用服务器进程数

select count(*) from v$session where SERVER='DEDICATED' and username is not null;

--查看共享服务器进程数
select count(*) from v$session where SERVER='SHARED' and username is not null;
--查询 Dispatchers个数
select count(*) as Dispatchers from V$DISPATCHER;
--Job Queue
select * from v$parameter where name ='job_queue_processes';  

--PGA的target
select value/1024/1024 from V$PGASTAT where name='aggregate PGA target parameter';
--PGA_Used
select sum(pga_used_mem)/1024/1024 from V$PROCESS;
--SGA Current Size
select sum(bytes)/1024/1024 from V$SGAINFO where NAME in ('Redo Buffers','Buffer Cache Size','Shared Pool Size','Streams Pool Size','Large Pool Size','Java Pool Size','Fixed SGA Size' );
--bufferCache Size
select bytes/1024/1024 from V$SGAINFO where name='Buffer Cache Size';
--Redo Buffers Size
select bytes/1024/1024 from V$SGAINFO where name='Redo Buffers';
--keep pool Size
select * from v$parameter where name='db_keep_cache_size';
--recycle pool Size
select * from v$parameter where name='db_recycle_cache_size';

--Java Pool Size
select bytes/1024/1024 from V$SGAINFO where name='Java Pool Size';
--free Java Pool
select * from v$sgastat a where a.pool = 'java pool' and a.NAME = 'free memory';
--Shared Pool Size
select bytes/1024/1024 from V$SGAINFO where name='Shared Pool Size';
--free share pool
select bytes/1024/1024 from v$sgastat a where a.pool = 'shared pool' and a.NAME = 'free memory';
--Large Pool Size
select bytes/1024/1024 from V$SGAINFO where name='Large Pool Size';
--free large pool
select bytes/1024/1024 from v$sgastat a where a.pool = 'large pool' and a.NAME = 'free memory';

--buffer cache hit ratio

SELECT ROUND(1 - ((physical.value - direct.value - lobs.value) / logical.value),3) *100||'%'
"Buffer Cache Hit Ratio"
FROM v$sysstat physical,
v$sysstat direct,
v$sysstat lobs,
v$sysstat logical
WHERE physical.name = 'physical reads'
AND direct.name = 'physical reads direct'
AND lobs.name = 'physical reads direct (lob)'
AND logical.name = 'session logical reads';

SELECT NAME,
physical_reads,
db_block_gets,
consistent_gets,
ROUND((1 - (physical_reads / (db_block_gets + consistent_gets))) * 100) || '%' ratio
FROM V$BUFFER_POOL_STATISTICS
WHERE NAME = 'DEFAULT';
SELECT NAME,current_size,buffers FROM v$buffer_pool;
SELECT (1 - (SUM(decode(NAME, 'physical reads', VALUE, 0)) /
(SUM(DECODE(NAME, 'db block gets', VALUE, 0)) +
SUM(DECODE(NAME, 'consistent gets', VALUE, 0))))) * 100 "Hit Ratio"
FROM v$sysstat;



--查看表空间的个数
select count(*) as TablespacesCount　from DBA_Tablespaces where tablespace_name!='temp';
--表空间总大小
select sum(bytes)/1024/1024/1024 from dba_data_files;
--可用表空间总大小
select sum(bytes)/1024/1024/1024 from DBA_FREE_SPACE;

--查看datafiels
select * from v$DATAFILE
select * from DBA_DATA_FILES;

--查看flashback recovery area 的size
select sum(space_limit)/1024/1024/1024 from v$recovery_file_dest;
--查看flashback recovery area in used
select sum(space_used)/1024/1024 from v$recovery_file_dest

--The number of Oracle redo log groups.
select count(group#)from v$log;
--Average size of Redo logs
select sum(bytes)/count(*)/1024/1024 from v$log;