连接到服务器后
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)