视图优化oracle,oracle 性能优化 02_OWI及性能视图

一、oracle等待接口

OWI (ORACLE WAIT INTERFACE)，从Oracle 7开始支持OWI，目的是让开发人员和维护人员能够了

解在Oracle的每个模块和OS中消耗了多少时间，通过Oracle提供的视图获取这些信息，是AWR，STATSPACK

等时间模型性能报告的基础，方便找到性能问题的根源，诊断系统故障。

1、核心视图

v$system_event

记录自实例启动以来各个等待事件的概括信息。常用于获取系统等待信息的历史影象。而通过两个

snapshot获取等待项增量，确定这段时间内系统的等待项。

v$session_event

记录session自启动以来所有的等待事件。

v$session_wait

会话等待事件明细信息，每三秒钟刷新一次等待时间

V$SESSION_WAIT_HISTORY

是对V$SESSION_WAIT的简单增强，记录活动SESSION的最近10次等待。

主要字段解释：

event:事件名称

total_waits: 自从实例启动以来的等待次数

total_timeouts: 事件被唤醒的总次数

time_waited: 按照cs统计的总的等待时间

average_wait: 平均每次等待的等待时间，单位是cs

SID: SESSION ID

seq#: 等待次数统计

p[1-3]: 等待的详细参数

p[1-3]raw: 参数的raw模式

p[1-3]text: 参数的名字

查询当前会话的SID

select * from v$mystat where rownum<2; #需要访问权限

select userenv('sid') from dual; #没有权限要求

v$system_wait_class

系统等待事件分类统计信息

v$session_wait_class;

会话等待事件分类统计信息

v$sys_time_model

系统时间统计模型

V$SESS_TIME_MODEL

会话对应的时间统计模型

v$osstat

操作系统统计信息

v$waitstat

保存自实例启动所有的等待事件统计信息。

v$filestat

记录文件的IO信息

v$latch

记录闩锁信息

2、等待事件分类

IDLE #空闲

APPLICATION #行锁、表锁、DDL锁等

Configuration #由于配置导致的

Administrative #特权用户的某些维护操作导致的

Concurrency #由于并发量大引起的

commit #log file sync

Network

User I/O waits #前台进程，SMON,MMON

System I/O Waits #除了SMON,MMON外的后台进程

Scheduler #资源管理引起的

CLUSTER #RAC

Other

3、OWI诊断方法及过程

通过查询等待接口核心视图或者生产statpack/AWR报告，找到较大等待时间和次数的等待事件，针对具体

的等待时间，细致跟踪分析。

诊断过程：OS性能状态及开销->alert log->查询OWI视图或生成报告->命中率->参数调优->TOP SQL->查找

具体SQL进行SQL调优->事件跟踪...

4、OWI相关的SQL语句

4.1. 监控事例的等待

select  event,sum(decode(wait_Time,0,0,1)) "Last wait",sum(decode(wait_Time,0,1,0)) "Current waiting",

count(*) "Tot"   from  v$session_Wait   group by event   order by 4;

4.2. 回滚段的争用情况

select name, waits, gets, waits/gets "Ratio"

from  v$rollstat a, v$rollname b  where  a.usn = b.usn;

4.3. 监控表空间的 I/O 读写情况

select df.tablespace_name name,df.file_name "file",f.phyrds,f.phyblkrd,f.phywrts, f.phyblkwrt

from   v$filestat f, dba_data_files df  where  f.file# = df.file_id

order  by df.tablespace_name;

4.4. 监控数据文件的 I/O 情况

select  substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",  a.status, a.bytes, b.phyrds,

b.phywrts,b.phyblkrd,b.phyblkwrt,b.readtim,b.writetim

from  v$datafile a, v$filestat b  where  a.file# = b.file#;

4.5. 查找找所有的索引

select b.table_name, b.index_name,  uniqueness, column_name

from   user_ind_columns a, user_indexes  b

where  a.index_name = b.index_name and a.table_name = b.table_name

order by b.table_type, b.table_name, b.index_name, column_position;

4.6. 监控 SGA 中字典缓冲区的命中率

select   parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",

(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"

from  v$rowcache where  gets+getmisses <>0  group by parameter, gets, getmisses;

4.7. 监控 SGA 中共享缓存区的命中率，应该小于1%

select  sum(pins) "Total Pins", sum(reloads) "Total Reloads",

sum(pinhits-reloads)/sum(pins) "hit radio",  sum(reloads)/sum(pins) *100 "reload percent"

from  v$librarycache;

4.8. 显示所有数据库对象的类别和大小

select   type, count(name) object_nums, sum(source_size) source_size,

sum(parsed_size) parsed_size, sum(code_size) code_size, sum(error_size) error_size,

sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required

from  dba_object_size  group by type  order by 1;

4.9.  监控内存和硬盘的排序比率，

SELECT  name, value   FROM  v$sysstat

WHERE  name IN ('sorts (memory)', 'sorts (disk)');

4.10. 监控当前数据库谁在运行什么SQL语句

SELECT   osuser, username, sql_text from  v$session a, v$sqltext b

where  a.sql_address =b.address order by address, piece;

4.11. 碎片程度

select   tablespace_name,count(tablespace_name)

from  dba_free_space  group by tablespace_name  having count(tablespace_name)>10;

alter tablespace name coalesce; #当pctincrease=0 时候合并相邻的空闲extent

alter table name deallocate unused; #释放高HWM以上的空块

4.12、查看碎片程度高的表

SELECT   segment_name table_name , COUNT(*) extents  FROM  dba_segments

WHERE  owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name

HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM dba_segments

GROUP BY segment_name);