抓出返回行数较多的嵌套循环 SQL

此脚本不依赖统计信息。

两表关联返回少量数据应该走嵌套循环，如果返回大量数据，应该走 HASH 连接，或者是排序合并连接。如果一个 SQL 语句返回行数较多（大于 1 万行），SQL 的执行计划在最后几步（Id<=5）走了嵌套循环，我们可以判定该执行计划中的嵌套循环是有问题的，应该走 HASH 连接。以下脚本抓出返回行数较多的嵌套循环 SQL。

select *
  from (select parsing_schema_name schema,
               sql_id,
               sql_text,
               rows_processed / executions rows_processed
          from v$sql
         where parsing_schema_name = 'SCOTT'
           and executions > 0
           and rows_processed / executions > 10000
         order by 4 desc) a
 where a.sql_id in (select sql_id
                      from v$sql_plan
                     where operation like '%NESTED LOOPS%'
                       and id <= 5);

在 scott 账户中分别创建 a 表和 b 表以及一个索引。

create table a as select * from dba_objects;

Table created.

create table b as select * from dba_objects;

Table created.

create index idx_b on b(object_id);

Index created.

运行如下 SQL 并且查看执行计划。

select /*+ use_nl(a,b) */ * from a,b where a.object_id=b.object_id;

72695 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2104163270
-------------------------------------------------------------------------------------
| Id | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |       | 60140 |    23M|   120K  (1)| 00:24:07 |
|  1 |  NESTED LOOPS                |       |       |       |            |          |
|  2 |   NESTED LOOPS               |       | 60140 |    23M|   120K  (1)| 00:24:07 |
|  3 |    TABLE ACCESS FULL         | A     | 60140 |    11M|   187   (2)| 00:00:03 |
|* 4 |    INDEX RANGE SCAN          | IDX_B |     1 |       |     1   (0)| 00:00:01 |
|  5 |   TABLE ACCESS BY INDEX ROWID| B     |     1 |   207 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
        632  recursive calls
          0  db block gets
      22985  consistent gets
       1196  physical reads
          0  redo size
    6085032  bytes sent via SQL*Net to client
      53725  bytes received via SQL*Net from client
       4848  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72695  rows processed

我们可以使用脚本将错误的嵌套循环抓出。

 select *
    from (select parsing_schema_name schema,
                 sql_id,
                 sql_text,
                 rows_processed / executions rows_processed
            from v$sql
           where parsing_schema_name = 'SCOTT'
             and executions > 0
             and rows_processed / executions > 10000
           order by 4 desc) a
   where a.sql_id in (select sql_id
                        from v$sql_plan
                       where operation like '%NESTED LOOPS%'
                         and id <= 5);

SCHEMA          SQL_ID          SQL_TEXT                       ROWS_PROCESSED
--------------- --------------- ------------------------------ --------------
SCOTT           4dwp5u34yv7mj   select /*+ use_nl(a,b) */ *             72695
                                from a,b where a.object_id=b.object_id