我在这里不会介绍OLTP和OLAP的概念，这些入行的DBA都是必懂的。今天的话题是绑定变量为什么适合在OLTP系统而不宜于OLAP系统。从某种角度上我们可以这样看待OLTP和OLAP系统，OLTP系统小查询多而OLAP系统基本都是大查询，而它们致命的区别就是执行时间上(结果集有很大的区别)，所以我们也可以说OLTP系统适合短查询、OLAP系统则适合长查询。不管怎样，在OLTP系统，我们需要注重小查询的快速执行，为此而能够做的优化操作之一就是提高SQL命中率、使用索引(索引访问小结果集)，而绑定变量特性恰恰能够提高SQL的重复使用频率，减少反复解析次数，进而极大节省系统资源的消耗。在OLAP系统，报表类查询里经常有聚合排序操作，一般都是庞大的结果集，在这里解析所耗的系统资源基本可以忽略，为达到加快查询的优化目的，我们要注重执行计划的正确选择，而绑定变量的使用很可能让CBO选择错误的执行计划，故不适合使用。




下面通过示例查看绑定变量影响OLTP和OLAP系统的效果。






下面简要介绍OLAP系统下的影响：


-- 我有一张大表

luocs@MAA> select count(*) from tt1;


COUNT(*)

----------

   338752


luocs@MAA> select index_name, index_type, status from user_indexes where table_name='TT1';


INDEX_NAME      INDEX_TYPE      STATUS

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

INX_TT1_OWNER   BITMAP          VALID


luocs@MAA> select COLUMN_NAME, NUM_DISTINCT from user_tab_cols where table_name='TT1';


COLUMN_NAME                                                  NUM_DISTINCT

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

OWNER                                                                  15

OBJECT_NAME                                                         16272

SUBOBJECT_NAME                                                        333

OBJECT_ID                                                           21204

DATA_OBJECT_ID                                                       6221

OBJECT_TYPE                                                            36

CREATED                                                               855

LAST_DDL_TIME                                                         907

TIMESTAMP                                                             935

STATUS                                                                  1

TEMPORARY                                                               2

GENERATED                                                               2

SECONDARY                                                               2

NAMESPACE                                                              15

EDITION_NAME                                                            0


-- 查看OWNER为SYS和SYSTEM的记录数

luocs@MAA> select count(*) from tt1 where owner='SYS';


COUNT(*)

----------

   152960


luocs@MAA> select count(*) from tt1 where owner='SYSTEM';


COUNT(*)

----------

     8208


-- 查看执行计划

luocs@MAA> explain plan for select count(*) from tt1 where owner='SYS';


Explained.


luocs@MAA> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT

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

Plan hash value: 945689350


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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |     1 |     7 |     7   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE             |               |     1 |     7 |            |          |

|   2 |   BITMAP CONVERSION COUNT   |               |   153K|  1047K|     7   (0)| 00:00:01 |

|*  3 |    BITMAP INDEX SINGLE VALUE| INX_TT1_OWNER |       |       |            |          |

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


Predicate Information (identified by operation id):

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


3 - access("OWNER"='SYS')


15 rows selected.


luocs@MAA> explain plan for select count(*) from tt1 where owner='SYSTEM';


Explained.


luocs@MAA> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT

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

Plan hash value: 945689350


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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |     1 |     7 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE             |               |     1 |     7 |            |          |

|   2 |   BITMAP CONVERSION COUNT   |               |  8390 | 58730 |     1   (0)| 00:00:01 |

|*  3 |    BITMAP INDEX SINGLE VALUE| INX_TT1_OWNER |       |       |            |          |

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


Predicate Information (identified by operation id):

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


3 - access("OWNER"='SYSTEM')


15 rows selected.


-- 通过10046事件查看使用绑定变量后的结果


luocs@MAA> alter session set events '10046 trace name context forever, level 4';


Session altered.


luocs@MAA> variable l varchar2(10)

luocs@MAA> exec :l:='SYS'


PL/SQL procedure successfully completed.


luocs@MAA> select count(*) from tt1 where owner=:l;


COUNT(*)

----------

   152960


luocs@MAA> exec :l:='SYSTEM'


PL/SQL procedure successfully completed.


luocs@MAA> select count(*) from tt1 where owner=:l;


COUNT(*)

----------

     8208


luocs@MAA> alter session set events '10046 trace name context off';


Session altered.


luocs@MAA> select value from v$diag_info where name = 'Default Trace File';


VALUE

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

/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_9081.trc


-- 格式化下

[oracle@maa3 ~]$ tkprof /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_9081.trc luocs.txt aggregate=no


-- trace部分内容

SQL ID: 6yw9t0j4w7hvh Plan Hash: 0


BEGIN :l:='SYS'; END;


call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           1

Fetch        0      0.00       0.00          0          0          0           0

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

total        2      0.00       0.00          0          0          0           1


Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 51

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


SQL ID: dbuap8tqp73th Plan Hash: 945689350


select count(*)

from

tt1 where owner=:l


call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0          7          0           1

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

total        4      0.00       0.00          0          7          0           1


Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 51

Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

        1          1          1  SORT AGGREGATE (cr=7 pr=0 pw=0 time=276 us)

       11         11         11   BITMAP CONVERSION COUNT (cr=7 pr=0 pw=0 time=225 us cost=7 size=1072232 card=153176)

       11         11         11    BITMAP INDEX SINGLE VALUE INX_TT1_OWNER (cr=7 pr=0 pw=0 time=67 us)(object id 25884)

-- 注意，这里第一次绑定变量值为SYS的时候card=153176，执行计划很准确

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


SQL ID: fvp79xru6ftzd Plan Hash: 0


BEGIN :l:='SYSTEM'; END;


call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           1

Fetch        0      0.00       0.00          0          0          0           0

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

total        2      0.00       0.00          0          0          0           1


Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 51

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


SQL ID: dbuap8tqp73th Plan Hash: 945689350


select count(*)

from

tt1 where owner=:l


call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0          2          0           1

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

total        4      0.00       0.00          0          2          0           1


Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 51

Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

        1          1          1  SORT AGGREGATE (cr=2 pr=0 pw=0 time=81 us)

        1          1          1   BITMAP CONVERSION COUNT (cr=2 pr=0 pw=0 time=68 us cost=7 size=1072232 card=153176)

        1          1          1    BITMAP INDEX SINGLE VALUE INX_TT1_OWNER (cr=2 pr=0 pw=0 time=41 us)(object id 25884)

-- 但紧接给变量传SYSTEM值，CBO认为card依然为153176，这是不正确的，也可能会影响执行计划。

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




以上简单的示例可以证明，绑定变量适合于OLTP系统而不适合于OLAP系统。



oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html