总结笔记之sql调优

yellowlee

个人博客的部分内容，共享一下，欢迎拍砖，这个贴出来还真是有点长。。
更多内容见：http://space.itpub.net/16179598/


【调优篇基本原理】系统统计信息
10g使用以cpu开销为主，兼顾io开销的开销模型，oracle优化器很依赖系统的cpu和io性能，故收集系统统计信息是非常重要的。
看一个查询：

SQL> select * from sys.aux_stats$;

SNAME                         PNAME           PVAL1           PVAL2
-------------------- -------------  ---------- ------------
SYSSTATS_INFO                 STATUS                        COMPLETED
SYSSTATS_INFO                 DSTART                        02-17-2008 02:28
SYSSTATS_INFO                 DSTOP                         02-17-2008 02:28
SYSSTATS_INFO                 FLAGS                 1
SYSSTATS_MAIN                 CPUSPEEDNW   576.865795
SYSSTATS_MAIN                 IOSEEKTIM            10
SYSSTATS_MAIN                 IOTFRSPEED         4096
SYSSTATS_MAIN                 SREADTIM           
SYSSTATS_MAIN                 MREADTIM            
SYSSTATS_MAIN                 CPUSPEED               
SYSSTATS_MAIN                 MBRC                                 
SYSSTATS_MAIN                 MAXTHR                          
SYSSTATS_MAIN                 SLAVETHR                                 

13 rows selected
可以看到SYSSTATS_INFO的status为completed，代表成功收集了系统统计信息，而SYSSTATS_MAIN下面的CPUSPEEDNW，IOSEEKTIM，IOTFRSPEED，表示的是noworkload statistics，非工作量系统统计信息，随后的SREADTIM，MREADTIM，CPUSPEED，MBRC，MAXTHR，SLAVETHR则为工作量统计，示意的库并没有工作量统计信息

可以使用下列语句来完成收集工作量统计信息的工作：
begin
Dbms_stats.gather_system_stats(gathering_mode => 'start'
                              );
end;
/
可以看到'SYSSTATS_TEMP下的一些信息，代表目前正在收集统计信息：
SQL> select a.pname,a.pval1,a.pval2
2  from sys.aux_stats$ a where a.sname = 'SYSSTATS_TEMP';

PNAME                              PVAL1 PVAL2
------------------------------ ---------- --------------------------------------------------------------------------------
CACHE_JOB                              1
CPUCYCLES                       11538660
CPUTIM                          22621138
JOB                                    0
MBLKRDS                            11764
MBLKRDTIM                          29600
MBRTOTAL                          277943
SBLKRDS                           190001
SBLKRDTIM                          70980

9 rows selected

使用下列语句来结束收集
begin
Dbms_stats.gather_system_stats(gathering_mode => 'stop'
                              );
end;
/
SQL> select a.pname,a.pval1 from sys.aux_stats$ a where a.sname = 'SYSSTATS_MAIN' ;

PNAME                              PVAL1
------------------------------ ----------
CPUSPEED                             510
CPUSPEEDNW                    576.865795
IOSEEKTIM                             10
IOTFRSPEED                          4096
MAXTHR                        
MBRC                          
MREADTIM                     
SLAVETHR                     
SREADTIM                             0.6

9 rows selected

具体列出上述涉及到的一些统计信息：
CPUSPEED
负载下的cpu速度，即每秒钟的cpu周期数(单位是百万次millon)
CPUSPEEDNW
无负载cpu速度，通过设置gathering_mode=NOWORKLOAD或者手动设置统计。
IOSEEKTIM
Io的查找时间，通过设置gathering_mode=NOWORKLOAD或者手动设置统计，单位是ms。
IOTFRSPEED
Io的传输速度，通过设置gathering_mode=NOWORKLOAD或者手动设置统计，单位是bytes/ms
MAXTHR
最大的io吞吐量，bytes/sec
MBRC
多块读的平均每次读的块的数量，单位是block
MREADTIM
顺序读多块的平均时间
SLAVETHR
平均并行io吞吐量
SREADTIM
随即读单块的平均时间

Dbms_stats还提供了锁定和解锁统计信息的方法：
Lock_schema_stats或者lock_table_stats用来锁定
Unlock_schema_stats或者unlock_table_stats用来解锁

[ 本帖最后由 yellowlee 于 2010-10-24 13:48 编辑 ]

yellowlee

【调优篇基本原理】优化器相关参数配置
看看影响优化器的一些参数(本例的数据库为dedicated server)：
SQL> show parameter optimizer_

NAME                                TYPE       VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling          integer    2
optimizer_features_enable           string     10.2.0.4
optimizer_index_caching             integer    0
optimizer_index_cost_adj            integer    100
optimizer_mode                      string     ALL_ROWS
optimizer_secure_view_merging       boolean    TRUE

optimizer_mode是优化器模式，10g默认是all_rows，也可以设置为其他参数：
SQL> alter session set optimizer_mode = 'asd';  
ERROR:
ORA-00096: invalid value asd for parameter optimizer_mode, must be from among
first_rows_1000, first_rows_100, first_rows_10, first_rows_1, first_rows,
all_rows, choose, rule

从这里的提示可以看出优化模式的选择值。一般来讲，需要获取所有记录更重要，应该吧参数设置为all_rows，很多应用都是这么设置的，包括olap和oltp，一些对相应时间的要求非常高的系统，可能会修改这个值，使用first_rows_n这里的n根据具体应用来选择。

optimizer_dynamic_sampling
这个值的设置范围是0-10，如果optimizer_features_enable设置为10.0.0或者以上，默认为2，9.2.0则为1，9.0.1或者以下则为0，可以在系统级别和会话级别来修改这个参数。

optimizer_index_caching
这个参数影响嵌套循环连接的探测索引的代价，0-100表示在使用嵌套循环或这in-list迭代时将索引缓存在buffer cache的百分比。例如，设置为100，则优化器认为100%能在内存中找到索引数据，会按照这个设定来计算cost和选择执行计划。

optimizer_index_cost_adj
和optimizer_index_caching一样，这个参数也是cbo用来计算cost的，这个参数可以用来调整使用索引的代价，默认值是100，范围是1-10000，它表示索引扫描和全表扫描的比值。例如设置为10，意味着使用通过索引路径访问是正常通过索引路径访问的10%（oracle 10g performace tuning guide），也即可以设置索引参与计算代价的不同值。

optimizer_secure_view_merging
这个参数控制视图合并，默认值是true，在不影响安全问题的情况下允许视图合并，如果设置为false，则在任何情况下允许视图合并。

SQL> show parameter cursor_sharing

NAME                                TYPE       VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                      string     EXACT
SQL>
cursor_sharing
这个参数用来设置sql如何来使用绑定变量，有三个值：SIMILAR, EXACT, FORCE，默认是EXACT，一般来说不会去更改这个设置，即使shared pool hit miss较大，也应该首先去思考是否需要优化应用。这个参数的测试见：
http://space.itpub.net/16179598/viewspace-627268

SQL> show parameters pga_aggregate_target;

