【Oracle】Oracle wrong result一则（优化器问题）

最新推荐文章于 2019-08-14 16:40:00 发布
最新推荐文章于 2019-08-14 16:40:00 发布
阅读量277 收藏
点赞数
文章标签： Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u014346634/article/details/81535245
版权

现象如下：

SYS@proc> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@proc> drop table test purge;

Table dropped.

SYS@proc> with t as (select a.*,rownum rn from dba_objects a) select count(*) from t where t.rn=(select max(rn) from t);

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

SYS@proc> create table test as select * from dba_objects;

Table created.

SYS@proc> with t as (select a.*,rownum rn from test a) select * from t where t.rn=(select max(rn) from t);

no rows selected

先看下两者的执行计划：

SYS@proc> set long 9999 pagesize 9999 lines 500
SYS@proc> set autotrace traceonly
SYS@proc> with t as (select a.*,rownum rn from dba_objects a) select count(*) from t where t.rn=(select max(rn) from t);


Execution Plan
----------------------------------------------------------
Plan hash value: 1413014202

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                           |     1 |    13 |  1336   (1)| 00:00:17 |
|   1 |  TEMP TABLE TRANSFORMATION       |                           |       |       |            |          |
|   2 |   LOAD AS SELECT                 | SYS_TEMP_0FD9D6606_10C429 |       |       |            |          |
|   3 |    COUNT                         |                           |       |       |            |          |
|   4 |     VIEW                         | DBA_OBJECTS               | 86954 |    13M|   301   (1)| 00:00:04 |
|   5 |      UNION-ALL                   |                           |       |       |            |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID| SUM$                      |     1 |     9 |     1   (0)| 00:00:01 |
|*  7 |        INDEX UNIQUE SCAN         | I_SUM$_1                  |     1 |       |     0   (0)| 00:00:01 |
|   8 |       TABLE ACCESS BY INDEX ROWID| OBJ$                      |     1 |    30 |     3   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN          | I_OBJ1                    |     1 |       |     2   (0)| 00:00:01 |
|* 10 |       FILTER                     |                           |       |       |            |          |
|* 11 |        HASH JOIN                 |                           | 86953 |    10M|   299   (2)| 00:00:04 |
|  12 |         TABLE ACCESS FULL        | USER$                     |    92 |  1564 |     3   (0)| 00:00:01 |
|* 13 |         HASH JOIN                |                           | 86953 |  9000K|   296   (2)| 00:00:04 |
|  14 |          INDEX FULL SCAN         | I_USER2                   |    92 |  2024 |     1   (0)| 00:00:01 |
|* 15 |          TABLE ACCESS FULL       | OBJ$                      | 86953 |  7132K|   294   (1)| 00:00:04 |
|  16 |        NESTED LOOPS              |                           |     1 |    29 |     2   (0)| 00:00:01 |
|* 17 |         INDEX SKIP SCAN          | I_USER2                   |     1 |    20 |     1   (0)| 00:00:01 |
|* 18 |         INDEX RANGE SCAN         | I_OBJ4                    |     1 |     9 |     1   (0)| 00:00:01 |
|  19 |       NESTED LOOPS               |                           |     1 |   105 |     2   (0)| 00:00:01 |
|  20 |        TABLE ACCESS FULL         | LINK$                     |     1 |    88 |     2   (0)| 00:00:01 |
|  21 |        TABLE ACCESS CLUSTER      | USER$                     |     1 |    17 |     0   (0)| 00:00:01 |
|* 22 |         INDEX UNIQUE SCAN        | I_USER#                   |     1 |       |     0   (0)| 00:00:01 |
|  23 |   SORT AGGREGATE                 |                           |     1 |    13 |            |          |
|* 24 |    VIEW                          |                           | 86954 |  1103K|   517   (1)| 00:00:07 |
|  25 |     TABLE ACCESS FULL            | SYS_TEMP_0FD9D6606_10C429 | 86954 |    13M|   517   (1)| 00:00:07 |
|  26 |     SORT AGGREGATE               |                           |     1 |    13 |            |          |
|  27 |      VIEW                        |                           | 86954 |  1103K|   517   (1)| 00:00:07 |
|  28 |       TABLE ACCESS FULL          | SYS_TEMP_0FD9D6606_10C429 | 86954 |    13M|   517   (1)| 00:00:07 |
--------------------------------------------------------------------------------------------------------------

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

   6 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   7 - access("S"."OBJ#"=:B1)
   9 - access("EO"."OBJ#"=:B1)
  10 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
              "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND
              "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR
              ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10
              OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR
              "O"."TYPE#"=87) AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS
              (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88
              AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
              edition_id')))))
  11 - access("O"."SPARE3"="U"."USER#")
  13 - access("O"."OWNER#"="U"."USER#")
  15 - filter("O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND
              "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0)
  17 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  18 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  22 - access("L"."OWNER#"="U"."USER#")
  24 - filter("T"."RN"= (SELECT MAX("RN") FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
              "OWNER","C1" "OBJECT_NAME","C2" "SUBOBJECT_NAME","C3" "OBJECT_ID","C4" "DATA_OBJECT_ID","C5"
              "OBJECT_TYPE","C6" "CREATED","C7" "LAST_DDL_TIME","C8" "TIMESTAMP","C9" "STATUS","C10"
              "TEMPORARY","C11" "GENERATED","C12" "SECONDARY","C13" "NAMESPACE","C14" "EDITION_NAME","C15" "RN"
              FROM "SYS"."SYS_TEMP_0FD9D6606_10C429" "T1") "T"))


Statistics
----------------------------------------------------------
         12  recursive calls
       1342  db block gets
       3718  consistent gets
       2626  physical reads
       1116  redo size
        526  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SYS@proc> 
SYS@proc> 
SYS@proc> with t as (select a.*,rownum rn from test a) select * from t where t.rn=(select max(rn) from t);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1063871704

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 94909 |    19M|   676   (1)| 00:00:09 |
|*  1 |  VIEW                 |      | 94909 |    19M|   338   (1)| 00:00:05 |
|   2 |   COUNT               |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL  | TEST | 94909 |    18M|   338   (1)| 00:00:05 |
|   4 |   SORT AGGREGATE      |      |     1 |    13 |            |          |
|   5 |    VIEW               |      | 94909 |  1204K|   338   (1)| 00:00:05 |
|   6 |     COUNT             |      |       |       |            |          |
|   7 |      TABLE ACCESS FULL| TEST | 94909 |       |   338   (1)| 00:00:05 |
------------------------------------------------------------------------------

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

   1 - filter("T"."RN"= (SELECT MAX("RN") FROM  (SELECT ROWNUM "RN"
              FROM "TEST" "A") "T"))

Note
-----
   - dynamic sampling used for this statement (level=2)


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

从第二个执行计划的Predicate Information处看出，filter里边的T表给解析成只有一个字段”RN”（也应该有结果出来？）。
尝试加hint让with as的部分放在temp表，执行结果以及执行计划如下：

SYS@proc> set autotrace off
SYS@proc> with t as (select /*+MATERIALIZE*/ a.*,rownum rn from test a) select count(*) from t where t.rn=(select max(rn) from t);

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

SYS@proc> set autotrace traceonly
SYS@proc> l
  1* with t as (select /*+MATERIALIZE*/ a.*,rownum rn from test a) select count(*) from t where t.rn=(select max(rn) from t)
SYS@proc> /


Execution Plan
----------------------------------------------------------
Plan hash value: 340454420

--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |     1 |    13 |  1790   (1)| 00:00:22 |
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6608_10C429 |       |       |            |          |
|   3 |    COUNT                   |                           |       |       |            |          |
|   4 |     TABLE ACCESS FULL      | TEST                      | 94909 |    18M|   338   (1)| 00:00:05 |
|   5 |   SORT AGGREGATE           |                           |     1 |    13 |            |          |
|*  6 |    VIEW                    |                           | 94909 |  1204K|   726   (1)| 00:00:09 |
|   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6608_10C429 | 94909 |    18M|   726   (1)| 00:00:09 |
|   8 |     SORT AGGREGATE         |                           |     1 |    13 |            |          |
|   9 |      VIEW                  |                           | 94909 |  1204K|   726   (1)| 00:00:09 |
|  10 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6608_10C429 | 94909 |    18M|   726   (1)| 00:00:09 |
--------------------------------------------------------------------------------------------------------

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

   6 - filter("T"."RN"= (SELECT MAX("RN") FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
              "OWNER","C1" "OBJECT_NAME","C2" "SUBOBJECT_NAME","C3" "OBJECT_ID","C4" "DATA_OBJECT_ID","C5"
              "OBJECT_TYPE","C6" "CREATED","C7" "LAST_DDL_TIME","C8" "TIMESTAMP","C9" "STATUS","C10"
              "TEMPORARY","C11" "GENERATED","C12" "SECONDARY","C13" "NAMESPACE","C14" "EDITION_NAME","C15"
              "RN" FROM "SYS"."SYS_TEMP_0FD9D6608_10C429" "T1") "T"))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         12  recursive calls
       1339  db block gets
       3873  consistent gets
       3866  physical reads
        764  redo size
        526  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

