DBA Hub

📋Steps in this guide1/3

Multitenant : Memory Resource Management for PDBs in Oracle Database 12c Release 2 (12.2)

Control the amount of memory used by a PDB, making consolidation more reliable in Oracle Database 12c Release 2 (12.2).

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

PDB Memory Parameters

The following parameters can be set at the PDB level. - : The minimum buffer cache size for the PDB. - : The minimum shared pool size for the PDB. - : The maximum PGA size for the PDB. - : The target PGA size for the PDB. - : The minimum SGA size for the PDB. - : The maximum SGA size for the PDB. Despite this, according to the following MOS note the only SGA memory sizing parameter that Oracle recommends setting at the PDB level is . - ORA-04031 on Multitenant Database with Excessive Amounts of KGLH0 and / or SQLA Memory and Parameter SHARED_POOL_SIZE or SGA_MIN_SIZE Set at the PDB Level (Doc ID 2590172.1) There are a number of restrictions regarding what values can be used, which are explained in the documentation here . To summarise. - The parameter is set to FALSE in the root container. - The parameter is unset or set to "0" in the root container. - The individual parameters have a variety of maximum limits to prevent you from over-allocating memory within the PDB and the instance generally. If you attempt to set an incorrect value an error will be produced.
2

Setting PDB Memory Parameters

The process of setting memory parameters for a PDB is similar to setting regular instance parameters. The example below uses the parameter, but the approach is similar for the other parameters. Check the current settings for the root container. Check the current settings for the pluggable database. Set the for the current PDB. Attempt to make the too big compared to the value in the root container. The value can be set to "0" or reset if you no longer want to control this parameter. The PDB memory settings aren't written to the SPFILE, like those of the CDB. Instead they are stored in the table. You can query it using the following query from the root container.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
CONN / AS SYSDBA
SHOW PARAMETER sga_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 2544M
SQL>

conn / as sysdba
alter session set container=pdb1;

show parameter sga_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0
SQL>

SQL> alter system set sga_target=1g scope=both;

System altered.

SQL> show parameter sga_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 1G
SQL>

SQL> alter system set sga_target=3g scope=both;
alter system set sga_target=3g scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-56747: invalid value 3221225472 for parameter sga_target; must be smaller
than parameter sga_target of the root container

SQL>

alter system set sga_target=0 scope=both;
alter system reset sga_target;

set linesize 120
column pdb_name format a10
column name format a30
column value$ format a30

select ps.db_uniq_name,
       ps.pdb_uid,
       p.name as pdb_name,
       ps.name,
       ps.value$
from   pdb_spfile$ ps
       join v$pdbs p on ps.pdb_uid = p.con_uid
order by 1, 2, 3;
3

Monitoring Memory Usage for PDBs

Oracle now provides views to monitor the resource (CPU, I/O, parallel execution, memory) usage of PDBs. Each view contains similar information, but for different retention periods. - : A single row per PDB, holding the last of the 1 minute samples. - : 61 rows per PDB, holding the last 60 minutes worth of samples from the view. - : Cumulative statistics since the CDB resource plan was set. - : AWR snaphots, retained based on the AWR retention period. Access to the AWR views require additional licensing, so be warned. The following queries are examples of their usage. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
conn / as sysdba

set linesize 150
column pdb_name format a10
column begin_time format a26
column end_time format a26
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'; 
alter session set nls_timestamp_format='dd-mon-yyyy hh24:mi:ss.ff'; 

-- Last sample per PDB.
select r.con_id,
       p.pdb_name,
       r.begin_time,
       r.end_time,
       r.sga_bytes,
       r.pga_bytes,
       r.buffer_cache_bytes,
       r.shared_pool_bytes
from   v$rsrcpdbmetric r,
       cdb_pdbs p
where  r.con_id = p.con_id
order by p.pdb_name;

-- Last hours samples for PDB1
select r.con_id,
       p.pdb_name,
       r.begin_time,
       r.end_time,
       r.sga_bytes,
       r.pga_bytes,
       r.buffer_cache_bytes,
       r.shared_pool_bytes
from   v$rsrcpdbmetric_history r,
       cdb_pdbs p
where  r.con_id = p.con_id
and    p.pdb_name = 'pdb1'
order by r.begin_time;

-- Check you are licensed to do this before trying!
-- All AWR snapshot information for PDB1.
select r.snap_id,
       r.con_id,
       p.pdb_name,
       r.begin_time,
       r.end_time,
       r.sga_bytes,
       r.pga_bytes,
       r.buffer_cache_bytes,
       r.shared_pool_bytes
from   dba_hist_rsrc_pdb_metric r,
       cdb_pdbs p
where  r.con_id = p.con_id
and    p.pdb_name = 'PDB1'
order by r.begin_time;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!