Toad World

Oracle Using Bind Variables

Avatar photo
Post author:Written By

Processing SQL Statements

The first time a select query is executed inside the Oracle engine, the statement will generally need to proceed through 4 different stages in order to return results back to the user. These stages are:

  1. Parse. The Oracle engine verifies the SQL statement’s syntax, table and column names, and access rights.
  2. Optimize. The Oracle engine finds the best way to process the SQL statement using statistics from the indexes and tables being accessed.
  3. Execution. The data is accessed using the execution path from the Optimizing step.
  4. Fetch. For SELECT statements only, where rows of data are returned to the application.

The first execution of DML statements (insert, update or delete) will go through the first three stages, but will not go through the fourth stage.

The execution of the first two steps may consume considerable resources. SQL statements can be written in such a way as to eliminate the need to execute the first two steps, increasing the efficiency of future operations of that statement. Additionally, efficient applications (such as those that use cursors in PL/SQL) should try to minimize overhead by performing the Parse and Optimize steps as few times as possible.

All currently executing SQL statements are cached in the shared SQL area within the Shared Pool structure of the SGA. When a SQL statement is parsed, it is compared to the currently cached statements. If a match is found then the parse step can be minimized to checking access rights. This is called a soft parse. If no match is found a full parse is performed called a hard parse. Soft parses will take less time than hard parses.

The first time a SQL statement is executed the optimization of that statement occurs. It is during this operation that the Oracle engine defines the access path that Oracle needs to take to access the data in the database. Once the optimize stage is complete, the SQL statement and the data access paths and associated parse trees are stored in the Library Cache. The SQL statement is then executed. Once initial parsing and execution have occurred, the same SQL statement can be executed again very efficiently because the statement does not need to be parsed or optimized. This is facilitated through the use of a hashing algorithm that is applied to the issued SQL statement. The hash value of the issued SQL statement is then compared to hash values within the library cache and if a match is found, a parse is not required. Note that in Oracle8i and earlier different spaces and line feeds in a given SQL statement will cause it not to match an otherwise identical SQL statement. This is not true in Oracle9i and later versions.

Just like the database Buffer Cache, SQL statements can be aged out of the Library Cache, which will cause a parse to occur during any following execution. The Library Cache is also purged if the database is restarted or the shared pool is flushed.

Optimizing SQL

It should be the goal of all developers and DBA’s to write efficient code. Poorly tuned SQL code is one of the main causes of database performance problems. Most of the resources of an Oracle database are dedicated to processing queries on the data contained in the database and to making modifications to the data contained in the database. These requests are generally received by the database through the use of SQL statements. Therefore, making sure that SQL is optimally tuned should be one of your top priorities when performing tuning tasks.

The following are some of the reasons why you should make sure that SQL is optimally tuned:

  • To improve interactive response time of an ORACLE based application. A major component of the response time of these applications is the amount of time it takes to retrieve or update data in the database. By tuning the SQL underlying these applications, response times can be reduced from excessive to acceptable or outstanding.
     
  • To improve batch throughput. Batch systems can be required to process thousands, or millions of rows of data in rigidly defined batch windows (the period of time allocated for batch jobs). Improving the SQL that drives batch jobs allows more rows to be processed in a given time period and allows these jobs to complete in their allotted time. Often, problems in batch reports are not noticed until a steadily degrading batch job exceeds the time limit (for example, when the daily report takes longer than 24 hours to run).
     
  • To ensure scalability in an application. As you increase the load on your system (as measured by the number of users connected to the system or the data volume in the database) performance (as measured by response time or throughput) should degrade gradually. However, many applications degrade quickly, rather than gradually, as load increases.
     
  • To reduce system load. Even when performance is within acceptable bounds, tuning the application can free up system resources for other purposes.
     
  • To avoid hardware upgrades. Hardware upgrades are often recommended as a solution for applications that perform poorly. However, hardware upgrades may allow you to avoid tuning, but they introduce other issues. Non-scaleable applications can require a series of hardware upgrades, which can reach the limits of availability and affordability.

Use of bind variables is one method of improving performance of SQL operations. Let’s look at how they can be used to improve response time.

Bind Variables

Hard parses are bad. Let’s start with that statement. Avoiding hard parses can reduce overall run time of a given SQL statement be a second or more. That might not seem like a lot, but if that statement is running 100,000 times in several hours time, that one second can be an eternity. Since Oracle only considers identical SQL statements to be the same, hard parses will be performed for both of these statements

select * from cars_table where car_id = 1234;

And

select * from cars_table where car_id = 435;

To the optimizer, the two SQL statements above are not identical. This will result in two different cursors being stored in the Library Cache.

You can avoid this problem through the use of bind variables used within your SQL and PL/SQL code. To use a bind variable, prepare your SQL statement or PL/SQL block and use a colon followed by a variable name to indicate a bind variable is being used. You can use bind variables for input variables in any DELETE, INSERT, SELECT, or UPDATE statement, or PL/SQL block, in any position in the statement where you can use an expression or a literal value.

Here is a piece of PL/SQL written without the use of a bind variable:

declare
v_return varchar2(30);
begin
select ename into v_return from emp where empno=1111;
dbms_output.put_line(v_return);
end;
/

and one written using bind variables:

variable v_value number
declare
v_return varchar2(30);
begin
:v_value:=1111;
select ename into v_return from emp where empno=:v_value;
dbms_output.put_line(v_return);
end;
/

