oracle每月20号执行,sql每一个session第一次执行固定花费20秒

大家好，现在遇到一个问题

现象：一条sql每一个session第一次执行固定花费20秒

环境，oracle 11g 11.2.0.4 ,RAC 2节点，centos 6.8

sql语句如下：

select ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;

起初以为是执行计划或者统计信息问题，重新收集，然后还是一样，新开一个session，执行第一次花费20秒

执行计划如下：

SQL> select ST_AsText(ST_Geometry('POINT (10 10)', 0)) from dual;

ST_ASTEXT(ST_GEOMETRY('POINT(1010)',0))

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

POINT  ( 10.00000000 10.00000000)

Execution Plan

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

Plan hash value: 1388734953

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

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

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

|   0 | SELECT STATEMENT |        |     1 |     2   (0)| 00:00:01 |

|   1 |  FAST DUAL         |        |     1 |     2   (0)| 00:00:01 |

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

Statistics

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

2092  recursive calls

208  db block gets

3232  consistent gets

16  physical reads

0  redo size

1167  bytes sent via SQL*Net to client

949  bytes received via SQL*Net from client

5  SQL*Net roundtrips to/from client

113  sorts (memory)

0  sorts (disk)

1  rows processed

经过几次执行发现，每次都是固定20秒，所以怀疑不是sql执行计划的问题，这时候拿出AWR报告

发现file:///C:/Users/Administrator/Desktop/%E5%BE%AE%E4%BF%A1%E5%9B%BE%E7%89%87_20181205141235.png

Foreground Wait Class

s - second, ms - millisecond - 1000th of a second

ordered by wait time desc, waits desc

%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Captured Time accounts for 102.6% of Total DB time 204.68 (s)

Total FG Wait Time: 146.06 (s) DB CPU time: 63.86 (s)

Wait Class        Waits        %Time -outs        Total Wait Time (s)        Avg wait (ms)        %DB time

Application        11,266        1        90        8        44.12

Foreground Wait Events

s - second, ms - millisecond - 1000th of a second

Only events with Total Wait Time (s) >= .001 are shown

ordered by wait time desc, waits desc (idle events last)

%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Event        Waits        %Time -outs        Total Wait Time (s)        Avg wait (ms)        Waits /txn        % DB time

External Procedure initial connection        3        0        60        20046        0.00        29.38

External Procedure call        9,708        0        30        3        0.96        14.57

reliable message        3,597        0        22        6        0.36        10.75

Data file init write        2,124        0        7        3        0.21        3.60

Background Wait Events

ordered by wait time desc, waits desc (idle events last)

Only events with Total Wait Time (s) >= .001 are shown

%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Event        Waits        %Time -outs        Total Wait Time (s)        Avg wait (ms)        Waits /txn        % bg time

Streams AQ: qmn coordinator waiting for slave to start        9        89        43        4802        0.00        13.10

发现这个：External Procedure initial connection 稳定耗时20秒，sql是调用一个lib文件，空间库的东西(不懂)，

但是现象是在本地和测试的rac环境，执行都是正常，唯独在线上环境执行，耗时很久，线上环境是本地的cp

本地awr截图

file:///C:/Users/Administrator/Desktop/TIM%E6%88%AA%E5%9B%BE20181205142048.png

Foreground Wait Events

s - second, ms - millisecond - 1000th of a second

Only events with Total Wait Time (s) >= .001 are shown

ordered by wait time desc, waits desc (idle events last)

%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Event        Waits        %Time -outs        Total Wait Time (s)        Avg wait (ms)        Waits /txn        % DB time

reliable message        1,836        0        2        1        1.53        11.16

log file sync        126        0        0        3        0.10        2.96

gc current grant busy        73        0        0        2        0.06        0.82

查阅了很多资料，发现该问题比较少，特来pub看看有人遇到过没有，给个思路。。。

目前应该能确定问题是出现在External Procedure initial connection上。。