Intelligent open query cursor management

摘要

An enhanced mechanism for intelligently managing an open query cursor built from a query access plan (AP) by a query optimizer of a database management system (DBMS). In an embodiment, a cursor management system (CMS) detects whether an open query cursor was previously requested for use within another job. If the open query cursor was previously requested, the CMS intelligently manages the query cursor for subsequent use within a different job. For example, the open query cursor may be recycled for subsequent use within the different job. Hence, system resources are not wasted in building and destroying the same cursors for multiple jobs. Alternatively, the query cursor may be pre-built before the cursor is requested for subsequent use within the different job. The CMS may also obtain, release and re-obtain locks for cursors being managed. Alternatively, the job that uses the cursor may obtain the lock.

主权项

1. A computer-implemented method for intelligent open query cursor management, comprising the steps of:
opening a query cursor for use within a first job; detecting whether the open query cursor was previously requested for use within another job, wherein the step of detecting whether the open query cursor was previously requested for use within another job includes the steps of:
keeping a log of at least one of recent requests and/or most populous requests;determining whether the log indicates that the open query cursor has been requested at a predetermined threshold level; andupdating the log to include an entry corresponding to the open query cursor; and if the open query cursor was previously requested for use within another job, intelligently managing the query cursor for subsequent use within a different job, wherein the step of intelligently managing the query cursor for subsequent use within a different job includes either the step of recycling the open query cursor for subsequent use within the different job or the step of pre-building the query cursor before it is requested for subsequent use within the different job, and wherein the step of intelligently managing the query cursor for subsequent use within a different job further includes the step of periodically rechecking the log to see if the saved query cursor or the pre-built query cursor continues to be requested at the predetermined threshold level, wherein the step of determining whether the log indicates that the open query cursor has been requested at a predetermined threshold level includes the step of determining if the open query cursor was requested at least a predetermined number of times within a predetermined period of time and/or the open query cursor was requested at least a predetermined percentile with respect to a total sample of requests.