orale 10 RAC升级失败，升级后性能超慢

macrossplus

alter file 和awr report贴出来瞧瞧
操作系统有无异常？
syslog贴出来

Yong Huang

原帖由 lifewise 于 2009-7-14 22:21 发表
没有triggle，基本确定问题在
28365 10:53:13 shmat(425985, 0x60000000, 0) = ?
28365 10:53:15 getcwd("/home/oracle/10.2.0.4/db/dbs"..., 256) = 29

谢谢棉花的帮忙

通过应用的小测试程序，慢应该在连接的地方，去数据库取一个小表，升级前30ms，升级后3000ms

So attaching to the shared memory segment is slow. What's your plan to improve it? Decrease SGA? Search on Metalink?

Yong Huang

lifewise

更新一下，供大家参考，找了oracle support，将pre_page_sga修改成false就可以了。

Yong Huang

原帖由 lifewise 于 2009-8-13 01:51 发表
更新一下，供大家参考，找了oracle support，将pre_page_sga修改成false就可以了。

Thanks for the feedback. I think you're using Linux. Is it Red Hat Linux? What release is it?

Metalink Note:361468.1 says "HugePages already makes the SGA pages non-swappable, so those parameters are not needed to have SGA pages unswappable. But those parameters still can provide additional performance advantages for the internal mechanism of Oracle Database memory management."

I strongly suggests you configure HugePages if you haven't done so. It boosts overall database performance and shortens the time to create a new server process (when a client connects to the database).

The reason I ask about your exact Red Hat Linux release is that RHEL 5.0 and 5.1 has problems with HugePages, especially when pre_page_sga is true. See Note:550443.1.

Yong Huang

ixpubber

原帖由 Yong Huang 于 2009-8-13 22:33 发表


Thanks for the feedback. I think you're using Linux. Is it Red Hat Linux? What release is it?

Metalink Note:361468.1 says "HugePages already makes the SGA pages non-swappable, so those parameters are not needed to have SGA pages unswappable. But those parameters still can provide additional performance advantages for the internal mechanism of Oracle Database memory management."

I strongly suggests you configure HugePages if you haven't done so. It boosts overall database performance and shortens the time to create a new server process (when a client connects to the database).

The reason I ask about your exact Red Hat Linux release is that RHEL 5.0 and 5.1 has problems with HugePages, especially when pre_page_sga is true. See Note:550443.1.

Yong Huang

为什么会这样，不应该啊，只是一起都把内存读进去了

ixpubber

原帖由 lifewise 于 2009-8-13 15:51 发表
更新一下，供大家参考，找了oracle support，将pre_page_sga修改成false就可以了。

看看你的换入换出

Yong Huang

原帖由 ixpubber 于 2009-8-13 10:17 发表

为什么会这样，不应该啊，只是一起都把内存读进去了

You mean why when pre_page_sga is true, making a new connection is slow? The article explains it. Each connection requires a new server process to be created on the server. When pre_page_sga is true, each new process needs to allocate all memory pages rather than simply allocate the minimum needed and allocate more as it needs more later on. Does that make sense? I think you can check pga_used_mem and pga_alloc_mem in v$process when you play with pre_page_sga. Pga_alloc_mem is for memory reservation and pga_used_mem is what I call memory allocation, i.e. really physically allocated. (Confusing names, aren't they?)

Yong Huang

棉花糖ONE

这参数设置成true不关启动数据库的时候会去touch sga ,而且建立连接的时候也会去touch sga

PRE_PAGE_SGA can increase the process startup duration, because every process that starts must access every page in the SGA. The cost of this strategy is fixed; however, you might simply determine that 20,000 pages must be touched every time a process starts. This approach can be useful with some applications, but not with all applications. Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off.

The advantage that PRE_PAGE_SGA can afford depends on page size. For example, if the SGA is 80 MB in size and the page size is 4 KB, then 20,000 pages must be touched to refresh the SGA (80,000/4 = 20,000).

If the system permits you to set a 4 MB page size, then only 20 pages must be touched to refresh the SGA (80,000/4,000 = 20). The page size is operating system-specific and generally cannot be changed. Some operating systems, however, have a special implementation for shared memory whereby you can change the page size.

棉花糖ONE

我想不通的是为什么9i里这个参数为true没影响,可能是bug的原因导致pre_sga_page没起作用

Yong Huang

原帖由 棉花糖ONE 于 2009-8-14 20:10 发表
我想不通的是为什么9i里这个参数为true没影响,可能是bug的原因导致pre_sga_page没起作用

Ahh, I see. I don't have a 9i database on Linux. All I have is 10g or 11g. If you or anybody has 9i on Linux, do a test to find out. As I said, the two PGA columns v$process right after a new connection is made may be able to tell whether memory is fully allocated up front.

Yong Huang