oracle 巡檢指令碼（自動化）
vi /scripts/check/xunjian.sql
set heading off
select '一、資料庫的基本情況' from dual;
set heading off
select '1、資料庫版本' from dual;
set heading on
select * from v$version;
set heading off
select '2、檢視資料庫基本資訊' from dual;
set heading on
set linesize 500
col host_name for a20
select dbid,name,instance_name,instance_name,version,parallel rac,host_name from v$database,v$instance;
set heading off
select '3、例項狀態' from dual;
set heading on
select instance_number,instance_name ,status from gv$instance;
set heading off
select '4、資料庫執行時間' from dual;
set heading on
select to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') 啟動時間,
TRUNC(sysdate - (startup_time))||'天 '||TRUNC(24*((sysdate-startup_time) -TRUNC(sysdate-startup_time)))
||'小時 '||MOD(TRUNC(1440*((SYSDATE-startup_time)-
TRUNC(sysdate-startup_time))),60)
||'分 '||MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME)-
TRUNC(SYSDATE-startup_time))),60)
||'秒' 執行時間
from v$instance;
set heading off
select '5、記憶體情況' from dual;
set heading on
select * from v$sgainfo;
set heading off
select '6、cpu情況' from dual;
set heading on
col STAT_NAME for a20
col COMMENTS for a50
Select stat_name,value,comments from v$osstat where stat_name in ('NUM_CPUS','IDLE_TIME','BUSY_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME');
set heading off
select '二、檢查Oracle物件狀態' from dual;
set heading off
select '1、檢視引數檔案位置' from dual;
show parameter spfile
set heading off
col NAME for a50
select '2、檢視控制檔案' from dual;
set heading on
select status,name from v$controlfile;
set heading off
select '3、檢視線上日誌' from dual;
set heading on
col MEMBER for a50
select group#,status,type,member from v$logfile;
set heading off
select '4、檢查日誌切換頻率' from dual;
set heading on
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from v$log_history where first_time > sysdate - 1 order by first_time ,minutes;
set heading off
select '5、檢視資料檔案' from dual;
set heading on
col NAME for a50
select name,status from v$datafile;
set heading off
select '6、檢視無效的物件' from dual;
set heading on
set linesize 500
select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';
set heading off
select '7、檢視回滾段狀態' from dual;
set heading on
select segment_name,status from dba_rollback_segs;
set heading off
select '8、檢查是否有禁用約束' from dual;
set heading on
set linesize 1000
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P';
set heading off
select '9、檢查是否有禁用觸發器' from dual;
set heading on
col owner for a10
col taigger_name for a10
col table_name for a30
col table_name for a30
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';
set heading off
select '10、Oracle Job是否有失敗' from dual;
set heading on
select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';
set heading off
select '11、檢查失效的索引' from dual;
set heading on
select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID';
set heading off
select '三、檢查Oracle相關資源的使用情況' from dual;
set heading off
select '1、檢視錶空間的使用情況' from dual;
set heading on
set linesize 1000
SELECT UPPER(F.TABLESPACE_NAME) "tablespace_name",
D.TOT_GROOTTE_MB "tablesapce_size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "used_tablespace_size(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "used%",
F.TOTAL_BYTES "free_size(M)",
F.MAX_BYTES "max_byte(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
set heading off
select '2、檢視臨時表空間使用情況' from dual;
set heading on
select tablespace_name , sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name;
set heading off
select '3、檢視臨時段使用的情況' from dual;
set heading on
COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT username, segtype, extents "Extents Allocated"
,blocks "Blocks Allocated"
FROM v$tempseg_usage;
set heading off
select '4、檢視所有資料檔案i/o情況' from dual;
set heading on
SELECT ts.name AS ts, fs.phyrds "Reads", fs.phywrts "Writes"
,fs.phyblkrd AS br, fs.phyblkwrt AS bw
,fs.readtim "RTime", fs.writetim "WTime"
FROM v$tablespace ts, v$datafile df, v$filestat fs
WHERE ts.ts# = df.ts# AND df.file# = fs.file#
UNION
SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes"
,ts.phyblkrd AS br, ts.phyblkwrt AS bw
,ts.readtim "RTime", ts.writetim "WTime"
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;
set heading off
select '5、檢視top 10 熱segment' from dual;
set heading on
col objct_name for a30
col OWNER for a20
select * from
(select
ob.owner, ob.object_name, sum(b.tch) Touchs
from x$bh b , dba_objects ob
where b.obj = ob.data_object_id
and b.ts# > 0
group by ob.owner, ob.object_name
order by sum(tch) desc)
where rownum <=10;
set heading off
select '6、檢視物理讀最多的object' from dual;
set heading on
select * from (select owner,object_name,value from v$segment_statistics where statistic_name='physical reads' order by value desc) where rownum<=10;
set heading off
select '7、檢視熱點資料檔案(從單塊讀取時間判斷)' from dual;
set heading on
SELECT t.file_name,
t.tablespace_name,
round(s.singleblkrdtim / s.singleblkrds, 2) AS CS,
s.READTIM,
s.WRITETIM
FROM v$filestat s, dba_data_files t
WHERE s.file# = t.file_id and rownum<=10 order by cs desc;
set heading off
select '8、檢查Oracle初始化檔案中相關引數值' from dual;
set heading on
select resource_name,max_utilization,initial_allocation,
limit_value from v$resource_limit;
set heading off
select '注：若LIMIT_VALU - MAX_UTILIZATION<=5，則表明與RESOURCE_NAME相關的Oracle初始化引數需要調整。可以透過引數檔案調整。' from dual;
set heading off
select '9、檢查資料庫連線情況' from dual;
set heading on
select sid,serial#,username,program,machine,status from v$session;
set heading off
select "(注：殺掉會話的語句alter system kill session 'SID,SERIAL#')" from dual;
set heading off
select '10、檢視熱點資料檔案' from dual;
set heading on
SELECT t.file_name,
t.tablespace_name,
round(s.singleblkrdtim / s.singleblkrds, 2) AS CS,
s.READTIM,
s.WRITETIM
FROM v$filestat s, dba_data_files t
WHERE s.file# = t.file_id and rownum<=10 order by cs desc;
set heading off
select ' 11、檢查一些擴充套件異常的物件 ' from dual;
set heading on
select Segment_Name, Segment_Type, TableSpace_Name,
(Extents/Max_extents)*100 Percent
From sys.DBA_Segments
Where Max_Extents != 0 and (Extents/Max_extents)*100>=95
order By Percent;
set heading off
select ' 12、檢查system表空間內的內容 ' from dual;
set heading on
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM';
set heading off
select ' 13、檢查物件的下一擴充套件與表空間的最大擴充套件值 ' from dual;
set heading on
select a.table_name, a.next_extent, a.tablespace_name
from all_tables a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk
union
select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk;
set heading off
select '四、記憶體的具體檢視' from dual;
set heading off
select ' 1、檢視記憶體佔用各個池子大小' from dual;
set heading on
COL name FORMAT a32;
SELECT pool, name, bytes FROM v$sgastat
WHERE pool IS NULL
OR pool != 'shared pool' OR (pool = 'shared pool'
AND (name IN('dictionary cache','enqueue','library
cache','parameters',
'processes','sessions','free memory')))
ORDER BY pool DESC NULLS FIRST, name;
set heading off
select ' 2、檢查shered pool free space ' from dual;
set heading on
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';
set heading off
select ' 3、檢查shared pool中library cach ' from dual;
set heading on
select namespace,pinhitratio from v$librarycache;
set heading off
select ' 4、檢查整體命中率(library cache)' from dual;
set heading on
select sum(pins) "hits",
sum(reloads) "misses",
sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"
from v$librarycache;
set heading off
select ' 5、library cache中詳細比率資訊' from dual;
set heading on
SELECT 'Library Lock Requests' "Ratio"
, ROUND(AVG(gethitratio) * 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE
UNION
SELECT 'Library Pin Requests' "Ratio", ROUND(AVG(pinhitratio)
* 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE
UNION
SELECT 'Library I/O Reloads' "Ratio"
, ROUND((SUM(reloads) / SUM(pins)) * 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE
UNION
SELECT 'Library Reparses' "Ratio"
, ROUND((SUM(reloads) / SUM(pins)) * 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE;
set heading off
select ' 6、檢查資料字典的命中率' from dual;
set heading on
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
set heading off
select '注：row cache的命中率至少小於90%' from dual;
set heading off
select ' 7、每個子shared pool由 單獨的shared pool latch保護 檢視 他們的命中率 ' from dual;
set heading on
col name format a15
select addr,name,gets,misses,1-misses/gets from v$latch_children where name='shared pool';
set heading off
select ' 8、檢視shared pool建議' from dual;
set heading on
column c1 heading 'Pool |Size(M)'
column c2 heading 'Size|Factor'
column c3 heading 'Est|LC(M) '
column c4 heading 'Est LC|Mem. Obj.'
column c5 heading 'Est|Time|Saved|(sec)'
column c6 heading 'Est|Parse|Saved|Factor'
column c7 heading 'Est|Object Hits' format 999,999,999
SELECT shared_pool_size_for_estimate c1,shared_pool_size_factor c2,
estd_lc_size c3,estd_lc_memory_objects c4,estd_lc_time_saved c5,
estd_lc_time_saved_factor c6,to_char(estd_lc_memory_object_hits,99999999999) c7 FROM V$SHARED_POOL_ADVICE;
set heading off
select ' 9、檢視shared pool中 各種型別的chunk的大小數量' from dual;
set heading on
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIzE"
FROM X$KSMSP GROUP BY KSMCHCLS;
set heading off
select ' 10、檢視使用shard_pool保留池情況' from dual;
set heading on
SELECT request_misses, request_failures, free_space
FROM v$shared_pool_reserved;
set heading off
select '11、 pga 建議' from dual;
set heading on
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'pga_aggregate_target') "Current Mb"
, ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"
, ROUND(estd_pga_cache_hit_percentage) "%"
FROM v$pga_target_advice
ORDER BY 2;
set heading off
select ' 12、檢視buffer cache 命中率' from dual;
set heading on
select 1-(sum(decode(name, 'physical reads', value, 0))/
(sum(decode(name, 'db block gets', value, 0))+
(sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio"
from v$sysstat;
set heading off
select ' 13、檢視buffer cache設定大小建議' from dual;
set heading on
select size_for_estimate, estd_physical_read_factor, to_char(estd_physical_reads,99999999999999999999999) as"estd_physical_reads" from v$db_cache_advice where name = 'DEFAULT';
set heading off
select '14、檢視buffer cache中defalut pool 命中率' from dual;
set heading on
select name,1-(physical_reads)/(consistent_gets+db_block_gets)
from v$buffer_pool_statistics;
set heading off
select '注：default池命中率至少要大於90%' from dual;
set heading off
select '15、檢查lgwr i/o效能' from dual;
set heading on
select total_waits,time_waited,average_wait,time_waited/total_waits as avg from v$system_event where event = 'log file parallel write';
set heading off
select '16、檢查與redo相關效能指標' from dual;
set heading on
set linesize 500
select name,value from v$sysstat where name like '%redo%';
set heading off
select ' 17、查詢redo block size' from dual;
set heading on
select max(lebsz) from x$kccle;
set heading off
select '18、 計算出每個事務平均處理多少個redo block' from dual;
set heading on
select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo blocks written') a ,(select value trancount from v$sysstat where name='user commits') b;
set heading off
select ' 19、 檢查undo rollback segment 使用情況' from dual;
set heading on
col name for a60
select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstat a,v$rollname b where a.usn=b.usn order by waits desc;
set heading off
select ' 20、計算每秒鐘產生的undoblk數量' from dual;
set heading on
select sum(undoblks)/sum((end_time-begin_time)*24*60*60) from v$undostat;
set heading off
select ' 21、查詢undo具體資訊' from dual;
set heading on
COL undob FORMAT 99990;
COL trans FORMAT 99990;
COL snapshot2old FORMAT 9999999990;
SELECT undoblks "UndoB", txncount "Trans"
,maxquerylen "LongestQuery", maxconcurrency "MaxConcurrency"
,ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSpaceWait"
FROM v$undostat;
set heading off
select ' 22、查詢rollback 段詳細資訊(收縮次數,擴充套件次數,平均活動事務等)' from dual;
set heading on
COL RBS FORMAT a50;
SELECT n.name "RBS", s.extends "Extends", s.shrinks
"Shrinks"
,s.wraps "Wraps", s.aveshrink "AveShrink"
,s.aveactive "AveActive"
FROM v$rollname n JOIN v$rollstat s USING(usn)
WHERE n.name != 'SYSTEM';
set heading off
select ' 23、查詢當前rollback segment使用情況' from dual;
set heading on
COL RBS FORMAT a50;
SELECT n.name "RBS", s.status, s.waits, s.gets, to_char(s.writes,'9999999999999')
,s.xacts "Active Trans"
FROM v$rollname n JOIN v$rollstat s USING(usn)
WHERE n.name != 'SYSTEM';
set heading off
select '24、查詢使用rollback segment時等待比率' from dual;
set heading on
SELECT ROUND(SUM(waits/gets)*100,2)||'%' "Contention" FROM
v$rollstat;
set heading off
select '25、查詢使用rollback segment時等待比率及其平局活動事務數' from dual;
set heading on
COL contention FORMAT 9999999990;
SELECT AVG(xacts) "Trans per RBS"
,ROUND(SUM(waits/gets)*100,2)||'%' "Contention"
FROM v$rollstat;
set heading off
select '五、檢查Oracle資料庫效能' from dual;
set heading off
select '1、檢查資料庫的等待事件' from dual;
set heading on
set pages 80
set lines 120
col event for a40
select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';
set heading off
select '2、檢視與redo相關等待事件' from dual;
set heading on
col event format a40
select event,total_waits,total_timeouts,average_wait from v$system_event where upper(event) like'%REDO%';
set heading off
select '3、檢視session redo event' from dual;
set heading on
select event,total_waits,total_timeouts,average_wait from v$session_event where upper(event) like'%REDO%';
set heading off
select '4、Disk Read最高的SQL語句的獲取' from dual;
set heading on
SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM<=5 order by SQL_TEXT desc
set heading off
select '5、查詢前十條效能差的sql' from dual;
set heading on
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10 ;
set heading off
select '6、等待時間最多的5個系統等待事件的獲取' from dual;
set heading on
SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
set heading off
select '7、檢查執行很久的SQL' from dual;
set heading on
COLUMN USERNAME FORMAT A12
COLUMN OPNAME FORMAT A16
COLUMN PROGRESS FORMAT A8
SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;
set heading off
select '9、檢查碎片程度高的表' from dual;
set heading on
SELECT segment_name table_name,COUNT(*) extents FROM dba_segments WHERE owner NOT IN
('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*))
FROM dba_segments GROUP BY segment_name);
set heading off
select '10、檢查死鎖及處理' from dual;
set heading on
col sid for 999999
col username for a10
col schemaname for a10
col osuser for a16
col machine for a16
col terminal for a20
col owner for a10
col object_name for a30
col object_type for a10
select sid,serial#,username,SCHEMANAME,osuser,MACHINE,
terminal,PROGRAM,owner,object_name,object_type,o.object_id
from dba_objects o,v$locked_object l,v$session s
where o.object_id=l.object_id and s.sid=l.session_id;
set heading off
select '11、檢視資料庫中行chain' from dual;
set heading on
SELECT 'Chained Rows ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'table fetch continued row')
/ (SELECT SUM(value) FROM V$SYSSTAT
WHERE name IN ('table scan rows gotten', 'table fetch by
rowid'))
* 100, 3)||'%' "Percentage"
FROM DUAL;
set heading off
select '12、查詢解析比率' from dual;
set heading on
SELECT 'Soft Parses ' "Ratio"
, ROUND(
((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)')
- (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)'))
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count')
* 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Hard Parses ' "Ratio"
, ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') * 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Parse Failures ' "Ratio"
, ROUND((SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'parse count (failures)')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)')* 100, 5)||'%' "Percentage" FROM DUAL;
set heading off
select '13、檢視與latch有關的event資訊' from dual;
set heading on
COL event FORMAT a20;
COL waits FORMAT 9999990;
COL timeouts FORMAT 99999990;
COL average FORMAT 99999990;
SELECT event "Event", time_waited "Total Time", total_waits
"Waits"
,average_wait "Average", total_timeouts "Timeouts"
FROM V$SYSTEM_EVENT
WHERE event = 'latch free'
ORDER BY EVENT;
set heading off
select '14、檢視大表小表掃描對應的值' from dual;
set heading on
SELECT value, name FROM V$SYSSTAT WHERE name IN
('table fetch by rowid', 'table scans (short tables)'
, 'table scans (long tables)');
SELECT 'Short to Long Full Table Scans' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'table scans (short tables)')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
('table scans (short tables)', 'table scans (long tables)'))
* 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Short Table Scans ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'table scans (short tables)')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))
* 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Long Table Scans ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'table scans (long tables)')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name
IN ('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))
* 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Table by Index ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch by rowid')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name
IN ('table scans (short tables)', 'table scans (long tables)'
, 'table fetch by rowid'))
* 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Efficient Table Access ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT WHERE name
IN ('table scans (short tables)','table fetch by rowid'))
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name
IN ('table scans (short tables)', 'table scans (long tables)'
, 'table fetch by rowid'))
* 100, 2)||'%' "Percentage"
FROM DUAL;
set heading off
select '15、index使用比率' from dual;
set heading on
col name for a30
SELECT to_char(value,'999999999999999999999'), name FROM V$SYSSTAT WHERE name IN
('table fetch by rowid', 'table scans (short tables)'
, 'table scans (long tables)')
OR name LIKE 'index fast full%' OR name = 'index fetch by
key';
SELECT 'Index to Table Ratio ' "Ratio" , ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name LIKE 'index fast full%'
OR name = 'index fetch by key'
OR name = 'table fetch by rowid')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
('table scans (short tables)', 'table scans (long tables)')
),0)||':1' "Result"
FROM DUAL;
set heading off
select '16、等待class' from dual;
set heading on
col wait_class for a30
SELECT wait_class, COUNT(wait_class) FROM v$system_event
GROUP BY wait_class ORDER BY 1;
vi xunjian.sh
sqlplus / as sysdba <<eof
spool /scripts/check/xunjian.doc
@/scripts/check/xunjian.sql
spool off
EOF
</eof
set heading off
select '一、資料庫的基本情況' from dual;
set heading off
select '1、資料庫版本' from dual;
set heading on
select * from v$version;
set heading off
select '2、檢視資料庫基本資訊' from dual;
set heading on
set linesize 500
col host_name for a20
select dbid,name,instance_name,instance_name,version,parallel rac,host_name from v$database,v$instance;
set heading off
select '3、例項狀態' from dual;
set heading on
select instance_number,instance_name ,status from gv$instance;
set heading off
select '4、資料庫執行時間' from dual;
set heading on
select to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') 啟動時間,
TRUNC(sysdate - (startup_time))||'天 '||TRUNC(24*((sysdate-startup_time) -TRUNC(sysdate-startup_time)))
||'小時 '||MOD(TRUNC(1440*((SYSDATE-startup_time)-
TRUNC(sysdate-startup_time))),60)
||'分 '||MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME)-
TRUNC(SYSDATE-startup_time))),60)
||'秒' 執行時間
from v$instance;
set heading off
select '5、記憶體情況' from dual;
set heading on
select * from v$sgainfo;
set heading off
select '6、cpu情況' from dual;
set heading on
col STAT_NAME for a20
col COMMENTS for a50
Select stat_name,value,comments from v$osstat where stat_name in ('NUM_CPUS','IDLE_TIME','BUSY_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME');
set heading off
select '二、檢查Oracle物件狀態' from dual;
set heading off
select '1、檢視引數檔案位置' from dual;
show parameter spfile
set heading off
col NAME for a50
select '2、檢視控制檔案' from dual;
set heading on
select status,name from v$controlfile;
set heading off
select '3、檢視線上日誌' from dual;
set heading on
col MEMBER for a50
select group#,status,type,member from v$logfile;
set heading off
select '4、檢查日誌切換頻率' from dual;
set heading on
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from v$log_history where first_time > sysdate - 1 order by first_time ,minutes;
set heading off
select '5、檢視資料檔案' from dual;
set heading on
col NAME for a50
select name,status from v$datafile;
set heading off
select '6、檢視無效的物件' from dual;
set heading on
set linesize 500
select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';
set heading off
select '7、檢視回滾段狀態' from dual;
set heading on
select segment_name,status from dba_rollback_segs;
set heading off
select '8、檢查是否有禁用約束' from dual;
set heading on
set linesize 1000
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P';
set heading off
select '9、檢查是否有禁用觸發器' from dual;
set heading on
col owner for a10
col taigger_name for a10
col table_name for a30
col table_name for a30
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';
set heading off
select '10、Oracle Job是否有失敗' from dual;
set heading on
select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';
set heading off
select '11、檢查失效的索引' from dual;
set heading on
select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID';
set heading off
select '三、檢查Oracle相關資源的使用情況' from dual;
set heading off
select '1、檢視錶空間的使用情況' from dual;
set heading on
set linesize 1000
SELECT UPPER(F.TABLESPACE_NAME) "tablespace_name",
D.TOT_GROOTTE_MB "tablesapce_size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "used_tablespace_size(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "used%",
F.TOTAL_BYTES "free_size(M)",
F.MAX_BYTES "max_byte(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
set heading off
select '2、檢視臨時表空間使用情況' from dual;
set heading on
select tablespace_name , sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name;
set heading off
select '3、檢視臨時段使用的情況' from dual;
set heading on
COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT username, segtype, extents "Extents Allocated"
,blocks "Blocks Allocated"
FROM v$tempseg_usage;
set heading off
select '4、檢視所有資料檔案i/o情況' from dual;
set heading on
SELECT ts.name AS ts, fs.phyrds "Reads", fs.phywrts "Writes"
,fs.phyblkrd AS br, fs.phyblkwrt AS bw
,fs.readtim "RTime", fs.writetim "WTime"
FROM v$tablespace ts, v$datafile df, v$filestat fs
WHERE ts.ts# = df.ts# AND df.file# = fs.file#
UNION
SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes"
,ts.phyblkrd AS br, ts.phyblkwrt AS bw
,ts.readtim "RTime", ts.writetim "WTime"
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;
set heading off
select '5、檢視top 10 熱segment' from dual;
set heading on
col objct_name for a30
col OWNER for a20
select * from
(select
ob.owner, ob.object_name, sum(b.tch) Touchs
from x$bh b , dba_objects ob
where b.obj = ob.data_object_id
and b.ts# > 0
group by ob.owner, ob.object_name
order by sum(tch) desc)
where rownum <=10;
set heading off
select '6、檢視物理讀最多的object' from dual;
set heading on
select * from (select owner,object_name,value from v$segment_statistics where statistic_name='physical reads' order by value desc) where rownum<=10;
set heading off
select '7、檢視熱點資料檔案(從單塊讀取時間判斷)' from dual;
set heading on
SELECT t.file_name,
t.tablespace_name,
round(s.singleblkrdtim / s.singleblkrds, 2) AS CS,
s.READTIM,
s.WRITETIM
FROM v$filestat s, dba_data_files t
WHERE s.file# = t.file_id and rownum<=10 order by cs desc;
set heading off
select '8、檢查Oracle初始化檔案中相關引數值' from dual;
set heading on
select resource_name,max_utilization,initial_allocation,
limit_value from v$resource_limit;
set heading off
select '注：若LIMIT_VALU - MAX_UTILIZATION<=5，則表明與RESOURCE_NAME相關的Oracle初始化引數需要調整。可以透過引數檔案調整。' from dual;
set heading off
select '9、檢查資料庫連線情況' from dual;
set heading on
select sid,serial#,username,program,machine,status from v$session;
set heading off
select "(注：殺掉會話的語句alter system kill session 'SID,SERIAL#')" from dual;
set heading off
select '10、檢視熱點資料檔案' from dual;
set heading on
SELECT t.file_name,
t.tablespace_name,
round(s.singleblkrdtim / s.singleblkrds, 2) AS CS,
s.READTIM,
s.WRITETIM
FROM v$filestat s, dba_data_files t
WHERE s.file# = t.file_id and rownum<=10 order by cs desc;
set heading off
select ' 11、檢查一些擴充套件異常的物件 ' from dual;
set heading on
select Segment_Name, Segment_Type, TableSpace_Name,
(Extents/Max_extents)*100 Percent
From sys.DBA_Segments
Where Max_Extents != 0 and (Extents/Max_extents)*100>=95
order By Percent;
set heading off
select ' 12、檢查system表空間內的內容 ' from dual;
set heading on
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM';
set heading off
select ' 13、檢查物件的下一擴充套件與表空間的最大擴充套件值 ' from dual;
set heading on
select a.table_name, a.next_extent, a.tablespace_name
from all_tables a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk
union
select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk;
set heading off
select '四、記憶體的具體檢視' from dual;
set heading off
select ' 1、檢視記憶體佔用各個池子大小' from dual;
set heading on
COL name FORMAT a32;
SELECT pool, name, bytes FROM v$sgastat
WHERE pool IS NULL
OR pool != 'shared pool' OR (pool = 'shared pool'
AND (name IN('dictionary cache','enqueue','library
cache','parameters',
'processes','sessions','free memory')))
ORDER BY pool DESC NULLS FIRST, name;
set heading off
select ' 2、檢查shered pool free space ' from dual;
set heading on
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';
set heading off
select ' 3、檢查shared pool中library cach ' from dual;
set heading on
select namespace,pinhitratio from v$librarycache;
set heading off
select ' 4、檢查整體命中率(library cache)' from dual;
set heading on
select sum(pins) "hits",
sum(reloads) "misses",
sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"
from v$librarycache;
set heading off
select ' 5、library cache中詳細比率資訊' from dual;
set heading on
SELECT 'Library Lock Requests' "Ratio"
, ROUND(AVG(gethitratio) * 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE
UNION
SELECT 'Library Pin Requests' "Ratio", ROUND(AVG(pinhitratio)
* 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE
UNION
SELECT 'Library I/O Reloads' "Ratio"
, ROUND((SUM(reloads) / SUM(pins)) * 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE
UNION
SELECT 'Library Reparses' "Ratio"
, ROUND((SUM(reloads) / SUM(pins)) * 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE;
set heading off
select ' 6、檢查資料字典的命中率' from dual;
set heading on
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
set heading off
select '注：row cache的命中率至少小於90%' from dual;
set heading off
select ' 7、每個子shared pool由 單獨的shared pool latch保護 檢視 他們的命中率 ' from dual;
set heading on
col name format a15
select addr,name,gets,misses,1-misses/gets from v$latch_children where name='shared pool';
set heading off
select ' 8、檢視shared pool建議' from dual;
set heading on
column c1 heading 'Pool |Size(M)'
column c2 heading 'Size|Factor'
column c3 heading 'Est|LC(M) '
column c4 heading 'Est LC|Mem. Obj.'
column c5 heading 'Est|Time|Saved|(sec)'
column c6 heading 'Est|Parse|Saved|Factor'
column c7 heading 'Est|Object Hits' format 999,999,999
SELECT shared_pool_size_for_estimate c1,shared_pool_size_factor c2,
estd_lc_size c3,estd_lc_memory_objects c4,estd_lc_time_saved c5,
estd_lc_time_saved_factor c6,to_char(estd_lc_memory_object_hits,99999999999) c7 FROM V$SHARED_POOL_ADVICE;
set heading off
select ' 9、檢視shared pool中 各種型別的chunk的大小數量' from dual;
set heading on
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIzE"
FROM X$KSMSP GROUP BY KSMCHCLS;
set heading off
select ' 10、檢視使用shard_pool保留池情況' from dual;
set heading on
SELECT request_misses, request_failures, free_space
FROM v$shared_pool_reserved;
set heading off
select '11、 pga 建議' from dual;
set heading on
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'pga_aggregate_target') "Current Mb"
, ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"
, ROUND(estd_pga_cache_hit_percentage) "%"
FROM v$pga_target_advice
ORDER BY 2;
set heading off
select ' 12、檢視buffer cache 命中率' from dual;
set heading on
select 1-(sum(decode(name, 'physical reads', value, 0))/
(sum(decode(name, 'db block gets', value, 0))+
(sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio"
from v$sysstat;
set heading off
select ' 13、檢視buffer cache設定大小建議' from dual;
set heading on
select size_for_estimate, estd_physical_read_factor, to_char(estd_physical_reads,99999999999999999999999) as"estd_physical_reads" from v$db_cache_advice where name = 'DEFAULT';
set heading off
select '14、檢視buffer cache中defalut pool 命中率' from dual;
set heading on
select name,1-(physical_reads)/(consistent_gets+db_block_gets)
from v$buffer_pool_statistics;
set heading off
select '注：default池命中率至少要大於90%' from dual;
set heading off
select '15、檢查lgwr i/o效能' from dual;
set heading on
select total_waits,time_waited,average_wait,time_waited/total_waits as avg from v$system_event where event = 'log file parallel write';
set heading off
select '16、檢查與redo相關效能指標' from dual;
set heading on
set linesize 500
select name,value from v$sysstat where name like '%redo%';
set heading off
select ' 17、查詢redo block size' from dual;
set heading on
select max(lebsz) from x$kccle;
set heading off
select '18、 計算出每個事務平均處理多少個redo block' from dual;
set heading on
select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo blocks written') a ,(select value trancount from v$sysstat where name='user commits') b;
set heading off
select ' 19、 檢查undo rollback segment 使用情況' from dual;
set heading on
col name for a60
select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstat a,v$rollname b where a.usn=b.usn order by waits desc;
set heading off
select ' 20、計算每秒鐘產生的undoblk數量' from dual;
set heading on
select sum(undoblks)/sum((end_time-begin_time)*24*60*60) from v$undostat;
set heading off
select ' 21、查詢undo具體資訊' from dual;
set heading on
COL undob FORMAT 99990;
COL trans FORMAT 99990;
COL snapshot2old FORMAT 9999999990;
SELECT undoblks "UndoB", txncount "Trans"
,maxquerylen "LongestQuery", maxconcurrency "MaxConcurrency"
,ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSpaceWait"
FROM v$undostat;
set heading off
select ' 22、查詢rollback 段詳細資訊(收縮次數,擴充套件次數,平均活動事務等)' from dual;
set heading on
COL RBS FORMAT a50;
SELECT n.name "RBS", s.extends "Extends", s.shrinks
"Shrinks"
,s.wraps "Wraps", s.aveshrink "AveShrink"
,s.aveactive "AveActive"
FROM v$rollname n JOIN v$rollstat s USING(usn)
WHERE n.name != 'SYSTEM';
set heading off
select ' 23、查詢當前rollback segment使用情況' from dual;
set heading on
COL RBS FORMAT a50;
SELECT n.name "RBS", s.status, s.waits, s.gets, to_char(s.writes,'9999999999999')
,s.xacts "Active Trans"
FROM v$rollname n JOIN v$rollstat s USING(usn)
WHERE n.name != 'SYSTEM';
set heading off
select '24、查詢使用rollback segment時等待比率' from dual;
set heading on
SELECT ROUND(SUM(waits/gets)*100,2)||'%' "Contention" FROM
v$rollstat;
set heading off
select '25、查詢使用rollback segment時等待比率及其平局活動事務數' from dual;
set heading on
COL contention FORMAT 9999999990;
SELECT AVG(xacts) "Trans per RBS"
,ROUND(SUM(waits/gets)*100,2)||'%' "Contention"
FROM v$rollstat;
set heading off
select '五、檢查Oracle資料庫效能' from dual;
set heading off
select '1、檢查資料庫的等待事件' from dual;
set heading on
set pages 80
set lines 120
col event for a40
select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';
set heading off
select '2、檢視與redo相關等待事件' from dual;
set heading on
col event format a40
select event,total_waits,total_timeouts,average_wait from v$system_event where upper(event) like'%REDO%';
set heading off
select '3、檢視session redo event' from dual;
set heading on
select event,total_waits,total_timeouts,average_wait from v$session_event where upper(event) like'%REDO%';
set heading off
select '4、Disk Read最高的SQL語句的獲取' from dual;
set heading on
SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM<=5 order by SQL_TEXT desc
set heading off
select '5、查詢前十條效能差的sql' from dual;
set heading on
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10 ;
set heading off
select '6、等待時間最多的5個系統等待事件的獲取' from dual;
set heading on
SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
set heading off
select '7、檢查執行很久的SQL' from dual;
set heading on
COLUMN USERNAME FORMAT A12
COLUMN OPNAME FORMAT A16
COLUMN PROGRESS FORMAT A8
SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;
set heading off
select '9、檢查碎片程度高的表' from dual;
set heading on
SELECT segment_name table_name,COUNT(*) extents FROM dba_segments WHERE owner NOT IN
('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*))
FROM dba_segments GROUP BY segment_name);
set heading off
select '10、檢查死鎖及處理' from dual;
set heading on
col sid for 999999
col username for a10
col schemaname for a10
col osuser for a16
col machine for a16
col terminal for a20
col owner for a10
col object_name for a30
col object_type for a10
select sid,serial#,username,SCHEMANAME,osuser,MACHINE,
terminal,PROGRAM,owner,object_name,object_type,o.object_id
from dba_objects o,v$locked_object l,v$session s
where o.object_id=l.object_id and s.sid=l.session_id;
set heading off
select '11、檢視資料庫中行chain' from dual;
set heading on
SELECT 'Chained Rows ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'table fetch continued row')
/ (SELECT SUM(value) FROM V$SYSSTAT
WHERE name IN ('table scan rows gotten', 'table fetch by
rowid'))
* 100, 3)||'%' "Percentage"
FROM DUAL;
set heading off
select '12、查詢解析比率' from dual;
set heading on
SELECT 'Soft Parses ' "Ratio"
, ROUND(
((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)')
- (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)'))
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count')
* 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Hard Parses ' "Ratio"
, ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') * 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Parse Failures ' "Ratio"
, ROUND((SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'parse count (failures)')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)')* 100, 5)||'%' "Percentage" FROM DUAL;
set heading off
select '13、檢視與latch有關的event資訊' from dual;
set heading on
COL event FORMAT a20;
COL waits FORMAT 9999990;
COL timeouts FORMAT 99999990;
COL average FORMAT 99999990;
SELECT event "Event", time_waited "Total Time", total_waits
"Waits"
,average_wait "Average", total_timeouts "Timeouts"
FROM V$SYSTEM_EVENT
WHERE event = 'latch free'
ORDER BY EVENT;
set heading off
select '14、檢視大表小表掃描對應的值' from dual;
set heading on
SELECT value, name FROM V$SYSSTAT WHERE name IN
('table fetch by rowid', 'table scans (short tables)'
, 'table scans (long tables)');
SELECT 'Short to Long Full Table Scans' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'table scans (short tables)')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
('table scans (short tables)', 'table scans (long tables)'))
* 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Short Table Scans ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'table scans (short tables)')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))
* 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Long Table Scans ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'table scans (long tables)')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name
IN ('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))
* 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Table by Index ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch by rowid')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name
IN ('table scans (short tables)', 'table scans (long tables)'
, 'table fetch by rowid'))
* 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Efficient Table Access ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT WHERE name
IN ('table scans (short tables)','table fetch by rowid'))
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name
IN ('table scans (short tables)', 'table scans (long tables)'
, 'table fetch by rowid'))
* 100, 2)||'%' "Percentage"
FROM DUAL;
set heading off
select '15、index使用比率' from dual;
set heading on
col name for a30
SELECT to_char(value,'999999999999999999999'), name FROM V$SYSSTAT WHERE name IN
('table fetch by rowid', 'table scans (short tables)'
, 'table scans (long tables)')
OR name LIKE 'index fast full%' OR name = 'index fetch by
key';
SELECT 'Index to Table Ratio ' "Ratio" , ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name LIKE 'index fast full%'
OR name = 'index fetch by key'
OR name = 'table fetch by rowid')
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
('table scans (short tables)', 'table scans (long tables)')
),0)||':1' "Result"
FROM DUAL;
set heading off
select '16、等待class' from dual;
set heading on
col wait_class for a30
SELECT wait_class, COUNT(wait_class) FROM v$system_event
GROUP BY wait_class ORDER BY 1;
vi xunjian.sh
sqlplus / as sysdba <<eof
spool /scripts/check/xunjian.doc
@/scripts/check/xunjian.sql
spool off
EOF
</eof
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30345407/viewspace-1967850/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- （轉）ORACLE 巡檢指令碼Oracle指令碼
- 巡檢指令碼OS+Oracle指令碼Oracle
- oracle、filesystem、backup日常巡檢指令碼Oracle指令碼
- mysql巡檢指令碼MySql指令碼
- dba巡檢指令碼指令碼
- sqlserver 巡檢指令碼SQLServer指令碼
- SQLServer巡檢指令碼SQLServer指令碼
- Oracle運維指令碼-巡檢(RAC版)Oracle運維指令碼
- Oracle 10G RAC巡檢指令碼Oracle 10g指令碼
- （轉）：oracle、filesystem、backup日常巡檢指令碼Oracle指令碼
- SharePlex 基於Solaris 10 Linux自動巡檢指令碼Linux指令碼
- Oracle運維指令碼-巡檢(單機版)Oracle運維指令碼
- 【SCRIPT】Oracle日常巡檢指令碼通用版Oracle指令碼
- AIX巡檢指令碼(轉)AI指令碼
- 系統巡檢指令碼指令碼
- shell指令碼企業巡檢指令碼
- Oracle運維指令碼-巡檢(RAC版本)-V1.1Oracle運維指令碼
- mysql 伺服器巡檢指令碼MySql伺服器指令碼
- db2巡檢小指令碼DB2指令碼
- 【SCRIPT】Oracle12C日常巡檢指令碼通用版Oracle指令碼
- Linux 系統健康巡檢指令碼Linux指令碼
- specjvm自動化指令碼JVM指令碼
- docker指令碼自動化Docker指令碼
- 自動化RMAN指令碼指令碼
- shell指令碼實現多臺伺服器自動巡檢--可參考學習指令碼伺服器
- 「乾貨」介面自動化實踐：高效智慧介面場景自動巡檢方案
- Dockerfile---指令碼自動化Docker指令碼
- oracle巡檢（轉）Oracle
- oracle的巡檢Oracle
- windows下oracle自動啟動指令碼WindowsOracle指令碼
- Linux基礎服務巡檢指令碼模板Linux指令碼
- SCRIPT】Oracle巡檢報告html格式樣例指令碼，帶趨勢圖OracleHTML指令碼
- Oracle 資料庫巡檢指令碼 單例項 RAC 輸出HTML格式Oracle資料庫指令碼單例HTML
- 從零開始實現資料庫自動化巡檢(一)資料庫
- oracle自動冷備份指令碼Oracle指令碼
- ORACLE自動備份shell指令碼Oracle指令碼
- oracle報表自動FTP指令碼OracleFTP指令碼
- mydumper自動化安裝指令碼指令碼