一个执行计划的问题

bell6248

原帖由 anlinew 于 2010-8-4 13:33 发表

同意你的说法

不过你再仔细看看我给的两条sql，这里hints只是为了强制CBO做转换，实际上因为有了group by，这里的hints并没能生效，而没有group by时，不用hints CBO也能做转换的

另外，对于楼主当前的问题，sorts并非这里问题的关键，这里的group by并不会带来更多的sort

delete from test_filter_hash t1
where created <(select /*+unnest*/ max(created) from test_filter_hash t2 where t2.id=t1.id);


2个 sorts (memory)  这里为什么会有2个排序呢？

anlinew

原帖由 zergduan 于 2010-8-4 14:05 发表


... 不是说group by 在_complex_view_merging=true的时候，也可以merge的么？
怎么这里不可以？

这个。。。。
我们猜想一下写CBO那部分代码的程序员是怎么做的吧
我认为oracle实际在做这些merging的时候，可能为了尽量不对系统造成较大影响，可能只是在为那部分代码增加了一些分支，比如当出现
a.id in (select .....group by ....)时merging
这也符合我们通常出补丁的做法，呵呵


SQL> select * from test_filter_hash t1
  2  where created in (select  max(created) from test_filter_hash t2 where t2.id=t1.id
  3  GROUP BY id
  4  );

已选择999行。


执行计划
----------------------------------------------------------
Plan hash value: 2768489654

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    33 |    10  (30)| 00:00:01 |
|   1 |  NESTED LOOPS               |                  |     1 |    33 |    10  (30)| 00:00:01 |
|   2 |   VIEW                      | VW_SQ_1          |  1078 | 22638 |     8  (25)| 00:00:01 |
|   3 |    HASH UNIQUE              |                  |     1 | 36652 |            |          |
|*  4 |     HASH JOIN               |                  |  1078 | 36652 |     8  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL      | TEST_FILTER_HASH |  1078 | 12936 |     3   (0)| 00:00:01 |
|   6 |      VIEW                   | VW_GBD_4         |   999 | 21978 |     4  (25)| 00:00:01 |
|   7 |       HASH GROUP BY         |                  |   999 | 11988 |     4  (25)| 00:00:01 |
|   8 |        TABLE ACCESS FULL    | TEST_FILTER_HASH |  1078 | 12936 |     3   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS BY USER ROWID| TEST_FILTER_HASH |     1 |    12 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   4 - access("ITEM_1"="T1"."ID")
   9 - filter("CREATED"="MAX(CREATED)")


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        261  consistent gets


[ 本帖最后由 anlinew 于 2010-8-4 15:58 编辑 ]

anlinew

SQL> select/*+ optimizer_features_enable('10.2.0.4')*/ * from test_filter_hash t1
  2  where created in (select  max(created) from test_filter_hash t2 where t2.id=t1.id
  3  GROUP BY id
  4  );

已选择999行。


执行计划
----------------------------------------------------------
Plan hash value: 2892159413

------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |     1 |    33 |    11  (19)| 00:00:01 |
|*  1 |  HASH JOIN SEMI       |                  |     1 |    33 |    11  (19)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | TEST_FILTER_HASH |  1078 | 12936 |     3   (0)| 00:00:01 |
|   3 |   VIEW                | VW_SQ_1          |   999 | 20979 |     8  (25)| 00:00:01 |
|   4 |    HASH GROUP BY      |                  |   999 | 23976 |     8  (25)| 00:00:01 |
|*  5 |     HASH JOIN         |                  |  1163 | 27912 |     7  (15)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| TEST_FILTER_HASH |  1078 | 12936 |     3   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL| TEST_FILTER_HASH |  1078 | 12936 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - access("CREATED"="MAX(CREATED)" AND "ITEM_1"=ROWID)
   5 - access("T2"."ID"="T1"."ID")


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         21  consistent gets

SQL> select/*+ optimizer_features_enable('10.2.0.4')*/ * from test_filter_hash t1
  2  where created = (select  max(created) from test_filter_hash t2 where t2.id=t1.id
  3  GROUP BY id
  4  );

已选择999行。


执行计划
----------------------------------------------------------
Plan hash value: 4236364659

------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |     1 |    12 |  1626   (1)| 00:00:20 |
|*  1 |  FILTER               |                  |       |       |            |          |
|   2 |   TABLE ACCESS FULL   | TEST_FILTER_HASH |  1078 | 12936 |     3   (0)| 00:00:01 |
|   3 |   SORT GROUP BY NOSORT|                  |     1 |    12 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL  | TEST_FILTER_HASH |     1 |    12 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter("CREATED"= (SELECT MAX("CREATED") FROM "TEST_FILTER_HASH" "T2"
              WHERE "T2"."ID"=:B1 GROUP BY "ID"))
   4 - filter("T2"."ID"=:B1)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7066  consistent gets
          0  physical reads
          0  redo size
      17790  bytes sent via SQL*Net to client
       1141  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed


[ 本帖最后由 anlinew 于 2010-8-4 16:11 编辑 ]

cwjl11

谢谢大家。。。好久没来了。。嗯 那个group by 的确是没必要加，只是当时加后发现执行计划改变了，所以问下大家是为什么。再次感谢。。。。

ignu

原帖由 anlinew 于 2010-8-4 15:56 发表

这个。。。。
我们猜想一下写CBO那部分代码的程序员是怎么做的吧
我认为oracle实际在做这些merging的时候，可能为了尽量不对系统造成较大影响，可能只是在为那部分代码增加了一些分支，比如当出现
a.id in (select .....group by ....)时merging
这也符合我们通常出补丁的做法，呵呵


SQL> select * from test_filter_hash t1
  2  where created in (select  max(created) from test_filter_hash t2 where t2.id=t1.id
  3  GROUP BY id
  4  );

已选择999行。


执行计划
----------------------------------------------------------
Plan hash value: 2768489654

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    33 |    10  (30)| 00:00:01 |
|   1 |  NESTED LOOPS               |                  |     1 |    33 |    10  (30)| 00:00:01 |
|   2 |   VIEW                      | VW_SQ_1          |  1078 | 22638 |     8  (25)| 00:00:01 |
|   3 |    HASH UNIQUE              |                  |     1 | 36652 |            |          |
|*  4 |     HASH JOIN               |                  |  1078 | 36652 |     8  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL      | TEST_FILTER_HASH |  1078 | 12936 |     3   (0)| 00:00:01 |
|   6 |      VIEW                   | VW_GBD_4         |   999 | 21978 |     4  (25)| 00:00:01 |
|   7 |       HASH GROUP BY         |                  |   999 | 11988 |     4  (25)| 00:00:01 |
|   8 |        TABLE ACCESS FULL    | TEST_FILTER_HASH |  1078 | 12936 |     3   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS BY USER ROWID| TEST_FILTER_HASH |     1 |    12 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   4 - access("ITEM_1"="T1"."ID")
   9 - filter("CREATED"="MAX(CREATED)")


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        261  consistent gets

in 和= 在10.2.0.4下会导致执行计划不同， 这个版本下呢？

ignu

难道11g会改用nest loop 了？