How to update records without invalidating cursor oracle

02 Jan

When you have the same statement that has several versions (children), the view v$sql_shared_cursor shows the reason why the statement cannot be shared.

You may be able to find that for each child cursor except the first one, why it was not possible to share a previously created child cursor.

Note that this attribute can drastically affect the performance of a query since it directly affects the number of network round trips that need to be performed.

The information presented in this article is either missing or inadequately described in the documentation when it refers to these commonly used objects.But it won’t be so and two parent and hence two child cursors will be created since the two statements are textually different .If we have two textually identical statements, only one parent cursor will be created but multiple child cursors and hence multiple execution plans can be created if for example bind variables have different values/sizes for different executions of the same statement.The columns OWNER and OBJECT_NAME of DBA_OBJECTS are self-explanatory.Although the OBJECT_NAME is documented to be a VARCHAR2 column of length 30 characters (even in the Oracle Database 11g Documentation), since at least Oracle8i, this column has been of data type VARCHAR2 with a length of 128!