NAME                                TYPE       VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                big integer187M
SQL>
10g的pga_aggregate_target默认设置为sga_target的1/3，这个参数指定了pga能达到的期望值，我们知道pga主要用来为连接的用户储存会话的相关内容，其中比较重要的是pga用来做排序操作，例如一些基于排序的操作：order by ,group by ,rollup和开窗函数(window functions)，hash-join，bitmap merge，bitmap create，还有一些使用bulk或者load来写buffer的操作等。
有两个视图来帮助调整这个参数：v$pga_target_advice和v$pga_target_advice_histogram
通过查看视图v$pgastat可以看到pga的一些属性或者状态值。
Oracle推荐的pga_aggregate_target：
Oltp：（系统物理内存大小*80）*20%
Dss：（系统物理内存大小*80）*50%

下面来看看有关pga的一些专用区域大小的具体设置：
SQL> show parameter area_size
NAME                                TYPE       VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size              integer    1048576
create_bitmap_area_size             integer    8388608
hash_area_size                      integer    131072
sort_area_size                      integer    65536
workarea_size_policy                string     AUTO
SQL>                  
workarea_size_policy
对于10g来说，这个参数默认为AUTO，则必须要设置pga_aggregate_target的值，且必须至少大于10M，如果设置为0，即让数据自动调整的话，会出现下列错误：
SQL> alter system set pga_aggregate_target = 0 scope=both;
alter system set pga_aggregate_target = 0 scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_target must be between10Mand4096G-1

sort_area_size
排序区大小，设定oracle一次排序使用的最大内存数量，这个参数设置对排序性能比较重要。
SQL> select name,value
2  from v$sysstat a where a.NAME
3 like '%sort%';

NAME                                                                VALUE
---------------------------------------                         ----------
sorts (memory)                                                     1019434
sorts (disk)                                                             2
sorts (rows)                                                     161842945
可以查看磁盘排序和内存排序的比率来考虑是否设置了较为合理的排序区大小。

hash_area_size
这个参数设置单个会话的hash内存空间大小，可以在实例和会话级修改，默认值是sort_area_size的两倍，上面的数值也可以看出来。对于10g来说dedicated server的hash area是从pga中分配的，而multi-threaded server的hash area是从uga中分配的，如果设置了PGA_AGGRATE_TARGET参数，则将自动管理hsah area大小。

create_bitmap_area_size
指定为创建位图索引而分配的的内存量，10g默认是8m

bitmap_merge_area_size
bitmap合并区大小，在使用bitmap index时，pga中会用一块区域来进行位图索引排序和与位图合并，默认的大小是1M

workarea_size_policy
10g默认值是AUTO，意味着使用pga_aggregate_target来管理PGA内存，同时*_AREA_SIZE的设置全部失效。

另外如果是shared server那么会话内存是共享的，永久区在sga中，而dml/ddl和dedicated server一样都在pga中。

[ 本帖最后由 yellowlee 于 2010-10-24 13:35 编辑 ]

yellowlee

【sql调优之执行计划】获取执行计划
从sql开发进阶到开发高效的sql需要对oracle对sql的解析执行有一些了解。先看看如何获得执行计划。

要使用执行计划需要先执行脚本：$ORACLE_HOME/rdbms/admin/utlxplan.sql
这个脚本会创建一个plan_table表，简单看看这个表的结构：
SQL> desc plan_table
Name           Type         Nullable Default Comments
--------------- ------------- -------- ------- --------
STATEMENT_ID   VARCHAR2(30) Y              --语句id
TIMESTAMP      DATE         Y                --时间戳
REMARKS        VARCHAR2(80) Y                 
OPERATION      VARCHAR2(30) Y              --操作名称         
OPTIONS        VARCHAR2(30) Y               --选项
OBJECT_NODE    VARCHAR2(128) Y              --对象节点   
OBJECT_OWNER   VARCHAR2(30) Y              --对象所有者
OBJECT_NAME    VARCHAR2(30) Y              --对象名称
OBJECT_INSTANCE INTEGER      Y              --对象实例
OBJECT_TYPE    VARCHAR2(30) Y              --对象类型
OPTIMIZER      VARCHAR2(255) Y              --优化器模式
SEARCH_COLUMNS NUMBER       Y               --查询列
ID             INTEGER      Y                 --当前id
PARENT_ID      INTEGER      Y                --父id
POSITION       INTEGER      Y                 --位置id
COST           INTEGER      Y                  --开销
CARDINALITY    INTEGER      Y                --基数
BYTES          INTEGER      Y                 --字节数
OTHER_TAG      VARCHAR2(255) Y               
PARTITION_START VARCHAR2(255) Y              --分区开始         
PARTITION_STOP VARCHAR2(255) Y              --分区结束
PARTITION_ID   INTEGER      Y                --分区id
OTHER          LONG         Y                        
DISTRIBUTION   VARCHAR2(30) Y               --分发      
CPU_COST       INTEGER      Y                 --cpu开销
IO_COST        INTEGER      Y                 --io开销
TEMP_SPACE     INTEGER      Y

看看几个重要字段的具体值：

SQL> select a.operation,
2        a.object_name,
3        a.cost,
4        a.cardinality cd,
5        a.bytes bt,
6        a.io_cost io,
7        a.cpu_cost cpu
8   from plan_table a
9  where a.statement_id is null;

OPERATION                     OBJECT_NAME                         COST
------------------------------ ------------------------------ ----------
       CD        BT        IO       CPU
---------- ---------- ---------- ----------
SELECT STATEMENT                                                      3
        1        20         3

NESTED LOOPS                                                          3
        1        20         3

MERGE JOIN                                                            2
        3        42         2


OPERATION                     OBJECT_NAME                         COST
------------------------------ ------------------------------ ----------
       CD        BT        IO       CPU
---------- ---------- ---------- ----------
TABLE ACCESS                  T_GROUP_POLICY_PRODUCT                 1
        2        24         1

INDEX                         UNI_GROUP_POLICY_PRODUCT               3
        3                    3

BUFFER                                                                1
        2         4         1


OPERATION                     OBJECT_NAME                         COST
------------------------------ ------------------------------ ----------
       CD        BT        IO       CPU
---------- ---------- ---------- ----------
INDEX                         PK_T_PERIOD_TYPE                       1
        2         4         1

TABLE ACCESS                  T_PRODUCT_LIFE                         1
        1         6         1

INDEX                         PK_T_PRODUCT_LIFE
        1


9 rows selected.


运行脚本$ORACLE_HOME/sqlplus/admin/plustrce.sql可以创建plustrace角色便于管理使用执行计划的用户的权限，可以使用grant plustrace role to xxx来赋予用户相关的使用权限。


可以使用explain plan for来获得执行计划，然后查询plan_table（如前面的查询语句）来查看执行计划，或者使用dbms_xplan包的display方法，例如：
Select * from table(dbms_xplan.display);

也可以在sqlplus下使用set autotrace on/traceonly等语句，除了或者执行计划以外，还可以查看到执行统计信息，同时也可以在trace文件中查找相关的执行计划（结合使用tkprof，后续详述）。

可以通过查询系统视图来获得执行计划，例如：
SQL> select a.ADDRESS,a.HASH_VALUE,a.OPERATION,a.COST from v$sql_plan a where rownum = 1;

