抓出表被多次反复调用 SQL

此脚本不依赖统计信息。

在开发过程中，我们应该避免在同一个 SQL 语句中对同一个表多次访问。我们可以通过下面 SQL 抓出同一个 SQL 语句中对某个表进行多次扫描的 SQL。

select a.parsing_schema_name schema,
       a.sql_id,
       a.sql_text,
       b.object_name,
       b.cnt
  from v$sql a,
       (select *
          from (select sql_id,
                       child_number,
                       object_owner,
                       object_name,
                       object_type,
                       count(*) cnt
                  from v$sql_plan
                 where object_owner = 'SCOTT'
                 group by sql_id,
                          child_number,
                          object_owner,
                          object_name,
                          object_type)
         where cnt >= 2) b
 where a.sql_id = b.sql_id
   and a.child_number = b.child_number;

我们在 Scott 账户中运行如下 SQL。

select ename,job,deptno from emp where sal>(select avg(sal) from emp);

以上 SQL 访问了 emp 表两次，我们可以通过脚本将其抓出。

select a.parsing_schema_name schema,
         a.sql_id,
         a.sql_text,
         b.object_name,
         b.cnt
    from v$sql a,
        (select *
           from (select sql_id,
                         child_number,
                         object_owner,
                        object_name,
                         object_type,
                         count(*) cnt
                    from v$sql_plan
                   where object_owner = 'SCOTT'
                   group by sql_id,
                            child_number,
                            object_owner,
                            object_name,
                            object_type)
           where cnt >= 2) b
   where a.sql_id = b.sql_id
     and a.child_number = b.child_number;

SCHEMA          SQL_ID          SQL_TEXT                      OBJECT_NAME         CNT
--------------- --------------- ----------------------------- ------------ ----------
SCOTT           fdt0z70z43vgv   select ename,job,deptno from  EMP                  2
                                emp where sal>(select avg(sal)
                                 from emp)