[Oracle] The lifecycle of a cursor

A good knowledge of the life cycle and the functioning of cursors will allow you to optimize applications running SQL queries.

The steps in the execution of a cursor are:


Opening a cursor: memory is allocated for the cursor in the private memory of the server process (on the server side) associated with the session (UGA User Global Area). Note that no SQL query is associated with the cursor for now.

Cursor path: an SQL query is associated with it. The representation of the route of the request, including its implementation plan, which describes how the SQL engine executes the SQL statement is loaded into the Shared Pool, in the library cache. The structure in the UGA is updated to store a pointer to the area associated with the cursor in the library cache.

Definition of output variables: the SQL query returns data, so the variables related to the must be defined. This definition is also required for DELETE, INSERT and UPDATE requests (using the RETURNING clause).

Linking input variables: if the SQL query uses variables, they must be provided. No verification is done at the link. If invalid values are passed, a runtime error will be triggered.

Execution of the cursor: at this stage, the SQL is executed. Depending on the database engine, the phase of actual implementation may be deferred at the time of fetch.

Fetch cursor (recovery of content): If the SQL query returns a result,it is recovered. In general it is at this stage that treatment occurs.

Closing the cursor: the cursor associated with the resources at the UGA are automatically released. The cursor in the library cache is not deleted in response to possible future uses.