ADDRESS         HASH_VALUE OPERATION             COST
---------------- ---------- -----------------------------
07000004BFF2D0D0 3606577152 UPDATE STATEMENT      1

可以使用alter session/system set sql_trace=true/false;来打开关闭sql追踪，而在trace文件中获得执行计划。例如：

SQL> alter session set sql_trace=true;

Session altered.

SQL> alter session set sql_trace=false;

Session altered.

SQL>

也可以从statspack等工具的使用来获得。（后续详述）

或者使用一些工具，例如toad，plsql dev来获得，其原理还是查询相关的表或者系统视图。

[ 本帖最后由 yellowlee 于 2010-10-24 13:37 编辑 ]

yellowlee

【sql调优之执行计划】merge join cartesian and buffer sort
数据库版本：oracle 9208

有时在执行计划中看到了笛卡尔连接，通常会发现是关联有问题，例如：
SQL> select * from t_bank_class a,t_log_detail b;

no rows selected


Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1 Bytes=4652)
  1   0  MERGE JOIN (CARTESIAN)(Cost=4 Card=1 Bytes=4652)
  2   1    TABLE ACCESS (FULL) OF 'T_LOG_DETAIL' (Cost=2 Card=1 Byt
         es=4641)

  3   1    BUFFER (SORT) (Cost=2 Card=6 Bytes=66)
  4   3      TABLE ACCESS (FULL) OF 'T_BANK_CLASS' (Cost=2 Card=6 B
         ytes=66)





Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         3 consistent gets
         1 physical reads
         0 redo size
       795 bytes sent via SQL*Net to client
       237 bytes received via SQL*Net from client
         1 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         0 rows processed

SQL>
很多时候需要去检查一下是否是sql逻辑有问题，但也有时并不是因为逻辑问题而导致数据库选择这样的执行计划，
例如：

SQL> select 1 from t_group_policy_product tgpp,
2        t_product_life tpl,
3        t_period_type tpy
4        where tgpp.policy_id = 24
5    and tgpp.product_id = tpl.product_id
6    and tgpp.main_rider = '1'
7    and tpl.period_type =tpy.period_type
8    and tpy.period_type in (1,2)
9 ;


Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=20)
  1   0  NESTED LOOPS (Cost=3 Card=1 Bytes=20)
  2   1    MERGE JOIN (CARTESIAN) (Cost=2 Card=3 Bytes=42)
  3   2      TABLE ACCESS (BY INDEX ROWID) OF 'T_GROUP_POLICY_PRODU
         CT' (Cost=1 Card=2 Bytes=24)

  4   3        INDEX (RANGE SCAN) OF 'UNI_GROUP_POLICY_PRODUCT' (UN
         IQUE) (Cost=3 Card=3)

  5   2      BUFFER (SORT) (Cost=1 Card=2 Bytes=4)
  6   5        INDEX (FULL SCAN) OF 'PK_T_PERIOD_TYPE' (UNIQUE) (Co
         st=1 Card=2 Bytes=4)

  7   1    TABLE ACCESS (BY INDEX ROWID) OF 'T_PRODUCT_LIFE' (Cost=
         1 Card=1 Bytes=6)

  8   7      INDEX (UNIQUE SCAN) OF 'PK_T_PRODUCT_LIFE' (UNIQUE)




Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
        11 consistent gets
         0 physical reads
         0 redo size
       206 bytes sent via SQL*Net to client
       244 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         1 sorts (memory)
         0 sorts (disk)
         1 rows processed

SQL>


使用了MERGE JOIN (CARTESIAN)操作，笛卡尔合并连接，注意到这里INDEX (RANGE SCAN) OF 'UNI_GROUP_POLICY_PRODUCT'只会取到一行数据，
oracle仅仅将这个结果集与后面的INDEX (FULL SCAN) OF 'PK_T_PERIOD_TYPE'的结果集放到一起（使用笛卡尔乘积），然后将结果与T_PRODUCT_LIFE连接（外层是个nested loop）,
这是因为做笛卡尔乘积的两个表返回行的cardinality比较小，故而会比较高效。

asktom上面也有个相似的例子：
http://asktom.oracle.com/pls/ask ... ON_ID:4105951726381

将这个查询语句稍作修改：


SQL> select * from t_group_policy_product tgpp,
2        t_product_life tpl,
3        t_period_type tpy
4        where tgpp.policy_id = 24
5    and tgpp.product_id = tpl.product_id
6    and tgpp.main_rider = '1'
7    and tpl.period_type =tpy.period_type
8    and tpy.period_type in (1,2)
9 ;


Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=1411)
  1   0  NESTED LOOPS (Cost=3 Card=1 Bytes=1411)
  2   1    NESTED LOOPS (Cost=2 Card=2 Bytes=2798)
  3   2      TABLE ACCESS (BY INDEX ROWID) OF 'T_GROUP_POLICY_PRODU
         CT' (Cost=1 Card=2 Bytes=536)

  4   3        INDEX (RANGE SCAN) OF 'UNI_GROUP_POLICY_PRODUCT' (UN
         IQUE) (Cost=3 Card=3)

  5   2      TABLE ACCESS (BY INDEX ROWID) OF 'T_PRODUCT_LIFE' (Cos
         t=1 Card=1 Bytes=1131)

  6   5        INDEX (UNIQUE SCAN) OF 'PK_T_PRODUCT_LIFE' (UNIQUE)
  7   1    TABLE ACCESS (BY INDEX ROWID) OF 'T_PERIOD_TYPE' (Cost=1
          Card=1 Bytes=12)

  8   7      INDEX (UNIQUE SCAN) OF 'PK_T_PERIOD_TYPE' (UNIQUE)




Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
        11 consistent gets
         1 physical reads
         0 redo size
     22202 bytes sent via SQL*Net to client
       244 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed
         
虽然cost和card都没有改变，但由于使用了*使得需要从行中返回多个列，使得bytes值变得相对大很多（意味着可能使用更多的memory），
这时候可以看到执行计划已经改变了，oracle使用nested loops代替了之前的merge join(CARTESIAN)。

