Oracle optimization practice (variable binding)

Oracle optimization practice (variable binding)

Variable binding is the primary tool for Oracle to solve hard parsing. It can solve the excessive consumption of librarycache in OLTP systems to improve performance. However, the knife is worn too fast, sharp, but easy to break. There are two advantages and disadvantages in everything. When we adapt to local conditions, we can only weigh the advantages and disadvantages. This article describes how to bind a variable, its advantages and disadvantages, and usage scenarios.

I. bind variables

When talking about binding variables, you have to understand hard parsing and soft parsing. In short, an SQL statement has not been run and is running for the first time. Therefore, you need to perform syntax analysis, semantic recognition, and generate the best execution plan with the data statistics, then execute the command. In soft resolution, it is because the librarycache already has the same SQL statement text and running environment as the SQL statement, that is, the same parent cursor and sub-cursor. It adopts the tailism, run the command directly. Soft parsing also goes through syntax analysis, semantic recognition, and generates hashvalue. Next, search for the same hashvalue in librarycache. If so, soft Parsing is implemented.

C: \ Users \ mxq> sqlplus/as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Saturday May 30 20:16:40 2015

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Connect:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Clear previous shared pool data
SQL> alter system flush shared_pool;

System altered

SQL> select cust_id from T_SMSGATEWAY_MT where cust_id = 467 and dest_addr = 1817156
1555;

CUST_ID
----------
467
467
467

SQL> select cust_id from T_SMSGATEWAY_MT where cust_id = 467 and dest_addr = 1810839
9505;

Unselected row

SQL> select cust_id from T_SMSGATEWAY_MT where cust_id = 467 and dest_addr = 1897116
7925;

Unselected row

As shown in the following figure, oracle hard parses and generates three values for each execution, which is inefficient.
SQL> select SQL _text, hash_value from v $ SQL where SQL _text like '% dest_addr = 100 ';

SQL _TEXT
--------------------------------------------------------------------------------

HASH_VALUE
----------
Select SQL _text from v $ SQL where SQL _text like '% dest_addr = 100'
261357771

Select cust_id from T_SMSGATEWAY_MT where cust_id = 467 and dest_addr = 18108399505
2971670234

Select * from T_SMSGATEWAY_MT where cust_id = 467 and dest_addr = 18171561555
4160363108

SQL> select SQL _text, hash_value from v $ SQL where SQL _text like '% dest_addr = 100 ';

SQL _TEXT HASH_VALUE
------------------------------------------------------------------------------------------

Select cust_id from T_SMSGATEWAY_MT where cust_id = 467 and dest_addr = 18971167925 3796536237
Select * from T_SMSGATEWAY_MT where cust_id = 467 and dest_addr = 18108399505 2768207417
Select * from T_SMSGATEWAY_MT where cust_id = 467 and dest_addr = 18171561555 2177737916

Bind variables here
SQL> var dest number;
SQL> exec: dest: = 15392107000;

PL/SQL procedure successfully completed
Dest
---------
15392107000

SQL> select cust_id from T_SMSGATEWAY_MT where cust_id = 467 and dest_addr =: dest;

CUST_ID
---------
Dest
---------
15392107000

SQL> exec: dest: = 15310098199;

PL/SQL procedure successfully completed
Dest
---------
15310098199

SQL> select cust_id from T_SMSGATEWAY_MT where cust_id = 467 and dest_addr =: dest;

CUST_ID
---------
Dest
---------
15310098199

Generating a value indicates that oracle only parses the value once.
SQL> select SQL _text, hash_value from v $ SQL where SQL _text like '% dest_addr =: dest % ';

SQL _TEXT HASH_VALUE
------------------------------------------------------------------------------------------
Select SQL _text, hash_value from v $ SQL where SQL _text like '% dest_addr =: dest %' 627606763
Select cust_id from T_SMSGATEWAY_MT where cust_id = 467 and dest_addr =: dest 1140441667

Conclusion:
Variable binding can effectively eliminate hard Parsing