Oracle Binding Variable __oracle

In Oracle, for a committed SQL statement, there are two alternative parsing processes, one called hard parsing and one called soft parsing.

A hard parsing requires many steps, such as parsing, making the execution path, optimizing the access plan, and so on. Hard interpretation not only consumes a lot of CPU, but more importantly, it will occupy important latches (latch) resources, and seriously affect the scale of the system (that is, limit the system's concurrent lines), And the problems that arise cannot be solved by increasing the number of memory strips and CPUs. This is because the latch is set to sequentially access and modify some memory areas that cannot be modified at the same time. When an SQL statement is submitted, Oracle first checks to see if there are any identical statements in the shared pool, and if so, only perform a soft analysis, otherwise hard analysis is required.

The only way that Oracle can reuse the execution plan is to take the binding variable. The essence of a bound variable is the substitution variable that replaces the constant in the SQL statement. A binding variable can make every SQL statement that is committed exactly the same.

Normal SQL statement:

SELECT fname, lname, pcode from cust WHERE id = 674;

SELECT fname, lname, pcode from cust WHERE id = 234;

SELECT fname, lname, pcode from cust WHERE id = 332;

SQL statement with binding variables:

SELECT fname, lname, pcode from cust WHERE id =: cust_no;

Using binding variables in Sql*plus:

sql> variable x number;

sql> exec:x: = 123;

Sql> SELECT fname, lname, pcode from Cust WHERE ID =:x;

Pl/sql

Pl/sql often automatically bind variables without the programmer's concern that many of the SQL statements you write automatically take advantage of the binding variables, as shown in the following example:

Create or Replace procedure Dsal (p_empno in number)

As

Begin

Update emp

Set sal=sal*2

where empno = P_empno;

Commit

End

/

You may want to use the binding variable instead of p_empno at this point, but this is completely unnecessary, because in Pl/sql, a reference variable is a reference to a binding variable.

However, dynamic SQL is not the case in Pl/sql.

You have to explicitly take advantage of binding variables in Vb,java and other applications.

Support for binding variables is not limited to Oracle, and other RDBMS also supports this feature for SQL Server.

However, it is not always necessary to use binding variables, and here are two exceptions:

1. For SQL statements that are executed for a considerable period of time, this is the advantage of binding variables that can be offset by the inability to effectively use the optimizer

2. In the case of a data warehouse.

*************************************************************************************************************** *****************

It is often possible to encounter situations where the programmer's SQL statement is very unreasonable, causing the Oracle database query to slow down, and serious situations can cause the database to fail.
For example, I've encountered an Oracle database on a regular basis (a week or so) with a ORA-4031 error (Shared pool memory is not enough to connect to an Oracle database), and the database is no longer available. The reset must be turned off to release objects that cannot be freed up in the shared pool.
So for an Oracle DBA, you need to periodically check the memory space occupied by the SQL statements in the shared pool, and for SQL statements that are too heavy for shared pool to release, you must require the programmer to modify or optimize the SQL statement. Java code Select Sql_text, Sharable_mem from V$sql where Sharable_mem > ' 100000 ' Order by Sharable_mem;

The above SQL statement is a query for SQL statements that consume more than 100K of memory in a shared pool.

This SQL can be very effective at checking out those heavily memory-intensive SQL in Oracle shared Pool, and, in my experience, most problematic SQL statements leave traces here, by locating the problematic SQL statements here and modifying them, and then running the SQL script again and again, Until the problematic SQL is processed, this is the best optimization of the Oracle database on SQL, ensuring that Oracle database performance issues are not caused by the programmer's SQL statement problems.

A shared pool consists primarily of a library buffer (shared SQL and Pl/sql) and a data dictionary buffer.

SELECT * from V$sgastat; --state information for an explicit SGA.

Some people write SQL statements very complex, nested several layers, the SQL statement itself is poorly written, it is possible to occupy a large SQL region.

This is what I picked up from Oracle Metalink:

Reference If The shared_pool_size is large enough, most ORA-04031 errors are a result of dynamic SQL fragmenting the SHARED POOL  . This can is caused by:

o Not sharing SQL
o Making unnecessary parse calls (soft)
o not using bind variables in fact, many of the facts are not so complex SQL, because the programmer level is not enough, write that very bad very complex SQL, resulting in database performance problems.

There is also a common sense problem that can easily be overlooked. Like what:

Java code SELECT * FROM table_name where id = 1; SELECT * FROM table_name where id = 2;

SELECT * FROM table_name where id = 1; 

For this sql,id with parameter =? This place is called the position character (placeholder).

Take PHP For example, a lot of people like to write code

Java Code $sql = "SELECT * FROM table_name where id =";    $id = 1;    $stmt = Ociparse ($conn, $sql. $id)    Ociexecute ($stmt);;    ... $id = 2;    $stmt = Ociparse ($conn, $sql. $id) Ociexecute ($stmt);;

$sql = "SELECT * FROM table_name where id ="; 
$id =1; 
$stmt = Ociparse ($conn, $sql. $id) 
Ociexecute ($stmt);; 
...... 
$id = 2; 
$stmt = Ociparse ($conn, $sql. $id) 

Take Java for example, it's like this:
Java code String SQL = "SELECT * FROM table_name where id =";       Statement stmt = Conn.createstatement ();;    RSet = Stmt.executequery (sql+ "1"); ... rset = Stmt.executequery (sql+ "2")

String sql = "SELECT * FROM table_name where id ="; 
Statement stmt = Conn.createstatement ();; 

RSet = Stmt.executequery (sql+ "1"); 
...... 

This notation is exactly two different SQL statements for an Oracle database,
Java code SELECT * FROM table_name where id = 1; SELECT * FROM table_name where id = 2;

SELECT * FROM table_name where id = 1; 

Each query performs parsing of the SQL statements, and each SQL allocates an area to hold the SQL-parsed binary executable code. Imagine, if the ID is different 100,000 SQL. Oracle allocates 100,000 SQL regions to hold 100,000 separate SQL statements with different IDs. For a database-driven Web site in this case, the SGA is open again, will soon be depleted share pool, and finally reported a ORA-4031 error. The database is not connected, so it has to be reset.

The correct wording should be:

Java code $stmt = Ociparse ($conn, "SELECT * FROM table_name WHERE ID =: id");;       Ocibindbyname ($stmt, ": id",& $id, 12);    $id = 1;    Ociexecute ($stmt);;    . $id = 2; Ociexecute ($stmt);;

$stmt = Ociparse ($conn, "SELECT * FROM table_name WHERE ID =: id");; 
Ocibindbyname ($stmt, ": id",& $id,) 

$id =1; 
Ociexecute ($stmt);; 
... 
$id = 2; 

Java code PreparedStatement pstmt = Conn.preparestatement ("SELECT * FROM table_name WHERE id =?");;    Pstmt.setint (1, 1);;    RSet = Pstmt.executequery ();;    ... pstmt.setint (1, 2);; RSet = Pstmt.executequery ();;

PreparedStatement pstmt = conn.preparestatement ("SELECT * FROM table_name WHERE id =?");; 

Pstmt.setint (1,1); 
RSet = Pstmt.executequery ();; 
... 
Pstmt.setint (1,2); 

So the Oracle database will know that you are actually using the same SQL statement, in this form:
SELECT * FROM table_name WHERE id =: 1
After parsing the execution is stored in the SQL area, when there is the same SQL, replace the parameters, immediately execute, do not need to parse SQL. Both speeds up SQL execution speed and does not occupy the share pool with too many SGA.

Unfortunately, many programmers are aware of this problem, but not aware of the seriousness of the problem, because the above kind of writing, programming time is very flexible, the SQL statement can be dynamically constructed, easy to implement, the following kind of writing, SQL statements are written dead, parameters can not be changed, programming is often very troublesome.

This is the result of many database performance problems.

Interested in a production system, with the above SQL check, to see whether the choice of l have a lot of the same SQL statements, but the parameters are different, if so, it explains that the programmer's code to write a problem.
Oh, so sun only from statement to inherit out a PreparedStatement.

*************************************************************************************************************** We all know that every SQL statement in Oracle needs to be parsed before it is executed, which is divided into soft parsing and hard parsing. So what's the difference between these two kinds of parsing? And how do they parse it separately? How is the Oracle internal parsing process done? Here we discuss these topics together. There are two types of SQL statements in Oracle, one for DDL statements, and they are never shared, that is, each execution requires hard parsing. The other is DML statements, which choose either hard parsing or soft parsing depending on the situation. In 023 of Oracle 8i OCP textbooks, 1-12 contains parsing steps for SQL statements, and when an SQL statement is passed from a client process to a server-side process, the following steps are required:
• Search for an existing copy of an SQL statement in a shared pool
• Verify that the syntax of the SQL statement is accurate
• Perform a data dictionary lookup to validate table and column definitions
• Get an analysis lock on an object so that the definition of the object does not change during the parsing of the statement
• Check user access to Reference scheme objects
• Determine the best execution plan for a statement
• Load statements and execution plans into a shared SQL area
This preconceived notion has always been on my mind, and I think hard parsing is a few steps above. Relative to hard parsing, soft parsing is the first step to find a copy of an existing SQL statement, only need to verify that the user has permission to execute is, so omit the above several steps, relatively hard to resolve the performance overhead is very small. I have always maintained this view even when we were discussing it in the forum. It was not until the previous day that I saw Tom's "effective Oracle by-design" section on sentence processing that I knew that the point I had been insisting on was actually wrong.
In fact, the parsing steps for SQL statements in Oracle are as follows:
1, grammar detection. Determines whether the syntax of an SQL statement conforms to the SQL specification, such as executing the:sql> selet * from EMP; we can see that the statement cannot pass the syntax test because a "C" is missing from the SELECT keyword.
2, semantic inspection. The second step in parsing a syntactically correct SQL statement is to determine whether the tables and columns accessed by the SQL statement are accurate. Whether the user has permission to access or change the appropriate table or column. For example, the following statement:

Sql> select * from EMP; SELECT * FROM emp * ERROR at line 1: Ora-00942:table or view does not exist

Because the query user does not have an EMP object to access, the SQL statement cannot pass the semantic check.
3, check the shared pool is the same statement exists. If the executed SQL statement already has the same copy in the shared pool, then the SQL statement will be soft parsing, that is, you can reuse the parsed statements of the execution Plan and optimization scheme, you can ignore the statement parsing process of the most resource-intensive steps, which is why we have been emphasizing the reason to avoid hard parsing. This step can be divided into two steps:
(1) Verify that the SQL statement is fully consistent. In this step, Oracle will use the hash function operation to derive the hash value for the incoming SQL statement, and compare the hash value of the existing statement in the shared pool to see if the one by one corresponds. The hash value of the SQL statement in the existing database can be obtained by accessing the Hash_value column query in the data dictionary v$sql, V$sqlarea, V$sqltext, and so on. If the hash value of the SQL statement is consistent, then Oracle will actually need to test the semantics of the SQL statement again to determine whether it is consistent. So why does Oracle need to test the statement text again? The hash value for the SQL statement is not already mapped. As a matter of fact, the hash value of the SQL statement already corresponds, and it does not mean that the two SQL statements can already be shared. Let's start with the following example: If User A has an EMP of his own, he's going to execute the query: SELECT * from emp; User B also has an EMP table, also query the select * from EMP; so that their two statements are identical in text, Their hash values will be the same, but they are virtually impossible to share because of the different related tables involved in the query. If user C had to query the same statement at this time, he would query the table for the public synonyms under Scott, and Scott would also query the same one of his own table EMP, what would happen.

Sql> Connect a/a Connected. Sql> CREATE TABLE EMP (x int); Table created. Sql> select * from EMP; No rows selected Sql> Connect b/b Connected. Sql> CREATE TABLE EMP (x int); Table created. Sql> select * from EMP; No rows selected Sql> Conn Scott/tiger Connected. Sql> select * from EMP; Sql> Conn/C Connected. Sql> select * from EMP; Sql> Conn/as SYSDBA Connected. Sql> Select Address,hash_value, executions, Sql_text 2 from V$sql 3 where Upper (Sql_text) like ' SELECT * from emp% ' 4/ Address Hash_value Executions Sql_text -------- ---------- ---------- ------------------------ 78b89e9c 3011704998 1 SELECT * from emp 78b89e9c 3011704998 1 SELECT * from emp 78b89e9c 3011704998 2 Select * from emp

We can see that the statement text and hash values of these four queries are the same, but because the object of the query is different, only the following two statements can be shared, and the statements of different situations need to be hard resolved. Therefore, when checking shared pool Common SQL statements, it needs to be based on specific circumstances.
We can further query v$sql_shared_cursor to learn why SQL cannot be shared:

Sql> Select Kglhdpar, Address, 2 Auth_check_mismatch, Translation_mismatch 3 from V$sql_shared_cursor 4 where Kglhdpar in 5 (Select Address 6 from V$sql 7 where Upper (Sql_text) like ' SELECT * from emp% ') 8/ Kglhdpar Address A T -------- -------- - - 78b89e9c 786c9d78 N N 78b89e9c 786ac810 y y 78b89e9c 786a11a4 y y

Translation_mismatch indicates that the data objects involved in the SQL cursor are different; Auth_check_mismatch means that the conversion to the same SQL statement does not match.
(2,) Verify that the SQL statement execution environment is the same. For example, the same SQL statement, a query session with/*+ first_rows * * * hint, another user plus/*+ all_rows * * hint, they will produce different execution plans, although they are querying the same data. We have an example below to illustrate the impact of the SQL execution environment on parsing, and we see the effect on the execution of the same SQL statement by changing the Workarea_size_policy of the session:

Sql> alter system flush Shared_pool; System altered. Sql> Show Parameter Workarea_size_policy NAME TYPE VALUE ------------------------------------ ----------- -------------- Workarea_size_policy string AUTO Sql> Select COUNT (*) from T; COUNT (*) ---------- 5736 Sql> alter session set Workarea_size_policy=manual; Session altered. Sql> Select COUNT (*) from T; COUNT (*) ---------- 5736 Sql> Select Sql_text, Child_number, Hash_value, address 2 from V$sql 3 where upper (sql_text) = ' SELECT COUNT (*) from T ' 4/ Sql_text Child_number Hash_value Address ------------------------------ ------------ ---------- -------- Select COUNT (*) from T 0 2199322426 78717328 Select COUNT (*) from T 1 2199322426 78717328

You can see that even the same SQL statement cannot be shared because the different sessions workarea_size_policy set differently. By further querying v$sql_shared_cursor we can see that the optimizer environment for two sessions is different:

Sql> Select Optimizer_mismatch 2 from V$sql_shared_cursor 3 where Kglhdpar in 4 (Select Address 5 from V$sql 6 where upper (sql_text) = ' SELECT COUNT (*) from T '); O - N Y

After checking with the previous three steps, if the SQL statement is consistent, the Execution plan and optimization scheme of the original SQL statement is reused, which is what we usually call soft parsing. If the SQL statement does not find the same copy, then a hard parse is required.
4, Oracle According to the submitted SQL statement to query the corresponding data object whether there is statistical information. If statistics are available, the CBO will use these statistics to generate all possible execution plans (as many as tens of thousands) and corresponding cost, ultimately choosing the lowest cost execution plan. If the queried data object has no statistics, select the appropriate execution plan by Rbo's default rule. This step is also the most resource-intensive in parsing, so we should strive to avoid hard parsing. At this point, the parsing steps are complete and Oracle will execute the SQL statements and extract the corresponding data based on the resulting execution plan.