Which three actions should you perform to reduce shared pool fragmentation and avoid the
“ORA-04031:unable to allocate….bytes of shared memory”error for the shared
pool?(Choose three.)
A.
Configure the Server Result Cache.
B.
Configure shared server mode.
C.
Identify the packages or procedures that are causing the“ORA-04031:unable to
allocate….bytes of shared memory”error and use theDBMS_SHARED_POOL.KEEPto keep
them in the shared pool.
D.
UseDBMS_SHARED_POOL.KEEPto keep theSYS.STANDARD,
SYS.DBMS_STANDARD, andSYS.DIUTILpackages and frequently executed compiled
triggers.
E.
Use more anonymous procedures.
F.
Standardize the type, size, and naming conventions for bind variables and spacing
conventions for SQL statements and PL/SQL blocks.
imo C,D,F
imo BCD
See: Oracle Database 12c: Performance Management and Tuning 16 – 32
imo – BDF
Obviously, not A and E.
Not C because, dba might not want to pin the infrequently used objects just because it failed once with the ora-04031. Second, the logic behind the error could be because it did not get used frequent enough and it gets aged out (LRU), why would a dba wants to pin it to the db? If the option of C is happening too frequently, that will probably mandate to increase the shared pool because there will be quite a bit of random objects getting error out. Not really sure about option F is completely accurate but it seems better than C.
F is useless
BCD imo
BCD
BC – Oracle Database 12c: Performance Management and Tuning 16 – 36
D – By using the DBMS_SHARED_POOL package to load the SQL and PL/SQL areas before memory fragmentation occurs, the objects can be kept in memory. Consider using the DBMS_SHARED_POOL package: When loading large PL/SQL objects, such as the STANDARD and DIUTIL packages.
https://docs.oracle.com/database/121/TGDBA/tune_shared_pool.htm#TGDBA604