关于为什么说OLTP必须要求变量绑定而OLAP不应该绑定变量的原因

╰+哭是因爲堅強的太久メ 2021-09-17 14:46 213阅读 0赞

**一、解释OLTP必须要求变量绑定的原因：**

在OLTP系统中，SQL语句大多是比较简单或操作的结果集都很小。比如拿银行的ATM系统来说，尽管用户操作的表可能非常大(银行的用户非常多)，但是用户操作的记录数可能只有1行(或者几行)，如果再这张表上创建了索引，那么这种极小结果集的操作使用索引最适用，并且几乎所有SQL的执行计划中的索引都会毫无悬念地被选择，因为在这种情况下，索引可能只需要扫过几个数据块就可以定位到数据，而全表扫描将会相当费资源。因此，在这种情况下，即使每个用户的谓词条件不一样，执行计划也是一样的，就是统统使用索引来访问数据，几乎不大可能出现走全表扫描的情况。在这种执行计划几乎是唯一的情况下，Oracle使用变量来代替谓词常量，使用同一个执行计划是合适的。

下面通过一个例子来说明：

----创建一张jack表，并插入一些数据，让表的数据量看起来比较大，然后在表上创建索引----
     1 SQL> create table jack_tab as select object_id,object_name from dba_objects;
     2 
     3 Table created.
     4 
     5 SQL> begin 
     6   2     for i in 1..100 loop
     7   3    insert into jack_tab select object_id,object_name from dba_objects;
     8   4     end loop;
     9   5  end;
    10   6  /
    11 
    12 PL/SQL procedure successfully completed.
    13 
    14 SQL> select count(*) from jack_tab;
    15 
    16   COUNT(*)
    17 ----------
    18    7324722
    19 
    20 SQL> create index jack_tab_ind on jack_tab(object_id);
    21 
    22 Index created.
    23 
    24 SQL> @/u01/scripts/showtrace
    25 
    26 trace_file_name
    27 --------------------------------------------------------------------------------
    28 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5241.trc
    29 
    30 SQL> alter session set sql_trace=true;
    31 
    32 Session altered.
    33 
     ----执行两次SQL，其中一次是让Oralce自己选择执行计划，而另一次是强制SQL使用全表扫描的方式访问数据----
    34 SQL> select /*+ full(jack_tab) */ * from jack_tab where object_id=2010;
    35 
    36 ...
    37 101 rows selected.
    38 
    39 SQL> select * from jack_tab where object_id=2010;
    40 
    41 ...
    42 101 rows selected.
    43 
    44 SQL> alter session set sql_trace=false;
    45 
    46 Session altered.
    47 
     ----trace文件内容----
    48 SQL ID: 0nunj4n5nar4g
    49 Plan Hash: 1314397703
    50 select /*+ full(jack_tab) */ * 
    51 from
    52  jack_tab where object_id=2010
    53 
    54 
    55 call     count       cpu    elapsed       disk      query    current        rows
    56 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    57 Parse        1      0.00       0.00          0          0          0           0
    58 Execute      1      0.00       0.00          0          0          0           0
    59 Fetch        8      0.79       0.79      34833      69000          0         101
    60 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    61 total       10      0.79       0.79      34833      69000          0         101
    62 
    63 Misses in library cache during parse: 0
    64 Optimizer mode: ALL_ROWS
    65 Parsing user id: 105  
    66 
    67 Rows     Row Source Operation
    68 -------  ---------------------------------------------------
    69     101  TABLE ACCESS FULL JACK_TAB (cr=69000 pr=34833 pw=0 time=0 us cost=6222 size=7979 card=101)
    70 
    71 ********************************************************************************
    72 
    73 SQL ID: 10ynwthz9sd2s
    74 Plan Hash: 2073030600
    75 select * 
    76 from
    77  jack_tab where object_id=2010
    78 
    79 
    80 call     count       cpu    elapsed       disk      query    current        rows
    81 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    82 Parse        1      0.00       0.00          0          0          0           0
    83 Execute      1      0.00       0.00          0          0          0           0
    84 Fetch        8      0.00       0.00          0        111          0         101
    85 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    86 total       10      0.00       0.00          0        111          0         101
    87 
    88 Misses in library cache during parse: 0
    89 Optimizer mode: ALL_ROWS
    90 Parsing user id: 105  
    91 
    92 Rows     Row Source Operation
    93 -------  ---------------------------------------------------
    94     101  TABLE ACCESS BY INDEX ROWID JACK_TAB (cr=111 pr=0 pw=0 time=0 us cost=104 size=7979 card=101)
    95     101   INDEX RANGE SCAN JACK_TAB_IND (cr=10 pr=0 pw=0 time=2000 us cost=3 size=0 card=101)(object id 75303)
    96 
    97 ********************************************************************************

