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

2019独角兽企业重金招聘Python工程师标准>>>

一、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);                                                    

转载于:https://my.oschina.net/peakfang/blog/2245755