sys@EBANK>set linesize 132
sys@EBANK>column name format a30
sys@EBANK>column value format a25
sys@EBANK>select
  2    x.ksppinm  name,
  3    y.ksppstvl  value,
  4    y.ksppstdf  isdefault,
  5    decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,
  6    decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj
  7  from
  8    sys.x$ksppi x,
  9    sys.x$ksppcv y
 10  where
 11    x.inst_id = userenv('Instance') and
 12    y.inst_id = userenv('Instance') and
 13    x.indx = y.indx and
 14    x.ksppinm like '%_&par%'
 15  order by
 16    translate(x.ksppinm, ' _', ' ')
 17  /
Enter value for par: unnest
old  14:   x.ksppinm like '%_&par%'
new  14:   x.ksppinm like '%_unnest%'

NAME                           VALUE                     ISDEFAULT ISMOD      ISADJ
------------------------------ ------------------------- --------- ---------- -----
_distinct_view_unnesting       FALSE                     TRUE      FALSE      FALSE
_unnest_subquery               TRUE                      TRUE      FALSE      FALSE

可见，默认情况下此参数是true，也就是启用了子查询嵌套，下面对比一下该参数分别为true和false的情况：

sys@EBANK>set autot traceonly
sys@EBANK>select count(*) from test where object_id>(select avg(object_id) from test where
  2 
sys@EBANK>select count(*) from test o where object_id>(select avg(object_id) from test i where i.object_type=o.object_type);

Execution Plan
----------------------------------------------------------
Plan hash value: 731730653

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    35 |   275   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE       |         |     1 |    35 |            |          |
|*  2 |   HASH JOIN           |         |  1897 | 66395 |   275   (2)| 00:00:04 |
|   3 |    VIEW               | VW_SQ_1 |    32 |   768 |   138   (2)| 00:00:02 |
|   4 |     HASH GROUP BY     |         |    32 |   352 |   138   (2)| 00:00:02 |
|   5 |      TABLE ACCESS FULL| TEST    | 37936 |   407K|   136   (0)| 00:00:02 |
|   6 |    TABLE ACCESS FULL  | TEST    | 37936 |   407K|   136   (0)| 00:00:02 |
---------------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"="O"."OBJECT_TYPE")
       filter("OBJECT_ID">"VW_COL_1")

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

sys@EBANK>set autot off
sys@EBANK>alter system set "_unnest_subquery"=false;
alter system set "_unnest_subquery"=false
*
ERROR at line 1:
ORA-01031: insufficient privileges

sys@EBANK>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[ora10g@single ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 8 09:01:14 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@EBANK>alter system set "_unnest_subquery"=false;

System altered.

sys@EBANK>set autot traceonly
sys@EBANK>select count(*) from test o where object_id>(select avg(object_id) from test i where i.object_type=o.object_type);

Execution Plan
----------------------------------------------------------
Plan hash value: 2139949240

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    11 |  4502   (1)| 00:00:55 |
|   1 |  SORT AGGREGATE      |      |     1 |    11 |            |          |
|*  2 |   FILTER             |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL | TEST | 37936 |   407K|   136   (0)| 00:00:02 |
|   4 |    SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  5 |     TABLE ACCESS FULL| TEST |  1186 | 13046 |   136   (0)| 00:00:02 |
-----------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"> (SELECT AVG("OBJECT_ID") FROM "TEST" "I"
              WHERE "I"."OBJECT_TYPE"=:B1))
   5 - filter("I"."OBJECT_TYPE"=:B1)

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

由上可见，如果此参数为true，则sql被重写，避免了filter操作，而设置为false时，禁止了子查询嵌套，只能采用filter操作，逻辑读大幅增加