December 10, 2007 - 12am Central time zone
Reviewer: kris from Washington D.C
Thank you so much for your time Tom.
Cursor ...
December 10, 2007 - 1am Central time zone
Reviewer: kris
Hi Tom,
I just have two more question....
When a cursor is declared....does it occupy space in memory?
In case of queries, when 1 million records are retrived, are these stored in a cursor? does this
cursor occupy space to store 1 million records?
Thank you,
Kris
Followup December 10, 2007 - 11am Central time zone:
yes, it occupies space in memory - in fact, before it is declared, there is some memory allocated (init.ora parameter open_cursors has an impact on this, we allocate chunks of cursor handles at a time)
A cursor doesn't store data - in general, no data is touched until we need to touch it.
For example:
select * from ten_million_billion_row_table;
to open that query - instantaneous
to read from it - instantaneous
we read the data as you fetch it. Many queries are that way - we don't "store" or "copy" the data for your result set, we get it as you go along.
Some queries, like
select id, count(*) from ten_million_billion_row_table group by id;
that might have to answer the query in whole or in part before returning the first record, there we will use sort/hash areas in your UGA up to a limit and then start using TEMP (temporary tablespaces) to swap data out to disk.
Cursor in SQL
December 12, 2007 - 2am Central time zone
Reviewer: kris from Washington D.C
Hi Tom,
Based on the information presented at various places in your message board (and at other websites)
about cursors / SQL Processing, I have put together, what I understood about cursors. Can you
please read this and let me know if I understood correctly? I also have some questions.
A cursor is a name for a specific Private SQL area. A Cursor is created as soon as we create a
session. Private SQL areas only store bind variables, code for data type conversions, recursive
cursors, state of Parallel query and Sort Area (up to SORT_AREA_RETAINED_SIZE in UGA).
When I login to SQL- Plus, the SQL-Plus allocates a Cursor (in PGA, incase of dedicated Server) for
my Session. When I type a SQL statement like this in sqlplus:
Select * from dept where dept_no = 10;
Oracle does the following:
1) Checks for syntax and semantics.
2) Generate an execution plan
3) Checks the Shared pool if similar SQL has been executed before. If executed before by another
session, soft parsing comes into play or else it will be hard parse and a Shared SQL area is
created in Shared pool of SGA.
4) Associate my Cursor to SGA (shared pool)
5) The query is executed
6) The rows are fetched
Q 1) What is the role of Cursor in this case since there are no bind variables or there is no
sorting. Is there any need for associating my Private SQL area (or cursor) with Shared SQL area.
Had I issued a SQL statement such as :
Select * from dept where dept_no = :department_no
Q 2) If there are to pass 100 values to the bind variable department_no, will there be just one
value (the value that is currently being processed) in the cursor?
Q 3) In case of queries, when we are fetching in array processing, will groups of records (based
on the size of array) be stored in Cursor before sending it to client?
Followup December 12, 2007 - 8am Central time zone:
... A Cursor is created as soon
as we create a session. ...
cursors are created when a programmer asks for them to be created - by preparing a statement.
q1) a cursor is just a handle, just like a FILE handle in a program. You need *SOMETHING* to interact with. Cursors have nothing to do with binds or lack thereof, they are simply the only way you have to interact with the database. They are part of the programming api.
don't over analyze them, they are really rather simple things.
q2) does not make sense to me. Unless you mean like an array execute in pro*c or FORALL I processing in plsql. the binds are sent over to the server and the "cursor" is executed repeatedly with the different values. That is all we really need to know. The binds are sent to the server, the statement (eg: cursor) is bound/executed/bound/executed on the server and the client gets a message back at the end.
q3) don't think of it that way, you prepare a statement (set up a cursor), you bind it, you execute it (open it) and then you fetch from it, the data is fetched from the database into your server process - if you use a dedicated server the data is sent right back to you over the network. If you use shared server, the data is placed into the SGA and your dispatcher sends it back to you.
Curosr
December 12, 2007 - 9am Central time zone
Reviewer: Kris from Washington DC
Thank you Tom.
Are Cursor session specific?
December 24, 2007 - 4am Central time zone
Reviewer: reader from India
Hi Tom,
I have an oracle package in which i open a cursor.
The cursor processing takes around 1/2 an hour.
I close the cursor when the processing is over.
I have 2 jobs scheduled for that package.
Assuming 1st job is running..the cursor will be open and the processing is going on.
Q)Now if 2nd job begins for that package and it tries to open the cursor , will it get an error
"Cursor Alreay Open"
Regards,
Sourabh S Jain
Followup December 24, 2007 - 9am Central time zone:
no, each session has it's own "state"
think of plsql as you would any other programming language.
If you ran two copies of your code - would you expect variables in one execution to be changed by the other running bit of code?
Each session has it's own data segment, it's own copy of everything.
private sql area vs shared sql area
June 6, 2009 - 3pm Central time zone
Reviewer: noob from from singapore
hi tom,
i understand that a cursor is a private sql area.
and every sql statement is stored in the shared sql area.
i under that a private sql area might occupied space in the pga (if i am in dedicated mode)
and the shared sql area is always in the sga.
what i do not understand is,
what is the relationship between a private sql area and a shared sql area ?
when i declare a cursor - i created a private sql area.
then the sql (if new) is parsed and stored in the shared sql area.
(then what has my private sql area got to play a part in this)
is it possible to tell me how they work hand in hand for a simple scenario ?
thanks alot!
Followup June 8, 2009 - 1pm Central time zone:
the shared sql stuff is information all sessions can use - can read - at the same time. It is things like "the byte code that represents how to process the query (eg: the execution plan itself)".
A private sql area would have your bind variables and instruction pointers (where in the "byte code" that is the execution plan you are), they are private to you, no one else should see or use them.
Your private stuff points to the shared stuff (so as to not have it duplicated over and over and over)...
your answer about cursor
January 19, 2010 - 1am Central time zone
Reviewer: salim memon from India
Respected sir,
your answer about cursor is very fine.thanks u for explaining it very well.u r
great.
bye
cursor
January 19, 2010 - 1am Central time zone
Reviewer: salim memon from India
hi sir,
A cursor is just a handle to a DML statement.
is A cursor not a handle to a DDL statement?
Followup January 19, 2010 - 4pm Central time zone:
technically, yes, we use a cursor for DDL too - but you don't really see it. DDL is conceptually different from DML, you typically execute DDL once and don't need a handle to it at all.
Querying Cursor
January 21, 2010 - 11am Central time zone
Reviewer: satakare from INDIA
1) We have opened cursor cur_abc for select * from BillionRowTable with some filtering and got
thousands of rows.
2) Can we query this cursor cur_abc using Select query and get data into another cursor.
The reason behind this is to skip the select hit again to that billionRowtable.
Followup January 21, 2010 - 12pm Central time zone:
no, you would roll it all into a single query to retrieve all of the data you needed in one statement - rather than two.
Consider a GTT
January 22, 2010 - 10am Central time zone
Reviewer: Michael Tefft from Cleveland, OH USA
You might consider changing the 'open cursor' to an Insert to a Global Temporary Table, then
opening as many cursors as you need against that GTT.
cursor
January 29, 2010 - 4am Central time zone
Reviewer: MemonSalim from India
Thank You sir,You have solved one of the my problem.my god keep You healthy so that You can share
your knowledge.Bye sir
cursor
January 29, 2010 - 4am Central time zone
Reviewer: MemonSalim from India
and sorry for using "u" keyword bye sir.
cursor with locks
February 1, 2010 - 3pm Central time zone
Reviewer: James Su
Hi Tom,
If the cursor requires a lock, will it try to lock all the rows before fetch?
For example:
CURSOR c_test IS select id from test for update;
My test shows when some rows were locked by another session, then "open cursor" will get blocked
before fetch.
Followup February 1, 2010 - 4pm Central time zone:
select for update will lock all rows before returning the first row, yes.
rman backup spfile
May 13, 2010 - 5am Central time zone
Reviewer: mahmadsalim from india
Respected sir,
first problem when i backup of spfile in oracle 9i it give error.i used command as
under
RMAN>BACKUP SPFILE;
-----------------------------
and second problem is that
i set automatic controlfile backup on and whenever i use follwoing comman then
RMAN>BACKUP DATABASE;
it backups all the database and controlfile but not include backup of spfile. can You tell me why
in both case backup is not done?
i have started my instance with spfile;
please sir, reply me i am eager to know .
bye sir
Cursor
October 22, 2010 - 10am Central time zone
Reviewer: Kangy from India
Here is a C Program to deal with files.
1. FILE *fptr;
2.
3. fptr = fopen('C:\asd.txt','r'); /* Opens a slot in the
open file descriptor table */
4.
5. while(getc(fptr)) != EOF) /* get a character from file
also advances the seek
pointer in the open file
table for the file*/
6. {
7. /* Do something */
8. }
9.
10. fclose(fptr) /* removes the slot
from the file table */
Similar to above C program,
Open a cursor is to allocate a small amount of memory in the dedicated server process to hold bind
variable values and state information
Server process receives the Fetch (1st fetch) command from client process (sqlplus) and starts
accessing the blocks from SGA and extract the rows from the blocks. If the no. of rows extracted =
arraysize (sqlplus), it returns the row to sqlplus and wait for the next fetch command.Server
process also updates the Private SQL area (pointed by cursor) with the last accessed block
information.
Server process receives the 2nd Fetch command from the sqlplus and gets the last acccessed block
from private SQL area and start processing until it gets next set of result and return the result
to sqlplus.
That means, cursor is a handle which points a small chunk of memory in which the state information
for a query is stored. The state information includes the last block accessed. Please correct me if
my understanding is wrong.
Followup October 25, 2010 - 5pm Central time zone:
it is an analogy I have used myself - less and less these days as the chances of bumping into a fellow C programmer seems to decrease exponentially year after year.
< && < C
October 26, 2010 - 1pm Central time zone
Reviewer: Mark Williams from The Midwest, USA
> ...less and less these days as the chances of bumping into a fellow C programmer seems to
decrease exponentially year after year.
That's depressing however true it may be.
Followup October 26, 2010 - 8pm Central time zone:
hah, you VB programmer you :)
VB?
October 27, 2010 - 11am Central time zone
Reviewer: Mark Williams from The Midwest, USA
> hah, you VB programmer you :)
Oh, that's funny now! I can spell it I guess :)
October 29, 2010 - 2am Central time zone
Reviewer: Sonia from India
Hi Tom
Thanks
I have few questions.
Take the example of the following query in a dedicates server.
Select * form employee;
Say I open sqlplus and execute the above query.
When a sqlplus session is opened in a client side, a process is created in server side for this
sqlplus session.
A PGA is assigned to this process.
There is another memory type- UGA, that resides in the PGA and used for user requests.
Question1: who executes the query?
the dedicated process
or
the dedicated process takes help of some ohter oracle instance process?
Question2: After execution of the above query the result will be stored in UGA/PGA of the dedicated
process
or
the dedicated process directly fetch the data from SGA and send it to client sqlplus
Regards
Sonia
Followup November 1, 2010 - 5am Central time zone:
q1) the dedicated server does the brunt of the work. dbwr might be involved as it might have to make space in the buffer cache for the dedicated server. lgwr might be involved because it might have to write out some redo for the query. smon might be involved because maybe the query crashes and the dedicated server needs to be clean up after...
but the dedicated server could be said to "be doing the work" safely - without being terribly wrong.
q2) probably it won't be stored *anywhere*. For that simple of a query - we would almost certainly just read from the buffer cache and return to the client. we can easily retrieve the data as we need it.
November 2, 2010 - 9am Central time zone
Reviewer: Sonia from India
Thanks Tom.
Very useful information for me.
I have a questions --
Suppose employee table contains million of records.
say i write the following code
declare
cursor cur_1 is
select * from employee;
begin
for i in cur_1
loop
........some pl/sql and sql code......
end loop;
end;
----
what I know is---
a cursor named cur_a is open in UGA. This is the pointer to a private sql area?
question. What is this private SQL area? Is this private SQL area resides in the SGA or UGA?
Regards
Sonia
Followup November 2, 2010 - 11am Central time zone:
private would be in your session memory - the uga, the uga might be in your pga (dedicated server mode - you have your own process) or the sga (shared server mode - you share a process with many other sessions and might use many different processes).
this private area is your "context", the set of data needed to process your invocation of that query. Things like "where in the result set we are" would be there.
Context Area in PGA
November 3, 2010 - 9am Central time zone
Reviewer: Saibal Ghosh from India
Tom,
By context area in the PGA, are you referring to the runtime area or persistent area? Or both?
Am I correct in making the following assumptions?
1.Say,the client issues a 'select * from emp';
2. The application (or interface) makes a parse call to the database during which:
-The statement is checked for syntactic correctness.
-The statement is checked for semantic correctness.
-Checks whether the user submitting the statement has the
necessary privileges to run the statement or not.
-Checks whether a parsed version of the statement exists
in the shared pool or not.
-Allocates a Private SQL area in the PGA--the runtime
area.
3. Assuming a parsed version of the statement was not found
in the shared pool, the statement is converted into an
ASCII value, whereupon a hashing algorithm is applied on
the ASCII string to convert it into a hashed value.
4. It then places the cursor for it on a hash chain in the
shared pool.
5. If the statement has already been parsed and executed,
and the cursor handle is still in the client cache, the
cursor may be called and executed without actually
having to search the shared pool for the statement. And
in that case the execution plan in the cursor handle is
used to generate the I/O to fetch copies of the block in
the buffer cache.
6. The resultset generated is routed into the PGA via the
SGA, for any sorting etc. to be worked on it.
7. Finally, the sorted and otherwise consistent resultset
is displayed to the user.
Followup November 3, 2010 - 10am Central time zone:
the context in your pga is YOUR CONTEXT, your "state", where you are in YOUR query. It is your private stuff used to process your occurrence of your query.
1) ok
2) ok
3) the statement needed no converting, it was already a string - it was whatever was submitted to be parsed.
if a parsed version was not found then we optimize and row source generate (compile the plan into byte code to be run by a SQL virtural machine). We take the row source generated object and write it into the shared pool.
4) just say "place it into the shared pool - into v$sql"
5) we would have skipped much of 1-4 already if this was true, this is session cached cursors and would have preempted #2.
6) maybe - maybe not. we don't have to execute all of a query before returning the first row - in most cases we don't.
Result Cache
November 3, 2010 - 10am Central time zone
Reviewer: Saibal Ghosh from India
Thanks a lot for the answer Tom. Just one clarification needed:
You have written: 'maybe - maybe not. we don't have to execute all of a query before returning the
first row - in most cases we don't.'
Does what you say above apply if we are using a Result Cache as well?
Followup November 4, 2010 - 12am Central time zone:
if you are using a cached result set - we already *have* the answer - not sure what you mean?
When does oracle generate execution plan ?
November 3, 2010 - 6pm Central time zone
Reviewer: orel from France
Hi tom,
Considering the previous steps described by kris on December 12, 2007 - 2am :
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:580621300346132183#585212100346945
962
1) Checks for syntax and semantics.
2) Generate an execution plan
3) Checks the Shared pool if similar SQL has been executed before. If executed before by another
session, soft parsing comes into play or else it will be hard parse and a Shared SQL area is
created in Shared pool of SGA.
You didn't comment about these steps but I am quite surprised to see on step 2 "Generate an execution plan".
For me, due to bind variable peeking (especially before 11GR2) oracle doesn't generate a new plan till it didn't check for similar parsed SQL statement.
Saibal Ghosh just wrote :
The application (or interface) makes a parse call to the database during which:
-The statement is checked for syntactic correctness.
-The statement is checked for semantic correctness.
-Checks whether the user submitting the statement has the
necessary privileges to run the statement or not.
-Checks whether a parsed version of the statement exists
in the shared pool or not.
-Allocates a Private SQL area in the PGA--the runtime
area.
I don't see plan generation before checking the shared pool and as you agreed about that step, I guess step "Generate an execution plan" wasn't at the right place.
Could you confirm that point please ?
Followup November 4, 2010 - 3am Central time zone:
I don't edit everything - I just jumped to his questions that time.
the steps would be typically for a select:
programmer calls prepare statement
oracle checks syntax
oracle checks semantics (resolves name)
programmer binds
programmer executes
oracle hashes original sql text and searches shared pool for existing statements
if found then use that plan
else
generate execution plans
find lowest cost one
generate row source compiled binary plan for that one
place into shared spool
use that plan
end if
Plan Generation
November 4, 2010 - 12am Central time zone
Reviewer: Saibal Ghosh from India
Hi Orel,
I am sure Tom would agree that there is no generation of execution plans before a shared pool
check. In fact what happens is that, assuming a shared pool match wasn't found, the parsed version
of the query is transformed into query blocks through the TRANSFORMER, the input from the
transformer is taken by the ESTIMATOR to estimate the overall cost of a given plan based on
available statistics, selectivity and cardinality of the statement etc.
The inputs of the estimator is taken by the PLAN GENERATOR. The main function of the plan generator
is to test different possible plans for a given query and
pick the one that has the lowest cost. Since many different plans are possible because of the
various combinations of different access paths, join methods, and join orders that can be used to
access and process data in different ways and produce the same result, Oracle doesn't wait to get
at the 'absolute best plan'. Oracle just picks a plan that looks good enough to it and moves on.
The SQL statement processing phases would look something like this:
OPEN===>PARSE===>BIND===>EXECUTE===>FETCH===>CLOSE
HTH
Result Cache
November 4, 2010 - 1am Central time zone
Reviewer: Saibal Ghosh from India
Tom,
Re: Result Cache above.What I was trying to understand is that since you have written
'....we don't have to execute all of a query before returning the first row - in most cases we
don't.'
my question is if you are using result cache in Oracle 11g, then the entire result set first gets
stored in the RESULT CACHE in the shared pool and then it gets displayed to the user or it gets
displayed in a piecemeal manner, as you seem to imply in the quote.
In other words will
select * from emp follow the same sequence of steps as
select /*+ result_cache */ * from emp; ?
As usual, thanks a lot for your help.
Followup November 4, 2010 - 4am Central time zone:
of course they won't follow the same exact sequence of steps since one uses the result cache and the other does not.
I don't see how my quote implies how the result set cache works - or not. I said
we do not have to execute all of a query before returning first row - IN MOST CAESES (which means most or many - specifically not ALL) we don't
I wasn't commenting on the result cache at all. However, even with the result cache - we don't have to get the entire result set before returning the answer.
November 4, 2010 - 7am Central time zone
Reviewer: Sonia from India
Hi Tom
Thanks
So may I conclude the following:
In case of dedicated server
1. if we execute a query
select * from employee;
In this case the dedicated server process fetches the records (with the help of other background
processes) from SGA only and send it to the client (sqlplus) directly.
In this case the records are never copied to UGA.
2. If we execute a PL/SQL block like
declare
cursor cur_1 is
select * from employee;
begin
for i in cur_1
loop
........some pl/sql and sql code......
end loop;
end;
In this case a cursor (a pointer to a UGA memory location ) is opened and the cursor points to a
memory location in UGA. That means the cursor, and the memory location (cursor poits to this memory
location) both are in UGA. We can call this UGA memory location as private memory location. First
of all Dedicated server process fetches all the records from SGA to UGA (specifically in the
private sql area). And after all the records are fetched from SGA to UGA the PL/SQL loop is
executed.
Followup November 5, 2010 - 4am Central time zone:
1) maybe - probably - 100% assured - no, but most likely.
employee could be a view.
select * from employee might have a query rewrite rule written against it.
but if employee were just a regular old table - probably - the data would be fetched from the buffer cache (or disk into buffer cache if need be) AS the client requested it. Since the UGA is in the PGA in dedicated server mode, it really isn't relevant to say whether or not it is in the UGA - it hits the PGA.
2) the cursor in this case is just a "thing" (data) in your UGA. Don't get all wrapped up in pointers and this that and the other thing - keep it simple, keep it conceptual. You open a cursor and something in your UGA (data) is set up to manage that cursor state.
PL/SQL is just a client of the database like Java outside the database would be. It is not really any different than case #1 would be. we do not have to fetch all of the data into anything before giving plsql the first record. This query would be processed just the same as #1 would be - they are no different.
Cursor
May 24, 2012 - 5am Central time zone
Reviewer: Soumik Sen from India
Tom,
Will it be fair to say that cursor is certainly not an array which will store data , but certainly
has the visiblity of the datas to be fetched back to user after the cursor is "opened" or
"executed" and Private SQL area simply store "where are the cursor currently pointing to " during
the fetch of data .?
I had the understanding that bind informations are store in the heaps of chilld cursor in library
cache , not in private sql area .Can you kindly confirm?
Will like to know on persistent mem and runtime mem in v$sql if there is any relationship of these
two terms with cursor at all
Many Thanks,
Soumik
Followup May 24, 2012 - 9am Central time zone:
a cursor is like a "file" in a programming language. A "file" in C or Java is just a pointer to a structure that tells you where you are in the real file on disk.
A cursor is just a pointer to a data structure that tells us where we are in processing a query. If you have a cursor such as "select * from ten_billion_row_table" - and you open that cursor and fetch just the first row - we'll have done a single IO against that table and it'll return instantly.
A cursor might allocate one or more workareas in the PGA memory in order to sort/hash/bitmap merge and such - but the entire result set isn't really stored anywhere in general (well, with result set caching, it might be stored in the shared pool,, but that is different)
|