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

pkmonster · 发表于 2013-9-10 13:32

anlinew 发表于 2013-9-10 13:29
相同的sql？

把逗号改为减号了

anlinew · 发表于 2013-9-10 13:33

pkmonster 发表于 2013-9-10 13:32
把逗号改为减号了

才发现。。。

anlinew · 发表于 2013-9-10 13:52

oracle 快的时候相当于转换为下面的sql了：
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%'  group by e.a
      ) m
where b.a>=3
and b.a<=4
and b.b= m.a
group by b.b;

〇〇 · 发表于 2013-9-10 14:17

anlinew 发表于 2013-9-10 13:52
oracle 快的时候相当于转换为下面的sql了：
select b.b,
max(b.id) - /*很慢*/

group by就不需要distinct
参见
http://www.itpub.net/thread-1815231-2-1.html

myth8860 · 发表于 2013-9-10 14:53

anlinew 发表于 2013-9-10 13:33
才发现。。。

呵呵，我也看半天才发现的

anlinew · 发表于 2013-9-10 15:09

SQL>  select /*+NO_MERGE(m)*/ 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%'
  8          ) m
  9 where b.a>=3
10    and b.a<=4
11    and b.b= m.a
12 group by b.b;

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

------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    | 13 | 104 | 659 (6)| 00:00:08 |
| 1 |  HASH GROUP BY       |    | 13 | 104 | 659 (6)| 00:00:08 |
|*  2 | HASH JOIN          |    | 202K|  1584K| 646 (4)| 00:00:08 |
| 3 | VIEW             |    | 13 | 39 |    8  (13)| 00:00:01 |
| 4 |    HASH UNIQUE    |    | 13 | 39 |    8  (13)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| E | 500 |  1500 |    7 (0)| 00:00:01 |
|*  6 | TABLE ACCESS FULL  | BIG  | 452K|  4859K| 634 (4)| 00:00:08 |
------------------------------------------------------------------------------

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

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

〇〇 · 发表于 2013-9-10 15:28

anlinew 发表于 2013-9-10 15:09
SQL>  select /*+NO_MERGE(m)*/ b.b,
  2       max(b.id) - /*很慢*/
  3       min(b.id) min_us ...

这样可以，但distinct的味道不好

anlinew · 发表于 2013-9-10 16:26

〇〇发表于 2013-9-10 14:17
group by就不需要distinct
参见
http://www.itpub.net/thread-1815231-2-1.html

才发现链接里兔子已经发现了
其实就是CBO对view做了merge造成的，很多场景下，这种转换会带来效率的提升，但有时会出问题

〇〇 · 发表于 2013-9-10 16:47

anlinew 发表于 2013-9-10 16:26
才发现链接里兔子已经发现了
其实就是CBO对view做了merge造成的，很多场景下，这种转换会带来效率的提升 ...

这个更好的写法还是

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

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

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

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