而buffer (sort）则是指使用内存排序。一些操作（如Merge join或者order by）需要对行集排序。Buffer sort使用的内存数量与数据量和sort_area_size初始参数有关（如果workarea_size_policy参数被设置为AUTO，则与pga_aggregate_target参数相关），

为什么这里会使用buffer sort？一般来说，如果优化器倾向于使用IO cost model，谓词中的过滤条件不止一列且没有索引，而过滤条件预期的返回行只有一行，这种情况下可能会使用buffer sort，事实上，这个例子中执行计划已经提示了：
Note: cpu costing is off, PLAN_TABLE' is old version

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation                    | Name                    | Rows | Byte
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |                          |    1 |   2
|  1 | NESTED LOOPS                |                          |    1 |   2
|  2 |  MERGE JOIN CARTESIAN       |                          |    3 |   4
|  3 |   TABLE ACCESS BY INDEX ROWID| T_GROUP_POLICY_PRODUCT   |    2 |   2
|  4 |    INDEX RANGE SCAN         | UNI_GROUP_POLICY_PRODUCT |    3 |
|  5 |   BUFFER SORT               |                          |    2 |
|  6 |    INDEX FULL SCAN          | PK_T_PERIOD_TYPE         |    2 |
|  7 |  TABLE ACCESS BY INDEX ROWID | T_PRODUCT_LIFE           |    1 |
|  8 |   INDEX UNIQUE SCAN         | PK_T_PRODUCT_LIFE        |    1 |
--------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version

16 rows selected

[ 本帖最后由 yellowlee 于 2010-10-24 13:37 编辑 ]

yellowlee

【sql调优之执行计划】nested loops join and nested loop join outer
版本：10.2.0.4

Nested loops（嵌套循环）看一个例子：
SQL> select b.* from scott.emp a,scott.dept b
2 where a.deptno = b.deptno
3 and a.empno = 7369
4 ;

   DEPTNO DNAME         LOC
---------- -------------- -------------
       20 RESEARCH      DALLAS


Execution Plan
----------------------------------------------------------
Plan hash value: 2385808155

--------------------------------------------------------------------------------
--------

| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Ti
me    |

--------------------------------------------------------------------------------
--------

|  0 | SELECT STATEMENT            |        |    1 |   27 |    2  (0)| 00
:00:01 |

|  1 | NESTED LOOPS               |        |    1 |   27 |    2  (0)| 00
:00:01 |

|  2 |  TABLE ACCESS BY INDEX ROWID| EMP    |    1 |    7 |    1  (0)| 00
:00:01 |

|* 3 |   INDEX UNIQUE SCAN        | PK_EMP |    1 |      |    0  (0)| 00
:00:01 |

|  4 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    1  (0)| 00
:00:01 |

|* 5 |   INDEX UNIQUE SCAN        | PK_DEPT |    1 |      |    0  (0)| 00
:00:01 |

--------------------------------------------------------------------------------
--------


Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("A"."EMPNO"=7369)
  5 - access("A"."DEPTNO"="B"."DEPTNO")


Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         4 consistent gets
         0 physical reads
         0 redo size
       533 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

SQL>
简单的讲就是在表1中每取一行数据，然后从表2中查找匹配的行，然后再回到表1取下一行，如此循环取下去。那么表1中取得的行越少，表2中查找行越容易则嵌套循环的效率越高。
第一个表也通常称为驱动表（或者外部表），第二个表通常称为内部表。
对nested loops join的选择或者优化，应该考虑驱动表行的选择性和绝对数量大小，并且内部表要能方便的访问到，例如唯一索引，这种结构很多时候用于一个主数据表和一个关联表（或者字典表等）的关联，而关联表很容易使用与主表关联的字段上的索引访问。

上述的执行计划可以看出，先访问表emp，emp作为了驱动表，当然也可以使用dept作为驱动表：

SQL> select /*+ leading(b) use_nl(a,b)*/b.* from scott.dept b,scott.emp a
2 where a.deptno = b.deptno
3 and a.empno = 7369;

   DEPTNO DNAME         LOC
---------- -------------- -------------
       20 RESEARCH      DALLAS


Execution Plan
----------------------------------------------------------
Plan hash value: 3431005640

--------------------------------------------------------------------------------
-------

| Id | Operation                   | Name  | Rows | Bytes | Cost (%CPU)| Tim
e    |

--------------------------------------------------------------------------------
-------

|  0 | SELECT STATEMENT            |       |    1 |   27 |    7  (0)| 00:
00:01 |

|  1 | NESTED LOOPS               |       |    1 |   27 |    7  (0)| 00:
00:01 |

|  2 |  TABLE ACCESS FULL         |DEPT  |    4 |   80 |    3  (0)| 00:
00:01 |

|* 3 |  TABLE ACCESS BY INDEX ROWID|EMP   |    1 |    7 |    1  (0)| 00:
00:01 |

|* 4 |   INDEX UNIQUE SCAN        | PK_EMP |    1 |      |    0  (0)| 00:
00:01 |

--------------------------------------------------------------------------------
-------


Predicate Information (identified by operation id):
---------------------------------------------------

  3 - filter("A"."DEPTNO"="B"."DEPTNO")
  4 - access("A"."EMPNO"=7369)


Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
        15 consistent gets
         5 physical reads
         0 redo size
       533 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

SQL>

上述执行计划先访问dept表（全表扫描），然后通过index访问emp表，可以看到后者的nested loops的cost为7，大于前者的cost2，可以初步判断后者的效率不如前者，或者也可以简单的看看2者的逻辑读数量来判断一下优劣。具体的性能优化结合实例再述。

扩展一下，两个数据集做nested loops join和两个表相似。

Nested loops join outer则是由于做了外连接而产生的，例如：
SQL> select b.* from scott.emp a,scott.dept b
2 where a.deptno = b.deptno(+)
3 and a.empno = 7369;

   DEPTNO DNAME         LOC
---------- -------------- -------------
       20 RESEARCH      DALLAS


Execution Plan
----------------------------------------------------------
Plan hash value: 1858280091

--------------------------------------------------------------------------------
--------

| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Ti
me    |

--------------------------------------------------------------------------------
--------

|  0 | SELECT STATEMENT            |        |    1 |   27 |    2  (0)| 00
:00:01 |

|  1 | NESTED LOOPS OUTER         |        |    1 |   27 |    2  (0)| 00
:00:01 |

|  2 |  TABLE ACCESS BY INDEX ROWID| EMP    |    1 |    7 |    1  (0)| 00
:00:01 |

|* 3 |   INDEX UNIQUE SCAN        | PK_EMP |    1 |      |    0  (0)| 00
:00:01 |

|  4 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    1  (0)| 00
:00:01 |

|* 5 |   INDEX UNIQUE SCAN        | PK_DEPT |    1 |      |    0  (0)| 00
:00:01 |

--------------------------------------------------------------------------------
--------


Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("A"."EMPNO"=7369)
  5 - access("A"."DEPTNO"="B"."DEPTNO"(+))


Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         4 consistent gets
         0 physical reads
         0 redo size
       533 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

SQL>

[ 本帖最后由 yellowlee 于 2010-10-24 13:38 编辑 ]

yellowlee

【sql调优之执行计划】merge sort join
版本：10g 10.2.0.1
Sort merge join排序合并连接的原理是两个连接的行集按照连接列先分别排序，然后再做连接。
Oracle使用merge join的倾向程度和sort_area_size和db_file_mutliblock_read_count两个初始参数有关，因为需要排序，所以较大的sort_area_size设置会更有利于使用merge join。而多块读的设置，定义了oracle操作一次全表扫描或者索引范围扫描返回的block数量，如果设置的太高，优化器将倾向于全表扫描，而太低则倾向于使用索引。在没有索引的情况下，而两个行集都接近有序，则使用merge join也可能提升效率。

看个例子：
SQL> select /*+ use_merge(a,b)*/
2  *
3   from scott.emp a, scott.dept b
4  where a.deptno = b.deptno
5 ;

已选择15行。


执行计划
----------------------------------------------------------
Plan hash value: 844388907

--------------------------------------------------------------------------------

--------

| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Ti

me    |

--------------------------------------------------------------------------------

--------

|  0 | SELECT STATEMENT            |        |   15 |  855 |    6 (17)| 00

:00:01 |

|  1 | MERGE JOIN                 |        |   15 |  855 |    6 (17)| 00

:00:01 |

|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00

:00:01 |

|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00

:00:01 |

|* 4 |  SORT JOIN                 |        |   15 |  555 |    4 (25)| 00

:00:01 |

|  5 |   TABLE ACCESS FULL        | EMP    |   15 |  555 |    3  (0)| 00

:00:01 |

--------------------------------------------------------------------------------

--------


Predicate Information (identified by operation id):
---------------------------------------------------

  4 - access("A"."DEPTNO"="B"."DEPTNO")
      filter("A"."DEPTNO"="B"."DEPTNO")


统计信息
----------------------------------------------------------
        38 recursive calls
         5 db block gets
        12 consistent gets
         2 physical reads
      1012 redo size
      1687 bytes sent via SQL*Net to client
       385 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         1 sorts (memory)
         0 sorts (disk)
        15 rows processed

上述信息可以看到dept使用了索引，而emp为全表，由于索引已经排序，故而后面的1 sort(memory)即一次内存排序完成了对emp表按连接列deptno的排序。
下面稍稍修改一下上述sql，使得两个表都使用全表扫描，则可以看到，进行了2次sort join,后面的2 sorts (memory)两次内存排序也说明了这点，
例如：
SQL>
SQL> select /*+ no_index(a) no_index(b) use_merge(a,b)*/
2  *
3   from scott.emp a, scott.dept b
4  where a.deptno = b.deptno
5 ;

已选择15行。


执行计划
----------------------------------------------------------
Plan hash value: 1407029907

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |     |   15 |  855 |    8 (25)| 00:00:01 |
|  1 | MERGE JOIN        |     |   15 |  855 |    8 (25)| 00:00:01 |
|  2 |  SORT JOIN        |     |    4 |   80 |    4 (25)| 00:00:01 |
|  3 |   TABLE ACCESS FULL| DEPT |    4 |   80 |    3  (0)| 00:00:01 |
|* 4 |  SORT JOIN        |     |   15 |  555 |    4 (25)| 00:00:01 |
|  5 |   TABLE ACCESS FULL| EMP |   15 |  555 |    3  (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  4 - access("A"."DEPTNO"="B"."DEPTNO")
      filter("A"."DEPTNO"="B"."DEPTNO")


统计信息
----------------------------------------------------------
        21 recursive calls
         5 db block gets
        14 consistent gets
         5 physical reads
      1076 redo size
      1687 bytes sent via SQL*Net to client
       385 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         2 sorts (memory)
         0 sorts (disk)
        15 rows processed

SQL>

如果sort_area_size不足以完成排序操作，则将借助temp表空间来完成排序，即为磁盘排序，大量的磁盘排序是merge join性能下降的一个主要原因。
看一个例子：
SQL> create table t_policy_merge as
2 select * from t_policy a where rownum <1000;

表已创建。

create table t_policy_merge_big as
select * from t_policy union all
select * from t_policy union all
select * from t_policy union all
select * from t_policy union all
select * from t_policy ;

insert into t_policy_merge_big select* from t_policy_merge_big ;
commit;

SQL> select /*+ use_merge(a,b)*/
2  *
3   from t_policy_merge_big a, t_policy_merge b
4  where a.policy_id = b.policy_id;

已选择9990行。


执行计划
----------------------------------------------------------
Plan hash value: 2074009417

--------------------------------------------------------------------------------

------------------

| Id | Operation          | Name              | Rows | Bytes |TempSpc| Cost
(%CPU)| Time    |

--------------------------------------------------------------------------------

------------------

|  0 | SELECT STATEMENT   |                   | 3654 |   65M|      |  198

K (1)| 00:39:38 |

|  1 | MERGE JOIN        |                   | 3654 |   65M|      |  198

K (1)| 00:39:38 |

|  2 |  SORT JOIN        |                   | 1000 | 9149K|   15M| 1971

  (1)| 00:00:24 |

|  3 |   TABLE ACCESS FULL| T_POLICY_MERGE    | 1000 | 9149K|      |   16

  (7)| 00:00:01 |

|* 4 |  SORT JOIN        |                   | 99803 |  891M| 1559M|  196

K (1)| 00:39:15 |

|  5 |   TABLE ACCESS FULL| T_POLICY_MERGE_BIG | 99803 |  891M|      | 1321

  (6)| 00:00:16 |

--------------------------------------------------------------------------------

------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  4 - access("A"."POLICY_ID"="B"."POLICY_ID")
      filter("A"."POLICY_ID"="B"."POLICY_ID")

Note
-----
  - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
        60 recursive calls
       226 db block gets
      5776 consistent gets
     10271 physical reads
       132 redo size
    744643 bytes sent via SQL*Net to client
      7700 bytes received via SQL*Net from client
       667 SQL*Net roundtrips to/from client
         1 sorts (memory)
         1 sorts (disk)
      9990 rows processed

SQL>
如果使用hash join则会将小表T_POLICY_MERGE hash，从而避免了对大表的磁盘排序，从下面的黑体的db_block_gets, physical reads和sorts (disk)可以看到这个差别。
SQL> select
2  *
3   from t_policy_merge_big a, t_policy_merge b
4  where a.policy_id = b.policy_id;

已选择9990行。


执行计划
----------------------------------------------------------
Plan hash value: 3615210701

--------------------------------------------------------------------------------

-----------------

| Id | Operation         | Name              | Rows | Bytes |TempSpc| Cost (

%CPU)| Time    |

--------------------------------------------------------------------------------

-----------------

|  0 | SELECT STATEMENT  |                   | 3654 |   65M|      | 46107
(1)| 00:09:14 |

|* 1 | HASH JOIN        |                   | 3654 |   65M| 9168K| 46107
(1)| 00:09:14 |

|  2 |  TABLE ACCESS FULL| T_POLICY_MERGE    | 1000 | 9149K|      |   16
(7)| 00:00:01 |

|  3 |  TABLE ACCESS FULL| T_POLICY_MERGE_BIG | 99803 |  891M|      | 1321
(6)| 00:00:16 |

--------------------------------------------------------------------------------

-----------------


Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("A"."POLICY_ID"="B"."POLICY_ID")

Note
-----
  - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
         7 recursive calls
         0 db block gets
      6491 consistent gets
      5724 physical reads
         0 redo size
   2287605 bytes sent via SQL*Net to client
      7700 bytes received via SQL*Net from client
       667 SQL*Net roundtrips to/from client
         2 sorts (memory)
         0 sorts (disk)
      9990 rows processed

SQL>

注意到前面有些redo的情况，特别是scott下表的测试，做个10046
SQL> alter session set events '10046 trace name context forever, level 8';

会话已更改。

查看了trace内容后发现：
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,
userhost,terminal,action#,returncode, obj$creator,obj$name,auth$privileges,
auth$grantee, new$owner,new$name,ses$actions,ses$tid,logoff$pread,
logoff$lwrite,logoff$dead,comment$text,spare1,spare2, priv$used,clientid,
sessioncpu,proxy$sid,user$guid, instance#,process#,xid,scn,auditid,sqlbind,
sqltext)
values
(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP),    :4,:5,:6,:7,:8,    :9,:10,:11,
:12,    :13,:14,:15,:16,:17,    :18,:19,:20,:21,:22,    :23,:24,:25,:26,
:27,    :28,:29,:30,:31,:32,:33,:34)
insert into plan_table (statement_id, timestamp, operation, options,
object_node, object_owner, object_name, object_instance, object_type,
search_columns, id, parent_id, position, other,optimizer, cost, cardinality,
  bytes, other_tag, partition_start, partition_stop, partition_id,
distribution, cpu_cost, io_cost, temp_space, access_predicates,
filter_predicates, projection, time, qblock_name, object_alias, plan_id,
depth, remarks, other_xml )
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,
:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)

