用户反映一条SQL语句在绑定某个值的时候性能急剧下降。一般只需要要几秒钟的执行时间，而当绑定这个值的时候需要好几分钟。

SQL使用了connect_by，添加gather_plan_statistics hint实际执行后，查看执行计划

select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));

为了显示方便，我省略了一些信息

----------------------------------------------------------------------------------------------
| Id  | Operation                          | Starts |   A-Time   | Buffers | Reads  | Writes |
----------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS OUTER                |      1 |00:03:56.87 |    9654K|    136K|  67609 |
|   2 |   NESTED LOOPS                     |      1 |00:03:56.74 |    9647K|    136K|  67609 |
|   3 |    NESTED LOOPS                    |      1 |00:03:56.64 |    9642K|    136K|  67609 |
|   4 |     TABLE ACCESS BY INDEX ROWID    |      1 |00:00:00.01 |       3 |      0 |      0 |
|*  5 |      INDEX UNIQUE SCAN             |      1 |00:00:00.01 |       2 |      0 |      0 |
|   6 |     VIEW                           |      1 |00:03:56.63 |    9642K|    136K|  67609 |
|*  7 |      FILTER                        |      1 |00:03:56.63 |    9642K|    136K|  67609 |
|*  8 |       CONNECT_BY WITH FILTERING    |      1 |00:04:06.36 |    9642K|    136K|  67609 |
|   9 |        TABLE ACCESS BY INDEX ROWID |      1 |00:00:00.01 |      42 |      0 |      0 |
|* 10 |         INDEX RANGE SCAN           |      1 |00:00:00.01 |       3 |      0 |      0 |
|  11 |        NESTED LOOPS                |      4 |00:00:03.50 |     141K|      0 |      0 |
|  12 |         BUFFER SORT                |      4 |00:00:00.12 |       0 |      0 |      0 |
|  13 |          CONNECT_BY PUMP           |      4 |00:00:00.03 |       0 |      0 |      0 |
|  14 |         TABLE ACCESS BY INDEX ROWID|  25174 |00:00:03.67 |     141K|      0 |      0 |
|* 15 |          INDEX RANGE SCAN          |  25174 |00:00:01.39 |   52553 |      0 |      0 |
|  16 |        TABLE ACCESS FULL           |      1 |00:00:08.57 |   97566 |  97554 |      0 |
|* 17 |       TABLE ACCESS BY INDEX ROWID  |     45 |00:00:00.01 |     135 |      0 |      0 |
|* 18 |        INDEX UNIQUE SCAN           |     45 |00:00:00.01 |      90 |      0 |      0 |
|* 19 |    TABLE ACCESS BY INDEX ROWID     |   1069 |00:00:00.10 |    4240 |      0 |      0 |
|* 20 |     INDEX UNIQUE SCAN              |   1069 |00:00:00.07 |    3171 |      0 |      0 |
|* 21 |   TABLE ACCESS BY INDEX ROWID      |   1069 |00:00:00.21 |    7368 |      0 |      0 |
|* 22 |    INDEX RANGE SCAN                |   1069 |00:00:00.12 |    5359 |      0 |      0 |
----------------------------------------------------------------------------------------------

发现有大量的磁盘IO，并且在ID 16 “TABLE ACCESS FULL”这一步上，Starts不为0。在
pages 233 to 236中有提到如果这一步实际被执行的话，说明sort_area_size不够大，是的connect_by操作使用到了磁盘IO。在10203版本，有一个bug 5065418，描述如下：

Bug 5065418 CONNECT_BY performance degrades when temp space needed on disk
This note gives a brief overview of bug 5065418.
The content was last updated on: 03-APR-2009
Click here for details of each of the sections below.
Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11
Versions confirmed as being affected

* 9.2.0.7
* 9.2.0.8
* 10.2.0.3

Platforms affected Generic (all / most platforms affected)

Fixed:

This issue is fixed in

* 9.2.0.8 Patch 6 on Windows Platforms
* 10.2.0.3 Patch 2 on Windows Platforms
* 10.2.0.4 (Server Patch Set)
* 11.1.0.6 (Base Release)

Symptoms:

Related To:

* Performance Of Query/ies Affected

* Connect_By / Hierarchical Queries
* PGA_AGGREGATE_TARGET
* SORT_AREA_SIZE

Description

The performance of some connect_by queries degrades more than expected
when the operation uses temp space (spills to disk).

eg:
If a connect_by query that uses filtering (the query plan shows filtering
as the option for connect_by) runs slower, and there is not enough memory
for the connect operation to complete (it has to spill to disk), you may
be encountering this problem. One way to verify whether it has switched
to the no-filtering mode is to run the query with sql_trace turned on and
then check whether the third child of the connect_by row source is
outputting any rows.

Workaround
If possible, increase the amount of memory for connect_by.
This can be done by increasing pga_aggregate_target (in auto memory management mode)
or by increasing sort_area_size (when workarea_size_policy is set to manual).

在10204版本中测试，同样的SQL语句，同样的数据量，没有使用TEMP空间。估计10203的算法有点问题，导致connect_by操作需要使用更多的内存空间。

可以通过调大sort_area_size来临时解决性能问题，通过调大sort_area_size，执行时间缩短到正常的几秒钟时间。