The explicitly programmed cost-saving paradigm

It is assumed that readers have a general familiarity with the semantics of the Dbms_Sql API.

Suppose an application has to execute this statement...

select last_name from employees where department_id = :department_id

...on behalf of the user, repeatedly for different values of :department_id. The recommended program design looks like this...

task	Dbms_Sql calls
set up for the statement and do the the define-binding	Open_Cursor Parse Define_Column*
for each :department_id    do the in-binding and    establish the read-consistent snapshot for the query	Bind_Variable* Execute
loop until no more data       fetch the next row    end loop	Fetch_Rows Column_Value*
when no more :department_id values free the resources used by the cursor	Close_Cursor

The * means that this call may be made several times to process one SQL statement.

To test this, create two schemas programmer and observer.

Now create the package Employees_Cur_Dbms_Sql which exposes this API: Set_Up_Statement, Fetch_All_Rows and Free_Resources.

The costs associated with the Dbms_Sql calls are thus...

• Open_Cursor - memory allocation and initialization
• Parse searching in the v$sqlarea cache to match the current SQL statement; if not found, parsing the statement, calculating the execution plan and storing this in the cache; marking the slot in the cache as in use; storing a reference to the slot in the v$sqlarea cache in the cursor.
• Define_Column - for each column in the select list. Relatively trivial
• Bind_Variable - for each bind variable in the where clause. Relatively trivial
• Execute - establishing the read-consistent snapshot for the query. Possibly materializing the whole result set and sorting it
• Fetch_Rows and Column_Value - Possibly memory-to-memory copy from materialized and sorted results. Possibly periodic disk reads
• Close_Cursor - Doing whatever it takes to make the memory this cursor used available for new use. Marking the SQL statement in v$sqlarea cache as no longer in use by this cursor

The rationale for the cost-saving paradigm is that there is no need to incur the costs of Open_Cursor, Parse, Define_Column and Close_Cursor more than once when the only difference between successive SQL statements is the values of the in-bind variables. Saving these costs can deliver a significant performance benefit. (Of course the costs of Bind_Variable, Execute, Fetch_Rows and Column_Value must be re-incurred for each new value of :department_id.)