sys.aud$表以及plan_table有insert操作，是由于scott上开了审计功能。

[ 本帖最后由 yellowlee 于 2010-10-24 13:38 编辑 ]

yellowlee

sql调优之执行计划】temp table transformation
使用系统临时表的时候，执行计划会有个TEMP TABLE TRANSFORMATION的operation，可以叫做临时表转化，看一个具体的例子，使用hint：/*+ materialize */来强制让oracle创建临时表。
SQL> with temp as
2 (
3 select/*+ materialize */
4 a.* from scott.emp a,scott.dept b
5 where a.deptno = b.deptno
6 and a.empno = 7369
7 )
8 select count(*) from temp a ;

COUNT(*)
----------
        1


Execution Plan
----------------------------------------------------------
Plan hash value: 555904337

--------------------------------------------------------------------------------
-----------------------------

| Id | Operation                    | Name                       | Rows | By
tes | Cost (%CPU)| Time    |

--------------------------------------------------------------------------------
-----------------------------

|  0 | SELECT STATEMENT             |                            |    1 |
   |    3  (0)| 00:00:01 |

|  1 | TEMP TABLE TRANSFORMATION   |                            |      |
   |           |         |

|  2 |  LOAD AS SELECT             |                            |      |
   |           |         |

|* 3 |   TABLE ACCESS BY INDEX ROWID| EMP                        |    1 |
39 |    1  (0)| 00:00:01 |

