Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

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: 11.2.0.4.0

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Programmers are Humans, Too - How to Get Crusty Developers to Change

You Asked

Hi Connor

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,
Sandeep

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

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

June 24, 2016 - 10:55 am UTC

Reviewer: Sandeep Mishra

HI Connor,

Thank You.

The below concepts are clear.
1] Parent Cursor in library cache
2] Child cursor is library cache
3] Environmental change which leads to new children

But the relationship between cursor present in PGA and rest of the cursors are not clear.

Parent and Child present in LC
So the cursor present in PGA stores a pointer to parent cursor??

Not considering all possible scenarios, and focusing on a single user 'select * from emp', could you please help understanding the information flow from PGA cursor till the child cursor and what information travels back?

Thanks and Regards,
Sandeep




Connor McDonald

Followup  

June 25, 2016 - 3:17 am UTC

Probably the best diagram is in the docs themselves

http://docs.oracle.com/database/121/CNCPT/img/GUID-B71BA462-E5D5-4AB5-90BE-6E7668469CF2-default.gif

which comes from here

http://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT007

June 25, 2016 - 4:44 pm UTC

Reviewer: A reader

Hi Connor,

Thank You.

So can I summarize as the below ??

1] A session is established
2] A query is to be executed
3] A dedicated server process gets created
4] Some non-sharable memory is allotted [PGA]
5] The PGA contains session information
6] The PGA contains bind variable values
7] The PGA contains information about the number of rows retained so far for a full table scan
8] Sorting happens in PGA
9] Merging happens in PGA
10] PGA Contains a pointer the parent cursor [parsed query] in Library Cache
11] Child cursor in the Library cache has the execution plan
12] By another session, if the same query is fired but with a change in the literal (upper-case / lower-case), that is with the present of a parent cursor one more child cursor will be created.

Could you please confirm?

Thanks and Regards,
Sandeep

Connor McDonald

Followup  

June 27, 2016 - 12:40 am UTC

All good except 12, which just requires a little clarification.

It's not necessarily another session (although it could be). It is anything that means the current child cursor(s) are not appropriate for the execution just requested.

So it could session parameters, the names in the query resolve to different objects, even the lengths of bind variables. The majority of the potential reasons are described here

http://docs.oracle.com/database/121/REFRN/GUID-4993A6DE-5658-4745-B43E-F5AD9DB8DCCC.htm#REFRN30254