Shulou(Shulou.com)06/01 Report--

Best Practices and Recommendations for RAC DB SGA Over 100g

Version description:

The information in this document applies to any platform

Oracle Database Enterprise Edition 11.2.0.3 and above

Purpose:

The purpose of this article is to provide best practices and recommendations for users to set very large SGA (e.g. 100GB) in each instance of RAC (note that rac uses SGAs of uniform size throughout the cluster). This document was compiled and maintained based on Oracle's experience with RAC customers worldwide.

This document is not intended to replace the Oracle Documentation Set, but rather to supplement it. Reading, understanding, and referencing Oracle documentation is required to provide answers to any questions not explicitly addressed in this description.

All recommendations should be carefully reviewed by your own operations team and implemented only after an assessment of potential benefits and associated risks. Risk assessment can only be done with a detailed understanding of systems, applications, and business environments.

Because each customer environment is unique, the success of any oracle database implementation, including oracle rac implementation, depends on a successful test environment. oracle support has identified 100 gb as a large baseline that would benefit from the recommendations provided in this note. However, this is only a baseline and small SGAs (smaller ones) also benefit from these recommendations. Therefore, any recommendations in this note must be thoroughly tested and validated using a test environment, which is a copy of the production environment prior to implementation in the production environment, to ensure that there is no negative impact with the recommendations made.

Range:

This article applies to all new and existing RAC implementations.

Applicable to RAC databases only, as most of the parameters listed here are for RAC databases only.

Description:

Note that the recommendations presented in this note are the result of experience with databases with SGA of 1TB and 2.6 TB.

Also, databases with SGA of 100GB and 300GB benefit from these recommendations

Initial parameters:

a. Set parameter_lm_sync_timeout to 1200

This parameter is set to prevent refactoring and DRM timeout. It is a static parameter and supports rolling restart.

b. Set parameter_ksmg_granule_size to 134217728

Setting this reduces the time required to find resources for data blocks. It is a static parameter that supports rolling restart.

c. Set shared_pool_size to 15% or more of the total SGA size

For example, if the SGA size is 1TB, the shared pool size should be at least 150GB. This is a dynamic parameter.

d. Set parameter_gc_policy_minimum to 15000

If DRM is disabled by setting _gc_policy_time=0, this parameter is not required._ gc_policy_minimum is a dynamic parameter, and_gc_policy_time is a static parameter. Rolling restart is not supported. Disable DRM. This parameter can be set dynamically instead of_gc_policy_time,_lm_drm_disable.

e. Set parameter_lm_tickets to 5000

The default value is 1000, assigning more tickets (user-sent messages) to avoid problems during reconfiguration. It is a static parameter that supports rolling restart. When this parameter is increased, rolling restart is supported, but when it is decreased, cold start is required.

f. Set the parameter gcs_server_processes to twice the default value of the assigned lms process.

The default number of LMS processes depends on the number of CPUs (kernels) the server has. Therefore, refer to gcs_server_processes in the Initialization Parameters section of the Oracle Database Reference Guide. To confirm the default number of lms processes for the server, make sure that the total number of lms processes for all databases on the server is less than the total number of CPUs (kernels) on the server. Refer to document 558185.1, which is a static parameter that supports rolling restart.

Attachment: gcs_server_processes Default algorithm (refer to Oracle11gR2 Reference Guide)

If the number of CPUs is 1-3, the default value is 1

If the number of CPUs is 4-15, the default value is 2

If the number of CPUs is greater than 16, the algorithm is 2+(CPUs/32), and if the result includes scores, ignore. For example, CPUs of 20, then 2+(20/32), equals 2 GCS processes. If CLUSTER_DATABASE is equal to false, the value is 0. If ASM, the value is 1. Parameter range 1-36.

Multiple instances of Oracle RAC can be set to different values.

Recommended patches:

11.2.0.3.5 DB PSU or later, recommended to resolve issues with known large SGA sizes.

SGA is about 4TB on Linux systems, with the following bugs

BUG 18780342 - LINUX SUPPORT FOR > 4TB SGA

MOS: Best Practices and Recommendations for RAC databases with SGA size over 100GB (Doc ID 1619155.1)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.