可以清楚地看到，全表扫描执行计划的SQL扫描过的数据块远远大于使用索引执行计划的SQL。

从trace文件中还可以看到，在fetch阶段(抽取数据阶段)，第一条SQL(全表扫描)读取的数据块是69000个(query列)，而第二条SQL只有111个数据块。

在这种索引和全表扫描代价差异非常显著的OLTP系统中，不选择索引时不大可能的，只有谓词不相同的SQL的执行计划是应该(或者说是必须)相同的，这样我们就可以非常放心的在OLTP系统中使用变量绑定了。

**二、解释OLAP不应该使用绑定变量的原因**

OLAP系统在SQL的操作中就复杂得多，OLAP数据库上大多数时候运行的是一些报表SQL，这些SQL经常会用到聚合查询(比如GROUP BY)，而且结果集也非常庞大，在这种情况下，索引并不是必然的选择，甚至有的时候全表扫描的性能会更优于索引，即使相同的SQL，如果谓词条件不同，执行计划都有可能不同。

下面的例子将说明这个问题：

----创建一个分区表echo_tab(OLAP数据库中，由于表中的数据量非常大，大多数情况下，都采取了分区技术)，并插入比较多的数据来模拟一个比较大的表----
      1 SQL> create table echo_tab(object_id int,object_name varchar2(1000)) partition by range (object_id)
      2   2  (
      3   3    partition p1 values less than (5000),
      4   4    partition p2 values less than (10000),
      5   5    partition p3 values less than (15000),
      6   6    partition p4 values less than (20000),
      7   7    partition pm values less than (maxvalue)
      8   8  );
      9 
     10 Table created.
     11 
     12 SQL> begin 
     13   2     for i in 1..300 loop
     14   3        insert into echo_tab select object_id,object_name from dba_objects;
     15   4     end loop;
     16   5  end;
     17   6  /
     18 
     19 PL/SQL procedure successfully completed.
     20 
     21 SQL> select count(*) from echo_tab partition(p1);
     22 
     23   COUNT(*)
     24 ----------
     25    1479000
     26 
     27 SQL> select count(*) from echo_tab partition(p2);
     28 
     29   COUNT(*)
     30 ----------
     31    1433400
     32 
     33 SQL> select count(*) from echo_tab partition(p3);
     34 
     35   COUNT(*)
     36 ----------
     37    1441800
     38 
     39 SQL> select count(*) from echo_tab partition(p4);
     40 
     41   COUNT(*)
     42 ----------
     43    1500000
     44 
     45 SQL> select count(*) from echo_tab partition(pm);
     46 
     47   COUNT(*)
     48 ----------
     49   15903900
     ----在分区表上创建本地索引(echo_tab_ind)----
     50 SQL> create index echo_tab_ind on echo_tab(object_id) local;
     ----对表做一次分析(cascade=>true表示索引同时会被分析)----
     51 SQL> exec dbms_stats.gather_table_stats(user,'echo_tab',cascade=>true);
     52 
     53 PL/SQL procedure successfully completed.
     54 
     55 SQL> set linesize 200;
     56 
     57 SQL> set autotrace traceonly exp;
     ----执行3此只有谓词不相同的SQL语句，并使用下面的名称来观看执行计划---- 
     58 SQL> select object_id,count(*) from echo_tab where object_id>10000 and object_id<40000 group by object_id;
     59 
     60 Execution Plan
     61 ----------------------------------------------------------
     62 Plan hash value: 513472022
     63 
     64 -----------------------------------------------------------------------------------------------------------------
     65 | Id  | Operation         | Name     | Rows    | Bytes |TempSpc| Cost (%CPU)| Time    | Pstart| Pstop |
     66 -----------------------------------------------------------------------------------------------------------------
     67 |   0 | SELECT STATEMENT     |        | 29282 |   142K|    | 18393   (3)| 00:03:41 |    |    |
     68 |   1 |  PARTITION RANGE ITERATOR|        | 29282 |   142K|    | 18393   (3)| 00:03:41 |     3 |     5 |
     69 |   2 |   HASH GROUP BY      |        | 29282 |   142K|    99M| 18393   (3)| 00:03:41 |    |    |
     70 |*  3 |    INDEX FAST FULL SCAN  | ECHO_TAB_IND |  8667K|    41M|    |  8851   (2)| 00:01:47 |     3 |     5 |
     71 -----------------------------------------------------------------------------------------------------------------
     72 
     73 Predicate Information (identified by operation id):
     74 ---------------------------------------------------
     75 
     76    3 - filter("OBJECT_ID"<40000 AND "OBJECT_ID">10000)
     77 
     78 SQL> select object_id,count(*) from echo_tab where object_id>1 and object_id<5999 group by object_id;
     79 
     80 Execution Plan
     81 ----------------------------------------------------------
     82 Plan hash value: 2035879488
     83 
     84 -----------------------------------------------------------------------------------------------------
     85 | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
     86 -----------------------------------------------------------------------------------------------------
     87 |   0 | SELECT STATEMENT     |        |  5853 | 29265 |  1987   (4)| 00:00:24 |        |        |
     88 |   1 |  PARTITION RANGE ITERATOR|        |  5853 | 29265 |  1987   (4)| 00:00:24 |      1 |      2 |
     89 |   2 |   HASH GROUP BY      |        |  5853 | 29265 |  1987   (4)| 00:00:24 |        |        |
     90 |*  3 |    TABLE ACCESS FULL     | ECHO_TAB |  1732K|  8460K|  1936   (1)| 00:00:24 |      1 |      2 |
     91 -----------------------------------------------------------------------------------------------------
     92 
     93 Predicate Information (identified by operation id):
     94 ---------------------------------------------------
     95 
     96    3 - filter("OBJECT_ID"<5999 AND "OBJECT_ID">1)
     97 
     98 SQL> select object_id,count(*) from echo_tab where object_id>200000 group by object_id;
     99 
    100 Execution Plan
    101 ----------------------------------------------------------
    102 Plan hash value: 2469665820
    103 
    104 -------------------------------------------------------------------------------------------------------
    105 | Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    106 -------------------------------------------------------------------------------------------------------
    107 |   0 | SELECT STATEMENT       |          |     1 |     5 |     3    (0)| 00:00:01 |       |       |
    108 |   1 |  PARTITION RANGE SINGLE|          |     1 |     5 |     3    (0)| 00:00:01 |     5 |     5 |
    109 |   2 |   SORT GROUP BY NOSORT |          |     1 |     5 |     3    (0)| 00:00:01 |       |       |
    110 |*  3 |    INDEX RANGE SCAN    | ECHO_TAB_IND |     1 |     5 |     3    (0)| 00:00:01 |     5 |     5 |
    111 -------------------------------------------------------------------------------------------------------
    112 
    113 Predicate Information (identified by operation id):
    114 ---------------------------------------------------
    115 
    116    3 - access("OBJECT_ID">200000)

