Context Switching | Oracle

Context Switching – There is pl/sql engine for pl/sql code and sql engine for sql queries. Every time the control is passed between any of these two engines, its called context switching.

Context switching takes time and creates a delay in execution of the code. It’s a necessary requirement for the code, but too much switches will make the code work slow.

Ex- SELECT  emp_id,

       Dept_name(emp_id)

FROM emp

WHERE emp_id = v_id;

The above code is part of a procedure(plsql eng). When the code executes the query, the control is passed to (sql eng). While, executing query, the func dept_name is encountered and control is passed to (plsql eng). This function itself has a sql query to generate the department name and hence control goes to (sql eng) again. This is not a good practise and is not recommended.