Oracle PL/SQL for DBAs

Planning Cursor Use

It is good practice to limit the number of times a cursor has to be parsed—the optimal number of parses is one, of course. One option to achieve this ideal would be to preparse every possible cursor your application might ever execute. That way, every cursor would already be waiting in the shared pool when your application starts up. However, this approach would be extremely difficult to maintain in a large application or in one that allows ad hoc queries. Thus, it is better to take the hit the first time a cursor executes and then ensure that it is reused whenever possible later on.

Tip

Except where explicitly stated otherwise in this chapter, the CURSOR_SHARING parameter for all examples is set to EXACT. See the later section "Matching Algorithms" for a discussion of exact matching versus similar matching.

The following subsections explain how Oracle decides whether to reuse a cursor. This is the most important information to have when devising a plan for cursor reuse. Many PL/SQL developers are, unfortunately, blissfully unaware of this whole concept, so it is doubly important for DBAs to understand the use and consequences of cursor reuse. First, we’ll look at some details of Oracle’s hash algorithm, and then we’ll cover the nuances of cursor reuse. I encourage you to read this whole section before tackling any cursor reuse issues (real or perceived) in your applications.