In this chapter, you'll briefly look at how Oracle processes SQL statements and then start your journey into the world of statements in JDBC. JDBC statements provide a mechanism for creating and executing SQL statements to query and modify the data stored in a database. As a quick introduction, JDBC offers the following flavors of statement interfaces:

• Statement: A Statement object lets you execute SQL statements, but does not allow you to vary input variables to the statement at runtime. An example of using this interface appears in Chapter 3.
• PreparedStatement: A PreparedStatement object represents a precompiled SQL statement that can be executed multiple times. It extends the Statement interface and adds the ability to use bind variables. Bind variables are parameter markers represented by ? in the SQL string, and they are used to specify input values to the statement that may vary at runtime.
• CallableStatement: This interface extends PreparedStatement with methods to execute and retrieve results from stored procedures.

You can browse the javadoc API for these and other JDBC classes and interfaces at http://java.sun.com. In this chapter, we'll focus on the Statement and PreparedStatement interfaces and their Oracle extensions. We'll cover CallableStatement and its Oracle extensions in the next chapter. By the end of this chapter, I hope to convince you that, in production code, you should always use PreparedStatement (or CallableStatement) objects instead of Statement objects. In fact, in the next chapter, I make a strong case for almost exclusively using CallableStatement in production code.

Before starting the discussion of the Statement objects, let's take a quick look at how Oracle processes SQL statements submitted by a client (through SQL*Plus, a JDBC application, etc.). This information will be useful in helping us arrive at certain performance-related conclusions later in this chapter.

Overview of How Oracle Processes SQL Statements (DML)

For this discussion, we only consider Data Manipulation Language (DML) statements. In particular, we exclude Data Definition Language (DDL) statements, as these are typically (and should be) done at install time and are not part of the application code.

DML statements are the statements that you will encounter most often, as you use them to query or manipulate data in existing schema objects. They include select, insert, update, delete, and merge statements. Oracle goes through the following stages to process a DML statement:

• Parsing: In this step, the statement's syntax and semantics are parsed.
• Generating an execution plan: For each statement, an execution plan is generated and stored in a shared memory area called the shared pool (see the section "Memory Structures: Shared Pool" in Chapter 2).
• Executing: The statement executes, using the plan generated in step 2.

Step 2, generating the execution plan, can be very CPU-intensive. To skip this step in most cases, Oracle saves the results of the execution plan in a shared memory structure called the shared pool (see the section "Shared Pool and Bind Variables" of Chapter 2). When you submit a statement to Oracle, as part of the first step of parsing, it checks against the shared pool to see if the same statement was submitted by your session or some other, earlier session. If Oracle does not find the statement in the shared pool, it has to go through all three steps. This phenomenon is called a hard parse. On the other hand, if Oracle gets a hit in its shared pool cache, then it can skip the second step of generating the execution plan and directly go to the execution step. This phenomenon is called a soft parse.