Uncontrollable Bladder What To Do

BULK COLLECT with thousands of records and the LIMIT clause

I see many times that when using BULK COLLECT in PL / SQL code, not put the LIMIT clause. When we do not use this clause, all you win is to ruin the memory of the process.
The LIMIT clause allows us to define the amount of 'data' which we will place in memory. When use LIMIT, the ideal is to define a value between 100 to 500. Personally, I choose the value 100 because in my experience is usually the best value. But because I choose a value so small and not 1000 or 5000 for example? Well, for the simple reason that handle large amounts of data in memory is more expensive to handle small quantities.
If you choose a high value on the edge, can be 3 cases:
- that your code will run faster (unlikely).
- that your code will run in the same time (unlikely).
- that your code will run slower (likely).

An example to better understand the consequences of not using the LIMIT clause.

TEST First, create a table with 10,000 records (to see the difference in the use of the LIMIT clause, you do not run the example with millions of records. With thousands of records about us enough to understand the issue) and a table with TEST_2 TEST table structure but no records:

 
 SQL_9iR2> CREATE TABLE test AS SELECT level 
 2 id, 'oracle_'  not being used in my current session in order to see clearly the difference in the statistics taken in each run. 
 
 <= 10000 ;
 SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY; 
 
 PL / SQL procedure successfully completed. 
 
 
 execute a PL / SQL Bulk Collect but WITHOUT LIMIT clause: 
 
 
 SQL_9iR2> DECLARE 2 TYPE t_array_number 
 IS TABLE OF NUMBER; 

3 t_array_varchar2 TYPE IS TABLE OF VARCHAR2 (50);
4 t_array_id t_array_number; 5 t_array_texto
t_array_varchar2;

 6 7 
 CURSOR cur IS SELECT * FROM test; 
 
 8 BEGIN 9 OPEN cur; 
 10 LOOP 12 FETCH 

11
cur BULK COLLECT INTO t_array_id, t_array_texto;

 13 14 FORALL i IN 1 .. INSERT INTO 
 t_array_id.COUNT test_2 
 15 16 VALUES (t_array_id (i) t_array_texto (i)); 
 17 
 18 EXIT WHEN cur% NOTFOUND; 
 
 19 20 END LOOP; 
 21 COMMIT; 
 22 CLOSE cur; 
 23 END; 
 24 / 
 
 PL / SQL procedure successfully completed. 
 
 Elapsed: 00:00:00.04 
 
 
 Before running the second code, I will release the memory again and not being used in my current session. 
 
 
 SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY ; 
 
 PL / SQL procedure successfully completed. 
 
 
 Now run the same code PL / SQL Bulk Collect with LIMIT clause but WITH: 
 
 
 SQL_9iR2> DECLARE 2 TYPE t_array_number 
 IS TABLE OF NUMBER; 

3 t_array_varchar2 TYPE IS TABLE OF VARCHAR2 (50);
4 t_array_id t_array_number;
5 t_array_texto t_array_varchar2;

 6 7 
 CURSOR cur IS SELECT * FROM test; 
 
 8 BEGIN 9 OPEN cur; 
 

11 10 LOOP 12 FETCH
cur BULK COLLECT INTO t_array_id, t_array_texto

 LIMIT 100; 
 13 
 14 FORALL i IN 1 .. INSERT INTO 
 t_array_id.COUNT 15 test_2 
 16 VALUES (t_array_id (i) t_array_texto (i)); 
 17 
 18 EXIT WHEN cur% NOTFOUND; 
 
 19 20 END LOOP; 
 21 COMMIT; 
 22 CLOSE cur; 
 23 END; 
 24 /   PL / SQL procedure successfully completed. 
 
 Elapsed: 00:00:00.04 
 
 
 We see that in the second run, I'm loading into memory on each fetch 100 records that I do. Unlike the first implementation, which loads all the data at once. Let 
 
 statistics from the previous 2 versions: 
 
 
 Name Ejecución_1 Ejecución_2 
 Difference  ------------------------------ ----------- ----------- ----------- 
 LATCH.kwqit: protect wakeup ti           1           0          -1 
 LATCH.simulator lru latch                1           0          -1 
 LATCH.spilled msgs queues list           1           0          -1 
 LATCH.transaction allocation             3           0          -3 
 LATCH.session timer                      5           0          -5 
 LATCH.multiblock read objects            8           0          -8 

LATCH.channel operations paren 11 0 -11
LATCH.child cursor hash table 20 8 -12
LATCH.Consistent RBA 56 6 -50
LATCH.lgwr LWN SCN 56 6 -50
LATCH.mostly latch-free SCN 56 6 -50

 LATCH.active checkpoint queue           63           7         -56 
 LATCH.session idle bit                 183          45        -138 
 LATCH.enqueues                         219          49        -170 
 LATCH.redo writing                     241          25        -216 
 LATCH.SQL memory manager worka         337           0        -337 
 LATCH.messages                         427          42        -385 
 LATCH.simulator hash latch             844           4        -840 
 LATCH.dml lock allocation            1,428         154      -1,274 
 LATCH.shared pool                    1,586         271      -1,315 
 LATCH.row cache  enqueue latch        1,648         194      -1,454 
 LATCH.cache buffers lru chain        1,521           5      -1,516 
 LATCH.library cache pin alloca       2,900         362      -2,538 
 LATCH.row cache objects              4,438         502      -3,936 
 LATCH.enqueue hash chains            4,841         508      -4,333 
 LATCH.checkpoint queue latch         6,296         521      -5,775 
 LATCH.session allocation            19,333       1,893     -17,440 
 LATCH.undo global data              30,208       2,945     -27,263 
 LATCH.redo allocation               30,716       3,231     -27,485 
 LATCH.sequence cache                42,506       4,124     -38,382 
 LATCH.library cache pin -54.339 60.580 6.241 76.555 7.911 
 LATCH.library cache -68.644 
 
 Latch: 
 
 
 Ejecución_1 Ejecución_2 Percentage Difference 
 670.187 67.521 -602.666 992.56% 
 
 
 
 One of the things that interests me show you about these executions are LATCH (loquee). Although the implementation of the 2 PL / SQL code delayed to run the exact same thing (in this example, processing only 10,000 records), statistics show us that we lock for many more employees in the first execution in the second. But why does this happen? Recall that in the second run, all that changed in the code was the addition of the LIMIT clause. Well, as we said at the start, manage large amount of memory is more costly to handle short supply, so Oracle has to use much to handle loquee we climbed to 10,000 records in the first implementation report, which handle only 100 records in the second run. 
 This example is conducted with a concurrent user only .... but imagine what would happen if we have many concurrent users doing the same thing we ... and therefore, generating lots of loquee .... 
 
 NOTE: We must avoid at all costs and lock for that  los loqueos afectan la performance del sistema. Mientras mayor sea la cantidad de loqueos, nuestro sistema se vuelve cada vez menos escalable; y como consecuencia, cada vez soporta menor cantidad de usuarios concurrentes.