数据库巡检常用的SQL语句

连接到服务器后
su - oracle
sqlplus /nolog
conn / as sysdba

1.查看实例的状态：
set linesize 200
set pagesize 200
select instance_name,status
from v$instance;

2.查看数据文件的状态：
set linesize 200
set pagesize 200
select file_name,tablespace_name,status,autoextensible
from dba_data_files;

3.查看日志的状态信息：
set linesize 200
set pagesize 200
select sequence#,group#,status,archived
from v$log;

4.查看备份的有效性：
su - oracle
rman
connect target
list backup;
看当天备份的status，如果是AVAILABLE,说明备份有效

5.查看session的使用情况：
set linesize 200
set pagesize 200
select count(*)
from v$session;---总的session
select count(*)
from v$session
where status='ACTIVE';---活动的session

6.表空间使用情况：
set linesize 200
set pagesize 200
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"  
from  
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,   
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b   
where a.tablespace_name=b.tablespace_name   
order by ((a.bytes-b.bytes)/a.bytes) desc;

7.监控FRA空间使用率
set linesize 200
set pagesize 200
select * from v_$flash_recovery_area_usage;
SELECT (100 - sum(percent_space_used)) + sum(percent_space_reclaimable)FROM v$flash_recovery_area_usage;

8.缓冲区命中率:
set linesize 200
set pagesize 200
select (1-(sum(decode(name, 'physical reads',value,0))/(sum(decode(name, 'db block gets',value,0))
+sum(decode(name,'consistent gets',value,0))))) * 100 "Hit Ratio"
from v$sysstat;

9.数据字典缓存命中率：
set linesize 200
set pagesize 200
select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;

10.库缓存命中率：
set linesize 200
set pagesize 200
select Sum(Pins)/(Sum(Pins) + Sum(Reloads)) * 100 "Hit Ratio" from V$LibraryCache;

11.PGA内存排序命中率：
set linesize 200
set pagesize 200
select a.value "Disk Sorts",b.value "Memory Sorts",
       round((100*b.value) / decode((a.value+b.value),0,1,(a.value+b.value)),2) "Pct Memory Sorts"
from v$sysstat a,v$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)';

12.最消耗内存的前10个语句占所有语句的比例：---在没有调整的情况下，大多数系统中10个最常使用的SQL语句的访问量占了整个系统中内存读操作的50%以上
set linesize 200
set pagesize 200
select sum(pct_bufgets) "Percent"
from (select rank() over ( order by buffer_gets desc) as rank_bufgets,
      to_char(100*ratio_to_report(buffer_gets) over(),'999.99') pct_bufgets
      from v$sqlarea)
where rank_bufgets < 11;

13.调整前25个最占用内存的语句:
set serverout on size 1000000
declare
   top25 number;
   text1 varchar2(4000);
   x number;
   len1 number;
cursor c1 is
   select buffer_gets,substr(sql_text,1,4000)
   from v$sqlarea
   order by buffer_gets desc;
begin
   dbms_output.put_line('Gets'||' '||'Text');
   dbms_output.put_line('----------'||' '||'----------------------');
   open c1;
   for i in 1..25 loop
     fetch c1 into top25,text1;
     dbms_output.put_line(rpad(to_char(top25),9)||' '||substr(text1,1,66));
     len1:=length(text1);
     x:=66;
     while len1 > x-1 loop
       dbms_output.put_line('" '||substr(text1,x,66));
     x:=x+66;
     end loop;
   end loop;
end;
/

14.最浪费磁盘读操作的前10个语句占所有语句的比例：
set linesize 200
set pagesize 200
select sum(pct_bufgets) "Percent"
from (select rank() over (order by disk_reads desc) as rank_bufgets,
      to_char(100*ratio_to_report(disk_reads) over (),'999.99') pct_bufgets
      from v$sqlarea)
where rank_bufgets < 11;

15.查询25个滥用磁盘读操作的最主要语句
set serverout on size 1000000
declare
   top25 number;
   text1 varchar2(4000);
   x number;
   len1 number;
cursor c1 is
   select disk_reads,substr(sql_text,1,4000)
   from v$sqlarea
   order by disk_reads desc;
begin
   dbms_output.put_line('Reads'||' '||'Text');
   dbms_output.put_line('----------'||' '||'----------------------');
   open c1;
   for i in 1..25 loop
     fetch c1 into top25,text1;
     dbms_output.put_line(rpad(to_char(top25),9)||' '||substr(text1,1,66));
     len1:=length(text1);
     x:=66;
     while len1 > x-1 loop
       dbms_output.put_line('" '||substr(text1,x,66));
     x:=x+66;
     end loop;
   end loop;
end;
/

16.查看锁定问题
set linesize 200
set pagesize 200
select username,lockwait,status,machine,program
from v$session
where sid in
          (select session_id from v$locked_object);

17.查看锁定的sql(如果发现锁定的话)
set linesize 200
set pagesize 200
select sql_text
from v$sql
where hash_value in
          (select sql_hash_value from v$session where sid in
            (select session_id from v$locked_object));
或者
SELECT
  REPLACE(SQL_TEXT, CHR(13), CHR(10) || CHR(13))
   FROM v$sqltext a
  WHERE (a.HASH_VALUE, a.ADDRESS) IN
        (SELECT decode(sql_hash_value, 0, prev_hash_value, sql_hash_value),
                decode(sql_hash_value, 0, prev_sql_addr, sql_address)
           FROM v$session b
          where b.sid = &sid
            and b.serial# = &serial) order by rownum desc;

18.查看锁定的位置(如果发现锁定的话)
set linesize 200
set pagesize 200
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S
WHERE l.SESSION_ID=S.SID;

19.删除锁定的会话(如果发现锁定的话)
set linesize 200
set pagesize 200
alter system kill session 'sid,serial#';
如：alter system kill session '1076,1263'; （其中sid=l.session_id）

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16978544/viewspace-691846/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16978544/viewspace-691846/