年底了,要對負責的進行,效能健康檢查,特此記錄.

*******************os層檢查

#檢查程式
ps -ef|grep ora|wc -l

crs_stat -t -v
top
glance
crsctl check cluster (cssd)

vmstat 3600 6

[@more@]

***********************檢視基本資訊
#database 概況資訊檢查
#檢查database基本資訊
select * from v$version;
select name ,open_mode,log_mode from v$database;
select instance_number,instance_name ,status from gv$instance;
show parameter cpu_c
show parameter block_size
select group#,thread#,members,bytes/1024/1024 from gv$log;
show sga
select count(*) from v$controlfile
select count(*) from v$tempfile;
select count(*) from v$datafile;

********************************檢視資料檔案資訊
#檢查表空間資料檔案資訊
col tablespace_name for a30
select tablespace_name , sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name;
#檢查表空間
SELECT TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES;

#檢查資料檔案狀態
select count(*),status from v$datafile group by status;

#檢查表空間使用情況
select
f.tablespace_name,
a.total,
f.free,(a.total-f.free)/1024 "used SIZE(G)"
,round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free"
/

#查詢臨時segment使用情況
COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT username, segtype, extents "Extents Allocated"
,blocks "Blocks Allocated"
FROM v$tempseg_usage;

#檢視資料檔案資訊,若檔案較多可以根據需要欄位進行排序輸出top 10
SELECT fs.phyrds "Reads", fs.phywrts "Writes"
,fs.avgiotim "Average I/O Time", df.name "Datafile"
FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#;

#檢視所有資料檔案i/o情況,若檔案太多,可以改寫為top 10 select *( order by xx desc) where rownum<=10
COL ts FORMAT a10 HEADING "Tablespace";
COL reads FORMAT 999990;
COL writes FORMAT 999990;
COL br FORMAT 999990 HEADING "BlksRead";
COL bw FORMAT 999990 HEADING "BlksWrite";
COL rtime FORMAT 999990;
COL wtime FORMAT 999990;
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;

#檢視所有資料檔案i/o情況(比率),若檔案太多,可以改寫為top 10 select *( order by xx desc) where rownum<=10
COL ts FORMAT a10 HEADING "Tablespace";
COL reads FORMAT 999990;
COL writes FORMAT 999990;
COL br FORMAT 999990 HEADING "BlksRead";
COL bw FORMAT 999990 HEADING "BlksWrite";
COL rtime FORMAT 999990;
COL wtime FORMAT 999990;
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;

#獲取top 10 熱segment
col objct_name for a30
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

#判斷物理讀最多的object
select * from (select owner,object_name,value from v$segment_statistics where statistic_name='physical reads' order by value desc) where rownum<=10

#檢視熱點資料檔案(從單塊讀取時間判斷)

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

******************************檢視redo
#檢查日誌切換頻率
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;

#檢查lgwr i/o效能 (time_waited/total_waits：表示平均lgwr寫入完成時間若>1表示寫入過慢)
select total_waits,time_waited,average_wait,time_waited/total_waits as avg from v$system_event where event = 'log file parallel write';

#檢查與redo相關效能指標
select name,value from v$sysstat where name like '%redo%';

#查詢redo block size
select max(lebsz) from x$kccle;

#檢視redo allocation latch
col name for a30
select name,gets,misses,misses/gets from v$latch_children where name='redo allocation';

#檢視與redo相關等待事件
col event format a40
select event,total_waits,total_timeouts,average_wait from v$system_event where upper(event) like'%REDO%';

#檢視session redo event
select event,total_waits,total_timeouts,average_wait from v$session_event where upper(event) like'%REDO%';

#檢視user commit次數
select to_number(value,99999999999) from v$sysstat where name='user commits';

#檢視系統執行時間
select (sysdate - startup_time)*24*60*60 as seconds from v$instance

#計算出每秒使用者提交次數
select user_commit次數/系統執行時間 from dual;

#計算出每個事務平均處理多少個redo block
select value from v$sysstat where name = 'redo blocks written';
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
'

***************************sga,pga,命中率
# sga,pga,命中率
#檢查sga
show sga
select * from v$sga;

#檢視buffer cache 命中率
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;

#檢視buffer cache建議
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';

COL pool FORMAT a10;
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'db_cache_size') "Current Cache(Mb)"
,name "Pool", size_for_estimate "Projected Cache(Mb)"
,ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%"
FROM v$db_cache_advice
WHERE block_size = (SELECT value FROM v$parameter
WHERE name = 'db_block_size')
ORDER BY 3;

#檢視pga
show parameter pga

#檢視cache 池
show parameter cache

