oracle巡檢（轉）

1) 資料庫session連線數
select count(*) from v$session;
2) 資料庫的併發數
select count(*) from v$session where status='ACTIVE';
3) 是否存在死鎖
set linesize 200
column oracle_username for a16
column os_user_name for a12
column object_name for a30
SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,l.process,
l.session_id,s.serial#, l.locked_mode,o.object_name
FROM v$locked_object l,dba_objects o,v$session s
where l.object_id = o.object_id and s.sid = l.session_id;
select t2.username||'   '||t2.sid||'   '||t2.serial#||'   '||t2.logon_time||'   '||t3.sql_text
      from v$locked_object t1,v$session t2,v$sqltext t3
      where t1.session_id=t2.sid
      and t2.sql_address=t3.address
      order by t2.logon_time;
4) 是否有enqueue等待
select eq_type "lock",total_req# "gets",total_wait# "waits",cum_wait_time from v$enqueue_stat where total_wait#>0;
5) 是否有大量長事務
set linesize 200
column name for a16
column username for a10
select a.name,b.xacts,c.sid,c.serial#,c.username,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn
and b.usn=e.XIDUSN
and c.taddr=e.addr
and c.sql_address=d.ADDRESS
and c.sql_hash_value=d.hash_value
order by a.name,c.sid,d.piece;
6)表空間使用率
set linesize 150
column file_name format a65
column tablespace_name format a20
select f.tablespace_name tablespace_name,round((d.sumbytes/1024/1024/1024),2) total_g,
round(f.sumbytes/1024/1024/1024,2) free_g,
round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_g,
round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2) used_percent
from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,
(select tablespace_name,sum(bytes) sumbytes from dba_data_files group by tablespace_name) d
where f.tablespace_name= d.tablespace_name
order by d.tablespace_name;
臨時檔案：
set linesize 200
column file_name format a55
column tablespace_name format a20
select a.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2) total_g,
round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g,
round((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g,
round(((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_g
from dba_temp_files a,dba_free_space b
where a.file_id = b.file_id(+)
group by a.tablespace_name,a.file_name,a.bytes
order by a.tablespace_name;
select a.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2) total_g,
round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g,
round((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g,
round(((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_g
from dba_temp_files a,dba_free_space b
where a.file_id = b.file_id(+)
group by a.tablespace_name,a.file_name,a.bytes
order by a.tablespace_name;
 
歸檔的生成頻率:
set linesize 120
column begin_time for a26
column end_time for a26
select a.recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') begin_time,
b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,
round((b.first_time - a.first_time)*24*60,2) minutes
from v$log_history a,v$log_history b
where b.recid = a.recid+1;

sql讀磁碟的頻率:
select a.username,b.disk_reads,b.executions,
round((b.disk_reads/decode(b.executions,0,1,b.executions)),2) disk_read_ratio,b.sql_text
from dba_users a,v$sqlarea b
where a.user_id = b.parsing_user_id
and disk_reads > 5000;

###Datafile I/O:
col tbs for a12;
col name for a46;
select c.tablespace_name tbs,b.name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,a.phyblkrd,a.phyblkwrt
from v$filestat a,v$datafile b,dba_data_files c
where b.file# = a.file#
and b.file# = c.file_id
order by tablespace_name,a.file#;

########Disk I/O
select substr(b.name,1,13) disk,c.tablespace_name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,
a.phyblkrd,a.phyblkwrt,((a.readtim/decode(a.phyrds,0,1,a.phyblkrd))/100) avg_rd_time,
((a.writetim/decode(a.phywrts,0,1,a.phyblkwrt))/100) avg_wrt_time
from v$filestat a,v$datafile b,dba_data_files c
where b.file# = a.file#
and b.file# = c.file_id
order by disk,c.tablespace_name,a.file#;
select a.username,round(b.buffer_gets/(1024*1024),2) buffer_gets_M,b.sql_text
from dba_users a,v$sqlarea b
where a.user_id = b.parsing_user_id
and b.buffer_gets > 5000000;

大事務：
select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,message from v$session_longops
where message like '%RMAN%';
select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,message from v$session_longops
where sofar <> totalwork
where (sofar/totalwork)*100 < 100;

等待事件：
set linesize 200
column username for a12
column program for a30
column event for a28
column p1text for a15
column p1 for 999,999,999,999,999
select s.username,s.program,sw.event,sw.p1text,sw.p1 from v$session s,v$session_wait sw
where s.sid=sw.sid and s.status='ACTIVE'
order by sw.p1;
select event,p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait
order by event;
where event = 'buffer busy waits';
select owner,segment_name,segment_type,file_id,block_id from dba_extents
where file_id = &P1 and &P2 between block_id and block_id + blocks -1;
column event for a35;
column p1text for a40;
select sid,event,p1,p1text from v$session_wait order by event;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25964700/viewspace-700300/，如需轉載，請註明出處，否則將追究法律責任。