一个oracle执行计划不好的例子，dm的计划如何？

myth8860

〇〇 发表于 2013-9-9 21:21
查询是dm快，oracle不知哪里秀逗了

dm7可以看到执行计划里面每个操作符的执行时间，如果秀逗的话，可以知道在哪里秀逗了，呵呵

〇〇

myth8860 发表于 2013-9-10 09:22
dm7可以看到执行计划里面每个操作符的执行时间，如果秀逗的话，可以知道在哪里秀逗了，呵呵

贴出一个dm秀逗的看看

myth8860

〇〇 发表于 2013-9-10 09:24
贴出一个dm秀逗的看看

目前发现的秀逗的都改掉了，我再发现一个秀逗的发你看看。

〇〇

oracle显示的实际计划

SQL> set autot off
SQL> select /*+ gather_plan_statistics */   b.b,
  2         max(b.id) , /*很快*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;
select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
         B  MAX(B.ID)   MIN_USED
---------- ---------- ----------
         1     999863         59
        11     999989         11
        12     999961        157
        10     999988         10

SQL> /
  2  ;
select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));/
                                                                         *
第 1 行出现错误:
ORA-00911: 无效字符


SQL> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  anbybgr6yf8u1, child number 0
-------------------------------------
select /*+ gather_plan_statistics */   b.b,        max(b.id) , /*很快*/
     min(b.id) min_used   from big b,        (select distinct e.a
    from e         where e.a like '1%') m where b.a>=3    and b.a<=4
and b.b= m.a group by b.b

Plan hash value: 69571800

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |      4 |00:00:00.16 |    2230 |       |       |          |
|   1 |  HASH GROUP BY        |          |      1 |     13 |      4 |00:00:00.16 |    2230 |   838K|   838K| 1224K (0)|
|*  2 |   HASH JOIN           |          |      1 |    128K|  39410 |00:00:00.20 |    2230 |  1517K|  1517K|  828K (0)|
|   3 |    VIEW               | VW_GBF_5 |      1 |     13 |      4 |00:00:00.01 |      22 |       |       |          |
|   4 |     HASH GROUP BY     |          |      1 |     13 |      4 |00:00:00.01 |      22 |  1518K|  1518K|  708K (0)|
|*  5 |      TABLE ACCESS FULL| E        |      1 |    500 |   3077 |00:00:00.01 |      22 |       |       |          |
|*  6 |    TABLE ACCESS FULL  | BIG      |      1 |    285K|    285K|00:00:00.13 |    2208 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

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

   2 - access("B"."B"="ITEM_1")
   5 - filter(TO_CHAR("E"."A") LIKE '1%')
   6 - filter(("B"."A">=3 AND "B"."A"<=4))


已选择28行。

SQL> select /*+ gather_plan_statistics */b.b,
  2         max(b.id) - /*很慢*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

         B   MIN_USED
---------- ----------
         1     999804
        11     999978
        12     999804
        10     999978

SQL> SQL>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  238f8vj22nb8m, child number 0
-------------------------------------
select /*+ gather_plan_statistics */b.b,        max(b.id) - /*很慢*/
  min(b.id) min_used   from big b,        (select distinct e.a
from e         where e.a like '1%') m where b.a>=3    and b.a<=4
and b.b= m.a group by b.b

Plan hash value: 2033372611

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      4 |00:00:10.61 |    2230 |       |       |          |
|   1 |  HASH GROUP BY      |      |      1 |     13 |      4 |00:00:10.61 |    2230 |   838K|   838K| 4105K (0)|
|*  2 |   HASH JOIN         |      |      1 |   4926K|     30M|00:00:05.75 |    2230 |  1517K|  1517K| 1004K (0)|
|*  3 |    TABLE ACCESS FULL| E    |      1 |    500 |   3077 |00:00:00.01 |      22 |       |       |          |
|*  4 |    TABLE ACCESS FULL| BIG  |      1 |    285K|    285K|00:00:00.09 |    2208 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

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

   2 - access("B"."B"="E"."A")
   3 - filter(TO_CHAR("E"."A") LIKE '1%')
   4 - filter(("B"."A">=3 AND "B"."A"<=4))


已选择26行。

myth8860

〇〇 发表于 2013-9-10 09:48
oracle显示的实际计划

SQL> set autot off

dm7这两个语句的计划是一样的

〇〇

〇〇 发表于 2013-9-10 09:48
oracle显示的实际计划

SQL> set autot off

另一台window机器的计划

SQL> conn / as sysdba
已连接。
SQL> grant dba to scott;

授权成功。

已用时间:  00: 00: 00.02
SQL> conn scott/tiger
已连接。
SQL> select /*+ gather_plan_statistics */   b.b,
  2         max(b.id) , /*很快*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

                   B            MAX(B.ID)             MIN_USED
-------------------- -------------------- --------------------
                   1               999863                   59
                  11               999989                   11
                  12               999961                  157
                  10               999988                   10

已用时间:  00: 00: 00.26
SQL>
SQL> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  anbybgr6yf8u1, child number 1
-------------------------------------
select /*+ gather_plan_statistics */   b.b,        max(b.id) , /*很快*/
     min(b.id) min_used   from big b,        (select distinct e.a
    from e         where e.a like '1%') m where b.a>=3    and b.a<=4
and b.b= m.a group by b.b

