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

迷南。 2022-06-17 06:40 271阅读 0赞

*  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';

结果就类似如下的显示：  
![这里写图片描述][SouthEast]  
这里使用的列有：  
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][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报告看了一下：  
![这里写图片描述][SouthEast 1]  
而语句都类似于如下的形式：

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

也是很简单的语句。查看其执行计划如下：  
![这里写图片描述][SouthEast 2]  
讲道理的话，还是蛮好的，为何一次执行要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视图  
![这里写图片描述][SouthEast 3]  
可以看到大量的read by other session的等待事件  
这里介绍一下这个等待事件：  
这个等待事件是从buffer busy wait这个等待事件剥离出来的。  
什么情况会发生这个等待事件呢？  
（1）某个会话正在查询某个表的数据，把这些数据从磁盘里读到高速缓存区中，而其他会话这时如果也在请求相同的数据块。如果响应的数据块还没完全读到缓存中，就会发生这样这个等待事件。  
（2）因为有会话正在读取数据在内存，因此该事件同时会伴随着sequential read或者是scattered read，一般情况不会孤立存在。  
从上图中也可以看到大量的read by other session的会话被同一个会话阻塞，阻塞的会话正在进行着db file scattered read，这些会话又在干什么呢？  
以sql\_id为9hkvc6qf0v5f4为例查看正是如下的SQL语句，正好和awr报告中相吻合，也支持了问题的提出。

那么我们再来看看这个正在被阻塞等待要执行的会话是什么SQL语句呢？  
随便取一个sql\_id比如65c0dsv0zbba4的语句查看发现：

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

同样是类似的语句的存在在等待着执行。

*  4、性能问题分析

从问题场景的介绍，大概我们了解了之所以出现大量的时间消耗，大量的等待事件，都定位到了一类类似的SQL语句的性能差而导致的结果。  
这里强调问题就是：  
SQL语句类似为何执行计划一个走索引一个不走索引呢？  
首先看一下这个索引：  
![这里写图片描述][SouthEast 4]  
很明显是一个复合索引，索引前导列是currentdate列，之后是apporgcode，accountstatenew列  
对应列的信息统计如下：

SQL>  select count(*) from ReportOverdueFlowDtStatistics; COUNT(*)
    ----------
      44865762
    SQL> select accountstatenew,count(accountstatenew) from ReportOverdueFlowDtStatistics group by accountstatenew;
    AC COUNT(ACCOUNTSTATENEW)
    -- ----------------------
    04         942986
    01       36566202
    02        1487274
    03        1064456
    05        4804844
    SQL>  select apporgcode, count(apporgcode) from ReportOverdueFlowDtStatistics group by apporgcode;
    APPORGCODE COUNT(APPORGCODE)
    ---------- -----------------
    014401            354669
    055101          1277
    053401          1029
    053301          1508
    058101           276
    018101          1870
    052101           156
    057201            84
    012205             59239
    013402            378892
    017301            688490
    016103            552585
    012113             23313
    053101           517
    051101           477
    059201           434
    011203            525865
    017209            167779
    011507             66809
    015502             23786
    055201             9
    014302            407479
    011307             61394
    014411             48066
    059101            31
    013405            527166
    ......
    014506             58075
    057401            46
    217 rows selected.

从查询结果看，ReportOverdueFlowDtStatistics表有近4500w条记录，其中currentdate是时间列，肯定是随时变化的，apporgcode列有217个不同的值，并且从结果看分布很不均匀，有大到50-60w记录的列也有小到只有9条数据的列；accountstatenew列只有5个值其中值为01的数据最多有3500w条之多，其他的都分布较为均匀。  
对应这三列的数据类型为：  
currentdate数据类型是date  
apporgcode数据类型是varchar2()  
accountstatenew数据类型是varchar2()  
所以复合索引创建是可行的。  
然后我们对apporgcode列的值进行一下排序如下：  
![这里写图片描述][SouthEast 5]  
可以看到有一半以上的数据量是在20w数据以上，绝大部分数据在10w以上。  
然后我们锁定其中一个SQL语句sql\_id为9hkvc6qf0v5f4为例，其SQL语句如下：

我们来查询其绑定变量值：  
![这里写图片描述][SouthEast 6]  
然后取第一组绑定变量值带入查询：  
但是这里有一个奇怪的问题就是绑定变量:2对应的日期是timestamp类型的，并且是由anydata构造出来的数据，如下测试一下类似这样的一种转换：

SQL>   select to_timestamp('2017-04-17 12:00:00','yyyy-mm-dd hh24:mi:ss') from dual;
    TO_TIMESTAMP('2017-04-1712:00:00','YYYY-MM-DDHH24:MI:SS')
    ---------------------------------------------------------------------------
    17-APR-17 12.00.00.000000000 PM

正好和图中显示的timestamp的值是一致的，但是我们知道currentdate列是数据类型是date列的，所以怀疑这里存在隐式转换。  
既然拿到了值，我们可以测试一把：  
当按照实际环境的运行的话，其实类似于将第一个查询中的currentdate列进行to\_timestamp转换

SQL> select count(*) as N_M1_Act
      from (select t.loanacno
              from ReportOverdueFlowDtStatistics t
             where t.accountstatenew = '01'
               and t.apporgcode = '011303'
               and t.currentdate = to_timestamp('2017-04-17 12:00:00','yyyy-mm-dd hh24:mi:ss')
               and t.loanPath = '1'
            intersect
            select t.loanacno
              from ReportOverdueFlowDtStatistics t
             where t.accountstatenew = '02'
               and t.apporgcode = '011303'
               and t.currentdate = to_date('2017-04-18 00:00:00','yyyy-mm-dd hh24:mi:ss')
               and t.loanPath = '1');
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4055505861
    -----------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |               |     1 |   |  2589   (1)| 00:00:32 |
    |   1 |  SORT AGGREGATE         |               |     1 |   |        |      |
    |   2 |   VIEW              |               |     1 |   |  2589   (1)| 00:00:32 |
    |   3 |    INTERSECTION         |               |   |   |        |      |
    |   4 |     SORT UNIQUE         |               |     8 |   280 |  2583   (1)| 00:00:31 |
    |*  5 |      TABLE ACCESS BY INDEX ROWID| REPORTOVERDUEFLOWDTSTATISTICS |     8 |   280 |  2582   (1)| 00:00:31 |
    |*  6 |       INDEX SKIP SCAN       | IDX_RPTOFLOWDTSTATISTICS_02   |  2675 |   |   586   (0)| 00:00:08 |
    |   7 |     SORT UNIQUE         |               |     1 |    35 |     6  (17)| 00:00:01 |
    |*  8 |      TABLE ACCESS BY INDEX ROWID| REPORTOVERDUEFLOWDTSTATISTICS |     1 |    35 |     5   (0)| 00:00:01 |
    |*  9 |       INDEX RANGE SCAN      | IDX_RPTOFLOWDTSTATISTICS_02   |     1 |   |     4   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter("T"."LOANPATH"='1')
       6 - access("T"."APPORGCODE"='011303' AND "T"."ACCOUNTSTATENEW"='01')
           filter("T"."APPORGCODE"='011303' AND "T"."ACCOUNTSTATENEW"='01' AND
              INTERNAL_FUNCTION("T"."CURRENTDATE")=TIMESTAMP' 2017-04-17 12:00:00.000000000')
       8 - filter("T"."LOANPATH"='1')
       9 - access("T"."CURRENTDATE"=TO_DATE(' 2017-04-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T"."APPORGCODE"='011303' AND "T"."ACCOUNTSTATENEW"='02')
    Statistics
    ----------------------------------------------------------
          1  recursive calls
          0  db block gets
        906  consistent gets
        902  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到当使用了to\_timestamp的情况下走的是index skip scan，主要消耗时间也是在第一个查询，逻辑读和物理读都是900，从谓词信息来看  
确实发生了隐式转换，将时间转换成了timestamp类型；  
然后我们将to\_timestamp改写成原来的to\_date类型，不让其发生隐式转换情况下：

SQL> select count(*) as N_M1_Act
      from (select t.loanacno
              from ReportOverdueFlowDtStatistics t
             where t.accountstatenew = '01'
               and t.apporgcode = '011303'
               and t.currentdate = to_date('2017-04-17 12:00:00','yyyy-mm-dd hh24:mi:ss')
               and t.loanPath = '1'
            intersect
            select t.loanacno
              from ReportOverdueFlowDtStatistics t
             where t.accountstatenew = '02'
               and t.apporgcode = '011303'
               and t.currentdate = to_date('2017-04-18 00:00:00','yyyy-mm-dd hh24:mi:ss')
               and t.loanPath = '1');
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 270485034
    -----------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |               |     1 |   |    15  (14)| 00:00:01 |
    |   1 |  SORT AGGREGATE         |               |     1 |   |        |      |
    |   2 |   VIEW              |               |     1 |   |    15  (14)| 00:00:01 |
    |   3 |    INTERSECTION         |               |   |   |        |      |
    |   4 |     SORT UNIQUE         |               |     1 |    35 |     9  (12)| 00:00:01 |
    |*  5 |      TABLE ACCESS BY INDEX ROWID| REPORTOVERDUEFLOWDTSTATISTICS |     1 |    35 |     8   (0)| 00:00:01 |
    |*  6 |       INDEX RANGE SCAN      | IDX_RPTOFLOWDTSTATISTICS_02   |     5 |   |     4   (0)| 00:00:01 |
    |   7 |     SORT UNIQUE         |               |     1 |    35 |     6  (17)| 00:00:01 |
    |*  8 |      TABLE ACCESS BY INDEX ROWID| REPORTOVERDUEFLOWDTSTATISTICS |     1 |    35 |     5   (0)| 00:00:01 |
    |*  9 |       INDEX RANGE SCAN      | IDX_RPTOFLOWDTSTATISTICS_02   |     1 |   |     4   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter("T"."LOANPATH"='1')
       6 - access("T"."CURRENTDATE"=TO_DATE(' 2017-04-17 12:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T"."APPORGCODE"='011303' AND "T"."ACCOUNTSTATENEW"='01')
       8 - filter("T"."LOANPATH"='1')
       9 - access("T"."CURRENTDATE"=TO_DATE(' 2017-04-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T"."APPORGCODE"='011303' AND "T"."ACCOUNTSTATENEW"='02')
    Statistics
    ----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          6  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

我们可以看到这时候没有发生隐式转换，并且执行计划走的是index range scan，逻辑读和物理读只有不到10，谓词信息直接通过access条件获取结果。  
但是这里即使是使用to\_timestamp走的也是跳跃扫描而不是全表扫描啊？  
查看一下对应的’011303’对应的数据条数是96379条数据：

SQL> select apporgcode, count(apporgcode) from ReportOverdueFlowDtStatistics where apporgcode='011303' group by apporgcode;
    APPORGCODE COUNT(APPORGCODE)
    ---------- -----------------
    011303             96379

然后我们取一个更大的数比如011503对应有55w条数据：

SQL>  select apporgcode, count(apporgcode) from ReportOverdueFlowDtStatistics where apporgcode='011503' group by apporgcode;
    APPORGCODE COUNT(APPORGCODE)
    ---------- -----------------
    011503            559191

然后查看一下执行计划：  
当使用to\_timestamp时执行计划如下：

SQL> select count(*) as N_M1_Act
      from (select t.loanacno
              from ReportOverdueFlowDtStatistics t
             where t.accountstatenew = '01'
               and t.apporgcode = '011503'
               and t.currentdate = to_timestamp('2017-04-17 12:00:00','yyyy-mm-dd hh24:mi:ss')
               and t.loanPath = '1'
            intersect
            select t.loanacno
              from ReportOverdueFlowDtStatistics t
             where t.accountstatenew = '02'
               and t.apporgcode = '011503'
               and t.currentdate = to_date('2017-04-18 00:00:00','yyyy-mm-dd hh24:mi:ss')
               and t.loanPath = '1');
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 740429392
    -----------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |               |     1 |   |   107K  (1)| 00:21:29 |
    |   1 |  SORT AGGREGATE         |               |     1 |   |        |      |
    |   2 |   VIEW              |               |    11 |   |   107K  (1)| 00:21:29 |
    |   3 |    INTERSECTION         |               |   |   |        |      |
    |   4 |     SORT UNIQUE         |               |  1447 | 50645 |   107K  (1)| 00:21:28 |
    |*  5 |      TABLE ACCESS FULL      | REPORTOVERDUEFLOWDTSTATISTICS |  1447 | 50645 |   107K  (1)| 00:21:28 |
    |   6 |     SORT UNIQUE         |               |    11 |   385 |    34   (3)| 00:00:01 |
    |*  7 |      TABLE ACCESS BY INDEX ROWID| REPORTOVERDUEFLOWDTSTATISTICS |    11 |   385 |    33   (0)| 00:00:01 |
    |*  8 |       INDEX RANGE SCAN      | IDX_RPTOFLOWDTSTATISTICS_02   |    38 |   |     4   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter("T"."APPORGCODE"='011503' AND "T"."LOANPATH"='1' AND "T"."ACCOUNTSTATENEW"='01' AND
              INTERNAL_FUNCTION("T"."CURRENTDATE")=TIMESTAMP' 2017-04-17 12:00:00.000000000')
       7 - filter("T"."LOANPATH"='1')
       8 - access("T"."CURRENTDATE"=TO_DATE(' 2017-04-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T"."APPORGCODE"='011503' AND "T"."ACCOUNTSTATENEW"='02')
    Statistics
    ----------------------------------------------------------
          1  recursive calls
          0  db block gets
         393452  consistent gets
         393393  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

果然走了和实际的执行计划一样，全表扫描，发生了隐式转换，逻辑读和物理读有40w之多。性能很差。  
但是如果我们使用to\_date的情况下，执行计划就是完全另外一个样子：

SQL> select count(*) as N_M1_Act
      from (select t.loanacno
              from ReportOverdueFlowDtStatistics t
             where t.accountstatenew = '01'
               and t.apporgcode = '011503'
               and t.currentdate = to_date('2017-04-17 12:00:00','yyyy-mm-dd hh24:mi:ss')
               and t.loanPath = '1'
            intersect
            select t.loanacno
              from ReportOverdueFlowDtStatistics t
             where t.accountstatenew = '02'
               and t.apporgcode = '011503'
               and t.currentdate = to_date('2017-04-18 00:00:00','yyyy-mm-dd hh24:mi:ss')
               and t.loanPath = '1');
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 270485034
    -----------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |               |     1 |   |   677   (1)| 00:00:09 |
    |   1 |  SORT AGGREGATE         |               |     1 |   |        |      |
    |   2 |   VIEW              |               |    11 |   |   677   (1)| 00:00:09 |
    |   3 |    INTERSECTION         |               |   |   |        |      |
    |   4 |     SORT UNIQUE         |               |   246 |  8610 |   643   (1)| 00:00:08 |
    |*  5 |      TABLE ACCESS BY INDEX ROWID| REPORTOVERDUEFLOWDTSTATISTICS |   246 |  8610 |   642   (0)| 00:00:08 |
    |*  6 |       INDEX RANGE SCAN      | IDX_RPTOFLOWDTSTATISTICS_02   |   852 |   |     6   (0)| 00:00:01 |
    |   7 |     SORT UNIQUE         |               |    11 |   385 |    34   (3)| 00:00:01 |
    |*  8 |      TABLE ACCESS BY INDEX ROWID| REPORTOVERDUEFLOWDTSTATISTICS |    11 |   385 |    33   (0)| 00:00:01 |
    |*  9 |       INDEX RANGE SCAN      | IDX_RPTOFLOWDTSTATISTICS_02   |    38 |   |     4   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter("T"."LOANPATH"='1')
       6 - access("T"."CURRENTDATE"=TO_DATE(' 2017-04-17 12:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T"."APPORGCODE"='011503' AND "T"."ACCOUNTSTATENEW"='01')
       8 - filter("T"."LOANPATH"='1')
       9 - access("T"."CURRENTDATE"=TO_DATE(' 2017-04-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T"."APPORGCODE"='011503' AND "T"."ACCOUNTSTATENEW"='02')
    Statistics
    ----------------------------------------------------------
          1  recursive calls
          0  db block gets
         42  consistent gets
         40  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到第一个查询走了index range scan，逻辑读和物理读只有40，谓词信息也显示access直接获取数据。

5、解决总结  
由上述分析结论可以得到，可能由于数据库绑定变量窥探的特性，应该是由于传入的参数最开始对应的值很大，并且根据真实执行计划，使用了to\_timestamp的隐式转换，导致绑定变量的赋值出现问题，再加上apporgcode对应的值的数量大部分数据在20w以上，所以可能自适应游标未能成功再次获得好的执行计划，也就没有使用index skip scan，进而使得SQL语句的第一个查询找了全表扫描，性能下降；也可能是因为传入的绑定变量值一直都对应的数据量很大导致未能触发自适应游标特性。  
但是最大的症结点是在于to\_timestamp的隐式转换，造成的性能问题最大，如果更换成to\_date效果就完全不一样了。  
之后提交开发前端程序，旨在进行前端程序的修改，避免ANYDATA对于绑定变量的隐式转换的问题。

[SouthEast]: /images/20220617/efb99b441ca3401ab8bda35d2d5ede2e.png
[https_oracle-base.com_articles_misc_anydata]: https://oracle-base.com/articles/misc/anydata
[SouthEast 1]: /images/20220617/3cdc1da9ca8b4adc8ee38dd662b2d843.png
[SouthEast 2]: /images/20220617/6682e8b44cde48d4b335966cb68fc940.png
[SouthEast 3]: /images/20220617/42cbb78df72e4fb194b329ec4f2ddae5.png
[SouthEast 4]: /images/20220617/8d21cab2605644aa96adc76b6f0ba186.png
[SouthEast 5]: /images/20220617/f04146a331704e1fa2a38ed9dafb42af.png
[SouthEast 6]: /images/20220617/d9913b6992f64d77a0bc45e84ff61967.png