VF=>user  ID=>    Login Feedback FAQ Blog
1,077,316 quizzes played | 3,164 active players

Actions


  On Analyze size and memory consumption of programs: Techniques to Reduce PGA Consumption (Oracle PL/SQL)      Add to Favorites

Summary

Process Global Area (PGA) memory is utilized to store and manage session-specific data (compared to the System Global Area or SGA, which manages cross-session memory). If a session uses too much PGA, it can fail with the "ORA-04030: out of process memory when trying to allocate..." error. PL/SQL offers several techniques for reducing PGA consumption, including pipelined table functions, the LIMIT clause of BULK COLLECT, and the NOCOPY hint.

Details

Assumes Oracle Database 12c Release 1 or higher
Advanced Quiz, IDs: 15161/1464599/876855

Reviewer(s): Vitaliy Lyanchevskiy

The Question

I execute the following statements:

CREATE TABLE plch_data (
   data_id     INTEGER PRIMARY KEY,
   data_name   VARCHAR2 (1000) UNIQUE);

BEGIN
   INSERT INTO plch_data (data_id, data_name)
          SELECT LEVEL, 'Big Data ' || LEVEL FROM DUAL
      CONNECT BY LEVEL < 100000;
   COMMIT;
END;   
/

CREATE OR REPLACE PACKAGE plch_pkg
IS
   TYPE data_t IS TABLE OF plch_data%ROWTYPE INDEX BY PLS_INTEGER;
   TYPE names_t IS TABLE OF plch_data.data_name%TYPE;

   PROCEDURE update_lots_of_data;
   FUNCTION return_lots_of_data RETURN names_t;
   PROCEDURE show_lots_of_data;
   FUNCTION one_data (data_id_in IN INTEGER) RETURN plch_data%ROWTYPE;
   PROCEDURE change_data (data_in IN data_t, data_out OUT data_t);
END;
/

CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE update_lots_of_data IS
      l_data   data_t;
   BEGIN
      SELECT * BULK COLLECT INTO l_data FROM plch_data;

      FORALL indx IN 1 .. l_data.COUNT
         UPDATE plch_data SET data_name = UPPER (data_name)
          WHERE data_id = l_data (indx).data_id;
   END;

   FUNCTION return_lots_of_data RETURN names_t IS
      l_data names_t;
   BEGIN
      SELECT data_name BULK COLLECT INTO l_data FROM plch_data;
      RETURN l_data;
   END;

   PROCEDURE show_lots_of_data IS
      CURSOR data_cur IS SELECT * FROM plch_data;
      rec   data_cur%ROWTYPE;
   BEGIN
      OPEN data_cur;
      LOOP
         FETCH data_cur INTO rec;
         EXIT WHEN data_cur%NOTFOUND;
         DBMS_OUTPUT.put_line (rec.data_name);
      END LOOP;
      CLOSE data_cur;
   END;

   FUNCTION one_data (data_id_in IN INTEGER) RETURN plch_data%ROWTYPE IS
      l_data   data_t;
   BEGIN
      SELECT * BULK COLLECT INTO l_data FROM plch_data
        ORDER BY data_id;
      RETURN l_data (data_id_in);
   END;

   PROCEDURE change_data (data_in IN data_t, data_out OUT data_t) IS
   BEGIN
      FOR indx IN 1 .. data_in.COUNT
      LOOP
         IF MOD (indx, 3) = 0 THEN
            data_out (indx).data_name := UPPER (data_in (indx).data_name);
         END IF;
      END LOOP;
   END;
END;
/

Make the following assumptions:

1. All subprograms are used extensively and repeatedly by 100s of simultaneous connections.
2. All rows in plch_data are repeatedly fetched 1000s of times per minute through calls to plch_pkg.one_data.
3. Existing rows in plch_data are changed on average once per hour. Rows are never inserted or deleted and primary key values are never changed.
4. The return_lots_of_data function is only invoked from within the TABLE operator in a SELECT's FROM clause.
5. All suggested changes result in successful compilations.

Users complain that they often get this error:

ORA-04030: out of process memory when trying to allocate 40932 bytes

Which of the choices describe a change to a database object that might make it less likely for users to receive this error?

The Choices [↑]

