Oracle's variable binding and variable peering

Database environment

Leo1@leo1> select * from V$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition release 11.2.0.1.0-64bit Production

Pl/sql Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

An example demonstrates that the application domain of a binding variable is OLTP rather than OLAP

Variable binding: This is a cliché topic, and the binding I understand is the binding of the execution plan, and the variable I understand is the variable of the predicate substitution.

Variable binding mechanism: To say that the mechanism has to say the process of SQL execution, trilogy: Parse –> perform-> take operations, and binding variables occur in parsing this step, and parsing is divided into hard parsing and soft parsing.

Hard parsing: When an SQL statement executes for the first time, it first generates an execution plan and stores the execution plan in the Shared_pool library cache, a process called hard parsing.

Soft parsing: If the SQL statement has been hard resolved, then can be directly extracted from the library cache from the existing execution plan to reuse, the process is called soft parsing, to reduce the generation of execution plan this aspect of resource consumption. Why do you say that, hard parsing will consume some system resources, especially CPU resources, thus affecting the efficiency of the system, if the impact of the elimination of this aspect of the system, of course, is more and more, ha kan a few words.

SQL detailed execution procedure: When Oracle receives an SQL statement, it first makes a hash of the SQL statement character and then searches the library cache for the existence of the SQL that matches the hash value. If so, execute the current SQL statement directly using this SQL execution plan, and finally return the result to the user. If the same hash value is not found, Oracle will assume that this is a new SQL that will regenerate the execution plan to perform (check parsing and semantic analysis first in the process) and finally return the results to the user.

Experiment

Here we demonstrate the difference in resource consumption between bound and unbound variables

leo1@leo1> drop table Leo1 purge; Clean up the environment

Table dropped.

leo1@leo1> drop table Leo2 purge;

Table dropped.

Leo1@leo1> CREATE TABLE Leo1 as SELECT * from Dba_objects; Create Leo1

Table created.

Leo1@leo1> CREATE TABLE Leo2 as SELECT * from Dba_objects; Create Leo2

Table created.

Leo1@leo1> alter session set tracefile_identifier= ' Bind_variable '; Set trace file identification

Session altered.

Leo1@leo1> alter session set Sql_trace=true; Start the trace feature to track SQL resource consumption

Session altered.

Leo1@leo1> begin

For I in 1..100 loop

Execute immediate ' select * from leo1 where object_id=:i ' using I;

End Loop;

End

/

Pl/sql procedure successfully completed.

We executed 100 times on one SQL and adopted the binding variable technology, Oracle has only one hard parse for this SQL, no soft parsing, and repeated execution 100 times.

Leo1@leo1> alter session set Sql_trace=false; Turn off the trace feature

Session altered.

Leo1@leo1> Select Sql_text,parse_calls,loads,executions from V$sql where Sql_text like ' select * from leo1 where% ';

Sql_text parse_calls loads executions

-------------------------------------------------- ----------- -------------------------- ----------- ---------- --- ---------------

SELECT * from Leo1 where object_id=:i 1 1 100

Sql_text: The SQL statement we traced

Parse_calls: Hard parsing + soft resolution times 1 times only hard parsing without soft parsing

Loads: Hard resolution times 1 times

Executions: Execution times 100 times

Although the value is hidden in a variable, Oracle thinks it is the same in the parsing process

[Oracle@leonarding1 trace]$ tkprof leo1_ora_16433_bind_variable.trc bind_variable.txt sys=no

Tkprof:release 11.2.0.1.0-development on Fri Feb 1 13:18:08 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Using the Tkprof tool to filter and summarize the trace file, Sys=no does not output the SYS user recursive statement, default Yes, actually set to no more readable