CODEWITHSUNDEEP
oracle-databaseplsql
Vinay Taneja
Vinay Taneja

Reputation: 99

Oracle SQL and PL/SQL context switches

I have a code in oracle pl sql, want to really want to understand how much context switching is there

If x=0 then 

  curserx= select a from mytable1;

Else

  curserx=select a from mytable1 where id=:x;

End;

Loop

  Fetch on cursorx

  Select c from mytable2 where a=curserx.a;

End loop;

This is just a sample code so please pardon any text casing and logic error.

Upvotes: 3

Views: 4469

Answers (1)

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

I converted your pseudo code into PL/SQL and include comments indicating where I believe you will have a context switch from the PL/SQL engine to the SQL engine.

Note that if you are querying a non-trivial number of rows, you could use FETCH BULK COLLECT INTO and retrieve multiple rows with each fetch, greatly reducing context switches.

DECLARE
   l_x_value   INTEGER;
   l_cursor    SYS_REFCURSOR;
   l_fetched   mytble1.a%TYPE;
BEGIN
   /* context switch to open */
   IF x = 0
   THEN
      OPEN l_cursor FOR SELECT a FROM mytable1;
   ELSE
      OPEN l_cursor FOR
         SELECT a
           FROM mytable1
          WHERE id = l_x_value;
   END IF;

   LOOP
      /* context switch per fetch */
      FETCH l_cursor INTO l_fetched;

      EXIT WHEN l_cursor%NOTFOUND;

      /* context switch for implicit cursor */
      SELECT c
        INTO l_fetched
        FROM mytable2
       WHERE a = curserx.a;
   END LOOP;

   /* context switch to close */
   CLOSE l_cursor;
END;

But that's not all! Remember that the context switch works both ways: SQL -> PL/SQL and PL/SQL -> SQL. You can reduce the overhead of going from SQL to PL/SQL by declaring your function with the UDF pragma (12c+) or defining it with the WITH FUNCTION clause (also 12c+). There is still a context switch but some of the work is done at compile time instead of run time.

So in the code below, for each invocation of the function from within the SELECT, there is a switch.

CREATE OR REPLACE FUNCTION full_name (first_in   IN VARCHAR2,
                                      last_in    IN VARCHAR2)
   RETURN VARCHAR2
IS
BEGIN
   RETURN first_in || ' ' || last_in;
END;
/

DECLARE
   l_name   VARCHAR2 (32767);
BEGIN
   SELECT full_name (first_name, last_name) INTO l_name 
     FROM employees
    WHERE employee_id = 100;

   DBMS_OUTPUT.PUT_LINE (l_name);
END;
/

Finally a cautionary note: you should do everything you can to avoid executing SQL inside a function that is then called from SQL. The standard read consistency model that works for your SQL statement will not be "carried in" to the function's SQL. In other words, if you "outer" SELECT starts running at 10:00 and runs for an hour, and at 10:05, someone deletes rows from a table that is used in both the outer query and the query in the function (and commits), those two queries will be working with different states of those tables.

Upvotes: 4

Related Questions