Explanation of Result Icons
Choice 1 (29036)

Change update_lots_of_data to

   PROCEDURE update_lots_of_data
   IS
   BEGIN
      UPDATE plch_data
         SET data_name = UPPER (data_name);
   END;

I avoid the population of a collection, which consumes lots of PGA. Instead, I simply execute a "pure" SQL statement.




83%
Choice 2 (29037)

Change update_lots_of_data to

   PROCEDURE update_lots_of_data
   IS
      CURSOR data_cur
      IS
         SELECT * FROM plch_data;

      l_data   data_t;
   BEGIN
      OPEN data_cur;

      LOOP
         FETCH data_cur BULK COLLECT INTO l_data
           LIMIT 250;

         FORALL indx IN 1 .. l_data.COUNT
            UPDATE plch_data
               SET data_name = UPPER (data_name)
             WHERE data_id = l_data (indx).data_id;

         EXIT WHEN data_cur%NOTFOUND;
      END LOOP;

      CLOSE data_cur;        
   END;

I switch from an "unlimited" BULK COLLECT to one in which I specify a maximum of 250 rows fetched at a time. This reduces the PGA consumption.




93%
Choice 3 (29038)

Change update_lots_of_data to:

   PROCEDURE update_lots_of_data
   IS
      l_data   data_t;
   BEGIN
      SELECT *
        BULK COLLECT INTO l_data
        FROM plch_data
       ORDER BY data_id;

      FORALL indx IN 1 .. l_data.COUNT SAVE EXCEPTIONS
         UPDATE plch_data
            SET data_name = UPPER (data_name)
          WHERE data_id = l_data (indx).data_id;
   END;

The only change is to add an ORDER BY. That will definitely not reduce PGA consumption.




93%
Choice 4 (29039)

Change return_lots_of_data to

   FUNCTION return_lots_of_data
      RETURN names_t
   IS
         CURSOR data_cur
      IS
         SELECT data_name FROM plch_data;

      l_data   names_t;
      l_return names_t;
   BEGIN
      OPEN data_cur;

      LOOP
         FETCH data_cur BULK COLLECT INTO l_data
           LIMIT 250;

         FOR indx IN 1 .. l_data.COUNT
         LOOP
             l_return (l_return.COUNT + 1) := l_data (indx);
         END LOOP;

         EXIT WHEN data_cur%NOTFOUND;
      END LOOP;

      CLOSE data_cur; 

      RETURN l_return;
   END; 

I use BULK COLLECT LIMIT to reduce the number of rows returned with each fetch. That should reduce PGA consumption - except that I then append that collection to another, so in the end I use the same, or perhaps a little more memory.




59%
Choice 5 (29040)

Change header of return_lots_of_data function in specification to:

   FUNCTION return_lots_of_data
      RETURN names_t
      PIPELINED;

and change implementation in body to

   FUNCTION return_lots_of_data
      RETURN names_t
      PIPELINED
   IS
      l_data   names_t;
   BEGIN
      SELECT data_name
        BULK COLLECT INTO l_data
        FROM plch_data;

      FOR indx IN 1 .. l_data.COUNT
      LOOP
         PIPE ROW (l_data (indx));
      END LOOP;

      RETURN;
   END; 

Pipelining can reduce PGA consumption, but in this case, I still fill up the collection with a BULK COLLECT, but instead of simply returning it, I pipe each element out. That doesn't change the memory profile.




66%
Choice 6 (29056)

Change header of return_lots_of_data function in specification to:

   FUNCTION return_lots_of_data
      RETURN names_t
      PIPELINED;

and change implementation in body to:

   FUNCTION return_lots_of_data
      RETURN names_t
      PIPELINED
   IS
      CURSOR data_cur
      IS
         SELECT data_name FROM plch_data;

      l_data   names_t;
      l_return names_t;
   BEGIN
      OPEN data_cur;

      LOOP
         FETCH data_cur BULK COLLECT INTO l_data
           LIMIT 1000;

         FOR indx IN 1 .. l_data.COUNT
         LOOP
             PIPE ROW (l_data (indx));
         END LOOP;

         EXIT WHEN data_cur%NOTFOUND;
      END LOOP;

      CLOSE data_cur; 

      RETURN;
   END; 

