oracle 进程突现大量换页导致小型机重启

Yong Huang

> 我们这里是运行客户端的一个报表引起的，一月初将系统从原来的ibm3655rac集群（32位Linux32位oracle10.2.0.4）
> 迁移到ibmp550小机rac集群上，该报表就引起了这样的问题。这样的问题怎么解决，

Are you saying this problem didn't happen before and happened after the migration, and the client side reporting program remained the same?

There's no magic here. Just check the program. Make sure it does not do unnecessary big sorting and hashing, and no in-memory array that is too big to fit in memory. As DBA, you also can check the trace file to see the SQL that caused the problem. Watch the session when the reporting program runs. Monitor pga_alloc_mem column of v$process or v$sesstat for 'session pga memory' statistic.

Yong Huang

hxre

迁移后导致宕机的关键应该是，原来是32位linux，只能用1.7G内存，即使出现问题也不会完全使用系统内存，各个节点之间通讯是正常的，rac不会踢出这个节点，最多在数据库中报ora-4030；但在aix上， memory(kbytes)       unlimited，直接导致系统失去响应，从而使另一个节点把该节点踢掉

panzhkn

原32位Linux下，sga为1.7g，物理内存为8g；aix下物理内存为16g，sga设为8g，aix内存使用确实是unlimited。发生时，系统确实失去响应。oracle进程会去占用系统内存吗？除了修改报表sql语句外，还有什么办法吗？望指教！

Yong Huang

hxre's message #12 is good. The AIX box is 64-bit. Correct? "memory(kbytes) unlimited" probably refers to the fact that the OS and Oracle software is 64-bit so there's practically no limit in memory usage. But this is not shell limit, as seen in `ulimit -m' command output, because by default it should be unlimited on Linux as well.

> oracle进程会去占用系统内存吗?

Of course. Why not?

> 除了修改报表sql语句外，还有什么办法吗？

If you don't, you must add more physical memory to the box or reduce SGA.

There're times an Oracle session should not use that much PGA and it does. It could be due to a bug. Work with Oracle. A similar case on my mind is:
http://www.freelists.org/post/or ... -AGGREGATE-TARGET,3

Yong Huang

棉花糖ONE

建议你先看看为什么内存会突然增大很多,是不是有什么特殊的操作