表:
1、监控表的增长
select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='FOUNDER' ORDER BY bytes/1024/1024 desc;
2、表和索引分析信息
SELECT 'table', COUNT (*) FROM dba_tables WHERE last_analyzed IS NOT NULL GROUP BY 'table'
|
UNION ALL
SELECT 'index', COUNT (*) FROM dba_indexes WHERE last_analyzed IS NOT NULL GROUP BY 'index';
3.未建索引的表
SELECT /*+ rule */
owner, segment_name, segment_type, tablespace_name,
TRUNC (BYTES / 1024 / 1024, 1) size_mb
FROM dba_segments t
WHERE NOT EXISTS (
SELECT 'x'
FROM dba_indexes i
WHERE t.owner = i.table_owner
AND t.segment_name = i.table_name)
AND t.segment_type IN ('TABLE', 'TABLE PARTITION')
AND t.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY 5 DESC;
升序用ASC
9.sort_segment检查
select tablespace_name,extent_size db_blocks_per_extent,total_extents,
used_extents,free_extents from v$sort_segment;
10.数据库总大小
select round(sum(space)) all_space_M from
(
select sum(bytes)/1024/1024 space from dba_data_files
union all
select nvl(sum(bytes)/1024/1024,0) space from dba_temp_files
union all
select sum(bytes)/1024/1024 space from v$log
);
11.检测连接数情况
(1)
select SW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT
from v$session S,v$session_wait SW where S.Username is not null and SW.Sid=S.Sid
and SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc;
(2)
select count(*) from v$session;
(3)
select sid,serial#,username,program,machine,status from v$session;
1)信息1 3)信息3 4)信息4 5)回滚段的争用情况 6)rollback信息 12.Redo log信息检查 1)Redo Log 文件状态 2)LogGroup信息 3)关于log_buffer 4)查询LOG大小及频率 break on report var redoMbytes number; print redoMbytes select trunc(first_time) d1 13.IO情况检查 select count(*) from v$session; 2)数据字典命中率 3)锁竞争 4)排序命中率 5)数据缓冲区命中率 6)Miss LRU Hit命中率 7)检查内存排序性能 8)redo log buffer retry ratio 9)wait等待检查 select event,total_waits,time_waited,average_wait select sid,seq#,event,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' 15、查询lock锁
col segment_name format a20
col tablespace_name format a20
select segment_name,owner,tablespace_name,
dba_rollback_segs.status
from dba_rollback_segs,v$Datafile where file_id=file#;
2)信息2
select segment_name,initial_extent,next_extent,min_extents,
owner,dba_rollback_segs.status status,optsize
from dba_rollback_segs,v$rollstat
where dba_rollback_segs.segment_id=v$rollstat.usn;
col Rollback_Name for a16
select substr(V$rollname.NAME,1,20) "Rollback_Name",
substr(V$rollstat.EXTENTS,1,6) "EXTENT",
v$rollstat.RSSIZE, v$rollstat.WRITES,
substr(v$rollstat.XACTS,1,6) "XACTS",
v$rollstat.GETS,
substr(v$rollstat.WAITS,1,6) "WAITS",
v$rollstat.HWMSIZE, v$rollstat.SHRINKS,
substr(v$rollstat.WRAPS,1,6) "WRAPS",
substr(v$rollstat.EXTENDS,1,6) "EXTEND",
v$rollstat.AVESHRINK,
v$rollstat.AVEACTIVE
from v$rollname, v$rollstat
where v$rollname.USN = v$rollstat.USN
order by v$rollname.USN;
select r.name Rollback_Name,
p.pid Oracle_PID,
p.spid OS_PID,
nvl(p.username,'NO TRANSACTION') Transaction,
p.terminal Terminal
from v$lock l, v$process p, v$rollname r
where l.addr = p.addr(+)
and trunc(l.id1(+)/65536)=r.usn
and l.type(+) = 'TX'
and l.lmode(+) = 6
order by r.name;
select name ,waits ,gets ,waits/gets "Ratio" from v$rollstat a ,v$rollname b where a.usn=b.usn;
select substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",
substr(sys.dba_segments.OWNER,1,8) "Owner",
substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name",
substr(sys.dba_segments.SEGMENT_NAME,1,12) "Rollback Name",
substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",
substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",
substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx",
substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx",
substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr",
substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",
substr(sys.dba_segments.EXTENTS,1,6) "Extent#",
substr(sys.dba_rollback_segs.STATUS,1,10) "Status"
from sys.dba_segments, sys.dba_rollback_segs
where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and
sys.dba_segments.segment_type = 'ROLLBACK'
order by sys.dba_rollback_segs.segment_id;
col member for a56
select f.member "member",
f.group# "group",
l.bytes/1024/1024 "size",
l.status
from v$logfile f, v$log l
where f.group#=l.group#
order by f.group#,f.member;
SELECT group#, sequence#, bytes, members, status from v$log;
select name,value from v$sysstat where name in('redo entries','redo buffer allocation retries');
set linesize 300
set pages 100
column d1 form a20 heading "Date"
column sw_cnt form 99999 heading 'Number|of|Switches'
column Mb form 999,999 heading "Redo Size"
column redoMbytes form 999,999,9999 heading "Redo Log File Size (Mb)"
compute sum of sw_cnt on report
compute sum of Mb on report
begin
select max(bytes)/1024/1024 into :redoMbytes from v$log;
end;
/
, count(*) sw_cnt
, count(*) * :redoMbytes Mb
from v$log_history
group by trunc(first_time)
/
col file_name for a46
select
df.name file_name,
fs.phyrds reads,
fs.phywrts writes,
(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) readtime,
(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) writetime
from
v$datafile df,v$filestat fs
where df.file#=fs.file#
order by df.name;
14.命中率相关检查
1)Shared Pool Size 命中率
select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %"
from v$librarycache where namespace
in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');
select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %"
from v$rowcache;
select substr(ln.name,1,25) Name,
l.gets, l.misses,
100*(l.misses/l.gets) "% Ratio (STAY UNDER 1%)"
from v$latch l, v$latchname ln
where ln.name in ('cache buffers lru chain')
and ln.latch# = l.latch#;
select a.value "Sort(Disk)", b.value "Sort(Memory)",
round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)';
select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio
from v$sysstat phy,v$sysstat cur,v$sysstat con
where phy.name='physical reads' and cur.name='db block gets' and con.name='consistent gets';
column "Miss LRU Hit%" format 99.9999999;
col name format a40
select name, (sleeps/gets) "Miss LRU Hit%"
from v$latch where name ='cache buffers lru chain';
select a.name, to_char(value)
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)');
select to_char(r.value/e.value) "redo log buffer retry ratio"
from v$sysstat r,v$sysstat e
where r.name='redo buffer allocation retries'
and e.name='redo entries';
select count(*) total_in_wait from v$session_wait
where event='log buffer space';
from v$system_event
where event like '%undo%';
and event not like 'rdbms%';
SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
ORDER BY id1, request;