This use of pipelining will reduce PGA consumption. I limit the population of the local collection to 1000 rows, then pipe them all out.




90%
Choice 7 (29041)

Change header of one_data function in specification to:

  FUNCTION one_data (data_id_in IN INTEGER)
      RETURN plch_data%ROWTYPE
      RESULT_CACHE;

and change implementation in body to:

   FUNCTION one_data (data_id_in IN INTEGER)
      RETURN plch_data%ROWTYPE
      RESULT_CACHE
   IS
      l_return plch_data%ROWTYPE;
   BEGIN
      SELECT * INTO l_return
        FROM plch_data
       WHERE data_id = data_id_in;

      RETURN l_return;
   END;

The one_data function in its original formulation is silly. Get all the rows from the table, put them in a collection, then return an element from the collection? Ugh. In this choice, I switch to a result cached function, and only select a single row at a time.




80%
Choice 8 (29096)

Change one_data function in the package body to:

   FUNCTION one_data (data_id_in IN INTEGER)
      RETURN plch_data%ROWTYPE
   IS
      l_data   data_t;
   BEGIN
      SELECT *
       BULK COLLECT INTO l_data
       FROM plch_data;

      RETURN l_data (data_id_in);
   END;

The same amount of PGA will be used as before; removing the ORDER BY might fill the collection faster, but it doesn't change the amount of data put into that collection.

The removal of an ORDER BY could, however, result in a reduction in memory consumption. In this case, however, the issue of sort vs no sort is rendered immaterial due to the fact that a sort does not take place in the original code due to the presence of a primary key index (index full scan leading to table access by index rowid for original code vs full table scan for this choice). If the ORDER BY had been instead on data_name, a sort would have taken place and more memory used to perform the sort either via the sort_area_size parameter or automatic memory management. [Thanks to Chad Lee for this second paragraph and the research needed to verify it!]




90%
Choice 9 (29097)

Add this statement to the declaration section of the one_data function:

PRAGMA UDF;

Generally, this pragma can help improve performance of functions called from SQL, but it will not make a difference to PGA consumption. Plus, this function cannot be called from SQL, due to the return type (%ROWTYPE).




76%
Choice 10 (29099)

Change the parameter list of change_data to:

(data_in IN data_t, data_out OUT NOCOPY data_t)

Adding the NOCOPY hint means that PL/SQL will not make a local copy of the collection being populated by the procedure. So PGA consumption will be reduced.




63%
Choice 11 (29336)

Change the implementation of one_data to:

   FUNCTION one_data (data_id_in IN INTEGER)
      RETURN plch_data%ROWTYPE
   IS
      l_data   plch_data%ROWTYPE;
   BEGIN
      SELECT *
        INTO l_data
       FROM plch_data
      WHERE data_id = data_id_in;

      RETURN l_data;
   END;

Back to the simplest form: Fetch one row and return it. No array, greatly reduced PGA consumption.




83%

Answer [↑]

The Oracle catalog contains hundreds of views (generally referred to as "data dictionary views") that provide information about the objects stored in the database (tables, views, PL/SQL program units, etc.). Several of these views are extremely helpful to PL/SQL developers in analyzing and managing their code.

Here are a few examples:

  • ALL_OBJECTS - information about all database objects which can be accessed by the current user.
  • ALL_ARGUMENTS - information about every argument of every packaged subprogram and schema-level program unit for which the current user has EXECUTE authority.
  • ALL_IDENTIFIERS - information about identifiers in program units, gathered by the Oracle Database 11g PL/Scope feature.

Process Global Area (PGA) memory is utilized to store and manage session-specific data (compared to the System Global Area or SGA, which manages cross-session memory). If a session uses too much PGA, it can fail with the "ORA-04030: out of process memory when trying to allocate..." error. PL/SQL offers several techniques for reducing PGA consumption, including pipelined table functions, the LIMIT clause of BULK COLLECT, and the NOCOPY hint.


Statistics [↑]

Click to View Verification Code [↑]

Quiz Link and Tweet [↑]

Pass along a link the quiz or use our text to tweet about this quiz. Thanks for helping to spread the word!

 
About Oracle | Terms of Use | Your Privacy Rights | Copyright 2010-2017, Oracle Corporation