#檢視buffer cache中defalut pool 命中率
select name,1-(physical_reads)/(consistent_gets+db_block_gets)
from v$buffer_pool_statistics;

#檢查shared pool

show parameter shared

#檢查shared pool中library cache

select namespace,pinhitratio from v$librarycache;

#檢查整體命中率(library cache)
select sum(pinhits)/sum(pins) from v$librarycache;

select sum(pins) "hits",
sum(reloads) "misses",
sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"
from v$librarycache;

#檢查shered pool free space
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';

#檢查row cache(資料字典緩衝區)命中率
#當執行一個dml或ddl都會造成對資料字典的遞迴修改
column updates format 999,999,999
SELECT parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter;

SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;

#檢視Shared pool latch(多池技術)

col parameter for a20
col session for a20
select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm = '_kghdsidx_count';

#每個子shared pool由單獨的shared pool latch保護檢視他們的命中率
shared pool latch，用於shared pool空間回收分配使用的latch

col name format a15
select addr,name,gets,misses,1-misses/gets from v$latch_children where name='shared pool';

#檢視shared pool建議

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;

#使用 v$shared_pool_advice 算不同shared pool大小情況下，響應時間，S單位

SELECT 'Shared Pool' component,
shared_pool_size_for_estimate estd_sp_size,
estd_lc_time_saved_factor parse_time_factor,
CASE
WHEN current_parse_time_elapsed_s + adjustment_s < 0 THEN
0
ELSE
current_parse_time_elapsed_s + adjustment_s
END response_time
FROM (SELECT shared_pool_size_for_estimate,
shared_pool_size_factor,
estd_lc_time_saved_factor,
a.estd_lc_time_saved,
e.VALUE / 100 current_parse_time_elapsed_s,
c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s
FROM v$shared_pool_advice a,
(SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e,
(SELECT estd_lc_time_saved FROM v$shared_pool_advice
WHERE shared_pool_size_factor = 1) c)
/

#檢視shared pool中各種型別的chunk的大小數量

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;

#檢視是否有庫緩衝有關的等待事件
select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like 'library%';

#row cache命中率
SELECT 'Dictionary Cache Hit Ratio ' "Ratio"
,ROUND((1 - (SUM(GETMISSES) / SUM(GETS))) * 100,2)||'%'
"Percentage"
FROM V$ROWCACHE;

#library cache中詳細比率資訊
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;

#查詢sga中各個pool 情況
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;

SELECT * FROM V$SGAINFO;

#檢視使用shard_pool保留池情況
SELECT request_misses, request_failures, free_space
FROM v$shared_pool_reserved;

#Oracle專門從共享池內建出一塊區域來來分配記憶體保持這些大塊。這個保留共享池的預設大小是共享池的5%(_shared_pool_reserved_pct 5 控制) oracle建設定為10%。大小透過引數SHARED_POOL_RESERVED_SIZE改。它是從共享池中分配，不是直接從SGA中分配的，它是共享池的保留部分，專門用於儲存大塊段
#shared pool中記憶體大於_SHARED_POOL_RESERVED_MIN_ALLOC 將放入shared pool保留池,保留池維護一個單獨的freelist,lru，並且不會在lru列表存recreatable型別chunks，普通shared pool的釋放與shared pool保留池無關。
#關於設定SHARED_POOL_RESERVED_SIZE
#1.如果系統出現ora-04031,發現請求記憶體都是大於 _SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) ,且v$shared_pool_reserved中有大量 REQUEST_MISSES(並且可以看下 LAST_MISS_SIZE ) 表示 SHARED_POOL_RESERVED_SIZE太小了需要大的記憶體的請求失敗,那麼需要加大 SHARED_POOL_RESERVED_SIZE
#2.如果ora-04031請求記憶體出現在4100-4400並造成shared pool lru合併,老化換出記憶體 ,可以調小 _SHARED_POOL_RESERVED_MIN_ALLOC 讓此部分記憶體進入 shared reserved pool,相應的加大SHARED_POOL_RESERVED_SIZE
#3.從v$shared_pool_reserved來判斷, 如果REQUEST_FAILURES>0(出現過ora-04031)且LAST_FAILURE_SIZE(最後請求記憶體大小)>_SHARED_POOL_RESERVED_MIN_ALLOC 表示shared reserved pool 缺少連續記憶體,可以加大SHARED_POOL_RESERVED_SIZE,減少 _SHARED_POOL_RESERVED_MIN_ALLOC 少放物件,並相對加大shared_pool_size
#要是反過來 REQUEST_FAILURES>0(出現過ora-04031)且LAST_FAILURE_SIZE(最後請求記憶體大小)<_shared_pool_reserved_min_alloc shared="" pool="">

#從library中獲取執行次數最多的top 10 檢視他們的具體資訊 (未含解析次數)
COL execs FORMAT 9999990
COL parses FORMAT 99990
COL fetches FORMAT 999990
COL loads FORMAT 9990
COL invalids FORMAT 9999990
COL i/o FORMAT 99990
COL hits FORMAT 9999990
COL rows FORMAT 999990
COL sorts FORMAT 9990
COL sql FORMAT a32
SELECT * FROM(
SELECT executions "Execs"--Executions
,parse_calls "Parses"--Parses
,fetches "Fetches"--Fetches
,loads"Loads"--Loads and reloads
,invalidations "Invalids"--Invalidations
,disk_reads "I/O"--I/O
,buffer_gets "Hits"--Buffer hits
,rows_processed "Rows"--Rows
,sorts"Sorts"--Sorts
,sql_text "SQL"
FROM v$sqlarea
ORDER BY executions DESC
) WHERE ROWNUM <= 10;

#查詢還保留在library cache中，解析次數和執行次數最多的sql(解析*執行)
COL sql_text FORMAT A38;
SELECT * FROM(
SELECT parse_calls*executions "Product", parse_calls
"Parses"
,executions "Execs", sql_text FROM v$sqlarea ORDER BY 1 DESC)
WHERE ROWNUM <= 10;

#檢視shared pool中,row cache詳細資訊
SELECT COUNT "Entries", GETS "Requests", GETMISSES "Misses"
,MODIFICATIONS "DML Activity", parameter "Area"
FROM v$rowcache;

#DBMS_SHARED_POOL.[UN]KEEP (object, type);

#檢視shared pool建議
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'shared_pool_size') "Current Mb"
, shared_pool_size_for_estimate "Projected Mb"
, ROUND(shared_pool_size_factor*100) "%"
, ESTD_LC_SIZE "Library Mb"
, ESTD_LC_TIME_SAVED "Parse Savings"
,to_char(ESTD_LC_MEMORY_OBJECT_HITS,'999999999999') "Hits"
FROM v$shared_pool_advice
ORDER BY 1;

