Temp表空间上进程的查询
1
2
3
4
5
|
select a.tablespace, b.sid, b.serial#, a.blocks,c.sql_text from v$sort_usage a,v$session b,v$sqltext c where a.session_addr = b.saddr and b.sql_address = c.address order by a.tablespace,b.sid,b.serial#,c.address, c.piece; |
查看表锁
1
|
select * from sys.v_$sqlarea where disk_reads>100; |
监控事例的等待
1
2
3
4
|
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 ; |
回滚段的争用情况
1
2
3
|
select name , waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn; |
查看前台正在发出的SQL语句
1
2
3
4
5
|
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' )); |
数据表占用空间大小情况
1
2
3
4
|
select segment_name,tablespace_name,bytes,blocks from user_segments where segment_type= 'TABLE' ORDER BY bytes DESC ,blocks DESC ; |
查看表空间碎片大小
1
2
3
4
|
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; |
查看表空间占用磁盘情况
1
2
3
4
5
6
7
8
9
10
11
|
select b.file_id 文件ID号, b.tablespace_name 表空间名, b.bytes 字节数, (b.bytes- sum (nvl(a.bytes,0))) 已使用, sum (nvl(a.bytes,0)) 剩余空间, sum (nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id; |
查看Oracle 表空间使用率
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)" , SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)" , ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE ) * 100, 2) || '%' "USED_RATE(%)" , FREE_SPACE || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND ( SUM (BYTES) / (1024 * 1024), 2) SPACE , SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND ( SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --如果有临时表空间 SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)" , USED_SPACE || 'M' "USED_SPACE(M)" , ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)" , NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND ( SUM (BYTES) / (1024 * 1024), 2) SPACE , SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND ( SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND ( SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1; |
查看Temp 表空间实际使用磁盘大小
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
Select f.tablespace_name, d.file_name "Tempfile name" , round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB" , round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" , round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "Used MB" , round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) / round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100, 2) as "Used_Rate(%)" from SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p where f.tablespace_name(+) = d.tablespace_name and f.file_id(+) = d.file_id and p.file_id(+) = d.file_id; |
查看session使用回滚段
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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区剩余可用内存
1
2
3
4
5
6
|
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' ; |
–监控表空间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
order by df.tablespace_name;
监控SGA命中率
1
2
3
4
5
6
7
|
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 ; |
监控 SGA 中字典缓冲区的命中率
1
2
3
4
5
|
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 **享缓存区的命中率,应该小于1%
1
2
3
|
select sum (pins) "Total Pins" , sum (reloads) "Total Reloads" , sum (reloads)/ sum (pins) *100 libcache from v$librarycache; |
监控 SGA 中重做日志缓存区的命中率,应该小于1%
1
2
3
4
5
|
SELECT name , gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ( 'redo allocation' , 'redo copy' ); |
监控内存和硬盘的排序比率,最好使它小于 .10
1
2
3
|
SELECT name , value FROM v$sysstat WHERE name IN ( 'sorts (memory)' , 'sorts (disk)' ) ; |
监控字典缓冲区
1
2
|
SELECT SUM (GETS) "DICTIONARY GETS" , SUM (GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE ; |
系统用户建在system表空间中的表”>非系统用户建在SYSTEM表空间中的表
1
2
3
4
|
SELECT owner,table_name FROM DBA_TABLES WHERE tablespace_name in ( 'SYSTEM' , 'USER_DATA' ) AND owner NOT IN ( 'SYSTEM' , 'SYS' , 'OUTLN' , 'ORDSYS' , 'MDSYS' , 'SCOTT' , 'HOSTEAC' ); |
性能最差的SQL
1
2
3
4
|
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
1
|
select * from sys.v_$sqlarea where disk_reads>100; |
最频繁执行的sql
1
|
select * from sys.v_$sqlarea where executions>100; |
查询使用CPU多的用户session
1
2
3
4
5
6
|
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 ; |
当前每个会话使用的对象数
1
2
3
4
5
|
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) ; |