Oracle 常用性能监控T-SQL

谁借莪１个温暖的怀抱￠ 2022-09-30 00:39 148阅读 0赞

#### Oracle常用性能监控SQL语句 ####

\--查看表锁  
select \* from sys.v\_$sqlarea where disk\_reads>100  
\--监控事例的等待  
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 name, waits, gets, waits/gets "Ratio"  
from v$rollstat a, v$rollname b  
where a.usn = b.usn  
\--查看前台正在发出的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  
\--查看表空间占用磁盘情况  
     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  
\--查看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'  
\--监控表空间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命中率  
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 中字典缓冲区的命中率  
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%  
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",  
sum(reloads)/sum(pins) \*100 libcache  
from v$librarycache  
\--监控 SGA 中重做日志缓存区的命中率，应该小于1%  
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  
SELECT name, value  
FROM v$sysstat  
WHERE name IN ('sorts (memory)', 'sorts (disk)')  
\--监控字典缓冲区  
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"  
FROM V$ROWCACHE  
\--非系统用户建在SYSTEM表空间中的表  
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  
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)