Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use sga and pga memory in oracle12c to control pdb. I hope you will get something after reading this article. Let's discuss it together.

Memory Management using Resource Manager

Oracle Database Explorer (Resource Manager) can now manage memory usage between pluggable databases (PDBs) in a multi-tenant container database (CDB). This feature helps maintain the performance of all PDBs in CDB, ensuring that all PDBs does not consume more resources, resulting in resource contraction on other PDBs.

Memory usage of PDBs can be controlled only if the following conditions are met:

1. In the CDB root, the noncdb_compatible initialization parameter is set to false.

2. The MEMORY_TARGET initialization parameter is not set, or it is set to 0 (0) in the CDB root.

In 12.2, Resource Manager allows to:

1. Limit the memory usage of a specific PDB.

2. Specify the amount of memory guaranteed for a particular PDB.

3. Specify the maximum amount of memory that a particular PDB can use.

Along with other options such as:

Specify that different PDBs should receive different shares of system resources in order to allocate more resources to the more important PDBs.

Restrict CPU usage for a specific PDB.

Limit the number of parallel execution servers that a particular PDB can use.

Use PDB performance profiles for different PDB (see 2171135.1 for more information)

Restrict the use of resources for different sessions connected to a single PDB.

Limit the Istroke O generated by a particular PDBs.

Monitor the resource usage of PDBs.

1. Managing SGA for PDBs:

The SGA requirements for various PDBs in the container database will be different. If there is no mechanism to control the use of SGA, active PDB can consume most of the SGA space, resulting in resource constraints to other PDBs, thus affecting their performance.

1. From 12cR2, we can control the maximum SGA that PDB can use in the container database, and the minimum SGA that needs to be assigned to PDB.

The SGA_TARGET parameter can be used to limit the maximum SGA size of PDB. The SGA_TARGET setting in PDB must be less than or equal to the SGA_TARGET setting in the CDB root.

The SGA_TARGET and SGA_MIN_SIZE settings in CDB are enforced only if the SGA_TARGET initialization parameter is set to a non-zero value in the PDB root.

Alter session set container=PDB1

ALTER SYSTEM SET SGA_TARGET = 1500m SCOPE = BOTH

ALTER SYSTEM SET SGA_MIN_SIZE = 500m SCOPE = BOTH

two。 You can use the SGA_MIN_SIZE parameter to specify the minimum SGA size of the PDB.

The SGA_MIN_SIZE parameter ensures that the SGA of the PDB is never lower than the specified value.

The guidelines for setting the SGA_MIN_SIZE (minimum guaranteed SGA) parameter are:

It must be less than or equal to 50% of the CDB _ TARGET setting in the SGA root.

It must be less than or equal to 50% of the SGA_TARGET setting in PDB.

The sum of the SGA_MIN_SIZE settings for all PDBs must be less than or equal to 50% of the SGA _ TARGET setting in the CDB root.

Note: it is a best practice to limit the sum of the SGA_MIN_ size values of all PDBs to 50% or less of the SGA size of CDB.

II. Managing PGA for PDBs

To control the PGA usage of PDB, you can set the parameters PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT at the PDB level.

The PGA_AGGREGATE_TARGET setting is a target. Therefore, the Oracle database attempts to limit PGA memory usage to the target, but can be used more than the set number of times. To specify a hard limit on PGA memory usage, you can use the PGA_AGGREGATE_LIMIT initialization parameter. The Oracle database ensures that the PGA size does not exceed this limit. If the database exceeds the limit, the database aborts the call to the session with the highest tunable PGA memory allocation.

The PGA_AGGREGATE_TARGET parameter sets the target aggregate PGA size of the PDB.

The guidelines for setting up PGA_AGGREGATE_TARGET are:

It must be less than or equal to the PGA_AGGREGATE_ target value set at the CDB level.

It must be less than or equal to 50% of the value of the PGA_AGGREGATE_LIMIT initialization parameter at the CDB level.

It must be less than or equal to 50% of the PGA_AGGREGATE_ limit value in PDB.

The guidelines for setting up PGA_AGGREGATE_LIMIT are:

It must be less than or equal to the setting of PGA _ AGGREGATE_LIMIT in the CDB root.

It must be greater than or equal to twice the setting of PGA_AGGREGATE_TARGET in PDB.

Alter session set container=PDB1

ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 700m SCOPE = BOTH

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 300m SCOPE = BOTH

-- check the usage of current SGA and PGA of PDB

COLUMN PDB_NAME FORMAT A10

SELECT r.CON_ID, p.PDB_NAME, r.SGA_BYTES/1024/1024 SGA_M, r.PGA_BYTES/1024/1024 PGA_M

R.BUFFER_CACHE_BYTES/1024/1024 BUFFER_CACHE_M, r.SHARED_POOL_BYTES/1024/1024 SHARED_POOL_M

FROM V$RSRCPDBMETRIC r, CDB_PDBS p WHERE r.CON_ID = p.CON_ID

CON_ID PDB_NAME SGA_M PGA_M BUFFER_CACHE_M SHARED_POOL_M

--

3 PDB01 34.3150578 7.44008255 28.828125 5.48693275

After reading this article, I believe you have a certain understanding of "how to control sga and pga memory in pdb in oracle12c". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.