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.
Exchange Partition permite cargar en tablas particionadas datos en forma rápida y con muy poco impacto para los usuarios que se encuentran activos.