Optimizer Statistics Advisor Task Consumes Excessive PGA Memory and ORA-4036 Occurs. (Doc ID 2727813.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

• Optimizer Statistics Advisor task consumes excessive PGA memory then ORA-4036 error occurs:  
  -------------------------
  Top 10 processes:
  -------------------------
  (percentage is of 4159 MB total allocated memory)
  56% ospid 814 (J000): 2330 MB used, 2341 MB alloc <= CURRENT PROC <<<<<★
  3% ospid 3894 (W004): 111 MB used, 114 MB alloc
  3% ospid 9925 (W006): 111 MB used, 113 MB alloc
  3% ospid 3092 (W002): 111 MB used, 113 MB alloc
  3% ospid 4793 (W005): 111 MB used, 113 MB alloc
  =======

  ...

  [TOC00004]
  ----- Current SQL Statement for this session (sql_id=5t0s77hdvw8up) -----
  select owner, object_name, subobject_name, object_type from dba_objects where object_id = :1
  [TOC00005]
  ----- PL/SQL Stack -----
  ----- PL/SQL Call Stack -----
  object line object
  handle number name
  0x9c598f68 314 package body SYS.DBMS_STATS_ADVISOR.I_EXECUTE_TASK_CALLOUT
  0x9c598f68 7734 package body SYS.DBMS_STATS_ADVISOR.I_EXECUTE_TASK <<<<<<★
  0x106408b28 7 type body SYS.WRI$_ADV_STATS.SUB_EXECUTE
  0xb0ef8b28 915 package body SYS.PRVT_ADVISOR.COMMON_SUB_EXECUTE
  0xb0ef8b28 3451 package body SYS.PRVT_ADVISOR.COMMON_EXECUTE_TASK
  0x9c598f68 3389 package body SYS.DBMS_STATS_ADVISOR.EXECUTE_TASK_AUX
  0x9c598f68 3436 package body SYS.DBMS_STATS_ADVISOR.EXECUTE_TASK
  0xb222d8b8 24443 package body SYS.DBMS_STATS_INTERNAL.EXECUTE_ADVISOR_AUTO_TASK
  0x9c1bf650 51811 package body SYS.DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC
  [TOC00005-END]

    
  

   

Changes

 None.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.