结果已经执行出来了。
另外该语句在10g是可以不用加hint就能正常执行：

SYS@proc> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi


SYS@proc> with t as (select a.*,rownum rn from test a) select count(*) from t where t.rn=(select max(rn) from t);

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

这里尝试将11g里边的参数optimizer_features_enable修改成10g的，在看看执行计划以及结果：

SYS@proc> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      11.2.0.4
SYS@proc> alter session set optimizer_features_enable='10.2.0.4';

Session altered.

SYS@proc> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      10.2.0.4


SYS@proc> set pagesize 9999 long 9999 lines 500
SYS@proc> with t as (select a.*,rownum rn from test a) select count(*) from t where t.rn=(select max(rn) from t);

  COUNT(*)
----------
         0

SYS@proc> set autotrace traceonly
SYS@proc> l
  1* with t as (select a.*,rownum rn from test a) select count(*) from t where t.rn=(select max(rn) from t)
SYS@proc> /


Execution Plan
----------------------------------------------------------
Plan hash value: 4022736097

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    13 |   677   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE        |      |     1 |    13 |            |          |
|*  2 |   VIEW                 |      | 94909 |  1204K|   338   (1)| 00:00:05 |
|   3 |    COUNT               |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL  | TEST | 94909 |       |   338   (1)| 00:00:05 |
|   5 |    SORT AGGREGATE      |      |     1 |    13 |            |          |
|   6 |     VIEW               |      | 94909 |  1204K|   338   (1)| 00:00:05 |
|   7 |      COUNT             |      |       |       |            |          |
|   8 |       TABLE ACCESS FULL| TEST | 94909 |       |   338   (1)| 00:00:05 |
-------------------------------------------------------------------------------

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

   2 - filter("T"."RN"= (SELECT MAX("RN") FROM  (SELECT "A"."OWNER"
              "OWNER","A"."OBJECT_NAME" "OBJECT_NAME","A"."SUBOBJECT_NAME"
              "SUBOBJECT_NAME","A"."OBJECT_ID" "OBJECT_ID","A"."DATA_OBJECT_ID"
              "DATA_OBJECT_ID","A"."OBJECT_TYPE" "OBJECT_TYPE","A"."CREATED"
              "CREATED","A"."LAST_DDL_TIME" "LAST_DDL_TIME","A"."TIMESTAMP"
              "TIMESTAMP","A"."STATUS" "STATUS","A"."TEMPORARY"
              "TEMPORARY","A"."GENERATED" "GENERATED","A"."SECONDARY"
              "SECONDARY","A"."NAMESPACE" "NAMESPACE","A"."EDITION_NAME"
              "EDITION_NAME",ROWNUM "RN" FROM "TEST" "A") "T"))

