Oracle shared_pool_reserved_size Parameter Setting description

Enter Sqlplus, query Shared_pool, often see the results as follows:

Sql> Show Parameter Shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Shared_pool_reserved_size Big Integer 20971520
Shared_pool_size Big Integer 419430400

In Oracle, Shared_pool contains two parts, one of which is: Shared_pool_reserved_size. This parameter is used to specify the reserved shared pool space size that is used to meet future large contiguous shared pool space requests. When there is too much fragmentation in the shared pool, requesting large chunks of space can result in a wide range of Oracle lookups and freeing of shared pool memory to satisfy requests, which can lead to more severe performance degradation by setting the appropriate shared_pool_reserved_size parameters, combined with shared_ The Pool_reserved_min_alloc parameter can be used to avoid the resulting performance degradation.

The ideal value for this parameter should be large enough to satisfy any memory request to the reserved list, without requiring the database to refresh the object from the shared pool. The default value of this parameter is Shared_pool_size 5%, usually the recommended value for this parameter is the Shared_pool_size parameter's 10%~20% size, the maximum must not exceed shared_pool_size 50%. (more than 50% may cause the database to fail to start, pay attention!) ）

RAC System Modification Operations (the following modifications are recommended on a database that does not have an SGA enabled, this modification is not recommended for enabling SGA)

1. Modify the first node:

Alter system set shared_pool_size=5000m sid= ' Rac1 ' scope=spfile;

Alter system set shared_pool_reserved_size=500m sid= ' Rac1 ' scope=spfile;

Shutdown immediate

Startup

2. When the first node is started, modify the 2nd node:

Alter system set shared_pool_size=5000m sid= ' Rac2 ' scope=spfile;

Alter system set shared_pool_reserved_size=500m sid= ' Rac2 ' scope=spfile;

Shutdown immediate

Startup

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://williamyang.blog.51cto.com/3953960/1410045

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch