oracle 监控常用sql

select * from V$process;

SELECT osuser, username, sql_text
  from v$session a, v$sqltext b
 where a.sql_address = b.address
 order by address, piece;
 
 SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE; 
 --检测数据库中的事件和等待
 SELECT event, total_waits, total_timeouts,time_waited, average_wait FROM v$system_event;
 --查询会话中的事件和等待时间
 select sid, event, total_waits,average_wait from v$session_event;     
 --查询等待进程 
 SELECT sid, seq#, event, wait_time, state FROM v$session_wait;
--当前 sql 语句
select sql_text, users_executing, executions, loads from v$sqlarea;
--查看表锁 
select * from sys.v_$sqlarea where disk_reads>100


--查看前台正在发出的SQL语句
select user_name, sql_text 　　
  from v$open_cursor 　　
 where sid in (select sid
                 from (select sid, serial#, username, program 　　
                         from v$session 　　
                        where status = 'ACTIVE'))
                        
                        
--数据表占用空间大小情况
select segment_name, tablespace_name, bytes, blocks
  from user_segments
 where segment_type = 'TABLE'
 ORDER BY bytes DESC, blocks DESC
--查看表空间碎片大小
select tablespace_name,
       round(sqrt(max(blocks) / sum(blocks)) *
             (100 / sqrt(sqrt(count(blocks)))),
             2) FSFI
  from dba_free_space
 group by tablespace_name
 order by 1


--查看session使用回滚段
SELECT  r.name 回滚段名,
        s.sid,
        s.serial#,
        s.username 用户名,
        t.status,
        t.cr_get,
        t.phy_io,
        t.used_ublk,
        t.noundo,
        substr(s.program, 1, 78) 操作程序
FROM   sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE  t.addr = s.taddr and t.xidusn = r.usn
ORDER  BY t.cr_get,t.phy_io


--查看SGA区剩余可用内存
select name,
　　　　　　sgasize/1024/1024　　　　　　　　"Allocated(M)",
　　　　　　bytes/1024　　　　　　　　　　　 "**空间(K)",
　　　　　　round(bytes/sgasize*100, 2)　　　"**空间百分比(%)"
　　 from   (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
　　 where  f.name = 'free memory'    


--性能最差的SQL

SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text 
                FROM v$sqlarea 
                ORDER BY disk_reads DESC) 
WHERE ROWNUM<100

--读磁盘数超100次的sql

select * from sys.v_$sqlarea where disk_reads>100

--最频繁执行的sql

select * from sys.v_$sqlarea where executions>100


--查询使用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


--当前每个会话使用的对象数
SELECT a.sid,s.terminal,s.program,count(a.sid) 
FROM V$ACCESS a,V$SESSION s
WHERE a.owner <> 'SYS'AND s.sid = a.sid 
GROUP BY a.sid,s.terminal,s.program
ORDER BY count(a.sid) 


select sid,v$session.username 用户名,last_call_et 持续时间,status 状态,LOCKWAIT 等待锁,machine 用户电脑名,logon_time 开始登入时间,sql_text from v$session ,v$process ,v$sqlarea
  where paddr=addr and sql_hash_value=hash_value  
  and status='ACTIVE' and v$session.username is not null
  order by last_call_et desc;
  
  
  
  SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
  FROM v$session
 WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);
 
 
 SELECT LPAD(' ', DECODE(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
       o.owner,
       o.object_name,
       o.object_type,
       s.sid,
       s.serial#
  FROM v$locked_object l, dba_objects o, v$session s
 WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
 ORDER BY o.object_id, xidusn DESC;
 
 
select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

select name, waits, gets, waits / gets "Ratio"
  from v$rollstat a, v$rollname b
 where a.usn = b.usn;
 
--查看表空间的使用情况 
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name 
FROM dba_free_space 
GROUP BY tablespace_name; 
SELECT a.tablespace_name, 
a.bytes total, 
b.bytes used, 
c.bytes free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name; 

--查询日志状态
SELECT created, log_mode, log_mode FROM v$database; 


SELECT owner, object_type, status, COUNT(*) count# 
FROM all_objects 
GROUP BY owner, object_type, status; 
--查看表空间使用情况
SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name 


select event,
       sum(decode(wait_Time, 0, 0, 1)) "Prev",
       sum(decode(wait_Time, 0, 1, 0)) "Curr",
       count(*) "Tot"
  from v$session_Wait
 group by event
 order by 4;      
 
 
 
 select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name 
from x$bh a, dba_objects b 
where (a.obj = b.object_id or a.obj = b.data_object_id) 
and a.hladdr = &2 
union 
select hladdr, file#, dbablk, tch, obj, null 
from x$bh 
where obj in 
(select obj from x$bh 
where hladdr = &2 
minus 
select object_id from dba_objects 
minus 
select data_object_id from dba_objects) 
and hladdr = &2 
order by 4;