Plan hash value: 1357920637

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    1 |       |    4 |00:00:00.14 | 2230 |    |    |       |
| 1 |  HASH GROUP BY       |    |    1 |    13 |    4 |00:00:00.14 | 2230 | 838K| 838K| 1254K (0)|
|*  2 | HASH JOIN RIGHT SEMI|    |    1 | 122K|  39410 |00:00:00.13 | 2230 |  1517K|  1517K| 1028K (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"="M"."A")
3 - filter(TO_CHAR("M"."A") LIKE '1%')
4 - filter(("B"."A">=3 AND "B"."A"<=4))

已选择26行。

lyzhanhun · 发表于 2013-9-11 01:55

楼主的插入在Dm中一个存在的问题, 上面的Insert语句connect by后面用改用ROWNUM条件，跟用Level计划上有区别
SQL>desc a;
列名    类型    长度    标度    是否可以为空
---------- ---------- ---------- ---------- ------------
ID       INT       10       0       Y
SQL>explain insert into a select 1 from dual connect by level <= 1e5;
1 #INSERT : [0, 0, 0]; table(A), type(select)
2    #PRJT2: [0, 2, 1]; exp_num(1), is_atom(FALSE)
3    #HIERARCHICAL QUERY: [0, 2, 1];
4       #CSCN2: [0, 1, 1]; SYSINDEXSYSDUAL(DUAL)
5       #SLCT2: [0, 1, 0]; var2 <= 1.000000e+005
6          #CSCN2: [0, 1, 0]; SYSINDEXSYSDUAL(DUAL)
已用时间:101.804(毫秒). 执行号:0.
SQL>explain insert into a select 1 from dual connect by rownum <= 1e5;
1 #INSERT : [0, 0, 0]; table(A), type(select)
2    #PRJT2: [0, 2, 1]; exp_num(1), is_atom(FALSE)
3    #RN: [0, 2, 1]
4       #HIERARCHICAL QUERY: [0, 2, 1];
5          #CSCN2: [0, 1, 1]; SYSINDEXSYSDUAL(DUAL)
6          #SLCT2: [0, 1, 0]; exp_cast(rownum) <= 1.000000e+005
7          #CSCN2: [0, 1, 0]; SYSINDEXSYSDUAL(DUAL)
已用时间:0.975(毫秒). 执行号:0.

这里有个奇怪的问题两个语句计划6中 where 条件用Level直接是 LEVEL可能本身就是int 立即数比较
Dm里ROWNUM为BIGINT， Where中用rownum则需要把ROWNUM  Cast成Int，还是对每个ROWNUM都进行Cast，可能会影响执行时间，当然也有可能BIGINT的比较比较慢，故意这么做

这样的句子计划就是反的是对立即数做Cast
SQL>explain select 1 from dual where rownum < 100000;
1 #NSET2: [0, 1, 1]
2    #PRJT2: [0, 1, 1]; exp_num(1), is_atom(FALSE)
3    #RNSK: [0, 1, 1]; rownum < exp_cast(100000)
4       #CSCN2: [0, 1, 1]; SYSINDEXSYSDUAL(DUAL)
已用时间:0.547(毫秒). 执行号:0.

查看节点执行时间（需把dm.ini 中 ENABLE_MONITOR 改为3）
SQL>create or replace procedure pa(id int)
2 as
3 begin
4 select name, time_used, n_enter from v$sql_node_history a, v$sql_node_name
where a.type$ = b.type$ and exec_id = id;
5 end;
6 /
操作已执行
已用时间:709.892(毫秒). 执行号:19.
SQL>insert into a select level from dual connect by rownum < 1e5;
insert into a select level from dual connect by rownum < 1e5;
[-7083]:层次查询最大层次太小，请增大CNNTB_MAX_LEVEL.
SQL>select sf_get_para_value(1, 'CNNTB_MAX_LEVEL');
行号    SF_GET_PARA_VALUE(1,'CNNTB_MAX_LEVEL')
---------- --------------------------------------
1       10000
已用时间:3.438(毫秒). 执行号:21.
SQL>CALL SP_SET_PARA_VALUE(1, 'CNNTB_MAX_LEVEL', 100000);
PL/SQL 过程已成功完成
已用时间:5.846(毫秒). 执行号:22.
SQL>insert into a select level from dual connect by rownum < 1e5;
影响行数 100000
已用时间:00:00:01.422. 执行号:23.
SQL>insert into a select level from dual connect by level <= 1e5;
影响行数 100000
已用时间:763.357(毫秒). 执行号:24.
SQL>call pa(23);
行号    NAME TIME_USED          N_ENTER
---------- ------- -------------------- -----------
1       DLCK 6                   2
2       INSERT2 754151             100002
3       PRJT2 55497             200002
4       RN    35423             200002
5       CNNTB 114183             300002
6       CSCN2 20                2
7       SLCT2 94494             399999
8       CSCN2 209778             200000
8 rows got
已用时间:367.795(毫秒). 执行号:25.
SQL>call pa(24)
2 ;
行号    NAME TIME_USED          N_ENTER
---------- ------- -------------------- -----------
1       DLCK 4                   2
2       INSERT2 386918             102
3       PRJT2 5655                202
4       CNNTB 60566             200102
5       CSCN2 20                2
6       SLCT2 67177             399997
7       CSCN2 156772             199998
7 rows got
已用时间:5.673(毫秒). 执行号:26.

24，25 SLCT2 进入次数接近，时间差异可能在Cast上
Insert2 进入次数 25进入近100次每次1000行符合默认BDTA1000 批处理操作 1E5 行插入 24中进入了1E5次每次只插入1行？这里的差异会比较大
应该是利用RONUM作为连接查询条件导致每次只向上层返回了一行数据

一个简单的例子可以验证
SQL>create table d(id int);
操作已执行
已用时间:337.403(毫秒). 执行号:79.
SQL>insert into d values(1),(2),(3),(4),(5);
影响行数 5
已用时间:1.064(毫秒). 执行号:80.
SQL>commit;
操作已执行
已用时间:0.655(毫秒). 执行号:81.
SQL>insert into d select * from d where id = rownum;
影响行数 5
已用时间:1.323(毫秒). 执行号:82.
SQL>call pa(82);
行号    NAME TIME_USED          N_ENTER
---------- ------- -------------------- -----------
1       DLCK 5                   2
2       INSERT2 79                7
3       NTTS2 58                12
4       PRJT2 2                   12
5       RN    5                   12
6       SLCT2 12                12
7       CSCN2 37                6
7 rows got
已用时间:6.362(毫秒). 执行号:83.
SQL>rollbcak;
rollbcak;
第1 行附近出现错误[-2193]:无效的方法名[ROLLBCAK].
SQL>rollback;
操作已执行
已用时间:0.358(毫秒). 执行号:84.
SQL>insert into d select * from d;
影响行数 5
已用时间:0.674(毫秒). 执行号:85.
SQL>call pa(85);
行号    NAME TIME_USED          N_ENTER
---------- ------- -------------------- -----------
1       DLCK 4                   2
2       INSERT2 78                3
3       NTTS2 8                   4
4       PRJT2 2                   4
5       CSCN2 15                2
已用时间:5.270(毫秒). 执行号:86.

除去Insert不做插入操作的两次进入（猜的）， 86和83进入Insert的次数分别是1，和5， 83 明显每次一行插入
应该是优化器问题，不知道Oracle这样的句子表现怎么样

另插入时用level作条件的时间可能还是慢了点，扩大BDTA不知道有效果没，这里BDTA_SIZE 2000
SQL>drop table a;
操作已执行
已用时间:82.319(毫秒). 执行号:3.
SQL>create table a(id int, id1 int, id2 int);
操作已执行
已用时间:21.238(毫秒). 执行号:4.
SQL>insert into a select rownum l, mod(rownum, 7), mod(rownum, 29) from (select
1 from dual connect by level <= 1e5) a, (select 1 from dual connect by level <=
1e1);
影响行数 1000000
已用时间:831.548(毫秒). 执行号:5.

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

浏览过的版块