Forum Stats

  • 3,782,052 Users
  • 2,254,585 Discussions
  • 7,879,903 Comments

Discussions

Categories

PGA Memory Use

Eric K.
Eric K. Member Posts: 176 Bronze Badge
in General Database Discussions

Hallo,

My environment is Oracle 12.2 on windows 10.

My database has been running properly for over a year but suddenly, its having performance problems because of running out of memory. I have read in some articles that running;

alter system set pga_aggregate_limit = 0;

will reset the pga to pre-12c levels. I have done this and it doesn't seem to help. I cant even run a simple procedure or even a simple function that selects a single record. They all end up with

ORA-04030: out of process memory when trying to allocate 8216 bytes (PLS PGA hp,PL/SQL STACK)

I have tried to debug this issue for the last three days but nothing seems to work. I have run out of ideas on how to resolve this issue.

Any help is appreciated.

Eric.

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Mohamed Houri
    Mohamed Houri Member Posts: 1,224 Bronze Trophy
    Accepted Answer

    Hello

    an ORA-04030 that contains PL/SQL stack indicates a PL/SQL procedure that is in an infinite loop. Have a look to the following MOS Doc ID: 1461262.1 for more details.

    Also, check in the alert_log for the corresponding incident trace file and look for the culprit PL/SQL call which you can find in this ORA-04030 trace file

    Best regards

    Mohamed Houri

    · Share on TwitterShare on Facebook

Answers

  • Mohamed Houri
    Mohamed Houri Member Posts: 1,224 Bronze Trophy
    Accepted Answer

    Hello

    an ORA-04030 that contains PL/SQL stack indicates a PL/SQL procedure that is in an infinite loop. Have a look to the following MOS Doc ID: 1461262.1 for more details.

    Also, check in the alert_log for the corresponding incident trace file and look for the culprit PL/SQL call which you can find in this ORA-04030 trace file

    Best regards

    Mohamed Houri

    · Share on TwitterShare on Facebook
  • Eric K.
    Eric K. Member Posts: 176 Bronze Badge

    Thank you for Mohamed for your response. I will check the document.

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,608 Gold Crown

    an ORA-04030 that contains PL/SQL stack indicates a PL/SQL procedure that is in an infinite loop

    No. An ORA-04030 simply indicates that you are out of memory. It does NOT indicate the reason you are out of memory. A pl/sql in an infinate loop could possibly be the root cause, but it is far from definitive. A simple sql SELECT, manually issued from a sqlplus command line could also create the very same error if, for instance, the query generated a Cartesean join with a big sorting requirement. No PL/SQL needed. No loop needed. Just a need for more memory than is available.

    · Share on TwitterShare on Facebook
  • Eric K.
    Eric K. Member Posts: 176 Bronze Badge

    Thank you EdStevens for your response.

    Mohamed response was a big help. I've reviewed the code again and found a line that was causing the infinite loop. After removing it, it works fine.

    Thanks Ed and Mohamed again. Much appreciated.

    · Share on TwitterShare on Facebook
  • Mohamed Houri
    Mohamed Houri Member Posts: 1,224 Bronze Trophy

    Hello

    A simple sql SELECT, manually issued from a sqlplus command line could also create the very same error if, for instance, the query generated a Cartesean join with a big sorting requirement.

    As you might probably know there exist two types of pga memory

     1)     Tunable memory

    2)     Non-Tunable memory

    The first type of memory is the one that can be managed by the Memory Manager(MM). It includes all types of memory we can find in the gv$sql_workarea_active

    By abuse of language and without being far from reality, we can associate the "tunable memory" to everything in an execution plan that consumes memory: SORT, HASH JOIN, GROUP BY, BUFFER, BITMAP MERGE, and BITMAP CONSTRUCTION.

    So, your simple SQL SELECT, manually issued from a SQLPIus command line using a Cartesian join with a big sorting requirement should be managed by the Memory Manager and will, as much as possible, respect the pga_aggregate_target value by switching to the TEMP tablespace.

    However, a PL/SQL type is a non-tunable piece of memory that is not managed by the MM. Therefore, it can easily consume all the available PGA (in the case of pga_aggregate_limit =0) until it is stopped by the OS.

    What makes you think that the PLS PGA hp,PL/SQL STACK  in the ORA-04030 error message is not indicating that the allocation reason is not due to a PL/SQL heap?

    Best regards

    Mohamed Houri

    · Share on TwitterShare on Facebook