功能:
1. 數據庫的基本信息顯示(建庫時間,模式等)
2. 數據庫結構信息-database map(control file, redo log file, datafile, rollback segment)
3. 數據庫所有狀態信息
4. 數據庫級的db block buffer hit ratio
5. session級的db block buffer hit ratio
6. 前一天的log switch情況
7. redo log 空間需求查詢
8. log buffer 性能查詢
9. log file switch性能查詢
10. 檢查不完全的checkpoint
11. library cache 的 性能查詢
12. dictionary cache的 性能查詢
13. 查找最資源的sql語句
14. session級的cpu使用度
15. sort_area_size的性能查詢
16. sequence_cache_entries的性能查詢
17. chained row的查詢
18. rollback segment contention 檢查
19. 表空間碎片檢查
20. ｌatch contention 檢查
21. tablespace 用量檢查
22. 數據文件ｉ／ｏ檢查
23. 表和索引的碎片檢查
24. 表的ｈｗｍ檢查
使用方法舉例:
目前，作為一個dba，可以有很多工具來管理，維護和癥查數據庫．這只是我平時搜集的一些腳本，可以作為dba隨身攜帶的小工具程序
在沒有其他可視化的工具時，它可以用來對數據庫做一些基本的診斷．
１．打開sqlplus , connect system
２．@a:/check_db.sql (a:/是本文件的路徑)
３．執行完畢，結果儲存在c:/local.txt
４．local.txt 不但有每個數據的說明，同時介紹一些解決相關問題的方法以供參考
check_db.sql的內容：
set echo off
spool c:/local.txt
ttitle off
break on today
column today noprint new_value xdate
select substr(to_char(sysdate,'fmmonth dd, yyyy hh:mi:ss p.m.'),1,35) today
from dual
/
column name noprint new_value xdbname
select name from v$database
/
set heading on
set feedback off
set linesize 250
set pagesize 200
rem ######################################################################################
rem **** check_db_v2.1: performance tuning****
rem ######################################################################################
prompt *******************************************************************************
prompt database check information
prompt *******************************************************************************
ttitle left "database: "xdbname" (as of: "xdate")" skip 2
select name , created , log_mode from v$database
/
prompt
prompt *******************************************************************************
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ item ++++++++++++++++++++++++++++++++++
prompt + 0.0 database map +
prompt ++++++++++++++++++++++++++++++++++ item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** database map - control file ***"
column "control file" format a40
select status , name "control file" from v$controlfile
/
ttitle off
ttitle left "*** database map - redolog file ***"
column "log file" format a40
select f.member "log file",l.group# ,l.thread# ,l.bytes ,l.status from v$log l, v$logfile f
where l.group# = f.group#
/
ttitle off
ttitle left "*** database map - data file ***"
column file_name format a40
select file_name ,tablespace_name,bytes,blocks,status from dba_data_files order by tablespace_name , bytes desc
/
ttitle off
ttitle left "*** database map - rollback seg ***"
select n.name "rollback seg name" , r.extents , r.rssize , r.optsize , hwmsize ,status
from v$rollstat r,v$rollname n
where r.usn = n.usn
/
ttitle off
prompt ++++++++++++++++++++++++++++++++++ item ++++++++++++++++++++++++++++++++++
prompt + 1.0 database statistic +
prompt ++++++++++++++++++++++++++++++++++ item ++++++++++++++++++++++++++++++++++
set heading on
set termout on
ttitle left "*** database: "xdbname", database statistic(as of : "xdate" ) ***" skip 1
column "statistic name" format a55
column value format 9,999,999,999,999,990
select n.statistic# , n.name "statistic name", s.value
from v$statname n , v$sysstat s
where n.statistic# = s.statistic#
and value > 0
order by value desc
/
ttitle off
prompt ++++++++++++++++++++++++++++++++++ item ++++++++++++++++++++++++++++++++++
prompt + 2.0 db block buffer - hit ratio (database wise) +
prompt ++++++++++++++++++++++++++++++++++ item ++++++++++++++++++++++++++++++++++
set heading on
set termout on
column "physical reads" format 9,999,999,999,999
column "consistent gets" format 9,999,999,999,999
column "db block gets" format 9,999,999,999,999
column "hit ratio" format 999.99
ttitle left "*** database: "xdbname", db block buffers hit ratio (as of : "xdate" ) ***" skip 1-
left "percent = ((100*(1-(physical reads/(consistent gets + db block gets))))" skip 2
select pr.value "physical reads",
cg.value "consistent gets",
bg.value "db block gets",
round((1-(pr.value/(bg.value+cg.value)))*100,2) "hit ratio"
from v$sysstat pr, v$sysstat bg, v$sysstat cg
where pr.name = 'physical reads'
and bg.name = 'db block gets'
and cg.name = 'consistent gets'
/
prompt <<<<<<<<<<<<<<<<<<< note: >>>>>>>>>>>>>>>>>>>>>>
prompt 2.0 investigation if percent is less than 70% , increase db_block_buffers
prompt <<<<<<<<<<<<<<<<<<< note: >>>>>>>>>>>>>>>>>>>>>>
ttitle off
prompt
prompt
prompt ++++++++++++++++++++++++++++++++++ item ++++++++++++++++++++++++++++++++++
prompt + 2.1 db block buffer - hit ratio (session wise) +
prompt ++++++++++++++++++++++++++++++++++ item ++++++++++++++++++++++++++++++++++
clear breaks
clear computes
break on report
compute sum of consistent_gets on report
compute sum of block_gets on report
compute sum of physical_reads on report
column "hit ratio %" format 999.99
column username format a10
ttitle left "*** database: "xdbname", hit ratio for user sessions(as of : "xdate" ) ***" skip 1
select username,
osuser,
consistent_gets,
block_gets,
physical_reads,
100*( consistent_gets + block_gets - physical_reads)/
( consistent_gets + block_gets ) "hit ratio %"
from v$session,v$sess_io
where v$session.sid = v$sess_io.sid
and ( consistent_gets + block_gets )>0
and username is not null
order by username,"hit ratio %";
prompt
prompt <<<<<<<<<<<<<<<<<<< note: >>>>>>>>>>>>>>>>>>>>>>
prompt 2.0/2.1 investigation
prompt if you have 20 or more users and batch users cause less than 50%
prompt logical reads within your database , you should aim for a hit ratio
prompt of between 94% ~ 97%.
prompt if you have fewer than 20 users , the sharing of data among users depends
prompt heavily on the application , so you should aim for a hit ratio in the 89%~94%
prompt <<<<<<<<<<<<<<<<<<< note: >>>>>>>>>>>>>>>>>>>>>>
prompt
prompt
