实验环境

搭建平台：VMware Workstation

OS：RHEL 6.10

Grid&DB：Oracle 11.2.0.4

SQL参考

--只显示当前同一种编译环境运行过的SQL，比如SQLplus和plsql dev

select sql_id, sql_text, module

  from v$sql

 where parsing_schema_name = 'TEST'

   --and module = 'SQL*Plus'

   and sql_id in

       (select sql_id

          from (select sql_id,

                       count(*) over(partition by sql_id, child_number, depth) cnt

                  from v$sql_plan

                 where depth = 1

                   and (object_owner = 'TEST' or object_owner is null))

         where cnt >= 2);