抓出有标量子查询的 SQL

此脚本不依赖统计信息。

在开发过程中，我们应该尽量避免编写标量子查询。我们可以通过分析执行计划，抓出标量子查询语句。同一个 SQL 语句，执行计划中如果有两个或者两个以上的 depth=1 的执行计划就表示 SQL 中出现了标量子查询。以下脚本抓出 Scott 账户下在 SQL*Plus 中运行过的标量子查询语句。

select sql_id, sql_text, module
  from v$sql
 where parsing_schema_name = 'SCOTT'
   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 = 'SCOTT' or object_owner is null))
         where cnt >= 2);

我们在 SQL*Plus 中运行如下标量子查询语句。

select dname,
   (select max(sal) from emp where deptno = d.deptno) max_sal
  from dept d;

DNAME             MAX_SAL
-------------- ----------
ACCOUNTING           5000
RESEARCH             3000
SALES                2850
OPERATIONS

我们利用以上脚本将刚运行过的标量子查询抓出。

 select sql_id, sql_text, module
    from v$sql
   where parsing_schema_name = 'SCOTT'
    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 = 'SCOTT' or object_owner is null))
           where cnt >= 2);

SQL_ID          SQL_TEXT                                        MODULE
--------------- ----------------------------------------------  ---------------------
739fhcu0pbz28   select dname,  (select max(sal) from emp where  SQL*Plus
                 deptno = d.deptno) max_sal from dept d