ORACLE優化實戰（繫結變數）

            
    繫結變數是Oracle解決硬解析的首要利器，能解決OLTP系統中librarycache的過度耗用以提高效能。然刀子磨的
太快，使起來鋒利，卻容易折斷。凡事皆有利弊二性，因地制宜，因時制宜，全在如何權衡而已。本文講述了綁
定變數的使用方法，以及繫結變數的優缺點、使用場合。

　　一、繫結變數

　　提到繫結變數，就不得不瞭解硬解析與軟解析。硬解析簡言之即一條SQL語句沒有被執行過，處於首次執行，
則需要對其進行語法分析，語義識別，跟據統計資訊生成最佳的執行計劃，然後對其執行。而軟解析呢，則是由
於在librarycache已經存在與該SQL語句一致的SQL語句文字、執行環境，即有相同的父遊標與子游標，採用拿來
主義，直接執行即可。軟解析同樣經歷語法分析，語義識別，且生成hashvalue，接下來在librarycache搜尋相同
的hashvalue，如存在在實施軟解析。


C:\Users\mxq>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期六 5月 30 20:16:40 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


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

清空以前共享池資料
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;

未選定行

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

未選定行



在下面可以看到oracle把沒條執行都重新硬解析一遍生成三個值，這樣效率不高
SQL> select sql_text,hash_value from v$sql where sql_text like '%dest_addr=18%';


SQL_TEXT
--------------------------------------------------------------------------------

HASH_VALUE
----------
select sql_text from v$sql where sql_text like '%dest_addr=18%'
 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=18%';
 
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

 在這裡開始使用繫結變數
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
 
生成一個值說明oracle只硬解析一遍
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
 
 結論：
 繫結變數可以有效消除硬解析
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29065182/viewspace-1678586/，如需轉載，請註明出處，否則將追究法律責任。