Thanks for the question, Sandeep.
Asked: June 23, 2016 - 10:59 am UTC
Answered by: Connor McDonald - Last updated: June 27, 2016 - 12:40 am UTC
Category: Database - Version: 18.104.22.168.0
Viewed 1000+ times
I am going through the book "Troubleshooting Oracle Performance" by Christian Antognini.
The definition below are confusing.
Open cursor :- A memory structure for the cursor is allocated in the server-side private memory of the server process associated with the session.Note that No SQL statements are associated with the cursor yet.
Then he is also saying the below.
Some memory is allocated from the library cache, and a new parent cursor is stored inside it. The key information associated with the parent cursor is the text of the SQL statement
Some memory from the library cache is allocated for the child cursor. The key information associated with the child cursor are the execution plan and the execution environment.
The link below has the same description which is present against "Open cursor" above. https://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT1237
SO if the parent and child both are getting created in Library Cache, I dont understand what is the role of this "grand parent cursor" present in PGA.
Could you please help understanding.
Thanks and Regards,
and we said...
The term cursor covers a lot of things, but try to think of what's "needed" to let you run a SQL statement:
1) the SQL itself, plus metadata about it. So we'll have that in the library cache as a parent cursor.
2) Now *you* run that SQL with (say) optimizer_features_enable = 11.2, and *I* run that SQL with (say) optimizer_features_enable = 12.1. The choice of parameter is not relevant, its more just that the *same* SQL might actually be run in different ways under different circumstances. Similarly, "select * from emp" for you, might be different for me, if (for example) I have a private synonym called "emp" which points to something else.
So we may have many child cursors under the parent, representing that different ways the parent SQL might need to be handled by different users, sessions etc. The database itself may create children because after one execution, we might decide we need a new child next execution to come up with a better plan etc.
3) But let's say for simplicity, you and I have all our settings the same, so we are both running the same child cursor. We *still* need to have our *own* concept of what we are *doing* with the cursor. I may have just opened it, whereas you may have already fetched 10 rows from it. So we need some private metadata in our own session PGA to manage *our* current state for that cursor. We also need some memory to actually get rows from it and return them to the client.
Hope this helps.
and you rated our response
Is this answer out of date? If it is, please let us know via a Review