Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2880491
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2012-12-27 16:02:02


如果我们的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> 
阅读(3668) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~