|* 4 |    INDEX UNIQUE SCAN        | PK_EMP                     |    1 |
   |    0  (0)| 00:00:01 |

|  5 |  SORT AGGREGATE             |                            |    1 |
   |           |         |

|  6 |   VIEW                      |                            |    1 |
   |    2  (0)| 00:00:01 |

|  7 |    TABLE ACCESS FULL        |SYS_TEMP_0FD9D6613_A0A5C4A5|    1 |
39 |    2  (0)| 00:00:01 |

--------------------------------------------------------------------------------
-----------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  3 - filter("A"."DEPTNO" IS NOT NULL)
  4 - access("A"."EMPNO"=7369)


Statistics
----------------------------------------------------------
       222 recursive calls
        10 db block gets
        30 consistent gets
         1 physical reads
      1652 redo size
       411 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

SQL>
可以找到系统创建这个临时表的sql：
SQL> select sql_text from v$sqltext a where a.SQL_ID = 'aamnajt3sq3zt' order by a.PIECE;

SQL_TEXT
----------------------------------------------------------------
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D660E_A0A5C4A5
" ("C0" NUMBER(4),"C1" VARCHAR2(10),"C2" VARCHAR2(9),"C3" NUMBER
(4),"C4" DATE,"C5" NUMBER(10,2),"C6" NUMBER(10,2),"C7" NUMBER(2)
)IN_MEMORY_METADATACURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 425
4950926 ) NOPARALLEL
这里可以看到这个系统临时表存放在内存中，使用的是create global temporary语句创建。

[ 本帖最后由 yellowlee 于 2010-10-24 13:39 编辑 ]

yellowlee

【sql调优之执行计划】merge semi join and merge anti join
版本：10.2.0.4
Semi join（也有叫半连接的）多在子查询in或者exists等中使用，对于外部行集，查找内部（即子查询）行集，匹配第一行之后就返回，不再往下查找例如：
SQL> select b.*
2   from scott.dept b
3  where b.deptno in (select deptno from scott.emp a)
4 ;

   DEPTNO DNAME         LOC
---------- -------------- -------------
       10 ACCOUNTING    NEW YORK
       20 RESEARCH      DALLAS
       30 SALES         CHICAGO


Execution Plan
----------------------------------------------------------
Plan hash value: 1090737117

----------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |    3 |   69 |    6 (17)| 00:00:01 |
|  1 | MERGE JOIN SEMI            |        |    3 |   69 |    6 (17)| 00:00:01 |
|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |
|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |
|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |
|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  4 - access("B"."DEPTNO"="DEPTNO")
      filter("B"."DEPTNO"="DEPTNO")


Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         7 consistent gets
         0 physical reads
         0 redo size
       614 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         1 sorts (memory)
         0 sorts (disk)
         3 rows processed

SQL>
或者：
SQL> select b.*
2   from scott.dept b
3  where exists (select 1 from scott.emp a where a.deptno = b.deptno)
4 ;

   DEPTNO DNAME         LOC
---------- -------------- -------------
       10 ACCOUNTING    NEW YORK
       20 RESEARCH      DALLAS
       30 SALES         CHICAGO


Execution Plan
----------------------------------------------------------
Plan hash value: 1090737117

----------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |    3 |   69 |    6 (17)| 00:00:01 |
|  1 | MERGE JOIN SEMI            |        |    3 |   69 |    6 (17)| 00:00:01 |
|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |
|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |
|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |
|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  4 - access("A"."DEPTNO"="B"."DEPTNO")
      filter("A"."DEPTNO"="B"."DEPTNO")


Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         7 consistent gets
         0 physical reads
         0 redo size
       614 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         1 sorts (memory)
         0 sorts (disk)
         3 rows processed

SQL>
可以看到这种情况下，in和exsits的执行计划完全相同，且都使用了merge join semi的oporation
而not in或者not exists则不同，Oracle7.3版本之前not exists和not in还使用的tilter，merge anti join和hash anti join访问路径是后来增加的。
例子：
SQL> select b.*
2   from scott.dept b
3  where not exists (select 1 from scott.emp a where a.deptno = b.deptno)
4 ;

   DEPTNO DNAME         LOC
---------- -------------- -------------
       40 OPERATIONS    BOSTON


Execution Plan
----------------------------------------------------------
Plan hash value: 1353548327

----------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |    1 |   23 |    6 (17)| 00:00:01 |
|  1 | MERGE JOIN ANTI            |        |    1 |   23 |    6 (17)| 00:00:01 |
|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |
|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |
|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |
|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  4 - access("A"."DEPTNO"="B"."DEPTNO")
      filter("A"."DEPTNO"="B"."DEPTNO")


Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         5 consistent gets
         0 physical reads
         0 redo size
       535 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         1 sorts (memory)
         0 sorts (disk)
         1 rows processed
