How to calculate context swicthing for anonymous plsql block

Question

How many context switches will happen for the below given plsql block

    Declare
    ll_row_count number := 0;
    begin
    for i in (select * from employee) 
    loop
    ll_row_count := ll_row_count+1;
    update employee
    set emp_name = upper(emp_name)
    where emp_id = i.emp_id;
    commit;
    end loop;
    dbms_output.put_line('Total rows updated' || ll_row_count);
    end;
/

Answer 1

Context switching

While executing any code block or query, if executing engine needs to fetch data from other engine then it is called context switching. Here engine refers to the SQL engine and PL/SQL engine.

Means, While executing your code in PL/SQL, If there comes a SQL statement then PL/SQL engine need to pass this SQL statement to SQL engine, SQL engine fetches the result and passes it back to PL/SQL engine. Hence, Two context switch happens.

Now, Comming to your block, please see inline comments. We will use N as a number of record in table employee

Declare
ll_row_count number := 0;
begin
for i in (select * from employee) -- (CEIL(N/100)*2) context switch
loop
ll_row_count := ll_row_count+1;
update employee
set emp_name = upper(emp_name)
where emp_id = i.emp_id;  -- 2*N context switch
commit; -- 2*N context switch
end loop;
dbms_output.put_line('Total rows updated' || ll_row_count);
end;
/

Now, Why we divide N by 100?

Because In oracle 10g and above For loop is optimized to use bulk transaction of LIMIT 100 to reduce context switching in the loop.

So finally, the number of context switch are: (CEIL(N/100)*2) + 2*N + 2*N

Cheers!!

Answer 2

Context switches can happen for many reasons, including multitasking and interrupts. When speaking about Oracle database development, we generally mean only the switches between the PL/SQL engine and the SQL engine.

In your example, PL/SQL is calling SQL. There is one context switch when PL/SQL calls SQL and a second one when SQL returns to PL/SQL.

PL/SQL calls SQL to PARSE a statement, to EXECUTE a statement or to FETCH rows from a query. We can measure the number of calls using the SQL trace facility and the trace profiler called TKPROF.

I created a table with 199 rows and traced the execution of your code:

1. There were 3 PARSE calls: 1 for the SELECT, 1 for the UPDATE and 1 for the COMMIT.
2. There were 2 FETCH calls. When you code for i in (select * from employee) loop, PL/SQL will automatically fetch 100 rows at a time (in order to reduce the number of context switches).
3. There were 399 EXECUTE calls: 1 for the SELECT, 199 for the UPDATE and 199 for the COMMIT.

So there were 404 calls from PL/SQL to SQL, and 404 returns from SQL to PL/SQL, making 808 context switches in all.

We can cut the number of context switches in half by committing once after the loop. It is strongly recommended to avoid too frequent commits. If you commit within a SELECT loop, you can get an exception related to UNDO no longer being available.

Generally, the best way to reduce context switches and enhance performance is to use set-based SQL. Failing that, we can process a bunch of rows at a time using BULK COLLECT and FORALL.

Best regards, Stew Ashton