oracle 數據庫性能健康檢查腳本

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

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

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

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

vmstat 3600 6

***********************查看基本信息
#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_hh34: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中缺少連續內存,可以加減少_SHARED_POOL_RESERVED_MIN_ALLOC多放入一些對象,減少shared pool壓力,適當加大shared_pool_size,SHARED_POOL_RESERVED_SIZE

#從library中獲取 執行次數最多的top 10 sql 查看他們的具體信息 (未含解析次數)
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