这里是merge join anti（也叫反连接），和semi相反，只有外部行在内部不能匹配的时候才返回。
而，not in则和not exsits不同，执行计划显示的是filter：
SQL> select b.*
2   from scott.dept b
3  where b.deptno not in (select deptno from scott.emp a)
4 ;

   DEPTNO DNAME         LOC
---------- -------------- -------------
       40 OPERATIONS    BOSTON


Execution Plan
----------------------------------------------------------
Plan hash value: 3547749009

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |     |    3 |   60 |    7  (0)| 00:00:01 |
|* 1 | FILTER           |     |      |      |           |         |
|  2 |  TABLE ACCESS FULL| DEPT |    4 |   80 |    3  (0)| 00:00:01 |
|* 3 |  TABLE ACCESS FULL| EMP |    2 |    6 |    2  (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SCOTT"."EMP" "A" WHERE
             LNNVL("DEPTNO"<>:B1)))
  3 - filter(LNNVL("DEPTNO"<>:B1))


Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
        19 consistent gets
         5 physical reads
         0 redo size
       535 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

SQL>
我们知道not in与not exsits并不能等同，从执行计划上来看，not in的执行计划的operation是filter，而且内表和外表都是全表，没有使用索引，而从谓词信息中来看，operation 1为：
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SCOTT"."EMP" "A" WHERE
             LNNVL("DEPTNO"<>:B1)))
Null值对not in影响较大，如果稍稍修改一下这个查询，则又有不同了：
SQL> select b.*
2   from scott.dept b
3  where b.deptno not in (select nvl(deptno,0) from scott.emp a);

   DEPTNO DNAME         LOC
---------- -------------- -------------
       40 OPERATIONS    BOSTON


Execution Plan
----------------------------------------------------------
Plan hash value: 1353548327

----------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |    1 |   23 |    6 (17)| 00:00:01 |
|  1 | MERGE JOIN ANTI            |        |    1 |   23 |    6 (17)| 00:00:01 |
|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |
|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |
|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |
|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  4 - access("B"."DEPTNO"=NVL("DEPTNO",0))
      filter("B"."DEPTNO"=NVL("DEPTNO",0))


Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         5 consistent gets
         0 physical reads
         0 redo size
       535 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         1 sorts (memory)
         0 sorts (disk)
         1 rows processed

SQL>
奇怪的是，这里使用了索引，因为告诉了oracle不会有null值，而且谓词信息也发生了改变：
4 - access("B"."DEPTNO"=NVL("DEPTNO",0))
这也是使用not in需要注意的地方。

[ 本帖最后由 yellowlee 于 2010-10-24 13:40 编辑 ]

yellowlee

