【TUNE_ORACLE】列出有標量子查詢的SQL參考

實驗環境

搭建平臺：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);