oracle数据库监控要点.doc

数据库监控要点 作者：刘 靖在派综系统的大型数据库运行的过程中，尤其生产库经常发生系统性能的问题，在实际的操作中，积累如下的要点：1 表空间的管理问题1.1 表空间满解决办法：给表空间增加数据文件；2 ORACLE数据库基本参数的设置问题2.1 SGA的设置SGA的意思是共享全局区，在ORACLE中参数全称为sga_max_size，这个参数的设置在大型数据库系统中需要综合规划考虑。一个OS要预留200M以内的内存空间（不包含大量进程的PCB），应用的中间层（Weblogic）大约需要1G的内存空间，其他软件的运行也需要内存空间，也要考虑。在一般的数据库服务器系统上，如果只有数据库软件的运行，应用服务器中间层如果被分离出来，那么完全可以将SGA分配的内存空间加大在物理内存中的比例，在实际情况中一般把SGA划分为物理内存的45%65%（根据实际情况而定）。但是根据系统设计和数据容量的要求，SGA有个最小的限度，这就要求客户方的系统硬件性能达到这个标准。在派综系统实际经验中，如深圳、广州、佛山、东莞（该市还未正式启用系统）的数据生产库日增量和并发进程、会话的量很大，这就势必要求数据库物理内存和CPU性能参数比较高，与此同时SGA的参数绝对值设置也应该高于其他地市；例如在中山、江门、珠海、云浮、惠州、肇庆等二三线城市的数据生产库日增数据量和并发进程、会话的量相对较小，可以减少SGA的分配比例，但是SGA的绝对值设置也不得低于1G1.5G。所以在二三线城市的派综系统的数据库服务器上，物理内存至少要2G是必须的。关于文件系统和裸设备：操作系统对于文件系统，使用了大量的BUFFER来缓存操作系统块，当数据库获取数据块的时候，虽然SGA中没有命中，但是实际可能从操作系统的文件缓存中获取的。而假如操作系统和数据库支持异步I/O，则实际上数据库写进程DBWR写磁盘时，操作系统在文件缓存中标记该块为延迟写，等真正写入磁盘之后，操作系统才通知进程DBWR写磁盘完成。对于这部分文件的缓存，所需要的内存可能比较大，作为保守的估计，我们应该考虑在物理内存的0.20.3倍的大小。如果用的是裸设备，则不考虑这部分缓存的问题，这样SGA就有调大的机会。举实际的例子加以说明。2007年6月15日，在广州市公安局户政处的重点人口系统中，数据库从一个刀片服务器移植到IBM小型机上，该机的物理内存为8G，OS为IBM-AIX（裸设备），该库上有3个数据库用户：重点对象系统测试库（培训库）、重点对象系统正式库、门楼牌系统的正式库。我们将SGA的参数值设为4.8G。2.2 共享池的参数设置该参数的全称为：share_pool_size，在一个使用绑定变量的比较大的系统中，这个参数要设置在300M以内。除非系统使用了大量的过程、函数、包，可能这个值会达到500M甚至更高。一般来讲，1G的物理内存的系统，这个值设置为100M，2G的设为150M，8G的设置为200M到300M。不建议超过800M。以上所述为理想状态的设置。派综系统由于使用了很多非变量绑定的SQL。所以这个参数设置一定要加大，加到SGA的10%。广州市公安局户政处的重点人口系统数据库参数设置表：OS:AIX；物理内存：8G；IBM小型机 系统参数值Sga_max_size4801MShare_pool_size512MLarge_pool_size112MJava_pool_size16MDb_cache_size3008MPga_aggregate_target150Mprocess600sessions700Open_links10Open_links_per_instance10Open_cursors1024Job_queue_processes10深圳数据库设置系统参数值Sga_max_size8194MShare_pool_size800MLarge_pool_size112MJava_pool_size208MDb_cache_size5808MLog_buffer1MPga_aggregate_target400Mprocess800sessions885Open_links4Open_links_per_instance4Open_cursors1204Job_queue_processes102.3 大型池的设置（Large_pool_size）一般20-30M就足够了。但是派综系统考虑到使用大量数据的导入导出，要做RMAN系统备份，所以这个参数设置都保证在100M。2.4 JAVA池的设置（Java_pool_size）10-20M足够。（派综没有在数据库上用JAVA）2.5 LOG BUFFER设置1-3M。2.6 Db_cache_size一般设置在SGA的50%60%，不要将SGA加满，如果db_cache_size使用到最大值，系统会自动使用SGA中多余的部分。2.7 打开最大游标数的设置设置数据库参数时，要设置open_cursors,意义为：每个会话可以打开的游标数量，如果设为1024表示每个会话可以打开1024个游标，在视图v$open_cursors中，我们可以直接看到所有打开的游标记录，但是还可以看到每个会话打开的游标数量：select sql_text,count(*) from v$open_cursor group by sql_text将sql_text分组显示就可以看到每个会话打开的游标数，将这个数字与参数open_cursors比较就可以看到是否超出了游标打开数最大值的限制。2.8 最大会话数的设置设置数据库参数时，要设置sessions,意义为：数据库例程可以打开的最大会话数量，我们可以在视图v$session中看到当前的会话数量（包括活动的和不活动的以及等待的），只要行数不超过参数的设定值即可。该视图的主键为SID+SERIAL#，或者SID，即会话ID+会话的序列号或者会话ID。SERIAL#：当一个会话结束，另一个会话开始并使用了同一个SID，SERIAL#加1，但是当前是没有SID重复的。2.9 最大进程数的设置设置数据库参数时，要设置process,意义为：数据库例程可以打开的最大进程数量，我们可以在视图v$process中看到当前打开的进程数量，只要行数不超过参数的设定值即可。一个进程可以负载多个会话。2.10 关于命中率的计算和参数的调整2.10.1 数据缓冲区的命中率查找变量值：select * from v$sysstatPhysical reads8Physical reads direct8Physical reads direct(lob)8Consistent gets8Db block gets8令x = Physical reads direct * Physical reads direct(lob)命中率 = 100-（Physical reads - x）/( Consistent gets+ Db block gets-x)*100本例：x=8+8=16 命中率=100-（8-16）/（0）=100%如果命中率低于90%则要考虑增大Db_cache_size的值。2.10.2 共享池的命中率查找变量值：select sum(pinhits)/sum(pins)*100 hit radio from v$librarycacheHit redio100.561783012245假如这个值低于95%就要考虑调整应用（改写多为变量绑定）或者增加内存。2.10.3 排序查找变量值：select name,value from v$sysstat where name like %sort%sorts (memory)8664874sorts (disk)5171sorts (rows)4956821883sorts (disk)/（sorts (memory)+ sorts (rows)）=5171/（8664874+4956821883）=5171/4957688370=1.04；如果这部分比例过高，这就意味着sort_area_size部分内存较小，则要调整相应的参数。2.10.4 Log_buffer查找变量值：select name,value from v$sysstat where name in (redo entries,redo buffer allocation retries)redo entries244903571redo buffer allocation retries5897redo buffer allocation retries/ redo entries=2.4*e(-5)如果这个值超过1%，我们就要考虑提高log_buffer.3 性能低下的SQL语句问题这种情况需要找到这些严重影响数据库性能的SQL。ORACLE提供给我们一个视图：v$session_wait，这个视图描述了系统等待的会话积累，字段event描述了等待的会话事件名称，常见的情况有：事件名称引发的性能问题db file sequential read系统I/O吞吐量偏高direct path read write系统I/O吞吐量偏高Buffer busy wait系统I/O吞吐量偏高Latch free系统CPU吞吐量偏高注：系统的CPU占用不应长期在70%90%以上，瞬间偏高属于正常情况，但是长期偏高，数据库性能一定有问题，I/O吞吐量应在30%以下，如果长期在30%以上也是性能有问题的表现。视图v$session，v$session_wait通过与视图v$sql物理地址字段关联，找到event相关的sql,分析该SQL的性能：是否全表扫描，是否生成了笛卡尔积，所引用的字段是否有索引等等。SQL语句见代码3.1：找到事件之后，在该语句上可以找到该事件对应会话的SID，然后根据该会话的SID找到该会话对应的进程号PID。（见代码3.2）先PS ef|grep SPID确认是用户进程，不是oracle的系统进程；确认是用户进程，然后执行kill -9 SPID。4 数据库的死锁造成编译无效或者超时见文档关于派