【sql调优之执行计划】hash join
在大表和小表做join时，可能会使用到hash join的连接方式。其原理是将两个表中的一张表（较小的）通过hash算法装入内存（如果内存不够，则使用临时表空间），然后再读大表数据，读取它的记录，计算hash值，与内存中的hash值匹配。有关的cost大致是以这样的公式来计算的：
cost = (outer access cost * # of hash partitions) + inner access cost
inner table就是装入内存的小表，hash partions是小表的hash分区数。外表（大表）的行从内表（小表）的hash分区中匹配hash键值。
Hash join只在cbo下有效，等值连接情况下，当一个表有大量的数据需要join而另一个表只有一小部分需要join的时候使用，可以看一个例子：
SQL> select /*+ use_hash(a,b)*/b.*
2   from scott.dept b,scott.emp a
3   where a.deptno = b.deptno
4   ;

   DEPTNO DNAME         LOC
---------- -------------- -------------
       20 RESEARCH      DALLAS
       30 SALES         CHICAGO
       30 SALES         CHICAGO
       20 RESEARCH      DALLAS
       30 SALES         CHICAGO
       30 SALES         CHICAGO
       10 ACCOUNTING    NEW YORK
       20 RESEARCH      DALLAS
       10 ACCOUNTING    NEW YORK
       30 SALES         CHICAGO
       20 RESEARCH      DALLAS

   DEPTNO DNAME         LOC
---------- -------------- -------------
       30 SALES         CHICAGO
       20 RESEARCH      DALLAS

13 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |     |   14 |  322 |    7 (15)| 00:00:01 |
|* 1 | HASH JOIN        |     |   14 |  322 |    7 (15)| 00:00:01 |
|  2 |  TABLE ACCESS FULL| DEPT |    4 |   80 |    3  (0)| 00:00:01 |
|  3 |  TABLE ACCESS FULL| EMP |   14 |   42 |    3  (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("A"."DEPTNO"="B"."DEPTNO")


Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
        11 consistent gets
         0 physical reads
         0 redo size
       820 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
        13 rows processed

SQL>

如果hash table太大而不能在内存中装下，那么优化器将table划分为多个不同的分区，内存不能放下的分区，将写入磁盘，使用临时段。这也是hash join当需要hash的table大到一定程度时效率显著下降的原因，大量磁盘io带来了性能上的问题。
看看执行计划中的这一行：
1 - access("A"."DEPTNO"="B"."DEPTNO")
可以知道dept表的hash key是deptno，其实这个key的选择性不是很好，oracle优化器默认也没有使用hash，故而使用了hint强制使用hash join。

[ 本帖最后由 yellowlee 于 2010-10-24 13:40 编辑 ]

yellowlee

【sql调优之执行计划】in相关的operation
当where条件中有某字段的in条件，且in后的值为具体值而不是子查询，并且在该字段上有索引可以使用时，oracle优化器可能会使用in-lisy iterators操作，而不是concatrnation或者union all。而in可以用多个or来代替，所使用的执行计划相同，例子：
SQL> select /*+ index(a) */
2  a.deptno
3   from scott.emp a
4  where a.empno in (7339, 9000);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2355049923

---------------------------------------------------------------------------------------
| Id | Operation                   | Name  | Rows | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |       |    1 |    7 |    2  (0)| 00:00:01 |
|  1 | INLIST ITERATOR            |       |      |      |           |         |
|  2 |  TABLE ACCESS BY INDEX ROWID| EMP   |    1 |    7 |    2  (0)| 00:00:01 |
|* 3 |   INDEX UNIQUE SCAN        | PK_EMP |    1 |      |    1  (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("A"."EMPNO"=7339 OR "A"."EMPNO"=9000)

或者：
SQL> select /*+ index(a) */
2  a.deptno
3   from scott.emp a
4  where a.empno = 7339
5  or a.empno = 9000;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2355049923

---------------------------------------------------------------------------------------
| Id | Operation                   | Name  | Rows | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |       |    1 |    7 |    2  (0)| 00:00:01 |
|  1 | INLIST ITERATOR            |       |      |      |           |         |
|  2 |  TABLE ACCESS BY INDEX ROWID| EMP   |    1 |    7 |    2  (0)| 00:00:01 |
|* 3 |   INDEX UNIQUE SCAN        | PK_EMP |    1 |      |    1  (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("A"."EMPNO"=7339 OR "A"."EMPNO"=9000)

但是如果不是用empno上的索引，则有不同：
SQL> select /*+ full(a)*/
2  a.deptno
3   from scott.emp a
4  where a.empno in (7339, 9000);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT |     |    1 |    7 |    3  (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP |    1 |    7 |    3  (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("A"."EMPNO"=7339 OR "A"."EMPNO"=9000)

在scott.emp.ename上创建索引，修改一下查询，使得where clause中的两个条件字段上都有索引可以使用，看看执行计划的改变：
SQL> create index scott.ind_emp_ename on scott.emp(ename);

Index created.

SQL> select
2  a.deptno
3   from scott.emp a
4  where a.empno = 7369
5  or a.ename = 'asd'
6 ;

   DEPTNO
----------
       20


Execution Plan
----------------------------------------------------------
Plan hash value: 2971452327

----------------------------------------------------------------------------------------------
| Id | Operation                   | Name         | Rows | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |              |    2 |   26 |    3  (0)| 00:00:01 |
|  1 | CONCATENATION              |              |      |      |           |         |
|  2 |  TABLE ACCESS BY INDEX ROWID| EMP          |    1 |   13 |    2  (0)| 00:00:01 |
|* 3 |   INDEX RANGE SCAN         | IND_EMP_ENAME |    1 |      |    1  (0)| 00:00:01 |
|* 4 |  TABLE ACCESS BY INDEX ROWID| EMP          |    1 |   13 |    1  (0)| 00:00:01 |
|* 5 |   INDEX UNIQUE SCAN        | PK_EMP       |    1 |      |    0  (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("A"."ENAME"='asd')
  4 - filter(LNNVL("A"."ENAME"='asd'))
  5 - access("A"."EMPNO"=7369)
这里使用的是CONCATENATION操作（串接）。
可以使用/*+NO_EXPAND */的hint来禁用串接操作，例如：
select/*+NO_EXPAND */
a.deptno
from scott.emp a
where a.empno = 7369
or a.ename = 'asd'
再来看看in子查询的执行计划

SQL> select /*+ */* from scott.emp a
2 where a.deptno in
3 (select deptno from scott.dept where rownum < 2 );

…

Execution Plan
----------------------------------------------------------
Plan hash value: 650699563

-------------------------------------------------------------------------------
| Id | Operation         | Name    | Rows | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |         |    4 |  204 |    4  (0)| 00:00:01 |
|  1 | NESTED LOOPS     |         |    4 |  204 |    4  (0)| 00:00:01 |
|  2 |  VIEW            | VW_NSO_1 |    1 |   13 |    1  (0)| 00:00:01 |
|* 3 |   COUNT STOPKEY  |         |      |      |           |         |
|  4 |    INDEX FULL SCAN| PK_DEPT |    4 |   12 |    1  (0)| 00:00:01 |
|* 5 |  TABLE ACCESS FULL| EMP     |    4 |  152 |    3  (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - filter(ROWNUM<2)
  5 - filter("A"."DEPTNO" IS NOT NULL AND "A"."DEPTNO"="$nso_col_1")

SQL> select /*+ */* from scott.emp a
2 where a.deptno in
3 (select deptno from scott.dept where rownum < 3 );

…

Execution Plan
----------------------------------------------------------
Plan hash value: 1313905718

-------------------------------------------------------------------------------
| Id | Operation         | Name    | Rows | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |         |    9 |  459 |    5 (20)| 00:00:01 |
|* 1 | HASH JOIN SEMI   |         |    9 |  459 |    5 (20)| 00:00:01 |
|* 2 |  TABLE ACCESS FULL| EMP     |   13 |  494 |    3  (0)| 00:00:01 |
|  3 |  VIEW            | VW_NSO_1 |    2 |   26 |    1  (0)| 00:00:01 |
|* 4 |   COUNT STOPKEY  |         |      |      |           |         |
|  5 |    INDEX FULL SCAN| PK_DEPT |    4 |   12 |    1  (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("A"."DEPTNO"="$nso_col_1")
  2 - filter("A"."DEPTNO" IS NOT NULL)
  4 - filter(ROWNUM<3)

SQL> select /*+ */* from scott.emp a
2 where a.deptno in
3 (select deptno from scott.dept);

….
Execution Plan
----------------------------------------------------------
Plan hash value: 3074306753

------------------------------------------------------------------------------
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |        |   13 |  533 |    3  (0)| 00:00:01 |
|  1 | NESTED LOOPS     |        |   13 |  533 |    3  (0)| 00:00:01 |
|* 2 |  TABLE ACCESS FULL| EMP    |   13 |  494 |    3  (0)| 00:00:01 |
|* 3 |  INDEX UNIQUE SCAN| PK_DEPT |    1 |    3 |    0  (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("A"."DEPTNO" IS NOT NULL)
  3 - access("A"."DEPTNO"="DEPTNO")
可以看出，cbo下in的执行计划与in内的数据量，字段上的索引，是否空值等方面都有关系，从谓词信息可以看出，不同的操作，访问路径和应用条件的顺序有所不同。
再看一个常见的子查询问题：
SQL> select /*+ */* from scott.emp a
2 wherea.empnoin
3 (selectempnofrom scott.dept where rownum < 2 );

    EMPNO ENAME     JOB             MGR HIREDATE        SAL      COMM    DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH     CLERK          7902 17-DEC-80    812.61                   20
     7499 ALLEN     SALESMAN       7698 20-FEB-81    1609.6       300        30
     7521 WARD      SALESMAN       7698 22-FEB-81    1259.6       500        30
     7566 JONES     MANAGER        7839 02-APR-81    2009.6                   20
     7654 MARTIN    SALESMAN       7698 28-SEP-81    1259.6      1400        30
     7698 BLAKE     MANAGER        7839 01-MAY-81    2859.6                   30
     7782 CLARK     MANAGER        7839 09-JUN-81    2459.6                   10
     7788 SCOTT     ANALYST        7566 19-APR-87    3009.6                   20
     7839 KING      PRESIDENT           17-NOV-81    5009.6                   10
     7844 TURNER    SALESMAN       7698 08-SEP-81    1509.6         0        30
     7876 ADAMS     CLERK          7788 23-MAY-87    1109.6                   20

    EMPNO ENAME     JOB             MGR HIREDATE        SAL      COMM    DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES     CLERK          7698 03-DEC-81     959.6                   30
     7902 FORD      ANALYST        7566 03-DEC-81    3009.6                   20
     7934 update    CLERK          7782 23-JAN-82    1309.6

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3458227086

------------------------------------------------------------------------------
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |        |    1 |   38 |   10  (0)| 00:00:01 |
|* 1 | FILTER           |        |      |      |           |         |
|  2 |  TABLE ACCESS FULL| EMP    |   14 |  532 |    3  (0)| 00:00:01 |
|* 3 |  FILTER          |        |      |      |           |         |
|* 4 |   COUNT STOPKEY  |        |      |      |           |         |
|  5 |    INDEX FULL SCAN| PK_DEPT |    1 |      |    1  (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter( EXISTS (???)
  3 - filter(:B1=:B2)
  4 - filter(ROWNUM<2)
子查询中的表并不存在empno字段，然而这是可以运行的，从谓词信息中可以看到有一个exists的operation，这并不正确，但确实影响到了外层的查询，如果修改成rownum<1，那么这个查询将不返回行，需要注意。