This discussion is archived
4 Replies Latest reply: Nov 24, 2012 7:52 AM by John Spencer RSS

CURSOR

Oracle Maniac Explorer
Currently Being Moderated
from a book

"Once a connection is established, the next step is to open a cursorfor your SQL statements. A cursor is a
connection to a specific area in the Program Global Area (PGA) that contains information about a specific
SQL statement.


The cursor acts as the intermediary between the SQL statements your application submits and the actions
of the Oracle database as a result of those statements. The cursor holds information about the current state
of the SQL statement, such as the parsed version of the SQL statement. For statements that return multiple
rows of data, the cursor keeps track of where you are in terms of returning rows from the result set. This
important piece of information is also called a cursor, in that the cursor for a particular result set is the
pointer to the current row. But the cursor exists before any rows have been returned for a query, which is
why advancing the cursor to the next row at the start of retrieving data places the cursor on the first row
in the result set"

can anyone elaborate the line ". The cursor holds information about the current state
of the SQL statement, such as the parsed version of the SQL statement" & "For statements that return multiple
rows of data, the cursor keeps track of where you are in terms of returning rows from the result set."

Also , its in plsql only where we can access the cursor attributes ...such as rowcount ,notfound...etc ..not in sql ..Also , in plsql is it something like ..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...

These are very crucial topics but i seldom find any book ,pdf ,journals explaining the details...:(
  • 1. Re: CURSOR
    ranit B Expert
    Currently Being Moderated
    ..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.

    Refer -
    http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php

    Hope this Helps
    Ranit B.

    Edited by: ranit B on Nov 24, 2012 2:36 PM
  • 2. Re: CURSOR
    Oracle Maniac Explorer
    Currently Being Moderated
    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,
  • 3. Re: CURSOR
    Stew Ashton Expert
    Currently Being Moderated
    Rahul  K wrote:
    ...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...
    In your examples, a "context switch" is just PL/SQL calling the SQL engine.

    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.
  • 4. Re: CURSOR
    John Spencer Oracle ACE
    Currently Being Moderated
    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.

    John

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points