PRE_PAGE_SGA Behaviour Change in Oracle Release 12c

郑全

In this Document

Details
Actions
Contacts
References

---

Applies to:   Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.
The default value for the parameter PRE_PAGE_SGA has changed in 12c to TRUE. It used to be FALSE in prior releases.
DetailsPreviously, it is known that PRE_PAGE_SGA can increase the process start-up time because every process that starts must access all pages in the SGA. 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.
In 12c, this has been improved, the time the process takes to connect to the database is almost the same when PRE_PAGE_SGA is set to TRUE and FALSE.  This is because the design for SGA prepaging was changed in 12.1, and it helps in preventing foreground processes to take the hit of allocating and locking a large page into physical memory. Limiting the pre-paging activity to just the first process that allocates a granule to the SGA has the biggest impact. The other (minor) contributors are making pre-paging NUMA aware and taking the actual memory page size into account (e.g. huge pages) instead of reading a byte in every fixed 2K increment to trigger a page in operation.
Testing both settings for PRE_PAGE_SGA (i.e. TRUE vs FALSE) in 12c does not reveal any major difference compared to the difference in pre 12c releases. The test results are shared below:

登录/注册后可看大图