prompt
prompt ---------------- database information ----------------
set wrap off
set linesize 100
column name format a10
column log_mode format a16
column open_mode format a15
column remote_archive format a16
column database_role format a20
select name,log_mode,open_mode,remote_archive,database_role
from v$database;
prompt
prompt ---------------- instance information ----------------
set wrap off
set linesize 100
column instance_name format a15
column host_name format a10
column version format a10
column status format a8
column parallel format a8
column database_status format a15
column instance_role format a16
select instance_name,host_name,version,status,parallel,database_status,instance_role
from v$instance;
prompt
prompt ---------------- thread information ----------------
set wrap off
set linesize 100
column thread# format 9
column instance format a15
select thread#,status,instance,checkpoint_time
from v$thread;
prompt
prompt ---------------- SGA ----------------
select * from v$sga;
prompt
prompt ---------------- Check User Tablespace ----------------
col username form. a15
col account_status form. a20
col default_tablespace form. a20
col temporary_tablespace form. a20
select username,account_status,default_tablespace,temporary_tablespace
from dba_users
order by username;
prompt
prompt ---------------- check tablespace ----------------
set wrap off
set linesize 100
col host_name for a10
col tablespace_name for a15
SELECT D.TABLESPACE_NAME,
D.STATUS,
ROUND((A.BYTES / 1024 / 1024), 2) SUM_SPACE,
ROUND(((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024),
2) USED_SPACE,
ROUND(((A.BYTES / 1024 / 1024) -
(A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024),
2) FREE_SPACE,
(100 - ROUND((((A.BYTES / 1024 / 1024) -
(A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024) /
(A.BYTES / 1024 / 1024)) * 100.0,
2)) "USED_RATE(%)"
FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME
AND F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
UNION ALL
SELECT d.tablespace_name,
d.status,
ROUND((a.bytes / 1024 / 1024), 2) SUM_SPACE,
ROUND(NVL(t.bytes, 0) / 1024 / 1024, 2) USED_SPACE,
ROUND((a.bytes / 1024 / 1024) - (NVL(t.bytes, 0) / 1024 / 1024), 2) FREE_SPACE,
(100 - ROUND(100 - (NVL(t.bytes / a.bytes * 100, 0)), 2)) "USED_RATE(%)"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes
from SYS.v_$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
prompt
prompt ---------------- check invalid objects ----------------
set wrap off
set linesize 100
col owner for a10
col object_name for a35
SELECT owner,OBJECT_TYPE,object_name,STATUS FROM ALL_OBJECTS WHERE status<>'VALID';
prompt
prompt ---------------- check kill session ----------------
set linesize 150
col host_name for a10
col OS_USER_NAME for a20
col ORACLE_USERNAME for a20
col MACHINE for a20
select s.INST_ID,s.sid,s.SERIAL#,s.MACHINE,s.PROGRAM
from gv$session s,gv$locked_object l
where l.SESSION_ID =s.SID and l.PROCESS=s.PROCESS
order by s.MACHINE;
prompt
prompt ---------------- check locked object ----------------
set linesize 100
col username for a15
col sid for 999999
col serial# for 999999
select s.username, s.sid, s.serial#, s.logon_time
from v$locked_object l, v$session s
where l.session_id = s.sid
order by s.logon_time;
prompt
prompt ---------------- Buffer Hit Ratio ----------------
select
round(
(1 - (sum(decode(name, 'physical reads', value, 0)) /
(sum(decode(name, 'db block gets', value, 0)) +
sum(decode(name, 'consistent gets', value, 0))))) * 100
, 2) || '%' "Buffer Hit Ratio"
from v$sysstat;
prompt
prompt ---------------- Dictionary Hit Ratio ----------------
set linesize 30
select round((1 - (sum(getmisses) / sum(gets))) * 100, 2) || '%' "Dictionary Hit Ratio"
from v$rowcache;
prompt
prompt ---------------- Library Hit Ratio ----------------
set linesize 30
select round(sum(pins) / (sum(pins) + sum(reloads)) * 100, 2) || '%' "Library Hit Ratio"
from v$librarycache;
/*
prompt
prompt ---------------- Free Buffer Ratio ----------------
set linesize 100
col "Block Status" for a20
select decode(state,0,'FREE',1,decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),3,'BEING USED',state) "Block Status",count(*)
from x$bh
group by decode(state,0,'FREE',1,decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),3,'BEING USED',state);
*/
prompt
prompt ---------------- Redo Hit Ratio ----------------
set linesize 100
col name for a15
col ratio1 for a10
col ratio2 for a10
select name, gets, misses, immediate_gets, immediate_misses,
round((100 - decode(gets,0,0,misses/(gets+misses))*100),2) || '%' ratio1,
round((100 - decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses))*100),2) || '%' ratio2
from v$latch where name in ('redo allocation', 'redo copy');
prompt
prompt ---------------- TOP 5 SQL Used Frequently ----------------
set linesize 150
col sql_text format a85
select sql_text, executions
from
(select sql_text, executions,
rank() over (order by executions desc) exec_rank
from v$sql)
where exec_rank <= 5;
prompt
prompt ---------------- TOP 5 SQL Disk Read ----------------
set linesize 150
col sql_text format a85
select sql_text, disk_reads
from
(select sql_text, disk_reads,
dense_rank() over (order by disk_reads desc) disk_reads_rank
from v$sql)
where disk_reads_rank <= 5;
prompt
prompt ---------------- SQL Run Long Time ----------------
set wrap on
select a.inst_id,c.PID,c.spid "spid is os_process",a.sid,a.serial#,a.user#,a.lockwait,a.status,a.MACHINE,a.schemaname,a.program,a.module,a.logon_time,a.client_info,b.SQL_TEXT
from gv$session a,gv$sqlarea b,gv$process c
where a.SQL_ADDRESS=b.ADDRESS and c.ADDR=a.PADDR
and a.SCHEMANAME<>'SYS'
and a.status<>'INACTIVE'
and a.LOGON_TIME<=trunc(sysdate-1/120,'HH24');
prompt
prompt ---------------- 碎片程度高的数据对象 ----------------
set wrap off
SELECT owner, segment_name,segment_type,tablespace_name, count(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY owner, segment_name,segment_type,tablespace_name
HAVING COUNT(*) > 2;
prompt
prompt ---------------- Check Data File Autoextensible ----------------
set wrap off
set linesize 100
col file_name format a60
col autoextensible format a15
select file_name,autoextensible
from dba_data_files
where autoextensible='YES';
prompt
prompt ---------------- sorts in memeory and disk ----------------
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)','sorts (disk)');
prompt
prompt ---------------- Datafile I/O ----------------
set wrap off
set linesize 150
col file# format 999
col name format a50
col status format a10
col M format 999999
select a.file#, a.name, a.status, a.bytes/1024/1024 M, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
prompt
prompt ---------------- Tablespace I/O ----------------
set wrap off
set linesize 100
col tablespace_name format a15
col file_name format a40
col phyrds format 999999999
col phyblkrd format 999999999
col phywrts format 999999999
col phyblkwrt format 999999999
select df.tablespace_name,df.file_name,
f.phyrds,f.phyblkrd,f.phywrts, f.phyblkwrt
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
exit;
prompt ---------------- database information ----------------
set wrap off
set linesize 100
column name format a10
column log_mode format a16
column open_mode format a15
column remote_archive format a16
column database_role format a20
select name,log_mode,open_mode,remote_archive,database_role
from v$database;
prompt
prompt ---------------- instance information ----------------
set wrap off
set linesize 100
column instance_name format a15
column host_name format a10
column version format a10
column status format a8
column parallel format a8
column database_status format a15
column instance_role format a16
select instance_name,host_name,version,status,parallel,database_status,instance_role
from v$instance;
prompt
prompt ---------------- thread information ----------------
set wrap off
set linesize 100
column thread# format 9
column instance format a15
select thread#,status,instance,checkpoint_time
from v$thread;
prompt
prompt ---------------- SGA ----------------
select * from v$sga;
prompt
prompt ---------------- Check User Tablespace ----------------
col username form. a15
col account_status form. a20
col default_tablespace form. a20
col temporary_tablespace form. a20
select username,account_status,default_tablespace,temporary_tablespace
from dba_users
order by username;
prompt
prompt ---------------- check tablespace ----------------
set wrap off
set linesize 100
col host_name for a10
col tablespace_name for a15
SELECT D.TABLESPACE_NAME,
D.STATUS,
ROUND((A.BYTES / 1024 / 1024), 2) SUM_SPACE,
ROUND(((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024),
2) USED_SPACE,
ROUND(((A.BYTES / 1024 / 1024) -
(A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024),
2) FREE_SPACE,
(100 - ROUND((((A.BYTES / 1024 / 1024) -
(A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024) /
(A.BYTES / 1024 / 1024)) * 100.0,
2)) "USED_RATE(%)"
FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME
AND F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
UNION ALL
SELECT d.tablespace_name,
d.status,
ROUND((a.bytes / 1024 / 1024), 2) SUM_SPACE,
ROUND(NVL(t.bytes, 0) / 1024 / 1024, 2) USED_SPACE,
ROUND((a.bytes / 1024 / 1024) - (NVL(t.bytes, 0) / 1024 / 1024), 2) FREE_SPACE,
(100 - ROUND(100 - (NVL(t.bytes / a.bytes * 100, 0)), 2)) "USED_RATE(%)"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes
from SYS.v_$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
prompt
prompt ---------------- check invalid objects ----------------
set wrap off
set linesize 100
col owner for a10
col object_name for a35
SELECT owner,OBJECT_TYPE,object_name,STATUS FROM ALL_OBJECTS WHERE status<>'VALID';
prompt
prompt ---------------- check kill session ----------------
set linesize 150
col host_name for a10
col OS_USER_NAME for a20
col ORACLE_USERNAME for a20
col MACHINE for a20
select s.INST_ID,s.sid,s.SERIAL#,s.MACHINE,s.PROGRAM
from gv$session s,gv$locked_object l
where l.SESSION_ID =s.SID and l.PROCESS=s.PROCESS
order by s.MACHINE;
prompt
prompt ---------------- check locked object ----------------
set linesize 100
col username for a15
col sid for 999999
col serial# for 999999
select s.username, s.sid, s.serial#, s.logon_time
from v$locked_object l, v$session s
where l.session_id = s.sid
order by s.logon_time;
prompt
prompt ---------------- Buffer Hit Ratio ----------------
select
round(
(1 - (sum(decode(name, 'physical reads', value, 0)) /
(sum(decode(name, 'db block gets', value, 0)) +
sum(decode(name, 'consistent gets', value, 0))))) * 100
, 2) || '%' "Buffer Hit Ratio"
from v$sysstat;
prompt
prompt ---------------- Dictionary Hit Ratio ----------------
set linesize 30
select round((1 - (sum(getmisses) / sum(gets))) * 100, 2) || '%' "Dictionary Hit Ratio"
from v$rowcache;
prompt
prompt ---------------- Library Hit Ratio ----------------
set linesize 30
select round(sum(pins) / (sum(pins) + sum(reloads)) * 100, 2) || '%' "Library Hit Ratio"
from v$librarycache;
/*
prompt
prompt ---------------- Free Buffer Ratio ----------------
set linesize 100
col "Block Status" for a20
select decode(state,0,'FREE',1,decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),3,'BEING USED',state) "Block Status",count(*)
from x$bh
group by decode(state,0,'FREE',1,decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),3,'BEING USED',state);
*/
prompt
prompt ---------------- Redo Hit Ratio ----------------
set linesize 100
col name for a15
col ratio1 for a10
col ratio2 for a10
select name, gets, misses, immediate_gets, immediate_misses,
round((100 - decode(gets,0,0,misses/(gets+misses))*100),2) || '%' ratio1,
round((100 - decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses))*100),2) || '%' ratio2
from v$latch where name in ('redo allocation', 'redo copy');
prompt
prompt ---------------- TOP 5 SQL Used Frequently ----------------
set linesize 150
col sql_text format a85
select sql_text, executions
from
(select sql_text, executions,
rank() over (order by executions desc) exec_rank
from v$sql)
where exec_rank <= 5;
prompt
prompt ---------------- TOP 5 SQL Disk Read ----------------
set linesize 150
col sql_text format a85
select sql_text, disk_reads
from
(select sql_text, disk_reads,
dense_rank() over (order by disk_reads desc) disk_reads_rank
from v$sql)
where disk_reads_rank <= 5;
prompt
prompt ---------------- SQL Run Long Time ----------------
set wrap on
select a.inst_id,c.PID,c.spid "spid is os_process",a.sid,a.serial#,a.user#,a.lockwait,a.status,a.MACHINE,a.schemaname,a.program,a.module,a.logon_time,a.client_info,b.SQL_TEXT
from gv$session a,gv$sqlarea b,gv$process c
where a.SQL_ADDRESS=b.ADDRESS and c.ADDR=a.PADDR
and a.SCHEMANAME<>'SYS'
and a.status<>'INACTIVE'
and a.LOGON_TIME<=trunc(sysdate-1/120,'HH24');
prompt
prompt ---------------- 碎片程度高的数据对象 ----------------
set wrap off
SELECT owner, segment_name,segment_type,tablespace_name, count(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY owner, segment_name,segment_type,tablespace_name
HAVING COUNT(*) > 2;
prompt
prompt ---------------- Check Data File Autoextensible ----------------
set wrap off
set linesize 100
col file_name format a60
col autoextensible format a15
select file_name,autoextensible
from dba_data_files
where autoextensible='YES';
prompt
prompt ---------------- sorts in memeory and disk ----------------
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)','sorts (disk)');
prompt
prompt ---------------- Datafile I/O ----------------
set wrap off
set linesize 150
col file# format 999
col name format a50
col status format a10
col M format 999999
select a.file#, a.name, a.status, a.bytes/1024/1024 M, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
prompt
prompt ---------------- Tablespace I/O ----------------
set wrap off
set linesize 100
col tablespace_name format a15
col file_name format a40
col phyrds format 999999999
col phyblkrd format 999999999
col phywrts format 999999999
col phyblkwrt format 999999999
select df.tablespace_name,df.file_name,
f.phyrds,f.phyblkrd,f.phywrts, f.phyblkwrt
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
exit;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/90618/viewspace-609491/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/90618/viewspace-609491/