游标被刷出共享池如何跟踪

clevernby

近期系统中有个游标每天都会被刷出共享池，现象是按照SQL_ID查询v$sql和v$sql_plan都没有结果，现在想知道游标是在什么情况下被刷出的，是正常的age out还是人为操作导致，什么时间发送的哪个会话发起的，有办法实现吗？

Liu Maclean(刘相兵

给出sQL文本 ， 给出 你的诊断过程，不太信任你的结论

clevernby

Liu Maclean(刘相兵 发表于 2017-2-16 14:38
给出sQL文本 ， 给出 你的诊断过程，不太信任你的结论

谢谢刘大。完整的案例如下
首先SQL文本为
INSERT INTO tmp_table1
  SELECT day
        ,a.ex
        ,b.br
        ,traid
        ,c.inv
        ,d.inst
        ,tpar
        ,tgrp
        ,vol
        ,pri
        ,dir
        ,trat
        ,OFF
        ,hed
        ,userid
        ,prodc
        ,tt
    FROM t_table1  a
        ,t_table2  b
        ,t_table3  c
        ,t_table4  d
   WHERE a.day = :B1
         AND a.part = b.part
         AND a.ex = b.ex
         AND b.br = :B2
         AND b.isactive = 1
         AND a.ex = c.ex
         AND a.cl = c.cl
         AND a.ex = d.ex
         AND a.exi = d.exi
         AND a.tt IN (1,2)
         AND c.ic = 1;
这个SQL中有个重要的谓词a.day = :B1，a.day的值只有一种取值而且每天都会变，由于统计信息收集方案不当，这个SQL每次执行时a.day这一列的统计信息和实际表里的值大相径庭，这将导致t_table1的card被误估为1从而产生错误执行计划。
由于一些无法控制的原因，目前SQL和统计信息收集方案暂时不能调整，于是我在某一天这个SQL执行前收集了一次统计信息，这样SQL执行后得到一个正确的执行计划，在我的期望中后续执行复用该执行计划来达到workaround的目的。
第二天SQL执行前在系统中查询gv$sql和gv$sql_plan查不到任何记录
select * from gv$sql where sql_id='xxx'
select * from gv$sql_plan where sql_id='xxx'
后面几天每天都发现，SQL在执行前，共享池中并不存在游标，每次都是硬解析。
那我的问题就是如何诊断游标被刷出共享池，如何确定什么时间、什么会话、什么操作导致的，如何确定是不是正常的共享池age out？
绑定变量B1和B2均为NUMBER型，变量B2的值不会改变，B1的值每天都变

Liu Maclean(刘相兵

尝试 pin  这个SQL 是什么结果？

具体诊断 你需要定期监控 这个SQL的情况， 印象中目前没有主动的当某个SQL被刷出shared pool的警告或对应的event 设置。