Note
-----
   - dynamic sampling used for this statement (level=2)


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

Predicate Information的filter已经没错了，但是还是出不来结果。
这里不是很懂了。

PiscesCanon
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle分析函数多层使用时的一个bug及其解决方案
07-08 162
前几天因为客户数据统计错误，引起强烈反应，加班加点终于把问题解决。但是最终结果却出人意料，经排查，确定并非我们代码的问题，而是Oracle的一个bug。bug号及描述：6840911 Wrongs results fr...
Oracle for in loop循环时expression is of wrong type报错
小小的客栈 大大的江湖
05-27 4309
1、我本来想做一个嵌套的for in loop循环插入。第一层查出代码，然后利用这个代码再查出所有的日期。但是使用这个日期的时候一直报错，如下：BEGIN FOR item IN (SELECT DISTINCT SECINNERCODE,SECURITYCODE FROM FUND_BS_ATYPE WHERE ISUSABLE='1' AND TYPECODE IN (180108) O
Bug 10202228 wrong result when _allow_level_without_connect_by set to true
cuilingqiao0657的博客
10-26 504
Bug 10202228wrong result when _allow_level_without_connect_by set to true This note gives a brief o...
oracle数据库性能优化.pdf
05-27
执行计划控制则涉及SQL优化器的选择和 Hint的使用，以引导Oracle选择最佳执行路径。 此外，操作系统参数的调整和网络性能优化也不容忽视。比如，调整操作系统的内存分配，确保Oracle有足够的资源；网络层面，减少...
ORACLE存储过程性能优化技巧
06-19
ORACLE存储过程性能优化技巧
Oracle监听器被优化大师挂掉后的完美解决方法
09-09
本篇将详细介绍如何解决Oracle监听器在优化大师操作后无法启动的问题。 首先，问题的根源可能在于注册表中ORACLE_HOME变量的缺失或配置不正确。ORACLE_HOME变量指向Oracle的安装目录，通常包含了Oracle的所有可执行...
ORACLE-SQL性能优化大全.pdf
02-23
ORACLE_SQL性能优化大全.pdf
ORACLESQL性能优化.pptx
04-24
通过对 SQL 语句执行的过程、Oracle 优化器、表之间的关联、如何得到 SQL 执行计划、如何分析执行计划等内容的讨论，可以逐步掌握 SQL 优化的过程。 在 SQL 优化过程中，需要了解性能管理、性能问题、调整的方法、...
oracle ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
ORACLE-爬行
03-19 3282
在手工创建数据库的过程中遇到如下错误： SQL>  @/home/oracle/scripts/create.sql CREATE DATABASE PROD * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-01501: CREATE DATABASE failed
zabbix报错排错大全3
08-14 6141
zabbix报错排错大全 zabbix报错 https://www.cnblogs.com/losbyday/category/876878.html作者总结的很全棒 1.在启动zabbix-agent时系统日志输出 PIDfile/run/zabbix/zabb...
查询oracle数据库是否有重复数据（根据多个字段判断）
weixin_33878457的博客
12-04 3249
2019独角兽企业重金招聘Python工程师标准>>> ...
JDBC_使用ResultSet 执行查询操作（基于oracle数据库）
chance2015的博客
12-27 6266
上篇文章已经使用Statement实现了对数据库的增删改操作，接下来使用ResultSet结果集执行查询操作 步骤如下: ResultSet:结果集.封装了使用JDNC进行查询的结果 1.调用Statement对象的executeQuery(sql) 可以得到结果集 2.ResultSet返回的实际上就是一张数据表，有一个指针指向数据表的第一行的前面 可以调用next() 方法检测下一行是
Crypto_30_vHsm_Types.h
07-09
Crypto_30_vHsm_Types
vmware虚拟机安装教程.pdf
最新发布
07-10
VMware虚拟机安装教程是一个详细且系统的过程，涵盖了从下载、安装到配置虚拟机的多个步骤。以下是一个详尽的VMware虚拟机安装教程，旨在帮助用户顺利搭建自己的虚拟环境。
11111111111
07-10
11111111111
oracle-优化器详解.docx
09-12
"Oracle 优化器是数据库管理系统Oracle中用于确定如何执行SQL查询的一个关键组件。它通过分析可能的执行路径并选择最有效率的方式来执行SQL语句，从而提高数据库性能。Oracle9i引入了成本基优化器（Cost-Based ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值