rem 日常监测分析数据库的DBA_Monitor.sql程序
rem 我两年前的实际经验总结，以笔记奉献于众。
rem 主要参考《Oracle8 DBA Handbook》,《Oracle8 Tuning》。
rem 作者：丁聚岗 dingju@eastday.com
rem 参考 http://www.linuxeden.com/edu/doctext.php?docid=2754
set echo on
spool user_DBA_report.txt
set pages 333 lin 96
rem
---------------------------------------------------
rem
执行说明：本程序第一部分需要DBA权限,
rem
第二部分针对实际用户,它们拥有表,索引,Source等。
rem
# cat ding92sql
rem
sqlplus system/passwd@standax <<EOFa1
rem
@dba_monitor.sql
rem
connect standaxxx/passwd@standax
rem
@getuser_objects.sql
rem
EOFa1
rem
exit
rem 使用时服务器并不一定要设置为timed_statistics=true。
rem 《ORACLE数据库情况统计分析程序》, 2001.05创作,2003.08整理 Ding Jugang
rem 数据库性能调整包括三方面的内容:(硬件,软件,数据库)
rem 硬件，分别从CPU,Memory,Disk,NetWork提高；软件，就是应用程序的结构优化。
rem 数据库是我们DBA重点关心的，首先了解自己：
rem 表sys.DBA_tables : |名称|行数.变化|列数|Initail|Cache|TSpace|
rem 索引sys.DBA_indexes:|名称|表名|列数|类型|Initial|
rem 约束user_...:|名称|表名|相关性|类型|
rem 查询SQL的频率和效率v_$sqlarea,跟踪记录到sqls表中
rem 定期重建索引,做成脚本: index1rebld.sql, index2rebld.sql
rem 1、 参数dbwr_io_slave等三个从属进程可以分别设置为 40:12:6
rem 2、 参数process 一般是实际进程数据的1.5倍, 是为限制进程总数.
rem
process过大则占用系统资源，将降低系统的性能，可适当考虑降低该数值到600
rem 3、 当LOG FILE SWITCH时出现等待时，建议加大REDO LOG FILE,一般是30分钟
rem
一次切换。目前是128MB, 配合4MB LOG_BUFFER已经可以了(8M也不显效果).
rem 4、 在整个系统较繁忙时检测SHARED_POOL(一般情况下应该空余1/4)
rem 5、 SGA应该小于整个物理内存的一半，太大会导致OS内存换页出现(PI/PO)
rem 6、 MTS对于网站应用是理想选择，但在过分繁忙的客户端压力下，MTS会自动失败
rem
而重启专用进程(例如过多的PHP连接)，估计是应用类型不兼容。
rem 7、 命令instat,vmstat,top,w 能从OS级评估系统负荷。
rem
监测下面语句的执行结果的变化率，能得出数据库级的硬盘读写流量。
rem select count(FILE#),sum(PHYRDS),sum(PHYWRTS),sum(PHYBLKRD) from sys.V_$filestat;
rem================================================================
rem 创建SQL跟踪表SQLS
rem
create table SQLS as select * from sys.v_$sqlarea where disk_reads500;
rem当需要查询性能时，设置为跟踪模式，并执行下面的查询：
rem insert into sqls select * from sys.v_$sqlarea where disk_reads500 and executions<10;
rem 1)最高频率的SQL
rem
select disk_reads,executions,rows_processed,sql_text from SQLS
rem
where
executions 99 ;
rem 2)查询性能最差的SQL：
rem
select disk_reads,executions,rows_processed,first_load_time,sql_text from SQLS
rem
order by first_load_time;
select disk_reads,executions,rows_processed,first_load_time,sql_text
from sys.v_$sqlarea where disk_reads10 and executions <10
order by first_load_time;
rem ========================================================================
rem 567890123456789_1234567890123456789_1234567890123456789_1234567890123456
rem ==RowCache,LibraryCache
依赖于Shared_pool,参看sys.v_$sgastat ===
rem 此二者应当达到95%，实际系统已经达到99%
select (sum(pins - reloads)) / sum(pins) "lib cache" from sys.v_$librarycache;
select (sum(gets-getmisses-usage-fixed)) / sum(gets) from sys.v_$rowcache;
rem ==================== SGA ==============================================
rem ======= sys.v_$sgastat,SGA中详细说明 =====================
rem 剩余共享池: 保留Free Memory 大于25%
column name format A46
column value format 999999,999,999
select * from sys.v_$sgastat where rownum<5;
rem =================== SYS ===============================================
rem ========= sys.v_$SYSstat 详细列表,下面是几个指标的算法=======
rem 数据缓冲命中率：1- 40#/(39#+38#) = 99.8% 95%
rem 内存排序成功率：1- 162#/(161#) = 99.4% 92%
rem 脏缓冲区平均长度(oracle8i已废除之): 41#/42#=0.06<db_block_size/4
rem 应用效率：全表扫描<1%, 140#long/(139#short+140#long)
column class format 99999
column value format 999999,999,999
select * from sys.v_$sysstat
where STATISTIC# in (38,39,40,41,42,43,139,140,141,106,161,162,163);
select name, value from v$parameter
where name in ('db_block_buffers',
'db_block_size', 'shared_pool_size','sort_area_size');
rem DETERMINE IF THE DATA BLOCK BUFFERS IS SET HIGH ENOUGH
select 1-(sum(decode(name, 'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0)) +
sum(decode(name, 'consistent gets', value,0))))
"Read Hit Ratio" from v$sysstat;
rem 日志缓存要满足空间请求极小,每日300次,还可更小：
rem LOG_BUFFER=4MB,还可再大。
rem select * from sys.v_$sysstat where name like 'redo%';
rem
rem ==================回滚段1==============================================
rem 回滚段有效率：waits/gets<1%
即: 无等待命中率NoWait_Hit_Ratio接近于1
rem
回滚段数据量在4---100个,同样规格大小,尽可能稳定不变。
rem alter TABLESPACE RBS DEFAULT STORAGE
rem
(INITIAL 1M NEXT 1M MINEXTENTS 8 PCTINCREASE 0);
rem CREATE PUBLIC ROLLBACK SEGMENT RB21 TABLESPACE RBS;
rem ALTER ROLLBACK SEGMENT RB21 storage( minextents 4 optimal 8M);
rem CREATE PUBLIC ROLLBACK SEGMENT RB55 TABLESPACE RBS2 storage(minextents 8 optimal 8M);
column Ratios? format 99.9999
select count(*), sum(waits)/sum(gets) from sys.v_$rollstat;
rem rssize=最优保留值optimal,shrinks是动态收缩次数,每小时2次是允许的。
rem 例如，统计信息：rssize=8M,extents=8, waits和shrinks 小于每天2次
select usn,extents,gets,writes,rssize,waits,shrinks
from sys.v_$rollstat;
rem =======****** 回滚段2 ****===========
rem
这里是回滚段的汇总统计,分析其扩展段之定义。其命中率统计见v_$roolstat
rem dba_rollback_segs 下面验证一致性。
select owner,initial_extent,NEXT_EXTENT,Min_EXTENTS,MAX_EXTENTS,count(*),
TABLESPACE_NAME,STATUS
from dba_rollback_segs
group by owner,initial_extent,NEXT_EXTENT,min_extents,
MAX_EXTENTS,TABLESPACE_NAME,STATUS;
rem dba_rollback_segs -- detail 定义
column SEGMENT_name format a6
column TABLESPACE_NAME format a12
select SEGMENT_name,owner,initial_extent,NEXT_EXTENT,Min_EXTENTS,MAX_EXTENTS,
TABLESPACE_NAME,STATUS
from dba_rollback_segs;
rem ======================== 闩 ===========================================
rem 闩-Oracle内部锁，无等待命中率NoWait_Hit_Ratio接近于1
column name format A30
select
name, immediate_gets "Imme_gets",
immediate_misses "Imme_Mis",
round(immediate_gets/(immediate_gets+immediate_misses),3) "nowait_hit_ratio"
from
sys.v_$latch
where immediate_gets+immediate_misses != 0
order by name;
rem 闩-Oracle内部锁，命中率HITRATIO(即misses/gets)接近于0
rem
sleeps, immediate_gets "Imme_gets", 为了易于阅览,不要换行
column Mis/Get? format 99.999;
select
name, gets, misses, misses/gets "Mis/Get?",
immediate_misses "Imme_Mis"
from
sys.v_$latch
where gets 0
order by name;
rem ================== 系统等待 ===========================================
rem
系统等待累计次
