oracle性能监控相关sql

分享一些oracle监控经常涉及到的sql脚本，这些都是我项目中已经验证过的，有需要的同学可以参考下！

数据库实例

select inst_id,instance_number,instance_name,host_name,instance_role,version,to_char(startup_time,'yyyy-MM-dd HH24:mi:ss') as runtime,status,database_status 
from gv$instance order by inst_id asc;

Library Cache命中率

select round(sum(pinhits)/sum(pins)*100,2),inst_id from gv$librarycache group by inst_id;

Buffer Cache命中率

select round((1-(physical_reads/(db_block_gets + consistent_gets)))*100,2),inst_id from gv$buffer_pool_statistics where name='DEFAULT';

Share Pool命中率

select round(sum(pinhits-reloads)/sum(pins)*100,2),inst_id from gv$librarycache group by inst_id;

Redo切换速率

select b.switch_time,b.switch_count,a.inst_id from gv$instance a left join
(select round((switch_time-to_date('1970-1-1 8', 'YYYY-MM-DD HH24'))*86400) as switch_time,switch_count,inst_id from (select trunc(first_time, 'MI') as switch_time, count(*) as switch_count,inst_id from gv$log_history group by inst_id,trunc(first_time, 'MI'))) b
on a.inst_id=b.inst_id order by b.switch_time;

数据库总连接数

select decode(b.value, 0, '0', to_char(b.value)),b.inst_id from 
(select count(*) as value,inst_id from gv$session where type <> 'BACKGROUND' group by inst_id) b;

数据库活动连接数

select decode(a.value, 0, '0', to_char(a.value)),a.inst_id from 
(select count(*) as value,inst_id from gv$session where status = 'ACTIVE' and type <> 'BACKGROUND' group by inst_id) a;

Current SCN

select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER scn from dual;

物理读

select round(value/1000),inst_id from gv$sysstat where name ='physical reads';

逻辑读

select round((db_block_gets+consistent_gets)/1000),b.inst_id from 
(select inst_id,value as db_block_gets from gv$sysstat where name ='db block gets') b, 
(select inst_id,value as consistent_gets from gv$sysstat where name ='consistent gets') c 
where b.inst_id=c.inst_id;

表空间

SELECT a.tablespace_name,round(a.bytes/(1024 * 1024)) as tbs_size_total,round(b.bytes/(1024 * 1024)) as tbs_size_used,round(c.bytes/(1024 * 1024)) as tbs_size_free,round((b.bytes * 100) / a.bytes,2) as tbs_used_percent,round((c.bytes * 100) / a.bytes,2) as tbs_free_percent
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name;

数据文件

select a.file_id,a.file_name,a.status,(a.bytes - b.bytes)/1024/1024 as size_used,a.blocks,b.bytes/1024/1024 as size_free
from dba_data_files a, (select file_id, sum(bytes) bytes from dba_free_space group by file_id) b 
where a.file_id=b.file_id order by a.file_id;

Top Sql

select st.inst_id,ses.sid,ses.serial#,ses.event,p.spid,ses.username,ses.osuser,ses.machine,ses.module,st.value/100 as db_time_sec,stcpu.value/100 as cpu_time_sec,round(stcpu.value/st.value * 100, 2) as CPU_Percent,sql.sql_text,sql.sql_id,sql.last_load_time,
sql.executions,round(sql.buffer_gets / decode(sql.executions, 0, 1, sql.executions),2) as buffgts_exec_ratio
FROM gv$sesstat st 
left join gv$statname sn on (st.inst_id=sn.inst_id) 
left join gv$session ses on (st.inst_id=ses.inst_id) 
left join gv$sesstat stcpu on (st.inst_id=stcpu.inst_id) 
left join gv$statname sncpu on (st.inst_id=sncpu.inst_id) 
left join gv$process p on (st.inst_id=p.inst_id) 
left join gv$sqlarea sql on (st.inst_id=sql.inst_id) 
WHERE sn.name = 'DB time' AND st.statistic# = sn.statistic# AND st.sid = ses.sid AND sncpu.name = 'CPU used by this session' 
AND stcpu.statistic# = sncpu.statistic# AND stcpu.sid = st.sid AND ses.paddr = p.addr AND st.value > 0 AND ses.status = 'ACTIVE' AND ses.sql_id = sql.SQL_ID;