Oracle技术之在OLTP OR OLAP上绑定变量

zhk2369

　　我在这里不会介绍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
　　---------------------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------------------
　　|   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>　　---------------------------------------------------
　　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
　　---------------------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------------------
　　|   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>　　---------------------------------------------------
　　3 - access("OWNER"='SYSTEM')
　　15 rows selected.
　　-- 通过10046事件查看使用绑定变量后的结果

　　luocs@MAA>>
　　Session>　　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>>
　　Session>　　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>　　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>　　********************************************************************************

　　SQL>　　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>　　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>
　　11         11         11    BITMAP INDEX SINGLE VALUE INX_TT1_OWNER (cr=7 pr=0 pw=0 time=67 us)(object>　　-- 注意，这里第一次绑定变量值为SYS的时候card=153176，执行计划很准确
　　********************************************************************************

　　SQL>　　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>　　********************************************************************************

　　SQL>　　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>　　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>
　　1          1          1    BITMAP INDEX SINGLE VALUE INX_TT1_OWNER (cr=2 pr=0 pw=0 time=41 us)(object>　　-- 但紧接给变量传SYSTEM值，CBO认为card依然为153176，这是不正确的，也可能会影响执行计划。
　　********************************************************************************
　　以上简单的示例可以证明，绑定变量适合于OLTP系统而不适合于OLAP系统。
　　oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html