unable to shrink shared pool in CDB

From: Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
Date: Tue, 24 May 2022 18:09:15 +0000
Message-ID: <DM6PR09MB467753A0066BEEA2B3A22BC9DFD79_at_DM6PR09MB4677.namprd09.prod.outlook.com>

Shared pool not set in PDB.

Trying to shrink the shared_pool in the CDB. Here are my steps:

SYSTEM_at_cdb01t06>select component, current_size/1024/1024 CURRENT_SIZE_M, min_size/1024/1024 MIN_SIZE_M,   2 user_specified_size/1024/1024 user_size_m,   3 -- user_specified_size/1024/1024 USER_SPECIFIED_SIZE_M,   4 last_oper_type, granule_size/1024/1024 granule_size_m   5 from v$sga_dynamic_components;

COMPONENT                 CURRENT_SIZE_M MIN_SIZE_M USER_SIZE_M LAST_OPER_TYP


------------------------- -------------- ---------- ----------- -------------

GRANULE_SIZE_M

---

shared pool                         2480       2400        2304 GROW
            16

large pool                            16         16           0 STATIC
            16

java pool                            112         64          48 GROW
            16

streams pool                          48         48          48 STATIC
            16

unified pga pool                       0          0           0 STATIC
            16

memoptimize buffer cache               0          0           0 STATIC
            16

DEFAULT buffer cache                1552       1552         800 SHRINK
            16

KEEP buffer cache                      0          0           0 STATIC
            16

RECYCLE buffer cache                   0          0           0 STATIC
            16

DEFAULT 2K buffer cache                0          0           0 STATIC
            16

DEFAULT 4K buffer cache                0          0           0 STATIC
            16

DEFAULT 8K buffer cache                0          0           0 STATIC
            16

DEFAULT 16K buffer cache               0          0           0 STATIC
            16

DEFAULT 32K buffer cache               0          0           0 STATIC
            16

Shared IO Pool                       112        112         112 STATIC
            16

Data Transfer Cache                    0          0           0 STATIC
            16

In-Memory Area                         0          0           0 STATIC
            16

In Memory RW Extension Ar              0          0           0 STATIC
ea
            16

In Memory RO Extension Ar              0          0           0 STATIC
ea
            16

ASM Buffer Cache                       0          0         800 STATIC
            16

COMPONENT                 CURRENT_SIZE_M MIN_SIZE_M USER_SIZE_M LAST_OPER_TYP


------------------------- -------------- ---------- ----------- -------------

GRANULE_SIZE_M

---

20 rows selected.

SYSTEM_at_cdb01t06>alter system set shared_pool_size=2300m scope=both;

System altered.

SYSTEM_at_cdb01t06>rem waiting 2 min
SYSTEM_at_cdb01t06>select component, current_size/1024/1024 CURRENT_SIZE_M, min_size/1024/1024 MIN_SIZE_M,
  2  user_specified_size/1024/1024 user_size_m,

  3 -- user_specified_size/1024/1024 USER_SPECIFIED_SIZE_M,   4 last_oper_type, granule_size/1024/1024 granule_size_m   5 from v$sga_dynamic_components;

COMPONENT                 CURRENT_SIZE_M MIN_SIZE_M USER_SIZE_M LAST_OPER_TYP


------------------------- -------------- ---------- ----------- -------------

GRANULE_SIZE_M

---

shared pool                         2480       2400        2304 GROW
            16

large pool                            16         16           0 STATIC
            16

java pool                            112         64          48 GROW
            16

streams pool                          48         48          48 STATIC
            16

unified pga pool                       0          0           0 STATIC
            16

memoptimize buffer cache               0          0           0 STATIC
            16

DEFAULT buffer cache                1552       1552         800 SHRINK
            16

KEEP buffer cache                      0          0           0 STATIC
            16

RECYCLE buffer cache                   0          0           0 STATIC
            16

DEFAULT 2K buffer cache                0          0           0 STATIC
            16

DEFAULT 4K buffer cache                0          0           0 STATIC
            16

DEFAULT 8K buffer cache                0          0           0 STATIC
            16

DEFAULT 16K buffer cache               0          0           0 STATIC
            16

DEFAULT 32K buffer cache               0          0           0 STATIC
            16

Shared IO Pool                       112        112         112 STATIC
            16

Data Transfer Cache                    0          0           0 STATIC
            16

In-Memory Area                         0          0           0 STATIC
            16

In Memory RW Extension Ar              0          0           0 STATIC
ea
            16

In Memory RO Extension Ar              0          0           0 STATIC
ea
            16

ASM Buffer Cache                       0          0         800 STATIC
            16

COMPONENT                 CURRENT_SIZE_M MIN_SIZE_M USER_SIZE_M LAST_OPER_TYP


------------------------- -------------- ---------- ----------- -------------

GRANULE_SIZE_M

---

20 rows selected.

Notice that after altering the shared_pool size in the CDB, the last query still reports the original value. Why am I unable to shrink the shared_pool? Memory_target is not being used.

Jeffrey Beckstrom
Greater Cleveland Regional Transit Authority 1240 W. 6th Street
Cleveland, Ohio 44113

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 24 2022 - 20:09:15 CEST