Oracle library cache cursor child generation

This post is about library cache SQL cursors, and how these are managed by the database instance.

Whenever an Oracle database parses a SQL, it follows the following sequence of events (events which are of importance to the topic of cursor children):
1. The SQL text is hashed to generate a hash value.
2. The hash value is used to inspect the session cursor cache of the session to find a cached child.
If a cached entry is found, the child is inspected for compatibility, and then executed.
3. The hash value is used to calculate the library cache hash bucket.
4. The hash bucket is inspected, and the pointer is followed to find the parent handle.
5. In the parent handle a pointer is followed to the parent’s heap 0.
6. In the parent heap 0 a pointer to the hash table with the cursor children followed.
7. The hash table is read from the most recent child down to the oldest child.
If a compatible child is found, that child is executed.
8. If no compatible child is found, a new child is created.

I guess this is reasonably well known.

However, there are some nuances that are interesting to understand.

The first one is that with pluggable databases, a child has the container-id in which the child is generated as a property. During the inspection of the child list, the container-id of the child must match the container-id of the executing session, otherwise the child is skipped. This prevents from getting blocked from sessions across containers. Please mind the library cache hash table and the parent handle are shared between containers, although waiting for these should be negligible. See Oracle multi-tenant and library cache isolation.

Children are inspected from most recently created to the oldest. This is logical, more recent children have the highest probability to have been optimised with the most current state of the data.

Whenever there is no compatible child found, a new child must be created.

Incomplete list insertion.
The first thing that is done to create a new child, is an entry is allocated into the child list for the new child. This is done using the function kkshinins(), and a cursor trace will show this as:

kkshinins insert child into incomplete list bi=0x7ea05290 cld=21 flg=25

This is noteworthy, because to do so, the hash table (child list) mutex must be taken in exclusive mode. If a session wants to inspect the child list, it will briefly take the hash table mutex in shared mode. If the session creating the new child does find such a session, it will wait for ‘cursor: mutex X’. The other way around: if a session wants to inspect the child list, but find it taken by a session in exclusive mode, it will wait for ‘cursor: mutex S’.

I am not sure how to read ‘incomplete list’, and whether this means that the current child list is containing a child not yet complete, or hinting at a separate list of incomplete cursors. What I do know, is that the function kkshinins() inserts the new at this point not yet created (“incomplete”) child as the very last entry of the child list.

This means that at this point, if another session needs to find a compatible child, it will scan the child list and if no children are found compatible, it will find the incomplete child as the last one. At this point once the session finds the incomplete child, it has to wait for it, and will wait for ‘kksfbc child completion’.

Child load.
The session that is creating a new child will continue to create the new child, for which the next step is to allocate the child’s heap 0. This is done in the function kksLoadChild(). After the child’s heap 0 is allocated, it will pin the child in exclusive mode. Sessions that were waiting for ‘kksfbc child completion’ now will wait for ‘cursor: pin S wait on X’. Any other sessions that scanned the child list and did not find any compatible child, will run into this (last) child and wait for ‘cursor: pin S wait on X’ too.

There is a good reason the sessions wait for the child creation. If a session would not wait for the child creation, it would not have found any suitable child, because the incomplete child is the last one on the list, and therefore also would start creating a child. The reason this doesn’t happen is that in such case, there is a fair chance that both sessions would create identical children.

At this point, after the child cursor is pinned in exclusive mode, the session will continue to create the child and perform all the steps it needs to do during parsing. In fact, child generation is the actual thing that is considered ‘hard parse’. This includes syntax checking, semantic checking, execution path selection, optimization, creation of the executable version of the cursor (in heap 6) etc.

Incomplete list removal.
When the child creation is finished, the just created child is taken off the incomplete list (function kkshindel()), and inserted into child list as first entry (function kkshhcins()). This requires exclusive access to the child list once again, so when there is high concurrency, this might yield ‘cursor: mutex X’ for the session creating the child, and/or ‘cursor: mutex S’ for the sessions willing to scan the child list. A cursor trace shows:

kkshindel remove child from incomplete list bi=0x7ea05290 cld=21 flg=30
kkshhcins insert child into hash table bi=0x7ea05290 cld=21 flg=38

I am not sure if it’s a change in the child’s status (perhaps the ‘flg’ field shown in the trace?) being taken off the incomplete list, but what does happen is that the child after being taken off the incomplete list is inserted as first entry on the list of children. The next step is that the exclusive pin of this child is changed to shared. Any sessions that were waiting for child creation in ‘cursor: pin S wait on X’ now will continue and can inspect the child to check compatibility, and if found compatible execute the newly created child. If the child it was waiting for was not compatible, it has run out of children to check and the session will start creating a new child.

• child
• children
• cursor: pin S wait on X
• internals
• oracle
• parsing
• trace