大量并发SQL导致数据库性能问题诊断优化

• 1.环境介绍

操作系统：阿里云ECS
数据库：Oracle 11.2.0.1 ADG

• 2.涉及知识点介绍

首先介绍一下绑定变量的一些知识；
当Oracle在解析和执行目标SQL时，会根据目标SQL的SQL文本的哈希值去库缓存中查找匹配的parent cursor，这意味着只要待执行的目标SQL的SQL文本稍有不同，那么据此计算出来的哈希值就极有可能不同（就算是哈希值相同也没有关系，因为Oracle还会继续比对parent cursor所对应的SQL文本），也就是说这些SQL文本不完全相同的目标SQL之间是没法重用解析树和执行计划的。
使用绑定变量的最大的作用就是它可以有效降低系统硬解析的数量。但是绑定变量也不是一定是要用的，对于高并发的OLTP类型的系统，要用绑定变量，因为可以有效的降低系统硬解析的数量，这也是OLTP类型的系统在数据库端具备良好的性能和可扩展性的前提条件；但对于OLAP/DSS类型的系统，贝莱硬解析对系统性能的影响就微乎其微，这种情况下当然可以选择不用绑定变量，因为即使用了对系统性能的提升也非常有限。
但是我们不要当然也不能忘记绑定变量窥探的一个作用和弊端:
对于使用了绑定变量的目标SQL而言，Oracle可以选择如下两种方法来决定其执行计划。
a、使用绑定变量窥探。
b、如果不使用绑定变量窥探，则对于那些可选择率可能会随着具体输入值的不同而不同的谓词条件使用默认的可选择率（例如5%）。
绑定变量窥探（bind peeking）受到隐含参数_optim_peek_user_binds的控制，_optim_peek_user_binds默认值是true，表示默认是被启用的。
绑定变量窥探这种不管后续传入的绑定变量的具体输入值是什么而一直沿用之前硬解析时所产生的解析树和执行计划的特性一直饱受 诟病，这种情况一直到Oracle 11g中引入自适应游标共享后才有所缓解，因为它可能使CBO在某些情况下对应绑定变量的某些具体输入值所选择的执行计划并不是目标SQL在当前情形下的最优执行计划，而且它可能会带来目标SQL执行计划的突然改变，进而直接影响应用系统的性能。
通常情况下，可以使用v$sql_bind_capture来获取绑定变量的值，而我通常使用的语句如下：

select NAME, POSITION, datatype_string, max_length, value_string,ANYDATA.accesstimestamp (value_anydata) 
from v$sql_bind_capture where sql_id='&sql';  

结果就类似如下的显示：

这里使用的列有：
NAME VARCHAR2(30) Name of the bind variable
POSITION NUMBER Position of the bind variable in the SQL statement
DATATYPE_STRING VARCHAR2(15) Textual representation of the bind datatype
MAX_LENGTH NUMBER Maximum bind length
VALUE_STRING VARCHAR2(4000) Value of the bind represented as a string
VALUE_ANYDATA ANYDATA Value of the bind represented using the self-descriptive Sys.AnyData datatype. This representation is useful to programmatically decode the value of the bind variable.
这里着重介绍一下最后一个列：
我们一般通过ANYDATA.accesstimestamp (value_anydata)来获取该列的值，列的描述大致是使用自描述性Sys.AnyData数据类型表示的绑定值。该表示对于以编程方式解码绑定变量的值。
从oracle-base上获得的解释是：
The ANYDATA type includes CONVERT* constructor functions for the majority of Oracle data types that can be accessed from SQL
也就是说其表示的是一个针对数据类型进行的一种convert的构造函数，具体参考
https://oracle-base.com/articles/misc/anydata
好了，铺垫做好了，开始下一步工作

• 3、性能问题场景

