Oracle性能主要监控指标，精心整理纯干货
原来是一个Word文件，不便于查看，手工整理出来，便于使用，可以直接复制执行。
Oracle性能主要监控指标
--每秒事务数：每秒数据库产生的事务数大小
select round((select sum(to_number(value, 99999999999))
from v$sysstat
where name in ( 'user rollbacks','user commits') )/
(select (sysdate - startup_time)*24*60*60 as seconds
from v$instance),1) avg_tx_per_second
from dual;
--即TPS计算
---
--Oracle最大进程数：数据库最大进程数
select value from v$parameter where name = 'processes';
---
--数据库当前进程的连接数：数据库当前进程数
select count(*) from v$process;
--这个视图提供的信息，都是oracle服务进程的信息，没有客户端程序相关的信息，服务进程分两类，一是后台的，一是dedicate/shared server
---
--Oracle最大会话数：数据库最大会话数
select value from v$parameter where name = 'sessions';
---
--数据库当前会话的连接数：数据库当前会话数
select count(*) from v$session;
--v$session描述的对象是一个当前存在的会话（该会话不是包括当前连接到数据库的会话，这只是数据库前台服务器进程的会话，还有数据库后台进程的会话，它是没有连接的）
---
--open_cursor游标数：数据库当前打开游标数
select count(*) from v$open_cursor;
--当前已打开和解析的每个用户会话的游标
---
--当前长事务数：数据库当前长事务数
with transaction_details as( select inst_id , ses_addr , sysdate - start_date as diff from gv$transaction) select count(*)from gv$session s, transaction_details t where s.inst_id = t.inst_id and s.saddr = t.ses_addr order by t.diff desc
--长事务意味着可能由于跨越过多的日志文件，导致需要循环使用的日志文件不能及时释放。从而造成数据库系统挂起无法正常工作的可能性。
---
--libary cache库缓存命中率：数据库当前的libary cache命中率
SELECT round(SUM(pinhits)/sum(pins)*100,1) FROM V$LIBRARYCACHE;
--Oracle引入库高速缓存的目的是共享SQL和PL/SQL代码。长期运行的数据库的 library cache hitratio 最好应该在95%以上，sum(reloads)与sum(pins) 的比值应该小于 1%，如果pinhitratio 小于90%，或者reload ratio 大于1% ，而且shared pool 的free memory 很小或者为0，那么可以适当增加shared_pool的大小，检查应用程序代码效率，比如是否使用了绑定变量等等
---
--软解析命中率：server process在library cache里软解析的命中率
select sum(pinhits)/sum(pins)*100 from v$librarycache;
--这个跟库缓存命中率有所重叠。一般来讲, 服务器运行的时间越长,命中率越稳定, 正常来讲这两个命中率一般都在99% 以上, 如果98% 就有问题了
--数据字典命中率：rowcache(数据字典缓存)的命中率
select sum(gets),sum(getmisses),100*(1-sum(getmisses)/sum(gets)) from v$rowcache where gets > 0;
--当Oracle执行SQL时，会将相关的数据文件、表、索引、列、用户、其他的数据对象的定义和权限信息存放到数据字典高速缓存中。一般来讲, production服务器运行的时间越长,命中率越稳定,正常来讲这两个命中率一般都在99% 以上, 如果98% 就有问题了
---
--共享池总量：数据库当前共享池的空间总量
SELECT ROUND(current_size/1024/1024) AS current_size_mb
FROM v$memory_dynamic_components
WHERE current_size != 0 and component='shared pool';
--Oracle共享池（Share Pool）属于SGA，由库高速缓存（library cache）和数据字典高速缓存（data dictionary cache）组成。
--共享池剩余量：数据库当前共享池的空间剩余量
SELECT round(bytes/1024/1024) FROM v$sgastat WHERE name = 'free memory' and pool = 'shared pool';
--共享池(Shared pool)是SGA中最关键的内存片段 ，Library cache与Data dictionarycache两者共同组成了shared pool的大小
---
--缓存命中率buffer cache：数据库当前的buffer cache命中率
select round((1 - "physical reads" / ("db block gets" + "consistent gets"))*100, 1) as hit
from (SELECT max(decode(name, 'db block gets', value, null)) as "db block gets",
max(decode(name, 'consistent gets', value, null)) as "consistent gets",
max(decode(name, 'physical reads', value, null)) as "physical reads"
FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets', 'physical reads'));
--长期运行的database若hit 小于98%，考虑增大buffer cache。若小于90%，则已经命中率很低了,考虑调优。
---
--物理读：每秒物理读IO数据量
select sum(value)*8/1024/1024/1024 from GV$SYSSTAT where name='physical reads';
--physical reads： OS blocks read数。包括插入到SGA缓存区的物理读以及PGA中的直读这项统计并非i/o请求数。
---
--物理写：每秒物理写IO数据量
select round(sum(value)*8/1024/1024) MB from V$SYSSTAT where name='physical writes';
--physical writes： 从SGA缓存区被DBWR写到磁盘的数据块以及PGA进程直写的数据块数量。
---
--解析总次数 数据库当前的发生的解析次数
select value from v$sysstat where name = 'parse count (total)';
--parse count (total)：解析调用总数，包括软解析和硬解析。当session执行了一条sql语句，该语句已经存在于shared pool并且可以被使用则产生软解析。当语句被使用(即共享),所有数据相关的现有sql语句(如最优化的执行计划)必须同样适用于当前的声明。这两项统计可被用于计算软解析命中率。
---
--硬解析次数：数据库当前的发生的硬解析次数
select value from v$sysstat where name = 'parse count (hard)';
--parse count (hard)：在shared pool中解析调用的未命中次数。当sql语句执行并且该语句不在shared pool或虽然在shared pool但因为两者存在部分差异而不能被使用时产生硬解析。如果一条sql语句原文与当前存在的相同，但查询表不同则认为它们是两条不同语句，则硬解析即会发生。硬解析会带来cpu和资源使用的高昂开销，因为它需要oracle在shared pool中重新分配内存，然后再确定执行计划，最终语句才会被执行。
---
--软解析比例：数据库当前的发生的软解析次数占比
select round((1-(a.value/b.value))*100,1)
from v$sysstat a,v$sysstat b
Where a.name='parse count (hard)'and b.name='parse count (total)';
--parse count (total)：解析调用总数，包括软解析和硬解析。当session执行了一条sql语句，该语句已经存在于shared pool并且可以被使用则产生软解析。当语句被使用(即共享) 所有数据相关的现有sql语句(如最优化的执行计划)必须同样适用于当前的声明。这两项统计可被用于计算软解析命中率。
---
--临时表空间使用率：临时表空间使用率
select round(d.bytes_used/c.bytes*100,1) used_rate
from (select tablespace_name, sum(bytes) bytes
from dba_temp_files
GROUP by tablespace_name) c,
(select tablespace_name, sum(bytes_cached) bytes_used
from v$temp_extent_pool
GROUP by tablespace_name) d
where c.tablespace_name = d.tablespace_name;
--视图dba_temp_file, v$temp_extent_pool保存了临时表空间使用信息，bytes_used字段是已使用空间大小，可以通过统计总量与已使用空间得到空间使用率
---
--异常job数：查找最近1h的异常JOB个数
select count(*) from dba_scheduler_job_run_details t where error#!=0 and to_char(t.actual_start_date, 'yyyy/mm/dd hh24：mi：ss')>to_char(sysdate-1/24, 'yyyy/mm/dd hh24：mi：ss');
--查看最近1h的异常job
---
--等待事件：数据库当前的各种等待事件个数。考虑修改成当前系统各个等待事件的个数、等待时长、排名
SELECT inst_id,wait_class,EVENT, SUM(DECODE(WAIT_TIME, 0, 0, 1)) "Prev", SUM(DECODE(WAIT_TIME, 0, 1, 0)) "Curr", COUNT(*) "Tot" , state, sum(SECONDS_IN_WAIT) SECONDS_IN_WAIT FROM GV$SESSION_WAIT
WHERE event NOT
IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client','gcs remote message')
AND event NOT LIKE '%idle%'
AND event NOT LIKE '%Idle%'
AND event NOT LIKE '%Streams AQ%'
GROUP BY inst_id,wait_class,EVENT,state
ORDER BY 1,6 desc;
--说明：1）当state值为Waiting，Second_in_wait值才是实际的等待时间(单位：秒)，当state值为Waiting known time，那么wait_time值就是实际等待时间。2）Prev代表上一次等待次数，Curr代表当前等待次数
--统计非空闲等待事件的等待时长和个数
---
--top SQL排序：数据库中当前频繁执行的SQL
select round(100 * a.pct, 2) pct,
round(a.elapsed_time/1000000, 2) elapsed_time,
round(a.elapsed_time/a.executions/1000) ms_by_exec,
round(a.cpu_time/1000000, 2) cpu_time,
a.buffer_gets total_cost,
round(a.buffer_gets/a.executions) elem_cost,
a.executions exec,
a.rows_processed nb_rows,
s.sql_text
from (select *
from (select elapsed_time,
ratio_to_report(elapsed_time) over () pct,
cpu_time,
buffer_gets,
executions,
rows_processed,
address,
hash_value
from v$sql
order by elapsed_time desc)
where rownum < 11) a,
v$sqlarea s
where a.address = s.address
and a.hash_value = s.hash_value
and a.executions <> 0
order by pct desc, cpu_time desc;
--v$sql视图保存的是sql的一个副本，视图 中的列 EXECUTIONS为 语句的执行次数
---
--阻塞lock信息：数据库当前的阻塞信息
SELECT mm.inst_id,
mm.sid,
mm.TYPE,
mm.id1,
mm.id2,
LPAD(TRUNC(mm.ctime / 60 / 60), 3) || ' Hour ' ||
LPAD(TO_CHAR(TRUNC(mm.ctime / 60) - TRUNC(mm.ctime / 60 / 60) * 60,
'fm09'),
2) || ' Min ' ||
LPAD(TO_CHAR(mm.ctime - TRUNC(mm.ctime / 60) * 60, 'fm09'), 2) ||
' Sec' ctime,
CASE
WHEN mm.block = 1 AND mm.lmode != 0 THEN
'holder'
WHEN mm.block = 0 AND mm.request != 0 THEN
'waiter'
ELSE
NULL
END role,
CASE
WHEN ee.blocking_session IS NOT NULL THEN
'waiting for SID ' || ee.blocking_session
ELSE
NULL
END blocking_session,
dd.sql_text sql_text,
cc.event wait_event
FROM gv$lock mm, gv$session ee, gv$sqlarea dd, gv$session_wait cc
WHERE mm.sid IN (SELECT nn.sid
FROM (SELECT tt.*,
COUNT(1) OVER(PARTITION BY tt.TYPE, tt.id1, tt.id2) cnt,
MAX(tt.lmode) OVER(PARTITION BY tt.TYPE, tt.id1, tt.id2) lmod_flag,
MAX(tt.request) OVER(PARTITION BY tt.TYPE, tt.id1, tt.id2) request_flag
FROM gv$lock tt) nn
WHERE nn.cnt > 1
AND nn.lmod_flag != 0
AND nn.request_flag != 0)
AND mm.sid = ee.sid(+)
AND ee.sql_id = dd.sql_id(+)
AND mm.sid = cc.sid(+)
AND ((mm.block = 1 AND mm.lmode != 0) OR
(mm.block = 0 AND mm.request != 0))
ORDER BY mm.TYPE, mm.id1, mm.id2, mm.lmode DESC, mm.ctime DESC;
--优化后的阻塞方式计算
---
--运行长sql语句：数据库当前的运行时间长的SQL
select round(100 * a.pct, 2) pct,
round(a.elapsed_time/1000000, 2) elapsed_time,
round(a.elapsed_time/a.executions/1000) ms_by_exec,
round(a.cpu_time/1000000, 2) cpu_time,
a.buffer_gets total_cost,
round(a.buffer_gets/a.executions) elem_cost,
a.executions exec,
a.rows_processed nb_rows,
s.sql_text
from (select *
from (select elapsed_time,
ratio_to_report(elapsed_time) over () pct,
cpu_time,
buffer_gets,
executions,
rows_processed,
address,
hash_value
from v$sql
order by elapsed_time desc)
where rownum < 11) a,
v$sqlarea s
where a.address = s.address
and a.hash_value = s.hash_value
and a.executions <> 0
order by pct desc, cpu_time desc;
--v$sql_monitor视图包含当前正在运行的SQL语句，以及最近运行的SQL语句，如果一个SQL语句消耗了超过5秒的CPU或I/O时间，则会自动监控。
---
--告警日志：数据库的alert日志报错显示
select originating_timestamp,
to_char(originating_timestamp, 'yyyy-mm-dd hh24：mi：ss') ts,
--adr_home adr,
message_text msg
from v$diag_alert_ext
where originating_timestamp > sysdate - 12 / 1440
and message_text like '%ORA-%'
order by originating_timestamp desc;
--利用v$diag_alert_ext 视图记录告警日志文件的基本信息，包括文件类型，路径，报错信息。这里只通过筛选ORA类型的报错日志，并显示。