The later PL/SQL can be executed many times with different values for :v_value, and yet will only be hard parsed once. Take care to write your applications so that they are designed using bind variables rather than hard coded values so they will only need to do a single parse. This approach not only performs better but also is a much more scalable way of writing SQL and PL/SQL. This is because multiple concurrent statements will be able to be executed, and since there will be reduced parsing there will be much less latch contention in the shared pool. Each development platform is different, so you will have to find out how to use bind variables within that platform. If we had used bind variables in our first example, the SQL statement would have looked like:

select * from cars_table where car_id = :b0;

Both executions of we first talked about can use this same SQL statement. The bind variable, :b0, would have a value of 1234 for the first run, and a value of 435 for the second run.

Reuse Those Cursors

Cursors are the memory areas in the SGA that store the SQL statements and its parsed information. When executing the same SQL more than once, the Oracle engine will reuse the cursor. Creating applications that do not needlessly close cursors helps maximize soft parsing and minimizing hard parses. Different development platforms handle cursors differently, be sure to understand how the development tool of choice handles cursors.

Cursor Sharing

Bind variables are sometimes hard to implement because the application code is from a vendor and you do not have access to the source code. However the Oracle Cursor Sharing feature can still help you take advantage of the benefits of bind variables. Oracle offers the cursor_sharing initialization parameter starting with Oracle 8i. When cursor sharing is enabled via the setting of the parameter cursor_sharing to FORCE, EXACT, or SIMILAR (SIMILAR only available in 9i and 10g), Oracle will replace literal values with system generated bind values. This can have the effect of reducing hard parsing immensely.

When cursor_sharing is set to EXACT, SQL statements must be exactly the same to use the same cursor. This is the default value. When cursor_sharing is set to FORCE, it will force cursors that are the same except for literals to use the same cursor. Oracle 9i and 10g enhances cursor sharing by adding the SIMILAR option. Choosing this option allows the optimizer to make smarter choices in the way it approaches the reuse of cursors. Setting cursor_sharing = SIMILAR will allow to Oracle optimizer to consider issues such as data distribution when deciding to reuse a cursor. Beware of cursor sharing though; our experience with it is touch and go at best. In 8i there were several bugs associated with cursor sharing as well as in 9i. Most of these bugs should have been removed in the Oracle 10g release. So, before you implement, make sure you test it carefully.

You can implement cursor sharing on a session-by-session basis as well. This change will only be effective for that session. This change is made using the alter session command as seen in this example:

ALTER SESSION SET cursor_sharing=FORCE;

As with the cursor sharing parameter in the database parameter file, you can set cursor_sharing to FORCE, SIMILAR or EXACT in the alter session command we just demonstrated.

Important Points

  1. If bind variables are not used, then there is hard parsing of all SQL statements. This has a severe impact on performance, and it is non-scalable.
  2. Not using cursors results in repeated parses.
  3. Use cursors with bind variables that open the cursor and re-execute it many times.
  4. Be suspicious of applications generating dynamic SQL.

Implementing Bind Variables in Various Languages

  • Precompilers (PRO*C, PRO*COBOL) – Precompilers fully support bind variables. In versions 1.x of the precompilers bind, variables should be declared in the EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION statements. In version 2.x of the precompilers, any variable can be used as a bind variable.
     
  • OCI7 – Any host variable can become a bind variable in OCI. Bind variables are prefixed by colons in the SQL statement in the normal manner. When you are using the largely obsolete OBNDRN call to bind your variables, the bind variables are denoted by numbers (for example, :1, :2) in the SQL statement. Otherwise, the bind variables are defined as colon-prefixed names (for example, :SURNAME).

You bind each variable to the SQL statement with a separate call to the bind function. Three bind functions exist in OCI (OBNDRN, OBNDRV, and OBNDRA). The OBNDRA call provides the most functionality and should be used in new applications.

  • OCI8 – OCI8 supports bind variables. The OCIBindByName() function allows you to bind a named parameter. The OCIBindByPos() function allows you to bind positional parameters.
     
  • JDBC – Prepared statements can contain bind variables that allow a single statement to be reused with different input or selection criteria. Parameter values are set using Statement methods of the form setDtype method, where Dtype represents the datatype of the column for which the value is retrieved (for example, setString, setInt, setFloat, and so forth). The parameters are identified by their position in the SQL statement.
     
  • SQLJ – In SQLJ, any Java variable of an appropriate type can be a SQL bind variable. Bind variables are prefixed by a colon (:) in SQL statements.
     
  • Oracle Objects for OLE – ORACLE Objects for OLE supports bind variables. These bind variables are represented by the OraParameter object and the OraParameters collection of an OraDatabase object. In an SQL statement, the parameters are referenced using the usual leading colon convention. To create a bind variable, you use the Parameter.Add method of the database object.
     
  • ODBC – The ODBC API fully supports bind variables through the SQLbindparameter call. Bind variables are represented by a question mark (?) in the source SQL.

The ODBC API has a low-level implementation and is commonly accessed using middleware layers that allow a more high-level and productive programming environment. Therefore, the vendors who develop ODBC-based middleware products must often compromise performance for portability. For example, not all server databases support bind variables, and as a result, many ODBC products do not implement a bind variable capability. Array fetch and cursor reuse are more frequently, but not universally, implemented. When you are using such a middleware product, you should consult your user documentation.

  • PowerBuilder – Any PowerBuilder variable can be used as a bind variable in a SQL statement. The bind variable in a SQL statement is preceded by a colon (:). When the DisableBind DBParm parameter is set to 1, bind variables are not used and literal values are substituted to SQL and are sent by PowerBuilder to ORACLE. In earlier releases of PowerBuilder, the DisableBind option was generally set to avoid problems executing certain types of SQL. Therefore, you should make sure that DisableBind is not set unless absolutely necessary.
Tags: , ,

Start the discussion at forums.toadworld.com