Plan hash value: 69571800

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |      4 |00:00:00.25 |    2230 |       |    |             |
|   1 |  HASH GROUP BY        |          |      1 |      4 |      4 |00:00:00.25 |    2230 |   838K|   838K|  708K (0)|
|*  2 |   HASH JOIN           |          |      1 |  39409 |  39410 |00:00:00.38 |    2230 |  1517K|  1517K|  959K (0)|
|   3 |    VIEW               | VW_GBF_5 |      1 |      4 |      4 |00:00:00.01 |      22 |       |    |             |
|   4 |     HASH GROUP BY     |          |      1 |      4 |      4 |00:00:00.01 |      22 |  1518K|  1518K| 1249K (0)|
|*  5 |      TABLE ACCESS FULL| E        |      1 |   3077 |   3077 |00:00:00.01 |      22 |       |    |             |
|*  6 |    TABLE ACCESS FULL  | BIG      |      1 |    285K|    285K|00:00:00.57 |    2208 |       |    |             |
-----------------------------------------------------------------------------------------------------------------------

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

   2 - access("B"."B"="ITEM_1")
   5 - filter(TO_CHAR("E"."A") LIKE '1%')
   6 - filter(("B"."A">=3 AND "B"."A"<=4))

Note
-----
   - cardinality feedback used for this statement


已选择32行。

已用时间:  00: 00: 00.12
SQL>
SQL> select /*+ gather_plan_statistics */b.b,
  2         max(b.id) - /*很慢*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

                   B             MIN_USED
-------------------- --------------------
                   1               999804
                  10               999978
                  11               999978
                  12               999804

已用时间:  00: 00: 19.31
SQL>
SQL> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  238f8vj22nb8m, child number 1
-------------------------------------
select /*+ gather_plan_statistics */b.b,        max(b.id) - /*很慢*/
  min(b.id) min_used   from big b,        (select distinct e.a
from e         where e.a like '1%') m where b.a>=3    and b.a<=4
and b.b= m.a group by b.b

Plan hash value: 1216833904

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      4 |00:00:05.13 |    2230 |       |       |          |
|   1 |  SORT GROUP BY NOSORT|      |      1 |      4 |      4 |00:00:05.13 |    2230 |       |       |          |
|   2 |   MERGE JOIN         |      |      1 |     30M|     30M|00:03:42.88 |    2230 |       |       |          |
|   3 |    SORT JOIN         |      |      1 |   3077 |   3077 |00:00:00.03 |      22 | 70656 | 70656 |63488  (0)|
|*  4 |     TABLE ACCESS FULL| E    |      1 |   3077 |   3077 |00:00:00.01 |      22 |       |       |          |
|*  5 |    SORT JOIN         |      |   3077 |    285K|     30M|00:00:46.86 |    2208 |  8628K|  1150K| 7669K (0)|
|*  6 |     TABLE ACCESS FULL| BIG  |      1 |    285K|    285K|00:00:01.43 |    2208 |       |       |          |
------------------------------------------------------------------------------------------------------------------

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

   4 - filter(TO_CHAR("E"."A") LIKE '1%')
   5 - access("B"."B"="E"."A")
       filter("B"."B"="E"."A")
   6 - filter(("B"."A">=3 AND "B"."A"<=4))

Note
-----
   - cardinality feedback used for this statement


已选择33行。

已用时间:  00: 00: 00.06

pkmonster

myth8860 发表于 2013-9-10 10:14
dm7这两个语句的计划是一样的

贴一下达梦的计划, 和Oracle比较下.

需要查查Insert 慢的问题.

myth8860

pkmonster 发表于 2013-9-10 11:13
贴一下达梦的计划, 和Oracle比较下.

需要查查Insert 慢的问题.

oracle走了MERGE JOIN         

explain select b.b,
       max(b.id) , /*很快*/
       min(b.id) min_used
  from big b,
       (select distinct e.a
          from e
        where e.a like '1%') m
where b.a>=3
   and b.a<=4
   and b.b= m.a
group by b.b;


1   #NSET2: [119, 1, 16]
2     #PRJT2: [119, 1, 16]; exp_num(3), is_atom(FALSE)
3       #HAGR2: [119, 1, 16]; grp_num(1), sfun_num(2)
4         #HASH2 INNER JOIN: [119, 1, 16];  KEY_NUM(1);
5           #PRJT2: [1, 5, 4]; exp_num(1), is_atom(FALSE)
6             #DISTINCT: [1, 5, 4]
7               #SLCT2: [1, 500, 4]; exp_cast(E.A) LIKE 1%%
8                 #CSCN2: [1, 10000, 4]; INDEX33559697(E)
9           #SLCT2: [116, 2500, 12]; (B.A >= 3 AND B.A <= 4)
10            #CSCN2: [116, 1000000, 12]; INDEX33559699(B)


-------------------------------------------------------------------------------------------------

explain select b.b,
       max(b.id) - /*很慢*/
       min(b.id) min_used
  from big b,
       (select distinct e.a
          from e
        where e.a like '1%') m
where b.a>=3
   and b.a<=4
   and b.b= m.a
group by b.b;


1   #NSET2: [119, 1, 16]
2     #PRJT2: [119, 1, 16]; exp_num(2), is_atom(FALSE)
3       #HAGR2: [119, 1, 16]; grp_num(1), sfun_num(2)
4         #HASH2 INNER JOIN: [119, 1, 16];  KEY_NUM(1);
5           #PRJT2: [1, 5, 4]; exp_num(1), is_atom(FALSE)
6             #DISTINCT: [1, 5, 4]
7               #SLCT2: [1, 500, 4]; exp_cast(E.A) LIKE 1%%
8                 #CSCN2: [1, 10000, 4]; INDEX33559697(E)
9           #SLCT2: [116, 2500, 12]; (B.A >= 3 AND B.A <= 4)
10            #CSCN2: [116, 1000000, 12]; INDEX33559699(B)

pkmonster

达梦用一个显式的投影操作符(PRJT2)来计算查询项,  两个查询唯一的差别也在于查询项,后一个多了一个计算.

不明白oracle为什么要加个SORT, 然后做Merge, 应该慢在排序上。

anlinew

相同的sql？