#檢視library cache 記憶體分配情況(對哪類物件)
SELECT lc_namespace "Library"
,LC_INUSE_MEMORY_OBJECTS "Objects"
,LC_INUSE_MEMORY_SIZE "Objects Mb"
,LC_FREEABLE_MEMORY_OBJECTS "Freeable Objects"
,LC_FREEABLE_MEMORY_SIZE "Freeable Mb"
FROM v$library_cache_memory;

#檢視pga
show parameters area_size
# 檢視pga
SELECT * FROM v$pgastat;

檢視pga建議
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;

#Database read buffer cache hit ratio =
#1 – (physical reads / (db block gets + consistent gets))

SELECT to_char(value,'9999999999999'), name FROM V$SYSSTAT WHERE name IN
('physical reads', 'db block gets', 'consistent gets');

#查詢資料庫命中率
SELECT 'Database Buffer Cache Hit Ratio ' "Ratio"
, ROUND((1-
((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'physical reads')
/ ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block gets')
+ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'consistent gets')
))) * 100)||'%' "Percentage"
FROM DUAL;

# alter table xx cache

#檢視cache中所有pool,命中情況
COL pool FORMAT a10;
SELECT a.name "Pool", a.physical_reads, a.db_block_gets
, a.consistent_gets
,(SELECT ROUND((1-(physical_reads / (db_block_gets + consistent_gets)))*100)
FROM v$buffer_pool_statistics
WHERE db_block_gets+consistent_gets != 0
AND name = a.name) "Ratio"
FROM v$buffer_pool_statistics a;

#ALTER TABLE(INDEX) xx STORAGE(BUFFER_POOL KEEP);

# 檢視資料庫cache或keep了哪些object
COL table_name FORMAT A16
COL index_name FORMAT A16
SELECT table_name AS "Table", NULL, buffer_pool, cache FROM
user_tables
WHERE buffer_pool != 'DEFAULT' OR TRIM(cache)='Y'
UNION
SELECT table_name, index_name, NULL, buffer_pool FROM
user_indexes
WHERE buffer_pool != 'DEFAULT'
ORDER BY 1, 2 NULLS FIRST;

#取消cache或keep(keep pool)
#ALTER TABLE XX NOCACHE;
#SELECT 'ALTER INDEX '||index_name||' STORAGE(BUFFER_POOL DEFAULT);'
#FROM USER_INDEXES WHERE BUFFER_POOL!='DEFAULT';

**********************************************

#檢查undo
show parameter undo_
#檢查undo rollback segment 使用情況
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;

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;

