SQL执行时间不稳定

                                   SQL执行时间不稳定

       有朋友问同一个SQL执行时间很不稳定，时快时慢.他首先想到的是不是执行计划
改变了，在检查了相应的SQL_ID和plan_hash_value后，发现没有这些问题，再检查sql
执行慢的时间段的ash后，发现也没有存在争用问题.但是发现有些步骤是执行时间比较
长.
    
      为什么执行计划(plan_hash_value)是一样的，但是时间变得不稳定呢?我们知道
plan_hash_value是通过访问对像，访问方法，join方法和访问顺序计算来的，但是对于
一个谓词这样很重要的信息没有计算进去的，当然对于绑定变量这种sql,在执行的时候
才会得到相应的变量值，而在执行计划生成阶段只能通过peek得到，对于后续的变量无法
得知变量的值,所以导致执行时间产生很大的差异.

  来看一个测试例子,环境 :oracle 11.1.0.7.12,os:redhat 5.8
创建两个表,分别insert 10万条相同的数据和1条相同的数据
create table t1 (a int);
create table t2 (a int);

declare
i int;
begin
   for i in 1..100000
      loop
         insert into t1 values(10);    --对于t2也运行相同insert
         end loop;
     end;

insert into t2 values(1);
insert into t1 values(1);

SQL> var i number;
SQL> exec :i:=1;

SQL>  select count(*)
  2    from t1,t2
  3    where t1.a=t2.a
  4     and t1.a=:i
  5     ;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.03    --执行时间0.03秒

Execution Plan
----------------------------------------------------------
Plan hash value: 906334482

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

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

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

|   0 | SELECT STATEMENT    |      |     1 |    26 |       | 74210 (100)| 00:14:
51 |

|   1 |  SORT AGGREGATE     |      |     1 |    26 |       |            |
   |

|*  2 |   HASH JOIN         |      |    12G|   299G|  2624K| 74210 (100)| 00:14:
51 |

|*  3 |    TABLE ACCESS FULL| T1   |   107K|  1362K|       |    70   (3)| 00:00:
01 |

|*  4 |    TABLE ACCESS FULL| T2   |   115K|  1460K|       |    70   (3)| 00:00:
01 |

 

SQL> exec :i:=10;  --修改绑定的值为10

SQL> select count(*)
  2    from t1,t2
  3    where t1.a=t2.a
  4     and t1.a=:i
  5     ;

  COUNT(*)
----------
1.0000E+10

Elapsed: 00:05:12.04   --执时时间为05:12

Execution Plan
----------------------------------------------------------
Plan hash value: 906334482

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

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

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

|   0 | SELECT STATEMENT    |      |     1 |    26 |       | 74210 (100)| 00:14:
51 |

|   1 |  SORT AGGREGATE     |      |     1 |    26 |       |            |
   |

|*  2 |   HASH JOIN         |      |    12G|   299G|  2624K| 74210 (100)| 00:14:
51 |

|*  3 |    TABLE ACCESS FULL| T1   |   107K|  1362K|       |    70   (3)| 00:00:
01 |

|*  4 |    TABLE ACCESS FULL| T2   |   115K|  1460K|       |    70   (3)| 00:00:
01 |

可以看到同一个SQL,sql_id和plan_hash_value没有任何变化，但是由于谓词的选择性不一样
带来的SQL执行时间产生很大差异.