Oracle binding Variables

I have previously compiled an article about variable binding, which is not very detailed. Please try again.

Oracle variable binding

Http://blog.csdn.net/tianlesoftware/archive/2009/10/17/4678335.aspx

 

 

I. bind variables

Bind Variable: a variable in a SQL statement that must be replaced with a valid value, or the address of a value, in order for the statement to successfully execute.

 

Variable binding is a very important technology in OLTP systems. Good variable binding speeds up SQL Execution in the database of the OLTP system and provides extremely high memory efficiency. Without binding variables, the OLTP database may be overwhelmed and the resources will be severely exhausted by SQL parsing, the system runs slowly.

 

When a user establishes a connection with the database, an operation request is sent to the database, that is, an SQL statement is sent to the database. After Oracle receives these SQL statements, it first performs a hash function operation on the SQL statement to obtain a hash value, and then searches for the existence of the SQL statement that matches the hash value in the shared pool. If the SQL statement is found, Oracle runs the current SQL statement directly using the execution plan of the existing SQL statement and returns the result to the user. If no SQL statement with the same hash value is found in the Shared Pool, Oracle considers this as a new SQL statement. Will be parsed.

 

 

The steps for Oracle parsing are as follows:

(1) Syntax Parsing

(2) semantic parsing

(3) generate the execution plan, which is divided into soft parsing and hard parsing. Hard Parsing is very resource-consuming.

(4) SQL Execution

 

For SQL parsing, see blog:

Oracle SQL hard parsing and soft Parsing

Http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

 

After learning about the SQL Execution Process, let's look at some Bound variables. The essence of variable binding is that the SQL statements that originally require Oracle hard parsing become soft parsing, to reduce the time and resources that Oracle spends on SQL parsing.

 

Two SQL statements are added:

Select salary from user where name = 'a ';

Select salary from user where name = 'B ';

 

If no variable is bound, the two SQL statements are parsed twice because their predicates are partially different. If we use bind variables, such:

Select salary from user where name =: X;

 

At this time, the previous two SQL statements become a type of SQL. Oracle only needs to perform a hard parsing on each type of SQL statements, and then similar SQL statements use the execution plan generated by this SQL statement, this greatly reduces the resource overhead of the database for SQL parsing. The more SQL statements are executed, the more obvious the result is.

 

Simply put, binding a variable is to replace the predicate constant with a variable. Each time Oracle performs a hash operation on the SQL statement sent by the user, the calculated result is the same hash value, therefore, the SQL statements sent by all users are treated as the same SQL object.

 

 

Ii. bind variables in OLAP and OLTP Systems

There is a big difference between OLAP and OLTP systems. For details about the differences between them, refer to blog:

Introduction to Oracle OLAP and OLTP

Http://blog.csdn.net/tianlesoftware/archive/2010/08/08/5794844.aspx

 

In the OLTP system, we can bind variables because in OLTP, most SQL statements are relatively simple or the result set of operations is very small. When an index is created on a table, this very small result set operation uses the index most suitable, and almost all SQL Execution Plan indexes will be selected, because in this case, the index may only need to scan several data blocks to locate the data, and the full table scan will consume considerable resources. Therefore, in this case, even if the predicates of each user are different, the execution plan is the same, that is, the data is accessed using indexes, and the full table scan will not occur. When such an execution plan is almost unique, it is appropriate to use the bound variable to replace the predicate constant.

 

In OLAP systems, SQL operations are much more complex. Some Report SQL statements run on OLAP databases. These SQL statements often use Aggregate Queries (such as group ), in addition, the result set is very large. In this case, the index is not an inevitable choice, and sometimes the full table scan performance is better than the index, even if the same SQL statement, if the predicates are different, the execution plans may be different.

 

 

The following principles apply to variables bound to an OLAP system:

