PRE_PAGE_SGA Behaviour Change in Oracle Release 12c
(Doc ID 1987975.1)
Last updated on JUNE 08, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A 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.
Details
Previously, 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:
In this test, few users try to connect sequentially to the same database when PRE_PAGE_SGA is set to TRUE and FALSE. In another test, there are 10 concurrent session doing 100,000 logons onto the test machine (AMD x86-64 GNU/Linux 2.6.9 + 8GB RAM on Intel(R) Core(TM)2 Duo CPU E8400 @ 3.00GHz) which demonstrates almost the same outcomes and conclusions:
AWR screenshot from 12c with PRE_PAGE_SGA = False =>
AWR screenshot from 12c with PRE_PAGE_SGA = True =>
AWR screenshot from 11.2.0.4 with PRE_PAGE_SGA = False =>
AWR screenshot from 11.2.0.4 with PRE_PAGE_SGA = True =>
AWR screenshot from 10.2.0.5 with PRE_PAGE_SGA = False =>
AWR screenshot from 10.2.0.5 with PRE_PAGE_SGA = True =>
For test results details and analysis please see the excel sheet PRE_PAGE_SGA Behaviour Change in Oracle Release 12c.
This leaves the preference for PRE_PAGE_SGA to be set to TRUE in 12c for the gains it provides by design:
Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration
Database Reference
https://docs.oracle.com/database/121/REFRN/GUID-48C49E38-BE48-46FB-8654-493DE6D9987B.htm#REFRN10174
Ideally customers would be able to test the impact on their own using a tool like real application testing:
Actions
It is advised to leave PRE_PAGE_SGA to default value of TRUE in 12c.
Contacts
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Details |
| Actions |
| Contacts |
| References |