抓出走了 FILTER 的 SQL

此脚本不依赖统计信息。

当 where 子查询没能 unnest，执行计划中就会出现 FILTER，对于此类 SQL，我们应该在上线之前对其进行改写，避免执行计划中出现 FILTER，以下脚本可以抓出 where 子查询没能 unnest 的 SQL。

select parsing_schema_name schema, sql_id, sql_text
  from v$sql
 where parsing_schema_name = 'SCOTT'
   and (sql_id, child_number) in
       (select sql_id, child_number
          from v$sql_plan
         where operation = 'FILTER'
           and filter_predicates like '%IS NOT NULL%'
        minus
        select sql_id, child_number
          from v$sql_plan
         where object_owner = 'SYS');

我们在 Scott 账户中运行如下 SQL 并且查看执行计划。

 select *
   from dept
  where exists (select null
            from emp
          where dept.deptno = emp.deptno
           start with empno = 7698
          connect by prior empno = mgr);

    DEPTNO DNAME      LOC
---------- ---------- ---------------------------------------
        30 SALES      CHICAGO
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4210865686
-------------------------------------------------------------------------------------
| Id|Operation                                 |Name|Rows|Bytes| Cost(%CPU)|Time    |
-------------------------------------------------------------------------------------
|  0|SELECT STATEMENT                          |    |   1|   20|    9   (0)|00:00:01|
|* 1| FILTER                                   |    |    |     |           |        |
|  2|  TABLE ACCESS FULL                       |DEPT|   4|   80|    3   (0)|00:00:01|
|* 3|  FILTER                                  |    |    |     |           |        |
|* 4|  CONNECT BY NO FILTERING WITH SW (UNIQUE)|    |    |     |           |        |
|  5|    TABLE ACCESS FULL                     |EMP |  14|  154|    3   (0)|00:00:01|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE "EMP"."DEPTNO"=:B1 START WITH
              "EMPNO"=7698 CONNECT BY "MGR"=PRIOR "EMPNO"))
   3 - filter("EMP"."DEPTNO"=:B1)
   4 - access("MGR"=PRIOR "EMPNO")
       filter("EMPNO"=7698)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         36  consistent gets
          0  physical reads
          0  redo size
        550  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          1  rows processed

以上 SQL 执行计划中出现了 FILTER，我们通过脚本抓出走了 FILTER 的 SQL。

 select parsing_schema_name schema, sql_id, sql_text
    from v$sql
   where parsing_schema_name = 'SCOTT'
     and (sql_id, child_number) in
         (select sql_id, child_number
            from v$sql_plan
          where operation = 'FILTER'
            and filter_predicates like '%IS NOT NULL%'
          minus
          select sql_id, child_number
            from v$sql_plan
           where object_owner = 'SYS');

SCHEMA     SQL_ID          SQL_TEXT
---------- --------------- ---------------------------------------------
SCOTT      8rmn2fn149y2z   select * from dept   where exists (select null from emp 
                           where dept.deptno = emp.deptno   start with em
                           pno = 7698  connect by prior empno = mgr)