kris, December 10, 2007 - 12:23 am UTC
Thank you so much for your time Tom.
Cursor ...
kris, December 10, 2007 - 1:08 am UTC
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
December 10, 2007 - 11:15 am UTC
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
kris, December 12, 2007 - 2:18 am UTC
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?
December 12, 2007 - 8:20 am UTC
... 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
Kris, December 12, 2007 - 9:26 am UTC
Thank you Tom.
Are Cursor session specific?
reader, December 24, 2007 - 4:48 am UTC
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
December 24, 2007 - 9:18 am UTC
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
noob, June 06, 2009 - 3:50 pm UTC
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!
June 08, 2009 - 1:03 pm UTC
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
salim memon, January 19, 2010 - 1:04 am UTC
Respected sir,
your answer about cursor is very fine.thanks u for explaining it very well.u r great.
bye
cursor
salim memon, January 19, 2010 - 1:09 am UTC
hi sir,
A cursor is just a handle to a DML statement.
is A cursor not a handle to a DDL statement?
January 19, 2010 - 4:30 pm UTC
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
satakare, January 21, 2010 - 11:57 am UTC
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.
January 21, 2010 - 12:09 pm UTC
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
Michael Tefft, January 22, 2010 - 10:03 am UTC
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
MemonSalim, January 29, 2010 - 4:24 am UTC
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
MemonSalim, January 29, 2010 - 4:31 am UTC
and sorry for using "u" keyword bye sir.
cursor with locks
James Su, February 01, 2010 - 3:48 pm UTC
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.
February 01, 2010 - 4:14 pm UTC
select for update will lock all rows before returning the first row, yes.
rman backup spfile
mahmadsalim, May 13, 2010 - 5:06 am UTC
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
Kangy, October 22, 2010 - 10:15 am UTC
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.
October 25, 2010 - 5:25 pm UTC
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
Mark Williams, October 26, 2010 - 1:15 pm UTC
> ...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.
October 26, 2010 - 8:17 pm UTC
hah, you VB programmer you :)
VB?
Mark Williams, October 27, 2010 - 11:16 am UTC
> hah, you VB programmer you :)
Oh, that's funny now! I can spell it I guess :)
Sonia, October 29, 2010 - 2:16 am UTC
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
November 01, 2010 - 5:04 am UTC
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.
Sonia, November 02, 2010 - 9:49 am UTC
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
November 02, 2010 - 11:34 am UTC
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
Saibal Ghosh, November 03, 2010 - 9:01 am UTC
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.
November 03, 2010 - 10:04 am UTC
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
Saibal Ghosh, November 03, 2010 - 10:40 am UTC
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?
November 04, 2010 - 12:49 am UTC
if you are using a cached result set - we already *have* the answer - not sure what you mean?
When does oracle generate execution plan ?
orel, November 03, 2010 - 6:54 pm UTC
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#585212100346945962 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 ?
November 04, 2010 - 3:10 am UTC
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
Saibal Ghosh, November 04, 2010 - 12:27 am UTC
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
Saibal Ghosh, November 04, 2010 - 1:20 am UTC
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.
November 04, 2010 - 4:01 am UTC
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.
Sonia, November 04, 2010 - 7:49 am UTC
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.
November 05, 2010 - 4:56 am UTC
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
Soumik Sen, May 24, 2012 - 5:53 am UTC
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
May 24, 2012 - 9:18 am UTC
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)
v$open_cursor
A reader, May 28, 2012 - 3:42 am UTC
Hi Tom,
Consider 'cursor' as a 'file handler' is so easy for us to understand it well.
I tried some testing, but still have some uncertainty need your kindly help.
# in session 1
update test2 set object_name=object_name||'b';
# in session 2
select sql_text from v$open_cursor where sid=33 order by 1;
-- i can see sql in session 1 here, beside there are many other stuff like below
select text from view$ where rowid=:1
select type#,blocks,extents,minexts,maxexts,extsize,extpct,u
table_1_ff_210_0_0_0
table_1_ff_210_0_0_0
# in session 1
insert into test2(object_name) values('aaa');
# in session 2
select sql_text from v$open_cursor where sid=33 order by 1;
-- i can see above 'insert' sql in session 1, but can not see the first 'update' sql anymore
My question is:
1> why after 'insert', 'update' disappeared from v$open_cursor?
2> is it reused? if so, how comes it can be reused as i have not neither 'commit' or 'rollback'?
3> what are those strange cursors? Are they for backgound recursive sql?
May 28, 2012 - 8:44 am UTC
you are using sqlplus, a very very very simple tool that doesn't do cursors the way you would do cursors in your program. sqlplus has a main "cursor" that is uses to process your sql, sort of like this:
open a cursor
loop
read text
if exit - then exit
execute it using that cursor
end loop
close cursor
so your next statement overwrites your last statement.
yes, these strange cursors are recursive sql, lob locators, object relational handles and the like.
Interesting
A reader, May 28, 2012 - 8:22 pm UTC
interesting about SQL*plus "main cursor" processing.
What would the processing look like if the above sessions
were using say Pro*C please Tom
May 29, 2012 - 6:23 am UTC
you tell me, you'd be writing the code.
It could be identical, you could write sqlplus in pro*c, or jdbc, or oci, or odbc, or ....
You, the programmer, control the cursors.
JC, July 04, 2012 - 4:56 am UTC
Hi Tom,
Quick question on joining to a cursor in a PL/SQL procedure. We're doing currency conversions in some of our ETLs. We have a function that looks up the most recent rate for a given day. Since we don't have rates for every currency for every day, we don't want to multiply amounts by that function as part of the transform. Instead, we want to pull up a set for a given day and then multiply by that value.
If we were only doing this in one ETL, we would select the results of this function into a cursor and multiply that way. But since lots of ETLs will need these, we were thinking of persisting pairs of currency and most recent rate into a table. It'll be about 500 pairs.
Question is: would it be better to have the ETLs join directly to this table in the main query or would it still make sense to essentially select everything from the distilled table and join to the table itself?
Thank you
JC
July 05, 2012 - 7:09 am UTC
I cannot say this with 100% degree of certainty since there is very little information here to go on, but....
But since lots of ETLs will need
these, we were thinking of persisting pairs of currency and most recent rate
into a table. It'll be about 500 pairs.
sounds reasonable.
and then use that in bulk sql with as little procedural code as humanly possible.
A reader, July 05, 2012 - 8:25 am UTC
Thanks Tom. Sounds like what you're saying is that we should be accessing the rates in these pairs by joining directly to the table (straight sql) rather than pulling them into a cursor and joining that way (procedural).
July 05, 2012 - 9:11 am UTC
yup.
Ananya, July 13, 2012 - 12:05 am UTC
Hi Tom,
I read the whole thread, but still I am confused about open/fetch. I have a procedure in which cursor is declared and within the Begin and End I opened the cursor and fetched the records and completed processing in loop.
when the procedure is executed,
1. the cursor will be allocated cursor area in UGA.(am i right?)
2. Open cursor will parse the query, binds variables, executes the query.
When the query is executed by Open Cursor, where will the result set reside? Is it in the memory?
3. Fetch cursor .. into ..-- what happens here?
I greatly appreciate your reply.
thanks
July 16, 2012 - 1:43 pm UTC
It depends. PL/SQL caches cursors open for us for performance reasons. It might already really be open, even if you have to say "open" in plsql.
When the cursor is 'executed' - it will definitely have a cursor area in the UGA, yes.
Open in plsql will parse and bind and start the execute. But - the execute probably will do *nothing*, no work at all. We don't need to do work until you fetch. And even then, we many times only need to do a tiny bit of work (we don't have to get the last row of a result set ready before we send you the first row!)
see
Cursor ... December 10, 2007 - 1am Central time
comment above for more info on that.
See this:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923#39255764276301 for the details on result set processing...
doro, October 16, 2012 - 9:54 am UTC
Hi Tom
1)Can i think at a cursor as a gateway for data from the buffer cache to private sql area of PGA?
2)You say "A cursor might allocate one or more workareas in the PGA memory in order to sort/hash/bitmap merge and such" Does it meens that the cursor is also responsable of creating thease memory area and filling them with fetched data? If for a sort operation oracle needs to spill data on disk is the cursor somehow involved in the process?
3)In performance tunning guide there is this statement at Configuring and using shared pool chapter " Shared Cursors -Reuse of shared SQL for multiple users running the same application, avoids hard parsing." and (speaking of CURSOR_SPACE_FOR_TIME ) "This parameter specifies whether a cursor can be deallocated from the library cache to make room for a new SQL statement. "
i'm lost here ... cursors in SGA ? and it dosent specify that its for shared servers proc
4) from the same book can you explain this "Library Cache Concepts -The library cache holds executable forms of SQL cursors " -- "executable forms" of sql cursor ?
Thanks
October 16, 2012 - 10:07 am UTC
1) no, not really.
the data might be in the result cache (shared pool) or buffer cache or disk (direct path reads) or thin air (pipelined functions) or a flat file (external tables) or a database link or... or or or or
think of a cursor as an executing instance of a program, a program that takes input and produces a stream of output. the cursor has a state (instruction pointer, state information). It has binary code associated with it (the row source generator). When you execute it, it starts running the program and the program starts churning out rows and those rows might have come from many different places.
2) the program that is behind the cursor does that. the compiled sql
3) they are talking about the shared pool - when we parse a sql statement, we look to see if someone has already done so in the shared pool. If they have, we do a soft parse and start running that already compiled program. If they have not, we compile your sql into a program and put it into the shared pool so the next person to run it can run what you've already compiled.
4) the program, the compiled representation of the sql statement, the bytecode for the SQL.
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2588723819082
Sort of like a coroutine.
Shannon Severance, October 16, 2012 - 1:28 pm UTC
"think of a cursor as an executing instance of a program, a program that takes input and produces a stream of output"
Sort of like a coroutine, with flow of control moving back and forth between the producer of rows which we call a cursor and the consumer of rows.
cursor
SUMIT, November 23, 2012 - 7:49 am UTC
Hello,
I am facing big problem with cursor ,i am selecting data from table into cursor and sending this cursor to java side.so some time java programmer are getting the data and some time not.and if we create type inside package body with table_name%rowtype and use then also we are facing same problem.
so finally my Manager told convert all cursor into type and we will be create type outside not in package .we are about to doing .i don't think so this is a right way to change code without any solid logic i don't know his experience is telling this way or what.
or java side is problem
if they don't get data then it's okey.but if some time is getting and some time not then .how to trace the problem.
please help me out...
November 29, 2012 - 7:15 am UTC
so some time java programmer are getting
the data and some time not
huh???? they get whatever the defining query returns - no more, no less. If they get nothing, that just means you ran a query that fetches no data.
give me an example - i have no clue what you are talking about, honest
reader, April 18, 2013 - 9:20 am UTC
Hi Tom,
would like to seek some advice. I encountered something odd in cursor with my code.
the code goes like this
----------------------
declare
cursor abc is
select colA, colB, colC
from tableJ
where colD >=1 and colD <=10;
begin
for i in abc
loop
select colX
into xx
from tableX where col = i.colA;
--few more select into statements
Calling Oracle API
end loop;
commit;
end;
/
--------------
the selection goes normally as i expected, values generated are correct. dbms_output of each select into variables are correct too.
the calling of oracle API to perform data update/insertion, is genuinely missing the first record of the cursor, and it works for the rest until the final record of the cursor within the same loop. cursor construction verified to be working too.
any potential checking i could perform to sort out this?
April 22, 2013 - 7:56 pm UTC
I would question why you are not joining tableJ to tableX first and foremost. JUST JOIN - it is what databases where born to do (that and sort data).
all of those select intos need to disappear, become part of the ONE query.
create a new procedure:
create table my_log_table( seq number, ts1 timestamp, ts2 timestamp, msg1 varchar2(xxx), msg2 varchar2(xxx) );
create or replace function log_data( p_msg in varchar2 ) return number
as
pragma autonomous_transaction;
l_seq number;
begin
insert into my_log_table( seq, ts1, msg1 )
values ( some_seq.nextval, systimestamp, p_msg )
returning seq into l_seq;
commit;
return l_seq;
end;
/
create or replace procedure update_log_data( p_seq in number, p_msg in varchar2 )
as
pragma autonomous_transaction;
begin
update my_log_table
set ts2 = systimestamp, msg2 = p_msg
where seq = p_seq;
commit;
end;
/
and create the log table. then you can:
declare
l_seq number;
begin
l_seq := log_data( 'interesting information.....' );
oracle_api
update_log_data( l_seq, 'some more interesting data...' );
end;
then, analyze away - of course you can be more structured than just msg1/msg2 - but whatever you need.
If you are calling Oracle api with some record - you'll have a committed log of it BEFORE it happens....
(i suspect an exception block somewhere in the 'real' code that is turning an error into "not an error" personally)
open cursor
Andrei Busuioc, July 22, 2013 - 10:13 am UTC
Hi Tom,
I have a simple question regarding cursors. I am curently studying for the Oracle 11g PL/SQL Programming Aprentice and I found the folowing question in a test demo:
Q: User A has a package, let's call it mypac, in which there is a cursor defined and 3 procedures, one that opens the cursor, one that closes the cursor and one that does a fetch on the cursor. User A grants execute on the procedure that does the reading to user B. Now user A cals the first procedure and so opens the cursor and then uses the second procedure to read 3 times from the cursor. At this point user B uses the same procedure(to which he was granted the execute privilege) to fetch from the cursor.
What output do the 2 users get(the procedure that does the fetch-ing from the cursor also prints it)?
The answer which was given (which I think is rong) was that user A prints the first 3 lines in the cursor and user B prins the first line in the cursor.
Shouldn't user B get an error since he hasn't opened the cursor first?
P.S. Sorry about the bad english. Tho I can read and speak properly I still suck at writing.
July 31, 2013 - 4:11 pm UTC
... User A grants execute on the procedure
that does the reading to user B. ...
user a cannot grant execute on that procedure, that procedure remember is in a package. user a can grant execute on the PACKAGE, period. mistake number 1.
user A calls the first procedure - in user A's session. The cursor is open in user A's session - no other session on the planet.
user B calls the fetch procedure, user B will get an error stating cursor is not open.
I'd be suspicious at this point...