日常监测分析数据库的DBA_Monitor.sql程序

　　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_reads>500;
　　
　　rem当需要查询性能时，设置为跟踪模式，并执行下面的查询：
　　rem insert into sqls select * from sys.v_$sqlarea where disk_reads>500 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_reads>10 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　系统等待累计次数和累计时间
　　column class format A30
　　 select * from sys.v_$waitstat ;
　　　
　　column event format A28
　　 select * from sys.v_$system_event order by TOTAL_WAITS;
　　　
　　rem =======================================================================
　　rem ================ v_$session_wait ======================================
　　rem　会话正在等待的累计时间seconds
　　select event,sum(SECONDS_IN_WAIT),count(*)　
　　　from sys.v_$session_wait group by event ;
　　
　　rem rdbms ipc message　　　　　　　　　　3648　　　　　6
　　rem slave wait　　　　　　　　　　　　　31501　　　　 41 太大?
　　rem =======================================================================
　　rem　会话等待的累计次数和累计时间
　　column event format A32
　　 select event,type, sum(total_waits) "Waits",
　　　　　sum(time_waited) "SumTime",count(*) "Count"
　　　 from sys.v_$session s, sys.v_$session_event e
　　　 where　 s.sid = e.sid
　　　 group by event,type
　　　 order by type ;
　　rem =======================================================================
　　rem　这里是日志文件和控制文件的状态, 可用OS命令 ls -l 细看.
　　column member format A60
　　 select * from sys.v_$logfile;
　　　
　　column name format A60
　　 select * from sys.v_$controlfile;
　　rem 下面方法可以看出数据库的表空间创建方法。文件名要用单引号。
　　select 'create tablespace ',f.tablespace_name,
　　' datafile ' ||f.FILE_NAME||' SIZE '||BYTES/1024/1024||'M REUSE',
　　' DEFAULT STORAGE (INITIAL '||INITIAL_EXTENT/1024||'K NEXT '||NEXT_EXTENT/1024
　　||'K MaxEXTENTS '||Max_EXTENTS||' MinEXTENTS '||Min_EXTENTS||' PCTINCREASE '||PCT_INCREASE ||');'
　　from sys.dba_data_files f,dba_tablespaces ts where f.tablespace_name=ts.tablespace_name
　　order by f.FILE_id;
　　
　　rem 创建表的语句，可由exp/imp导入时指定indexfile='生成，或由DBA-Studio,
　　rem 及第三方SQL-Explorer,PowerBuilder:DB-Paint得到。
　　
　　rem =======================================================================
　　 set pages 333 lin 100
　　 column FSFI format 999.999
　　 column Percent format 999.9999
　　 column file_name format A60
　　 column tablespace_name format A15
　　　
　　rem　这里是数据库文件的状态, 可用OS命令 ls -l /oracle/h?/*细看.
　　rem DBF I/O List;
　　rem select FILE_ID, BYTES,blocks,maxbytes, autoextensible,
　　rem　 TABLESPACE_NAME,file_name
　　rem　 from sys.dba_data_files order by file_name;
　　　
　　rem =======数据库文件:读写频率、空闲率、碎片=============================
　　
　　rem =====　 数据库文件: 读写频率 ======
　　
　　rem　这里是数据库文件的I/O统计状态, DATA > RBS > indexes, 但是,
　　rem　除此之外的Redolog则有更大的流量.(可用OS命令 iostat 5 5)
　　rem Tablespace DBF I/O weight;
　　select　 FILE#, PHYRDS, PHYWRTS, PHYBLKRD, PHYBLKWRT,
　　 PHYBLKRD + PHYBLKWRT TotalIO,
　　 ( PHYBLKRD + PHYBLKWRT )/MaxIO Percent
　　 from sys.V_$filestat,
　　　(select max(PHYBLKRD + PHYBLKWRT) MaxIO from sys.V_$filestat) ;
　　rem　下面的方法可以
　　rem select sum(PHYRDS),sum(PHYWRTS),sum(PHYBLKRD) from sys.V_$filestat;
　　rem 由于用户要关心的是自己的详细数据的存放位置，下面分别得出index,tables
　　rem select tablespace_name,table_name,cache,initial_extent/1024 "Init"
　　rem　　from user_tables　order by tablespace_name,table_name;
　　rem select tablespace_name,table_name,index_name,initial_extent/1024 "Init"
　　rem　　from user_indexes　order by tablespace_name,table_name,index_name;
　　
　　rem ======= 数据库文件:空闲率 =======
　　
　　rem　这里是数据库文件的利用率统计状态, DATA,indexes,RBS 大于25%
　　rem Tablespace Free/Used/Total %
　　column %Free format 99.999 ;
　　select b.file_id "File#",　 substr(b.tablespace_name,1,12) "TableSpace",
　　　b.bytes/1024 "kBytes", (b.bytes- sum(nvl(a.bytes,0)))/1024 "kb-Used",
　　 sum(nvl(a.bytes,0))/1024 "kb-Free",(sum(nvl(a.bytes,0))/(b.bytes))*100 "%Free"
　　　from sys.dba_free_space　a, sys.dba_data_files b
　　 where a.file_id(+) = b.file_id
　　 group by b.tablespace_name,b.file_id,b.bytes
　　 order　by b.tablespace_name;
　　　
　　rem =======数据库文件: 碎片　=======
　　
　　rem　这里是数据库文件的碎片统计状态,可用Alter tablespace xxx coalesce;整理
　　rem FSFI free segment frage index
　　Alter tablespace temp coalesce;
　　Alter tablespace system coalesce;
　　Alter tablespace RBS2 coalesce;
　　Alter tablespace indexes coalesce;
　　Alter tablespace indexes2 coalesce;
　　Alter tablespace data coalesce;
　　
　　select tablespace_name,
　　Sqrt(max(blocks)/ sum(blocks)) * (100/Sqrt(Sqrt(count(blocks)))) FSFI,
　　　 count(blocks),sum(blocks),max(blocks)
　　　 from sys.dba_free_space group by tablespace_name;
　　　
　　rem =======================================================================
　　rem　这里是表空间的结构,类似回滚段的参数
　　
　　select initial_extent,NEXT_EXTENT,Min_EXTENTS,MAX_EXTENTS,pct_increase,
　　　TABLESPACE_NAME,contents
　　 from dba_tablespaces;
　　　
　　rem ===========================
　　rem　这里是表空间中碎片的又一指标,返回结果不精练,不常用.
　　rem　Segments where extents>9
　　rem　select　tablespace_name, segment_type, Extents,bytes,blocks , owner
　　rem　from sys.dba_segments where extents>9 order by tablespace_name;
　　　
　　rem ========================用户对象======================================
　　rem　这里是表空间中group的对象(表,索引),索引可调整或重建
　　rem Objects in every Tablespace
　　select tablespace_name,owner,count(*)||' Tables' Objects
　　　 from sys.DBA_tables　by Tablespace_name,owner
　　 union　select tablespace_name,owner,count(*)||' Index' Objects
　　　 from sys.DBA_indexes group by Tablespace_name,owner;
　　rem select * from sys.DBA_indexes where owner='xxx' AND table_NAME LIKE 'ICQ%';
　　rem user_tables,user_indexes,all_users;
　　
　　
　　rem ==========================安全=========================================
　　rem　这里是数据库授权安全的几个指标(用户,权限,资源),可调整
　　rem select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from sys.dba_users;
　　rem select * from sys.DBA_TS_QUOTAS;
　　rem select * from sys.dba_role_privs;
　　rem alter user cartoonxx default tablespace data temporary　tablespace temp;
　　　
　　rem ===================非缺省的参数========================================
　　rem　这里是数据库参数中自定义的几个参数指标,可调整init_sid.ora
　　Rem The init.ora parameters currently in effect:
　　 column name format a34
　　 column　value format a45
　　 rem column　isdefault format a6
　　select name, value from sys.v_$parameter
　　 where isdefault = 'FALSE'　 order by isdefault,name;
　　　
　　rem ========================MTS============================================
　　rem　以下是MTS 多线程服务器的几个性能指标,
　　rem　参数可查: (关键是mts_dispathers),
　　rem $ lsnrctl services
　　rem select name, value,isdefault from sys.v_$parameter
　　rem　where name like 'mts_%'　order by isdefault,name DESC;
　　　
　　select　count(*) "Count",　sum(idle)/(sum(busy)+sum(idle))*100 "Free%" ,
　　　substr(network,1,23) "Network Dispatcher" from sys.v_$dispatcher
　　　group by substr(network,1,23);
　　　
　　select　count(*) "Count",sum(wait), sum(totalq),　
　　　ROUND(sum(wait)/(sum(totalq) + 0.01),2) ||' %Sec'
　　　"Avg Response Queue Wait=1s",
　　 substr(network,1,23) "Net Dispatcher"
　　　from　sys.v_$queue a, sys.v_$dispatcher b
　　　where a.type ='DISPATCHER' AND A.PADDR=B.PADDR
　　　group by substr(network,1,23);
　　　
　　select totalq, TYPE, ROUND(wait/(totalq+0.01),2) "%Sec Avg Response Wait=1s"
　　　from　sys.v_$queue ;
　　　
　　rem //SELECT *　FROM　 sys.V_$SHARED_SERVER;
　　select　count(*) "Count", status, sum(idle)/(sum(busy)+sum(idle))*100 "Free%"
　　from sys.v_$shared_server　group by status;
　　rem SELECT COUNT(*),STATUS FROM　 sys.V_$SHARED_SERVER GROUP BY STATUS;
　　SELECT count(*), sum(MESSAGES),sum(BYTES),sum(BREAKS) FROM SYS.V_$CIRCUIT;
　　　
　　rem alter system set mts_dispatchers='TCP,8';
　　rem alter system set mts_servers=24;
　　　
　　rem ============================================================
　　rem　=LARGE_pool_size Optional-Para for MTS=　
　　rem select sum(value) from sys.v_$sesstat a , sys.v_$statname b
　　rem　 where a.statistic# =b.statistic# and name='session uga memory';
　　rem select sum(value) from sys.v_$sesstat a , sys.v_$statname b
　　rem　 where a.statistic# =b.statistic# and name='session uga memory max';
　　rem select * from sys.v_$sgastat where pool='shared pool' and name='free memory';
　　rem xxx select &pumem , &numusers , &sql,(&pumem * &numusers + &sql) from　dual;
　　rem　
　　　　　　　　
　　rem ========= 磁盘优化建议 ======================================
　　rem 建立独立多个独立物理硬盘分区,分别存放[系统/软件/数据1234/备份]等等,
　　rem　 减少磁盘碎片,易于重建系统,保证资源优先级.
　　rem 建立文件符号连接(快捷方式),易于快速查看和备份文件,例如: ls -l /oracle/h1/*
　　rem ========== SQL 跟踪选项示例 =========================================
　　rem 设置跟踪选项SQL_TRACE=TRUE,Timed_statictics=true,Max_dump_file_size=1280*512
　　rem 在user_dump_dest中记录SID_ora_PID.trc,再用tkprof解释和排序
　　rem 可以合并*1.trc为一个大文件,
　　rem $ cat *1.trc > MD_SID1.trc
　　rem 从中统计全表扫描的表,建议用Excel打开,可以"="为分隔符,处理列,统计行数(导入DB整理).
　　rem 从中找出全表扫描的语句
　　rem $ cat *1.trc|grep FULL > fullMD_SID1.txt
　　rem 从中找出select语句
　　rem $ cat *1.trc|grep select > selectMD_SID1.txt
　　rem 从中找出对某表（master）的访问
　　rem $ cat *1.trc|grep master > masterMD_SID1.txt
　　rem $ cat *1.trc|grep MASTER > MASTERMD_SID1.txt
　　rem 可以合并*1.trc,
　　rem $ tkprof MD_SID1.trc MD_SID1.txt explain=dbuser/logpasswd
　　rem　　　　　 =============== End of File ==================

原文转自：http://www.ltesting.net