CODEWITHSUNDEEP
oracle-databaseplsql
Mark
Mark

Reputation: 53

Does Context Switching occur in stored procedure when BULK COLLECT data is manipulated?

I am running on 11g and 12c Oracle systems. I have a question on some PL/SQL I am about to write. I've been researching extensively and I don't quite see the answer and want to get some input from the forum. My main goal is to maintain a high level of performance. I am trying to avoid context switching when working with this data.

The purpose of the SP is to join a set of data from 2 different tables in bulk, manipulate a set of fields in the collection and write it to another table in a different Instance. I would obviously like to do as much as possible in BULK but am unsure if, by the time I have completed it, I won't end up with some sort of single row context switching because of the complex field manipulations.

The details are as follows:

Declare a cursor which will do an inner join. In the SELECT I would like to do some of the data manipulation. I would like to call a Function to do this since it would be very messy to try to do it in the select statement: 

 CURSOR c1 is 
      SELECT DISTINCT A.ID,
        A.PT_NBR,
        A.PT_DT,
        A.PT_QTY,
      (COMPLX_CALC_FUNCTION(B.TR_TM,B.TR_CD ) nRESULT
      FROM PT_TABLE A
      INNER JOIN TRAN_TABLE B
        ON (A.PT_NBR           =  B.TRAN_NBR
        AND A.PT_DT         =  B.TRAN_DT
        AND A.PT_DT BETWEEN B.START_DT AND B.END_DT)
      WHERE A.ID = vID;

The results would be hundreds of thousands or millions of records so I want to do it in bulk 

BEGIN
  OPEN c1;
  LOOP 
    FETCH c1 BULK COLLECT INTO vTable LIMIT 20000;

  ---body code

  -- trying to avoid additional manipulation of the data here but there may still be some

 FORALL indx IN vTable.FIRST .. vTable.LAST

...

 INSERT INTO Table2 VALUES vTable(indx); 

--ending stuff

;

The COMPLX_CALC_FUNCTION would take the inputs and perform mathematical operations and call other functions such as NVL, SUM and CEIL.

So, the question is...will I be subject to context switching because of the function call in the cursor select or if I need to manipulate the data in the SP body? Also, are there any other performance things I need to consider for this scenario? Im trying to avoid dumping the data in a temporary physical table and manipulating it because that seems like it would just be much slower than doing it in memory. Your expert advise is appreciated.

Upvotes: 3

Views: 1483

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Hey Hello. Just got a chance to look into your query. What i would suggest to do a simple test. In my opinion whenevr it is possible always go with SQL if it is possible to avoid any Context switching.

Below snippet will definitely help you understand this. Hope it helps

set serveroutput on;
DECLARE
  lv_num DBMS_SQL.NUMBER_TABLE;
  lv_t1 PLS_INTEGER;
  lv_t2 PLS_INTEGER;
BEGIN
  lv_t1:=dbms_utility.get_time();
  SELECT LEVEL bulk collect INTO lv_num FROM DUAL CONNECT BY LEVEL < 1000000;
  FORALL I IN lv_num.FIRST..lv_num.LAST
  INSERT INTO NUM_TAB VALUES
    (lv_num(I)
    );
  lv_t2:=dbms_utility.get_time();
  dbms_output.put_line(lv_t2-lv_t1||' '||' Bulk collect');
  lv_t1:=dbms_utility.get_time();
  INSERT INTO NUM_TAB
  SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 1000000;
  lv_t2:=dbms_utility.get_time();
  dbms_output.put_line(lv_t2-lv_t1||' '||' SQL Statement');
END;

-------------------------------OUTPUT--------------------------------------

PL/SQL procedure successfully completed.
16493  Bulk collect
1475  SQL Statement

-----------------------------------------------------------------------------

Upvotes: 1

vishnu sable
vishnu sable

Reputation: 358

For performance you have two things here

  1. select statements : for this you can check execution plan of select statement and tune your select statement.

2.your insert statement looks perfectly fine as you are selecting 20000 rows(LIMIT 20000) at singly fetch and inserting it into another table with bulk insert. in this way you will have one context switch for 20000 rows and also there will no problem of memory shortage.

you just need to tune your select statement in this case.

Upvotes: 0

Rob Mascaro
Rob Mascaro

Reputation: 851

Call your function in the table in a memory loop? This will avoid the SQL - PLSQL context switch which is far more costly especially as it will be switched per row coming back. The FORALL loop can be substituted for a regular for loop in memory against the table in memory. This should still be extremely fast. If you want to bulk insert using the FORALL you can take the insert out of the "for loop" but for the extra overhead I don't think it will make much difference.

As for doing everything in bulk - my advice is that for "hundreds" of millions of rows - depending on the "thickness/thinness" of the table - you will certainly blow your PGA and sort area sizes within the DB unless you have a very kind DBA who gives you all the memory you require (doubtful). As a general rule, I try not to put more than 2-5 million rows in memory. 

declare
  l_complex number; -- dont know the real datatype here??
BEGIN
  OPEN c1;
  LOOP 
    FETCH c1 BULK COLLECT INTO vTable LIMIT 20000;

  ---body code
  -- trying to avoid additional manipulation of the data here but there may     still be some

 FOR indx IN vTable.FIRST .. vTable.LAST loop
     l_complex : = COMPLX_CALC_FUNCTION(vtable(indx).TR_TM,vtable(indx).TR_CD );


    INSERT INTO Table2 VALUES vTable(indx); 
 end loop;

...



--ending stuff

;

Upvotes: 1

Related Questions