如果我们的SQL采用了ROWNUM=1的查询方法，而且系统的CURSOR_SHARING设置了SIMILAR，
那么我们很有可能遇到Bug 4513695。
ORACLE并不承认这是个BUG，以下是ORACLE给出的解释：
This is not really a bug as CURSOR_SHARING=SIMILAR does not give the optimizer
all of the same information that EXACT would give it. However,
it looks like we could improve this。
我们模拟一下这个BUG。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table t as select * from all_objects;
表已创建。
SQL> exec dbms_stats.gather_table_stats(user,'t');
PL/SQL 过程已成功完成。
SQL> select /*+gather_plan_statistics*/ object_name from t where rownum=1;
OBJECT_NAME
------------------------------
ICOL$
SQL> set pagesize 300
SQL> set linesize 100
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 7aqzst3cmdmtq, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ object_name from t where rownum=1
Plan hash value: 508354683
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 4 |
| 2 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
已选择18行。
正常情况下ORACLE的执行计划中可以看到COUNT STOPKEY ，逻辑读仅仅为4.
下面我们修改 cursor_sharing=similar.
SQL> alter system set cursor_sharing=similar;
系统已更改。
/*为了防止执行计划共享，我将OBJECT_NAME改为了OBJECT_ID*/
SQL> select /*+gather_plan_statistics*/ object_id from t where rownum=1;
OBJECT_ID
----------
20
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID gsz52m22jatau, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ object_id from t where rownum=:"SYS_B_0"
Plan hash value: 4220795399
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 1 | COUNT | | 1 | | 1 |00:00:00.01 | 690 |
|* 2 | FILTER | | 1 | | 1 |00:00:00.01 | 690 |
| 3 | TABLE ACCESS FULL| T | 1 | 49770 | 49770 |00:00:00.20 | 690 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=:SYS_B_0)
已选择19行。
这次我们看到执行计划完全不一样了，没有出现COUNT STOPKEY ，ORACLE进行了整个全表扫描
而不是读取到第一条就返回，从E-ROWS A-ROWS 及其BUFFERS也可以看出。
ORACLCE给出了如下3中解决方法：
Use a /*+ CURSOR_SHARING_EXACT */ hint
or
Use ROWNUM < literal
or
USE ROWNUM <= literal
SQL> select /*+gather_plan_statistics CURSOR_SHARING_EXACT*/ object_type from t where rownum=1;
OBJECT_TYPE
-------------------
TABLE
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 5pydayux6fjjq, child number 0
-------------------------------------
select /*+gather_plan_statistics CURSOR_SHARING_EXACT*/ object_type from t
where rownum=1
Plan hash value: 508354683
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 4 |
| 2 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
已选择19行。
SQL> select /*+gather_plan_statistics*/ status from t where rownum<2;
STATUS
-------
VALID
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 8n9tkap658tt4, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ status from t where rownum<:"SYS_B_0"
Plan hash value: 508354683
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 4 |
| 2 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<:SYS_B_0)
已选择18行。
SQL> select /*+gather_plan_statistics*/ created from t where rownum<=1;
CREATED
--------------
30-8月 -05
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID ga42dsxaax8ws, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ created from t where rownum<=:"SYS_B_0"
Plan hash value: 508354683
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 4 |
| 2 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=:SYS_B_0)
已选择18行。
SQL>
这个问题不光发生在CURSOR_SHARING=SIMILAR的情况下，如果ROWNUM采用了绑定变量的话，也会遇到这个BUG。
如下：
SQL> alter system set cursor_sharing=exact;
系统已更改。
SQL> variable rn number;
SQL> exec :rn:=1;
PL/SQL 过程已成功完成。
SQL> select /*+gather_plan_statistics*/ last_ddl_time from t where rownum=:rn;
LAST_DDL_TIME
--------------
30-8月 -05
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 4p3vy7hfr04hn, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ last_ddl_time from t where rownum=:rn
Plan hash value: 4220795399
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 1 | COUNT | | 1 | | 1 |00:00:00.01 | 690 |
|* 2 | FILTER | | 1 | | 1 |00:00:00.01 | 690 |
| 3 | TABLE ACCESS FULL| T | 1 | 49770 | 49770 |00:00:00.15 | 690 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=:RN)
已选择19行。
SQL>
ORACLE在11.0.0.0.0解决了这个BUG。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table t as select * from all_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'t');
PL/SQL procedure successfully completed.
SQL> alter system set cursor_sharing=similar;
System altered.
SQL> select /*+gather_plan_statistics*/ object_id from t where rownum=1;
OBJECT_ID
----------
20
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8tt30xtxfy7gv, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ object_id from t where rownum=1
Plan hash value: 508354683
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | 15 |
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 4 | 15 |
| 2 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 4 | 15 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
19 rows selected.
SQL> alter system set cursor_sharing=exact;
System altered.
SQL> variable rn number;
SQL> exec :rn:=1;
PL/SQL procedure successfully completed.
SQL> select /*+gather_plan_statistics*/ last_ddl_time from t where rownum=:rn;
LAST_DDL_TIME
-------------------
2009-08-13 23:09:45
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4p3vy7hfr04hn, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ last_ddl_time from t where rownum=:rn
Plan hash value: 508354683
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | 15 |
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 4 | 15 |
| 2 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 4 | 15 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=:RN)
19 rows selected.
SQL>
