• Oracle数据库常用监控语句


    --在某个用户下找所有的索引
    select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
    from user_ind_columns, user_indexes
    where user_ind_columns.index_name = user_indexes.index_name
    and user_ind_columns.table_name = user_indexes.table_name 
    order by user_indexes.table_type, user_indexes.table_name,
    user_indexes.index_name, column_position;
    
    --监控表空间的I/O比例
    select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,
    f.phyblkwrt pbw 
    from v$filestat f,dba_data_files df
    where f.file#=df.file_id
    
    
    --监控 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;
    
    
    --监控 SGA 的命中率
    select a.value + b.value "logical_reads", c.value "phys_reads",
    round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" 
    from v$sysstat a, v$sysstat b, v$sysstat c
    where a.statistic# = 38 and b.statistic# = 39 
    and c.statistic# = 40;
    
    
    --监控内存和硬盘的排序比率,最好使它小于 .10,增加 sort_area_size
    SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
    
    --查看碎片程度高的表
    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);
    
    --找使用CPU多的用户session
    select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
    
    
    --监控log_buffer的使用情况:(值最好小于1%,否则增加log_buffer 的大小)
    select rbar.name,rbar.value,re.name,re.value,(rbar.value*100)/re.value||'%' "radio" from v$sysstat rbar,v$sysstat re where rbar.name='redo buffer allocation retries' and re.name='redo entries';

     引用链接:http://blog.csdn.net/dongzhumao86/article/details/8480499

  • 相关阅读:
    springmvc源码学习
    springboot启动原理解析
    spring源码学习五
    mybatis源码学习(三)-一级缓存二级缓存
    mybatis源码学习(二)--mybatis+spring源码学习
    mybatis源码学习(一) 原生mybatis源码学习
    spring源码学习(四)-spring生命周期用到的后置处理器
    redis安装(centos 7)
    Centos 7 学习笔记
    Mysql 查看及设置事物隔离级别
  • 原文地址:https://www.cnblogs.com/fxust/p/8117843.html
Copyright © 2020-2023  润新知