最近经过之前做的SQL优化之后晚上日终有所缓解，但是依然不容乐观，今天早上来看了一下elapsed time在凌晨0点到2点之前又标高了。

     17430 2017-04-18 21:00:53 2017-04-18 22:00:56 2.2418E+13 2.2417E+13        15.028409
     17431 2017-04-18 22:00:56 2017-04-18 23:00:58 2.2423E+13 2.2418E+13       84.6113666
     17432 2017-04-18 23:00:58 2017-04-19 00:00:01 2.2424E+13 2.2423E+13       12.7940667
     17433 2017-04-19 00:00:01 2017-04-19 01:00:53 2.2439E+13 2.2424E+13       245.712036
     17434 2017-04-19 01:00:53 2017-04-19 02:00:55 2.2452E+13 2.2439E+13       225.038459
     17435 2017-04-19 02:00:55 2017-04-19 03:00:58 2.2455E+13 2.2452E+13        40.387316
     17436 2017-04-19 03:00:58 2017-04-19 04:00:02 2.2460E+13 2.2455E+13       95.6910478
     17437 2017-04-19 04:00:02 2017-04-19 05:00:04 2.2461E+13 2.2460E+13       .588831683
     17438 2017-04-19 05:00:04 2017-04-19 06:00:07 2.2461E+13 2.2461E+13       2.96520707

无奈收集对应的awr报告看了一下：

而语句都类似于如下的形式：

select count(*) as N_M1_Act
  from (select t.loanacno
          from ReportOverdueFlowDtStatistics t
         where t.accountstatenew = '01'
           and t.apporgcode = :1
           and t.currentdate = :2
           and t.loanPath = '1'
        intersect
        select t.loanacno
          from ReportOverdueFlowDtStatistics t
         where t.accountstatenew = '02'
           and t.apporgcode = :3
           and t.currentdate = :4
           and t.loanPath = '1')  

也是很简单的语句。查看其执行计划如下：

讲道理的话，还是蛮好的，为何一次执行要4秒多呢，再加上大量的并发执行，直接导致了一堆的队列等待。
持着怀疑的态度，在sqlplus中看这条SQL的真正执行计划，但是由于是凌晨的SQL，已经无法使用display_cursor查看了，可能是已经被刷出了共享池。

SQL> select * from table(dbms_xplan.display_cursor('9hkvc6qf0v5f4'));
PLAN_TABLE_OUTPUT
-----------------------------------------------
SQL_ID  9hkvc6qf0v5f4, child number 0
select count(*) as N_M1_Act from ( select t.loanacno  from
ReportOverdueFlowDtStatistics t where t.accountstatenew = '01' and
t.apporgcode=:1 and t.currentdate=:2 and t.loanPath='1' intersect
select t.loanacno from ReportOverdueFlowDtStatistics t where
t.accountstatenew = '02' and  t.apporgcode=:3 and t.currentdate=:4 and
t.loanPath='1' )
NOTE: cannot fetch plan for SQL_ID: 9hkvc6qf0v5f4, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
13 rows selected.

然后使用display_awr看出了端倪：

SQL> select * from table(dbms_xplan.display_awr('9hkvc6qf0v5f4'));                           
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID 9hkvc6qf0v5f4
--------------------
select count(*) as N_M1_Act from ( select t.loanacno  from
ReportOverdueFlowDtStatistics t where t.accountstatenew = '01' and
t.apporgcode=:1 and t.currentdate=:2 and t.loanPath='1' intersect
select t.loanacno from ReportOverdueFlowDtStatistics t where
t.accountstatenew = '02' and  t.apporgcode=:3 and t.currentdate=:4 and
t.loanPath='1' )
Plan hash value: 740429392
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |   |   | 48989 (100)|      |
|   1 |  SORT AGGREGATE         |               |     1 |   |        |      |
|   2 |   VIEW              |               |     6 |   | 48989   (1)| 00:09:48 |
|   3 |    INTERSECTION         |               |   |   |        |      |
|   4 |     SORT UNIQUE         |               |   664 | 23240 | 48958   (1)| 00:09:48 |
|   5 |      TABLE ACCESS FULL      | REPORTOVERDUEFLOWDTSTATISTICS |   664 | 23240 | 48957   (1)| 00:09:48 |
|   6 |     SORT UNIQUE         |               |     6 |   210 |    31   (4)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| REPORTOVERDUEFLOWDTSTATISTICS |     6 |   210 |    30   (0)| 00:00:01 |
|   8 |       INDEX RANGE SCAN      | IDX_RPTOFLOWDTSTATISTICS_02   |    38 |   |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
25 rows selected.

可以看到对于intersect上边的查询语句走了全表扫描，并且主要消耗时间也是在这里，而intersect下边的查询消耗可以忽略不计。
这就奇怪了，这样出现了关键的问题：
问题：SQL语句类似为何执行计划一个走索引一个不走索引呢？
然后查看v$active_session_histroy视图