Method and system for implementing efficient cursor pruning

A computer implemented method for managing cursors in a database system, comprising:

• using at least one processor to perform a process, the process comprising:
• accessing a structure which is used to track sharable cursors in the database system, wherein
• the structure includes a diagnostic node associated with a child cursor;
• analyzing the structure to determine whether the child cursor can be re-used to execute a query, wherein the diagnostic node is checked against a sharing criteria for executing the query to identify any mis-matches for the sharing criteria;
• re-using the child cursor where the mis-matches are not identified;
• and
• creating a new child cursor if the mis-matches are identified and updating the diagnostic node to include information regarding why the child cursor is not sharable for executing the query or why the new child cursor is created instead of re-using the child cursor to execute the query based at least in part upon a determination that the child cursor is not to be shared for executing the query.

A computer program product embodied on a non-transitory computer usable medium, the non-transitory computer readable medium having stored thereon a sequence of instructions which, when executed by a processor causes the processor to execute a method for managing cursors in a database system, the method comprising:

• creating a structure to track sharable cursors in the database system, wherein
• the structure includes a diagnostic node associated with a pre-existing child cursor;
• and
• updating the diagnostic node to include information regarding why the pre-existing child cursor is not sharable for executing a query or why a child cursor was created instead of re-using the pre-existing child cursor to execute the query based at least in part upon a determination that the pre-existing child cursor is not to be shared for executing the query.

A computer program product embodied on a non-transitory computer usable medium, the non-transitory computer readable medium having stored thereon a sequence of instructions which, when executed by a processor causes the processor to execute a method for managing cursors in a database system, the method comprising:

• accessing a structure which is used to track sharable cursors in the database system,
• wherein
• the structure includes a diagnostic node associated with a child cursor;
• analyzing the structure to determine whether the child cursor can be re-used to execute a query, wherein the diagnostic node is checked against a sharing criteria for executing the query to identify any mis-matches for the sharing criteria;
• re-using the child cursor where the mis-matches are not identified;
• and
• creating a new child cursor if the mis-matches are identified and updating the diagnostic node to include information regarding why the child cursor is not sharable for executing the query or why the new child cursor is created instead of re-using the child cursor to execute the query based at least in part upon a determination that the child cursor is not to be shared for executing the query.

A computer-based system for managing cursors in a database system, comprising:

• a computer processor to execute a set of program code instructions;
• a non-transitory memory to hold the program code instructions, in which the program code instructions, when executed by the computer processor, cause the computer processor to:
• create a structure to track sharable cursors in the database system, wherein
• the structure includes a diagnostic node associated with a pre-existing child cursor, and
• update the diagnostic node to include information regarding why the pre-existing child cursor is not sharable for executing a query or why a child cursor was created instead of re-using the pre-existing child cursor to execute the query based at least in part upon a determination that the pre-existing child cursor is not to be shared for executing the query.

A computer-based system for managing cursors in a database system, comprising:

• a computer processor to execute a set of program code instructions;
• a memory to hold the program code instructions, in which the program code instructions comprise instructions which, when executed by the computer processor, causes the computer processor to:
• access a structure which is used to track sharable cursors in the database system, wherein
• the structure includes a diagnostic node associated with a child cursor, and
• the diagnostic node comprises information regarding why the child cursor is not sharable or why the child cursor was created instead of re-using a pre-exiting child cursor;
• and
• analyze the structure to determine whether the child cursor can be re-used to execute a query, wherein
• the diagnostic node is checked against a sharing criteria for executing the query to identify any mis-matches for the sharing criteria;
• re-use the child cursor if the mis-matches are not identified;
• and
• create a new child cursor if the mis-matches are identified and update the diagnostic node to include information regarding why the child cursor is not sharable for executing the query or why the new child cursor is created instead of re-using the child cursor to execute the query based at least in part upon a determination that the child cursor is not to be shared for executing the query.