(1) There is no need to bind variables to the OLAP system, which will only bring negative effects. For example, the cost of incorrectly selecting SQL statements may sometimes be disastrous; oracle performs a hard Analysis on each SQL statement to precisely know the value of the predicate condition, which is crucial to the execution plan selection. The reason for this is that in the OLAP system, the cost of SQL hard analysis can be ignored. The system resources are basically used for large SQL queries. Compared with queries, the resources consumed by SQL parsing are negligible. Therefore, it is very important to obtain an optimal execution plan.

(2) In the OLAP system, it is critical to let Oracle know the exact value of the predicate. It directly determines the choice of the SQL Execution Plan. In this way, do not bind variables.

(3) In OLAP systems, the analysis of tables and indexes is intuitive and important because it is the source and basis for Oracle to make correct execution plans for SQL, therefore, we need to establish a set of execution jobs for object analysis that can meet system requirements.

 

 

3. Bind peaking

Let's take a look at the instructions on the official website:

The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. this feature enables the optimizer to determine the selectivity of any where clause condition as if literals have been used instead of bind variables.

To ensure the optimal choice of cursor for a given bind value, Oracle database uses bind-aware cursor matching. the system monitors the data access was med by the query over time, depending on the BIND values. if bind peeking takes place, and if the database uses a histogram to compute selectivity of the predicate containing the Bind Variable, then the database marks the cursor as bind-sensitive.

Whenever the database determines that a cursor produces significantly different data access patterns depending on the BIND values, the database marks this cursor as bind-aware. oracle Database switches to bind-aware cursor matching to select the cursor for this statement. when bind-aware cursor matching is enabled, the database selects plans based on the BIND value and the optimizer estimate of its selectivity. with bind-aware cursor matching, a SQL statement with user-defined Bind Variable can have multiple execution plans, depending on the BIND values.

When bind variables appear in a SQL statement, the database assumes that cursor sharing is intended and that different invocations use the same execution plan. if different invocations of the cursor significantly benefit from different execution plans, then bind-aware cursor matching is required. bind peeking does not work for all clients, but a specific set of clients.

 

From: http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/optimops.htm#PFGRF94588

 

Bind peeking is a new feature introduced in Oracle 9i. It is used to view the values of the current SQL predicates during SQL statement hard analysis to generate the best execution plan. In versions earlier than Oracle 9i, Oracle only makes execution plans based on statistics.

 

Note that bind peeking only occurs during hard analysis, that is, when the SQL is executed for the first time, the subsequent variables will not be peeking. We can see that the BIND peeking does not ultimately solve the problem of selecting different execution plans due to different predicates. It can only make the Execution Plan Selection more accurate during the first SQL Execution, it cannot help the OLAP system solve the problem of incorrect Execution Plan Selection caused by variable binding. This is one reason why OLAP should not bind variables.

 

 

 

Summary:

For OLTP systems, the same SQL statement repetition frequency is very high. If the optimizer repeatedly parses SQL statements, system resources will be greatly consumed. In addition, the result set of user requests in OLTP systems is very small, therefore, indexes are basically considered. After the BIND peeking obtained a correct execution plan for the first time, all subsequent SQL statements are executed according to the execution plan, which greatly improves the system performance.

 

For OLAP systems, the SQL Execution Plan has a great relationship with the predicates. Different predicates may have different execution plans. If the same execution plan is used, the SQL Execution efficiency must be very low. In addition, the number of SQL statements executed by a database in an OLAP system is much smaller than that in OLTP, and the SQL repetition frequency is much lower than that in the OLTP system, compared with SQL Execution, the cost of SQL parsing can be completely ignored.

 

Therefore, for OLAP systems, variables do not need to be bound. If used, the execution plan may be incorrectly selected. In addition, if a variable is bound, bind peeking can only ensure that the execution plan can be correctly selected for the first hard analysis SQL statement. If the predicate after the change, the wrong execution plan may be selected. Therefore, it is not recommended to bind variables in the OLAP system.