Oracle 数据库监控相关SQL语句集合

Oracle · ruyi · 于 1年前发布 · 742 次阅读
  • 数据库版本信息

    select product,version from product_component_version where product like '%Database%';
    
  • 监控活动的会话信息,含连接信息,等待事件,SQL语句

    select s.SID,s.SERIAL#,s.STATUS,s.USERNAME,s.MACHINE,s.MODULE,sw.EVENT,sw.WAIT_CLASS,sq.SQL_ID,sq.SQL_TEXT,s.BLOCKING_SESSION,sw.SECONDS_IN_WAIT,sw.STATE,s.TERMINAL,s.PROGRAM,s.OSUSER from v$session s,v$session_Wait sw, v$sqlarea sq where s.SID=sw.SID and s.status='ACTIVE' AND sw.WAIT_CLASS not in('Idle') and s.sql_hash_value = sq.HASH_VALUE  and s.username is not null; 
    
  • 监控与等待事件相关的会话信息,含连接信息,等待事件,SQL语句

    select s.SID,s.SERIAL#,s.STATUS,s.USERNAME,s.MACHINE,s.MODULE,sw.EVENT,sw.WAIT_CLASS,sq.SQL_ID,sq.SQL_TEXT,s.BLOCKING_SESSION,sw.SECONDS_IN_WAIT,sw.STATE,s.TERMINAL,s.PROGRAM,s.OSUSER from v$session s,v$session_Wait sw, v$sqlarea sq where s.SID=sw.SID and s.status='ACTIVE' AND sw.WAIT_CLASS not in('Idle') and s.sql_hash_value = sq.HASH_VALUE  and s.username is not null and  ( sw.event like 'library%' or sw.event like 'cursor%' or sw.event like 'latch%'  or sw.event like 'enq%' or sw.event like 'log file%');
    
  • 监控表空间使用率

    select df.tablespace_name ,totalspace total_size, (totalspace-freespace) used_size,freespace avail_size ,round((1-freespace/totalspace)*100)  as used_ratio from (select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df,(select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name and df.tablespace_name not like 'UNDOTBS%';
    
  • 监控表空间使用率,考虑了自增数据文件的情况

    SELECT tablespace_name, sum_m + sum_cut , sum_free + sum_cut,to_char(100*((sum_m + sum_cut)-(sum_free+sum_cut))/(sum_m+sum_cut), '999.99') AS used_per, to_char(100*(sum_free+sum_cut)/(sum_m+sum_cut), '999.99')  AS free_per FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m,sum(decode(autoextensible,'YES',maxbytes-bytes,0))/1024/1024 AS sum_cut FROM dba_data_files GROUP BY tablespace_name),( SELECT tablespace_name AS ts_name, sum(bytes/1024/1024) AS sum_free FROM dba_free_space GROUP BY tablespace_name ) WHERE tablespace_name = ts_name (+) Union SELECT tablespace_name, sum_m + sum_cut , sum_free + sum_cut,to_char(100*((sum_m + sum_cut)-(sum_free+sum_cut))/(sum_m+sum_cut), '999.99')  AS used_per, to_char(100*(sum_free+sum_cut)/(sum_m+sum_cut), '999.99')  AS free_per FROM (select tablespace_name, sum(decode(autoextensible,'YES',maxbytes,bytes))/1024/1024 AS sum_m,sum(decode(autoextensible,'YES',maxbytes-bytes,0))/1024/1024 AS sum_cut  from dba_temp_files group by tablespace_name),(select tablespace_name AS ts_name, sum(BYTES_FREE /1024/1024) AS sum_free from v$temp_space_header group by tablespace_name) Where tablespace_name = ts_name (+) order by free_per;
    
  • 监控临时表空间上正在执行哪些SQL语句

    select se.username,se.sid,su.extents,su.blocks * to_number(rtrim(p.value)) as Space ,tablespace ,segtype,sql_text
    from v$sort_usage su,v$parameter  p ,v$session    se,v$sql s
    where p.name = 'db_block_size' and su.session_addr = se.saddr and   s.hash_value = su.sqlhash and s.address = su.sqladdr order  by se.username, se.sid
    
  • v$sql_monitor分析

    select status,MODULE,username,CLIENT_IDENTIFIER,CLIENT_INFO,PROGRAM,sql_id,SQL_EXEC_START,LAST_REFRESH_TIME ,ERROR_MESSAGE ,
    elapsed_time, cpu_time, fetches, buffer_gets, disk_reads,  direct_writes, application_wait_time,  concurrency_wait_time,  cluster_wait_time,  user_io_wait_time,
    plsql_exec_time,java_exec_time, SQL_TEXT from V$SQL_MONITOR where username !='SYS';
    
  • 回滚段监控

    select * from v$transaction t, v$rollstat r, v$rollname u, v$session s where s.taddr = t.addr   and t.xidusn = r.usn   and r.usn = u.usn order by s.username;
    
  • 表高水位监控

    SELECT * FROM (SELECT OWNER,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9 NEED_SPACE, BLOCKS*8/1024 TRUE_SPACE,(BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9) RECOVER_SPACE,(BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9)/(BLOCKS*8/1024)*100  RECOVER_RATE FROM dba_tables WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','WMSYS','APPQOSSYS','XDB')) T WHERE RECOVER_SPACE > 100 order by recover_rate desc";
    
  • SGA数据缓冲区命中率监控

    SELECT physical_reads, db_block_gets, consistent_gets, NAME,100 * ( 1 - ( physical_reads / (consistent_gets + db_block_gets - physical_reads))) "Data Buffer Hit Ratio" FROM v$buffer_pool_statistics';
    
  • 数据字典缓存命中率监控

    select 1 - sum(getmisses) / sum(gets) "data dictionary hitratio" from v$rowcache;
    
  • 失效索引监控

    select owner,index_name,index_type,table_owner,table_name,status,distinct_keys,num_rows,blevel  From dba_indexes Where (status<>'VALID' or blevel>3 ) and distinct_keys is not null and num_rows is not null and blevel is not null and owner not in( 'SYSTEM','SYS');
    
  • 无效对象监控

    SELECT owner, object_name, object_type,status FROM dba_objects WHERE status = 'INVALID' and object_type<> 'VIEW' and owner <> 'SYS';
    

本文由 ruyi 创作,采用 知识共享署名 3.0 中国大陆许可协议 进行许可。 可自由转载、引用,但需署名作者且注明文章出处。


如果这篇文章对您有帮助,不妨微信小额赞助我一下,让我有动力继续写出高质量的帖子。

共收到 0 条回复 oracle 监控sql
没有找到数据。
回复本帖 (需要登录)