从结果中我们可以清楚地看到，3条SQL语句只有谓词不同，但Oracle却分别选择了不同的执行计划：

第一条：INDEX Fast Full Scan

第二条：Table Access Full

第三条：INDEX Range Scan

**三、总结：**

在这种环境(OLAP),SQL语句即使只有谓词条件不同，Oracle仍然可能采取不同的执行计划，因为那样的代价将最小(Oracle从我们对表分析的数据中计算出哪一种访问方式代价最小)。因此，像OLTP那样所有的SQL都使用相同的执行计划，对OLAP数据库显然是不适合的，有时候甚至是灾难性的。

因此，根据上面的例子和分析，我们大致可以得出这样的结论：

(1)、OLAP系统完全没有必要绑定变量，那样只会带来负面的影响，比如导致SQL选择了错误的执行计划，这个代价有时候是灾难性的；让Oracle对每条SQL做硬分析，确切地知道谓词条件的值，这对执行计划的选择至关重要，这样做的原因是，在OLAP系统中，SQL硬分析的代价是可以忽略的，系统的资源基本上是用于做大的SQL查询，SQL解析消耗的资源显得微不足道，所以得到一个最优的执行计划变得尤为重要。

(2)、在OLAP系统上，让Oracle确切地知道谓词的数值至关重要。它直接决定了SQL执行计划的选择，这样做的方式就是不要绑定变量。

(3)、在OLAP系统中，表、索引的分析显得至关重要，因为它是Oracle为SQL做出正确的执行计划的信息来源和依据，所以需要建立一套能够满足系统要求的对对象分析的定时执行计划任务。