How to solve memory problems using oracle collections?

Question

I recently faced with an interview question which is: I have very little memory available in my session and I'm using a collection to store the entire records fetched by a cursor. Due to low memory, my session crashed. How will I handle this and optimize the performance of the session?

Answer 1

First of all its a hypothetical question. What do you mean by Little memory ?Pls check with the interviewer. If they want to check the approach then you may answer like before populating the records into collection make ensure you have use collection.delete methods.Especially when you iterate in loop.

You may also control the memory by ussing LIMIT clause while fetching cursor. Apart from that if you DB version is 12.1 or above you may use row limit clause to control the cursor memory. For exp:-

sql>
DECLARE
  CURSOR allrows_cur IS
    SELECT * FROM employees;
  TYPE employees_ntt IS TABLE OF employees%ROWTYPE;

  l_employees employees_ntt;
  l_row       PLS_INTEGER;
BEGIN
  --CLEAR  ARRAY TO LOAD RECORDS
  l_employees.DELETE;

  OPEN allrows_cur;

  LOOP
    FETCH allrows_cur BULK COLLECT
      INTO l_employees LIMIT 10;
    == > USE LIMIT Clause here EXIT WHEN l_employees.COUNT = 0;
  
    l_row := l_employees.FIRST;
    DBMS_OUTPUT.put_line('--------------------');
  
    WHILE (l_row IS NOT NULL) LOOP
      DBMS_OUTPUT.put_line(l_employees(l_row).last_name);
      l_row := l_employees.NEXT(l_row);
    END LOOP;
  END LOOP;

CLOSE allrows_cur;
l_employees.DELETE;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERR DTL-->' || sqlerrm);
END;