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的绝对值设置也不得低于1G~1.5G。所以在二三线城市的派综系统的数据库服务器上，物理内存至少要2G是必须的。
关于文件系统和裸设备：
操作系统对于文件系统，使用了大量的BUFFER来缓存操作系统块，当数据库获取数据块的时候，虽然SGA中没有命中，但是实际可能从操作系统的文件缓存中获取的。而假如操作系统和数据库支持异步I/O，则实际上数据库写进程DBWR写磁盘时，操作系统在文件缓存中标记该块为延迟写，等真正写入磁盘之后，操作系统才通知进程DBWR写磁盘完成。对于这部分文件的缓存，所需要的内存可能比较大，作为保守的估计，我们应该考虑在物理内存的0.2~0.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_size
4801M
Share_pool_size
512M
Large_pool_size
112M
Java_pool_size
16M
Db_cache_size
3008M
Pga_aggregate_target
150M
process
600
sessions
700
Open_links
10
Open_links_per_instance
10
Open_cursors
1024
Job_queue_processes
10
深圳数据库设置
系统参数
值
Sga_max_size
8194M
Share_pool_size
800M
Large_pool_size
112M
Java_pool_size
208M
Db_cache_size
5808M
Log_buffer
1M
Pga_aggregate_target
400M
process
800
sessions
885
Open_links
4
Open_links_per_instance
4
Open_cursors
1204
Job_queue_processes
10

2.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中看到当前的会话数量（包括活动的<ACTIVE>和不活动的<INACTIVE>以及等待的<SNIPED>），只要行数不超过参数的设定值即可。该视图的主键为SID+SERIAL#，或者SID，即会话ID+会话的序列号或者会话ID。SERIAL#：当一个会话结束，另一个会话开始并使用了同一个SID，SERIAL#加1，但是当前是没有SID重复的。
2.9 最大进程数的设置
设置数据库参数时，要设置process,意义为：数据库例程可以打开的最大进程数量，我们可以在视图v$process中看到当前打开的进程数量，只要行数不超过参数的设定值即可。
一个进程可以负载多个会话。
2.10 关于命中率的计算和参数的调整
2.10.1 数据缓冲区的命中率
查找变量值：select * from v$sysstat
Physical reads
8
Physical reads direct
8
Physical reads direct(lob)
8
Consistent gets
8
Db block gets
8
令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$librarycache
Hit redio
100.561783012245
假如这个值低于95%就要考虑调整应用（改写多为变量绑定）或者增加内存。
2.10.3 排序
查找变量值：select name,value from v$sysstat where name like '%sort%'
sorts (memory)
8664874
sorts (disk)
5171
sorts (rows)
4956821883
sorts (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 entries
244903571
redo buffer allocation retries
5897
redo 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 数据库的死锁造成编译无效或者超时
见文档《关于派综系统中的数据库编译和死锁问题解决》，戴黎鸣编写的。
5 查看ORACLE报警日志找原因
报警日志的目录位置：oracle/admin/实例名/bdump
文件名称：alert_实例名.log
取该文件的方法：
Ø 在运行中cmd：ftp 数据库服务器的IP；
Ø prompt off
Ø bin /转化为二进制文件
Ø cd oracle/admin/实例名/bdump
Ø get alert_实例名.log
Ø 该文件在初始CMD的目录下；
6 查找是否有死锁的问题
《锁机制》