資料庫巡檢常用的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/，如需轉載，請註明出處，否則將追究法律責任。