Problem locator: Every night at 10:00 pm when I run the automatic SQL optimization task, I get an error about insufficient memory.

The alert reports an error.

Errors in file /u01/app/oracle/diag/rdbms/cpty/cpty/trace/cpty_j003_28000.trc (incident=28312):
ORA-04030: After trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory) Out of process memory
Incident details in: /u01/app/oracle/diag/rdbms/cpty/cpty/incident/incdir_28312/cpty_j003_28000_i28312.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/cpty/cpty/trace/cpty_j003_28000.trc (incident=28313):
ORA-04030: After trying to allocate 8168 bytes (kxs-heap-c,kdbmal allocation) Out of process memory
ORA-04030: After trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory) Out of process memory
Incident details in: /u01/app/oracle/diag/rdbms/cpty/cpty/incident/incdir_28313/cpty_j003_28000_i28313.trc

Solution:

Increase the pga appropriately and set a minimum value for sharepool, largerpool, etc.

Mon Jan 07 08:30:49 2019
ALTER SYSTEM SET java_pool_size=’100M’ SCOPE=SPFILE;
ALTER SYSTEM SET large_pool_size=’100M’ SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_size=’500M’ SCOPE=SPFILE;
Mon Jan 07 08:31:26 2019
ALTER SYSTEM SET db_cache_size=’1000M’ SCOPE=SPFILE;
Mon Jan 07 08:32:03 2019
ALTER SYSTEM SET pga_aggregate_target=’3000M’ SCOPE=SPFILE;

The problem of memory auto-management is recommended to not apply memory auto-management and set sga_target=0.

Permanent solution to turn off sql optimization tasks

Turn off automatic optimization

BEGIN

dbms_auto_task_admin.disable(

client_name => ‘sql tuning advisor’,

operation => NULL,

window_name => NULL);

END;

/

Turn on automatic optimization

BEGIN

dbms_auto_task_admin.enable(

client_name => ‘sql tuning advisor’,

operation => NULL,

window_name => NULL);

END;

/