最佳化你的應用--請使用繫結變數

首先清空shared pool,確保測試資料庫處於乾淨狀態.
SQL> alter system flush SHARED_POOL;

系統已更改。

我們選取ALL_OBJECTS表做測試.它有29542條紀錄.

[@more@]

首先清空shared pool,確保測試資料庫處於乾淨狀態.
SQL> alter system flush SHARED_POOL;

系統已更改。

我們選取ALL_OBJECTS表做測試.它有29542條紀錄.
SQL> DESC ALL_OBJECTS
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> select count(*) from all_objects;

  COUNT(*)
----------
     29542
    

沒有使用繫結變數的情況.
我寫了一個簡單測試程式實現這樣一個查詢統計執行1000次的時間.
形如:
SELECE object_name FROM all_objects WHERE object_id=1;
SELECE object_name FROM all_objects WHERE object_id=2;
.
.
.
.
SELECE object_name FROM all_objects WHERE object_id=1000;
可以看到在沒有使用繫結變數的情況下執行時間為12.9 seconds (12.9秒)
pl/sql 1
SQL> declare
  2       type rc is ref cursor;
  3       l_rc rc;
  4       l_dummy all_objects.object_name%type;
  5       l_start number default dbms_utility.get_time;
  6   begin
  7       for i in 1 .. 1000
  8       loop
  9           open l_rc for
 10           'select object_name
 11              from all_objects
 12             where object_id = ' || i;
 13           fetch l_rc into l_dummy;
 14           close l_rc;
 15       end loop;
 16       dbms_output.put_line
 17       ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
 18         ' seconds...' );
 19   end;
 20  /
12.9 seconds...

PL/SQL 過程已成功完成。

使用繫結變數的情況.
我寫了一個簡單測試程式實現這樣一個查詢統計執行1000次的時間.
形如:
SELECE object_name FROM all_objects WHERE object_id=:object_id;
SELECE object_name FROM all_objects WHERE object_id=:object_id;
.
.
.
.
SELECE object_name FROM all_objects WHERE object_id=:object_id;
可以看到使用繫結變數的情況下執行時間為1.32 seconds... (1.32秒)
pl/sql 2
SQL>   declare
  2        type rc is ref cursor;
  3        l_rc rc;
  4        l_dummy all_objects.object_name%type;
  5        l_start number default dbms_utility.get_time;
  6    begin
  7        for i in 1 .. 1000
  8        loop
  9            open l_rc for
 10            'select object_name
 11               from all_objects
 12              where object_id = :x'
 13            using i;
 14            fetch l_rc into l_dummy;
 15            close l_rc;
 16        end loop;
 17        dbms_output.put_line
 18        ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
 19          ' seconds...' );
 20    end;
 21  /
1.32 seconds...

PL/SQL 過程已成功完成。

原因:
執行sql需要在shared_pool中生成執行計劃.如果沒有使用繫結變數,會使硬分析的次數增加.
一次硬分析的代價是很高的,包括這條sql的語法分析,語義分析,執行許可權分析.而當Oracle在職執行分析個過程中又要獲得latch(latch 一種低階序列鎖,保護shared_pool記憶體徵用),latch的資源對於一個繁忙的OLTP系統也是非常寶貴的.

沒有執行pl/sql 1時,硬分析統計.
SQL> select name,value from v$sysstat where name like '%parse%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                         4948
parse time elapsed                                                     4468
parse count (total)                                                  170148
parse count (hard)                                                     1619  (硬分析次數)
parse count (failures)                                                   80

執行pl/sql 1後,硬分析統計.
SQL> select name,value from v$sysstat where name like '%parse%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                         4948
parse time elapsed                                                     4468
parse count (total)                                                  170148
parse count (hard)                                                     2619  (硬分析次數)
parse count (failures)                                                   80
 

提高應用程式非常重要的手段之一就是採用PrepareStatement代替普通的Statement。