Friday, March 11, 2022

PGA Memory are not eligible to receive ORA-4036 interrupts

Users may receive ORA-04036 errors when PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA and you will see the errors written in the trace files as well.

ARC1 (PID:42467): Archived Log entry 10265 added for T-1.S-10440 ID 0xb264618a LAD:1
2022-05-15T02:07:49.670543-07:00
PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA
memory are not eligible to receive ORA-4036 interrupts. Further occurrences
of this condition will be written to the trace file of the DBRM process.

When you encounter this issue, the sessions consuming the PGA will be terminated until the bottleneck is cleared . Note that Oracle can exceed the amount of RAM without PGA_AGGREGATE_LIMIT which may lead to RAM buffer paging and RAC node eviction errors

The V$PGA_TARGET_ADVICE will help to predict how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view

SQL> select pga_target_for_estimate, pga_target_factor, estd_time  from v$pga_target_advice;

The below query can help to get PGA Target advice by querying v$pga_target_advice_histogram

SQL> SELECT LOW_OPTIMAL_SIZE/1024 "LOW VALUE IN KB", (HIGH_OPTIMAL_SIZE+1)/1024 "HIGH VALUE IN KB", ESTD_OPTIMAL_EXECUTIONS "OPTIMAL VALUE IN KB ", ESTD_ONEPASS_EXECUTIONS "ONE PASS EXECUTION", ESTD_MULTIPASSES_EXECUTIONS "MULTI-PASS EXECUTION "
FROM  V$PGA_TARGET_ADVICE_HISTOGRAM
WHERE PGA_TARGET_FACTOR = 2 AND ESTD_TOTAL_EXECUTIONS != 0
ORDER BY 1;

By default PGA_AGGREGATE_LIMIT is set to 2GB, when you receive the errors, I would suggest to double the value or set the appropriate value required for your environment.

You can run below command to get PGA_AGGREGATE_LIMIT and increase the value

SQL> show parameter PGA_AGGREGATE_LIMIT
SQL> alter system set PGA_AGGREGATE_LIMIT=<xGB> scope=spfile;

Sometimes users may receive below errors when PGA_AGGREGATE_LIMIT set to zero. Make sure you set the non-zero and appropriate value.

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error:
REP-300: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Users may experience "Database Crash" in Oracle 19c versions when USING DBMS_STATS.GATHER_TABLE_STATS . This is due to a Oracle product defect Bug:30846782 which is fixed in 21.1.

As a workaround you may try to reduce the memory usage, set hidden parameter "_fix_control"='20424684:OFF'.

At session level:
alter session set "_fix_control"='20424684:OFF';
At Instance level:
alter system set "_fix_control"='20424684:OFF';

Thanks & Regards,
http://oracleracexpert.com, Oracle ACE.








2 comments:

  1. Students same can also be Downloaded From the Direct Links Provided below, Students are Advised to go Through the same Carefully to Understand the Changes in the Paper 2023 Pattern. The Change in UP Board 7th Question Paper 2023 Pattern is also explained below. Board of High School and Intermediate Education Uttar Pradesh is Going to Conduct the Formative (FA) and Summative Assessment (SA) in 7th class Examination, is a Board of School Education in State of Uttar Pradesh, India. UPMSP 7th Class Model Paper 2023 It is a state agency of the Government of UP which is Responsible for the Promotion and Development of Secondary Education in the state. UPMSP is a Board of Education for Public and Private Schools under the State Government of UP, UP Board will announce its High School Education 7th SA, FA Exam Scheduled Announced soon, UP Board 12th Class Latest and Last Year Exam Study Material for Syllabus 2023, Question Paper 2023 etc, Hindi, English Medium Pdf Format.

    ReplyDelete
  2. Handling ORA-04036 errors related to PGA_AGGREGATE_LIMIT can be intricate. On a side note, for those delving into writing, a helpful tip is to discover the best free AI book cover generator. It is a fantastic resource for creating visually appealing book covers without breaking the bank.

    ReplyDelete