#計算每秒鐘產生的undoblk數量
select sum(undoblks)/sum((end_time-begin_time)*24*60*60) from v$undostat;

#Undospace=UR*UPS*blocksize + overload(10%),計算undo tablespace大小
show parameter block_size
show parameter undo_retention
#select undo_retention*每秒產生undoblk數量*block_size/1024/1024/1024+ (1+1undo_retention*每秒產生undoblk數量*block_size/1024/1024/1024*0.1) from dual;

#查詢undo具體資訊

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;

#查詢rollback 段詳細資訊(收縮次數,擴充套件次數,平均活動事務等)
COL RBS FORMAT a4;
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';

#查詢當前rollback segment使用情況
COL RBS FORMAT a4;
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';

#查詢使用rollback segment時等待比率
SELECT ROUND(SUM(waits/gets)*100,2)||'%' "Contention" FROM
v$rollstat;

#查詢使用rollback segment時等待比率及其平局活動事務數
COL contention FORMAT 9999999990;
SELECT AVG(xacts) "Trans per RBS"
,ROUND(SUM(waits/gets)*100,2)||'%' "Contention"
FROM v$rollstat;

******************************** 檢視其它效能指標資訊(等待事件,latch等,排序,解析,index使用率)
#檢視資料庫中行chain
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;

#在記憶體中排序比率(最優排序)
SELECT 'Sorts in Memory ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts (memory)')
/ (SELECT SUM(value) FROM V$SYSSTAT
WHERE name IN ('sorts (memory)', 'sorts (disk)')) * 100, 5)
||'%' "Percentage"
FROM DUAL;

#查詢解析比率
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;

#檢視與latch有關的event資訊
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;

#檢視資料庫中查詢時主要訪問方式,獲取大表小表訪問比率(2個表的訪問演算法不同)
#table scans (long tables)過多的話,一般db file scattered read比較顯著
#_small_table_threshold來定義大表和小表的界限。預設為2%的Buffer數量 ,>這個引數為大表
#default 大表的全表掃描會被置於LRU的末端(最近最少使用,冷端)，以期儘快老化(讓其儘快換出buffer cache)，減少Buffer的佔用

#檢視大表小表掃描對應的值
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;

#index使用比率

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

#等待class
col wait_class for a30
SELECT wait_class, COUNT(wait_class) FROM v$system_event
GROUP BY wait_class ORDER BY 1;

##下面將查詢到不含idle event的,其他重要event,但需要建立一個過濾函式
##CREATE OR REPLACE FUNCTION IdleEvent(pEvent IN VARCHAR2
##DEFAULT NULL)
##RETURN VARCHAR2 IS
##CURSOR cIdleEvents IS
##SELECT name FROM v$event_name
##WHERE wait_class = 'Idle'
##OR name LIKE '%null%' OR name LIKE '%timer%'
##OR name LIKE '%SQL*Net%' OR name LIKE '%rdbms ipc%'
##OR name LIKE '%ispatcher%' OR name LIKE '%virtual
##circuit%'
##OR name LIKE '%PX%' OR name LIKE '%pipe%'
##OR name LIKE '%message%' OR name LIKE 'jobq%'
##OR name LIKE 'Streams%';
##BEGIN
##FOR rIdleEvent in cIdleEvents LOOP
##IF pEvent = rIdleEvent.name THEN
##RETURN NULL;
##END IF;
##END LOOP;
##RETURN pEvent;
##EXCEPTION WHEN OTHERS THEN
##DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
##END;
##/
##ALTER FUNCTION IdleEvent COMPILE;
##/
##
##查詢重要event
##COL event FORMAT a30;
##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 IdleEvent(event) IS NOT NULL
##ORDER BY event;
##
##每個等待event所佔整體比例
##COL percentage FORMAT 9999999990;
##SELECT event "Event", total_waits "Waits", time_waited "Total
##Time"
##,TO_CHAR(
##(time_waited /
##(SELECT SUM(time_waited) FROM v$system_event
##WHERE IdleEvent(event) IS NOT NULL)
##)*100, 990.99) "Percentage"
##FROM v$system_event WHERE IdleEvent(event) IS NOT NULL ORDER
##BY event;

#檢查系統中當前等待事件
col event for a30
select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';

#檢查資料庫中無效物件
SELECT owner, object_type,count(object_name) FROM dba_objects WHERE status= 'INVALID' group by owner,object_type;

#檢查是否有禁用約束
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P'

#檢查是否有禁用trigger
col owner for a10
col taigger_name for a10
cok table_name for a30
col table_name for a30
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';

****************生成awr
cd $ORACLE_hOME/rdbms/admin

SQL> @awrrpt.sql

oracle 資料庫效能健康檢查指令碼[轉帖]

相關文章