This content has been marked as final. Show 4 replies
..if we issue a dml from within a plsql block ..suppose a statement like ..delete from employee where emp_id>1001 . so is it like this statement is sent >to the sql engine , a row is deleted and an acknowledgement is sent to plsql engine.another row is deleted and the same thing is repeated. This is >why we say we should avoid context switching between plsql and sql using bulk bindings...There are 2 Engines - SQL Engine & PL/SQL Engine.
When you issue any SQL statement/Query, the SQL Engine processes it and fetches the reult.
Similarly, when you write a PL/SQL block, PL/SQL Engine takes care. But if your block contains any SQL query, it is sent to SQL Engine from the PL/SQL Engine for processing. This is called as Context Switching between the engines which is an overhead.
Suppose, you have a cursor which fetches 100 rows. Now, you loop the cursor and process (say Insert/Update) the data fetched by it.
This looping is actually a row-by-row activity... or also fondly said as slow-by-slow activity. Because for each iteration of the FOR Loop, there is a context switch from PL/SQL Engine to SQL Engine.
For that Bulk Processing concepts were introduced.
How it is beneficial?*
Using this concept, firstly the fetched 100 rows is packed into a single result set (Using BULK COLLECT INTO clause).
Secondly, the LOOP thing is eliminated and FORALL is used.
So, now there's only 1 context switch, and data is sent as a whole.
Hope this Helps
Edited by: ranit B on Nov 24, 2012 2:36 PM
Sorry people , but this time i need a detailed answer . have stressed on two points related to cursor. Also does the context switching happens for each row manipulated by the sql engine ? or is it a statement level process.
.i.e. if i issue a delete statement from plsql which deletes 10 rows. so the context switching would be at the statement level or at the row level .....i.e. 10 times of a single time,
Rahul K wrote:In your examples, a "context switch" is just PL/SQL calling the SQL engine.
...does the context switching happens for each row manipulated by the sql engine ? or is it a statement level process.
.i.e. if i issue a delete statement from plsql which deletes 10 rows. so the context switching would be at the statement level or at the row level .....i.e. 10 times of a single time...
You can call the SQL engine to parse a statement, to execute it or to fetch from it.
Here you are talking about executing and fetching.
If you are doing INSERT, UPDATE or DELETE there is no fetching. You just execute. There is one "context switch". SQL does the work and returns.
In the special case of FORALL, you are actually doing one execution and sending an array of bind variables. There is only one "context switch", and if you look in V$SQL the "execution" count will increase by only one.
If you are doing SELECT, the number of "context switches" depends on how many fetches you do. You can fetch one row at a time or many rows at a time. Again, in V$SQL you can see the "fetch" count and the "rows" count increase by different amounts.
The only thing you cannot see by checking V*SQL is when the parse, the execution and the first fetch are all included together in one "context switch". I am sure PL/SQL does this whenever it is feasible.
That's not a bad description of a cursor, but I dislike the implication that the resultset is somehow matierialized in the cursor. A cursor is more like a program that tells the SQL engin how to get the rows from the database.
It is the "program", and the infomration about where it is in the program that is the "current state of the SQL statement" referred to in the description.
All SQL statements create a cursor, that is how the language works. The various attributes are available, but may not be exposed depending on the interface you are using. Sqlplus actually used the %rowcount attribute to display the X rows selected message after you run a query. You cannot use the cursor attributes in sqlplus because, by the time you get the prompt back after issuing a SQL statement the cursor opened for the statement has been closed by sqlplus, so there is no cursor to get the attributes from.
When you issue a SQL statement in PL/SQL, the statement is passed to the SQL engine which processes the entire statement. So, in the case of a delete statement, the SQL engine deletes all of the rows and passes back the information about the number of rows deleted to the PL/SQL engine, there is no communication between the two engines while the statement is being processed.
In the case of a cursor loop in PL/SQL in that the PL/SQL engine says to the SQL engine, open a cursor for me for this statement then give me back the results in batches of x rows when I ask for them. Each of the exchanges between PL/SQL saying give me another batch and the SQL engine providing that batch is a context switch. If the PL/SQL program then issues another SQL statement doing something with the rows it gets in the cursor, like inserting them into another table, or doing an update, then that too is a context switch.
These context swiches take time, so can substantially slow down processing.