ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 优化模式的选择对sql执行计划产生的不同的影响

优化模式的选择对sql执行计划产生的不同的影响

原创 Linux操作系统 作者:bpmfhu 时间:2019-05-22 18:24:05 0 删除 编辑
今天捕获到一个非常耗资源的sql语句,拿出来分析了一下,可以看到优化模式的选择对sql执行计划产生的不同的影响.

今天捕捉到一个sql语句:

SELECT aa1_.storyid, aa1_.storylength storylength_1, aa1_.holder,
       SYSDATE - aa1_.readtime AS readtime, aa1_.holder, aa1_.convey_time,
       aa1_.folder_id, aa1_.status_name, aa1_.delete_staff_id,
       aa2.text AS contenttext, aa2.title, aa2.subtitle, aa2.leadtitle,
       aa2.word_count, aa2.summary, aa3.create_time, aa3.modify_time,
       aa3.author, aa3.author_id, aa3.final_version_no, aa3.creator,
       aa3.final_editor, aa3.SCOPE, aa3.pub_time, aa3.approver,
       aa3.meeting_flag, 'TOMEETING    ' AS seted_flag,
       '已报稿' AS meeting_flag_name, '编前会通过' AS meeting_flag_name2,
       aa3.significance, aa3.urgency, aa3.TYPE, aa3.SOURCE,
       aa3.storylength AS actuallen, aa3.attach_count, aa3.tag, aa3.genre,
       aa3.confidence_id, aa3.copyrecord, aa3.subject_issue_id, aa3.ext,
       aa3.team_id, aa3.event_time, aa3.specify_length,
       ROUND (aa1_.storylength / 28.346, 2) AS storylength, aa4.issue_id
  FROM (SELECT ROWNUM AS recno2, recno, storyid, storylength, a.create_time,
               a.modify_time, a.holder, a.convey_time, a.folder_id,
               a.status_name, a.readtime, a.delete_staff_id
          FROM (SELECT   ROWNUM AS recno, vb.story_id AS storyid,
                         vp.storylength AS storylength, vb.create_time,
                         vb.modify_time, ra.holder, ra.convey_time,
                         ra.folder_id, rc.NAME status_name,
                         rd_user_readed_data.TIME readtime,
                         rb.delete_staff_id
                    FROM folder_data ra,
                         DATA rb,
                         data_status rc,
                         user_readed_data rd_user_readed_data,
                         story_property vb,
                         apgeometry vp
                   WHERE vb.meeting_flag = 'TOMEETING'
                     AND vb.subject_issue_id = 0
                     AND 1 = 1
                     AND vb.story_id = vp.story_id(+)
                     AND vb.story_id = ra.data_id
                     AND rb.delete_staff_id = 0
                     AND rb.ID = rd_user_readed_data.data_id(+)
                     AND rb.data_status_id = rc.ID(+)
                     AND rb.ID = ra.data_id
                     AND rd_user_readed_data.staff_id(+) = 442752
                ORDER BY vb.modify_time DESC) a) aa1_,
       story aa2,
       story_property aa3,
       subject_issue aa4
 WHERE aa1_.storyid = aa3.story_id
   AND aa1_.recno2 BETWEEN 1 AND 10
   AND aa3.subject_issue_id = aa4.ID(+)
   AND aa3.story_id = aa2.ID

1.在first_rows下面看到这样的执行计划,需要25s执行完,这样的计划肯定不是合理的。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=49924 Card=21308
           Bytes=58490460)

   1    0   NESTED LOOPS (Cost=49924 Card=21308 Bytes=58490460)
   2    1     NESTED LOOPS (OUTER) (Cost=43531 Card=21308 Bytes=387805
          6)

   3    2       NESTED LOOPS (Cost=37139 Card=21308 Bytes=3664976)
   4    3         VIEW (Cost=30746 Card=21308 Bytes=2258648)
   5    4           COUNT
   6    5             VIEW (Cost=30746 Card=21308 Bytes=1981644)
   7    6               SORT (ORDER BY) (Cost=30746 Card=21308 Bytes=1
          725948)

   8    7                 COUNT
   9    8                   NESTED LOOPS (OUTER) (Cost=30470 Card=2130
          8 Bytes=1725948)

  10    9                     NESTED LOOPS (OUTER) (Cost=24078 Card=21
          308 Bytes=1491560)

  11   10                       NESTED LOOPS (OUTER) (Cost=17686 Card=
          21308 Bytes=1150632)

  12   11                         NESTED LOOPS (Cost=11293 Card=21308
          Bytes=958860)

  13   12                           NESTED LOOPS (Cost=4886 Card=21354
           Bytes=726036)

  14   13                             INDEX (FULL SCAN) OF 'FOLDER_DAT
          A_I_DATA_ID_F_1' (NON-UNIQUE) (Cost=1716 Card=479828 Bytes=5
          757936)

  15   13                             TABLE ACCESS (BY INDEX ROWID) OF
           'STORY_PROPERTY' (Cost=4886 Card=1 Bytes=22)

  16   15                               BITMAP CONVERSION (TO ROWIDS)
  17   16                                 BITMAP AND
  18   17                                   BITMAP CONVERSION (FROM RO
          WIDS)

  19   18                                     INDEX (RANGE SCAN) OF 'I
          DX_SP_SUBJECT' (NON-UNIQUE)

  20   17                                   BITMAP CONVERSION (FROM RO
          WIDS)

  21   20                                     INDEX (RANGE SCAN) OF 'I
          DX_SP_MEETING_FLAG' (NON-UNIQUE)

  22   12                           TABLE ACCESS (BY INDEX ROWID) OF '
          DATA' (Cost=2 Card=1 Bytes=11)

  23   22                             INDEX (UNIQUE SCAN) OF 'PK_DATA'
           (UNIQUE)

  24   11                         TABLE ACCESS (BY INDEX ROWID) OF 'AP
          GEOMETRY' (Cost=2 Card=1 Bytes=9)

  25   24                           INDEX (UNIQUE SCAN) OF 'SYS_C00103
          34' (UNIQUE)

  26   10                       TABLE ACCESS (BY INDEX ROWID) OF 'USER
          _READED_DATA' (Cost=2 Card=1 Bytes=16)

  27   26                         INDEX (UNIQUE SCAN) OF 'PK_USER_READ
          ED_DATA' (UNIQUE)

  28    9                     TABLE ACCESS (BY INDEX ROWID) OF 'DATA_S
          TATUS' (Cost=2 Card=1 Bytes=11)

  29   28                       INDEX (UNIQUE SCAN) OF 'PK_DATA_STATUS
          ' (UNIQUE)

  30    3         TABLE ACCESS (BY INDEX ROWID) OF 'STORY_PROPERTY' (C
          ost=2 Card=1 Bytes=66)

  31   30           INDEX (UNIQUE SCAN) OF 'PK_STORY_PROPERTY' (UNIQUE
          )

  32    2       TABLE ACCESS (BY INDEX ROWID) OF 'SUBJECT_ISSUE' (Cost
          =2 Card=1 Bytes=10)

  33   32         INDEX (UNIQUE SCAN) OF 'PK_SUBJECT_ISSUE' (UNIQUE)
  34    1     TABLE ACCESS (BY INDEX ROWID) OF 'STORY' (Cost=2 Card=1
          Bytes=2563)

  35   34       INDEX (UNIQUE SCAN) OF 'PK_STORY' (UNIQUE)

2.这样注意到优化器对这段sql使用了大量的嵌套,于是首先想到对所有的表收集统计信息,包括柱状图,但是执行计划依旧。

3.对于这样的大表和小表的连接,我想应该用hash join会好一点。

4.于是加上改为rule的hint,看看执行计划有什么不同,发现数据库采用了hash join,速度得到大幅提高。

----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE (Cost=8061 Card=20603
          Bytes=56575838)

   1    0   NESTED LOOPS (Cost=8061 Card=20603 Bytes=56575838)
   2    1     HASH JOIN (OUTER) (Cost=1880 Card=20603 Bytes=3749746)
   3    2       HASH JOIN (Cost=1819 Card=20603 Bytes=3543716)
   4    3         VIEW (Cost=950 Card=20603 Bytes=2183918)
   5    4           COUNT
   6    5             VIEW (Cost=950 Card=20603 Bytes=1916079)
   7    6               SORT (ORDER BY) (Cost=950 Card=20603 Bytes=166
          8843)

   8    7                 COUNT
   9    8                   HASH JOIN (OUTER) (Cost=682 Card=20603 Byt
          es=1668843)

  10    9                     HASH JOIN (OUTER) (Cost=656 Card=20603 B
          ytes=1339195)

  11   10                       HASH JOIN (OUTER) (Cost=633 Card=20603
           Bytes=1112562)

  12   11                         HASH JOIN (Cost=612 Card=20603 Bytes
          =927135)

  13   12                           HASH JOIN (Cost=258 Card=20649 Byt
          es=702066)

  14   13                             TABLE ACCESS (BY INDEX ROWID) OF
           'STORY_PROPERTY' (Cost=2 Card=20649 Bytes=454278)

  15   14                               INDEX (RANGE SCAN) OF 'STORY_P
          ROPERTY_I_MEETING_FLAG' (NON-UNIQUE) (Cost=3 Card=109)

  16   13                             TABLE ACCESS (FULL) OF 'FOLDER_D
          ATA' (Cost=128 Card=480772 Bytes=5769264)

  17   12                           INDEX (FAST FULL SCAN) OF 'DATA_I_
          ID_DATA_STATUS_ID_DELET' (NON-UNIQUE) (Cost=189 Card=479718
          Bytes=5276898)

  18   11                         INDEX (FAST FULL SCAN) OF 'APGEOMETR
          Y_I_STORY_ID_STORYLEN' (NON-UNIQUE) (Cost=2 Card=2321 Bytes=
          20889)

  19   10                       TABLE ACCESS (FULL) OF 'DATA_STATUS' (
          Cost=2 Card=27 Bytes=297)

  20    9                     INDEX (RANGE SCAN) OF 'IDX_USER_READED_D
          ATA' (NON-UNIQUE) (Cost=2 Card=115 Bytes=1840)

  21    3         TABLE ACCESS (FULL) OF 'STORY_PROPERTY' (Cost=382 Ca
          rd=379364 Bytes=25038024)

  22    2       INDEX (FAST FULL SCAN) OF 'SUBJECT_ISSUE_I_ID_ISSUE_ID
          ' (NON-UNIQUE) (Cost=2 Card=2182 Bytes=21820)

  23    1     TABLE ACCESS (BY INDEX ROWID) OF 'STORY' (Cost=2 Card=1
          Bytes=2564)

  24   23       INDEX (UNIQUE SCAN) OF 'PK_STORY' (UNIQUE)

5.为什么cbo在first_rows没有采用正确的执行计划呢?

  先后调整了optimizer_index_cost_adj;hash_area_size,仍然无效。

6.由于这个sql的有一个条件是BETWEEN 1 AND 10,我将optimizer_mode设置为first_rows_10,终于看到这样的执行计划:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=31 Card=10 Bytes
          =27390)

   1    0   NESTED LOOPS (Cost=31 Card=10 Bytes=27390)
   2    1     NESTED LOOPS (OUTER) (Cost=21 Card=10 Bytes=1740)
   3    2       NESTED LOOPS (Cost=11 Card=10 Bytes=1640)
   4    3         VIEW (Cost=1 Card=20062 Bytes=1966076)
   5    4           COUNT
   6    5             VIEW (Cost=938 Card=20062 Bytes=1705270)
   7    6               SORT (ORDER BY) (Cost=938 Card=20062 Bytes=162
          5022)

   8    7                 COUNT
   9    8                   HASH JOIN (OUTER) (Cost=677 Card=20062 Byt
          es=1625022)

  10    9                     HASH JOIN (OUTER) (Cost=651 Card=20062 B
          ytes=1304030)

  11   10                       HASH JOIN (OUTER) (Cost=627 Card=20062
           Bytes=1123472)

  12   11                         HASH JOIN (Cost=607 Card=20062 Bytes
          =902790)

  13   12                           HASH JOIN (Cost=255 Card=20106 Byt
          es=683604)

  14   13                             TABLE ACCESS (BY INDEX ROWID) OF
           'STORY_PROPERTY' (Cost=2 Card=20106 Bytes=442332)

  15   14                               INDEX (RANGE SCAN) OF 'IDX_SP_
          MEETING_FLAG' (NON-UNIQUE) (Cost=1 Card=107)

  16   13                             TABLE ACCESS (FULL) OF 'FOLDER_D
          ATA' (Cost=128 Card=482316 Bytes=5787792)

  17   12                           INDEX (FAST FULL SCAN) OF 'DATA_I_
          ID_DATA_STATUS_ID_DELET' (NON-UNIQUE) (Cost=190 Card=481255
          Bytes=5293805)

  18   11                         TABLE ACCESS (FULL) OF 'DATA_STATUS'
           (Cost=2 Card=27 Bytes=297)

  19   10                       INDEX (FAST FULL SCAN) OF 'APGEOMETRY_
          I_STORY_ID_STORYLEN' (NON-UNIQUE) (Cost=2 Card=2341 Bytes=21
          069)

  20    9                     INDEX (RANGE SCAN) OF 'IDX_USER_READED_D
          ATA' (NON-UNIQUE) (Cost=2 Card=116 Bytes=1856)

  21    3         TABLE ACCESS (BY INDEX ROWID) OF 'STORY_PROPERTY' (C
          ost=1 Card=1 Bytes=66)

  22   21           INDEX (UNIQUE SCAN) OF 'PK_STORY_PROPERTY' (UNIQUE
          )

  23    2       TABLE ACCESS (BY INDEX ROWID) OF 'SUBJECT_ISSUE' (Cost
          =1 Card=1 Bytes=10)

  24   23         INDEX (UNIQUE SCAN) OF 'PK_SUBJECT_ISSUE' (UNIQUE)
  25    1     TABLE ACCESS (BY INDEX ROWID) OF 'STORY' (Cost=1 Card=1
          Bytes=2565)

  26   25       INDEX (UNIQUE SCAN) OF 'PK_STORY' (UNIQUE)


Statistics
----------------------------------------------------------
         32  recursive calls
          0  db block gets
       3571  consistent gets
          4  physical reads
          0  redo size
      11399  bytes sent via SQL*Net to client
      11697  bytes received via SQL*Net from client
         29  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          9  rows processed

整个sql的执行只要1s左右,相当明显。

7.接着测试all_rows,也很快:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=21929 Card=20062 B
          ytes=55110314)

   1    0   NESTED LOOPS (Cost=21929 Card=20062 Bytes=55110314)
   2    1     HASH JOIN (OUTER) (Cost=1867 Card=20062 Bytes=3651284)
   3    2       HASH JOIN (Cost=1808 Card=20062 Bytes=3450664)
   4    3         VIEW (Cost=938 Card=20062 Bytes=2126572)
   5    4           COUNT
   6    5             VIEW (Cost=938 Card=20062 Bytes=1865766)
   7    6               SORT (ORDER BY) (Cost=938 Card=20062 Bytes=162
          5022)

   8    7                 COUNT
   9    8                   HASH JOIN (OUTER) (Cost=677 Card=20062 Byt
          es=1625022)

  10    9                     HASH JOIN (OUTER) (Cost=651 Card=20062 B
          ytes=1304030)

  11   10                       HASH JOIN (OUTER) (Cost=627 Card=20062
           Bytes=1123472)

  12   11                         HASH JOIN (Cost=607 Card=20062 Bytes
          =902790)

  13   12                           HASH JOIN (Cost=255 Card=20106 Byt
          es=683604)

  14   13                             TABLE ACCESS (BY INDEX ROWID) OF
           'STORY_PROPERTY' (Cost=2 Card=20106 Bytes=442332)

  15   14                               INDEX (RANGE SCAN) OF 'IDX_SP_
          MEETING_FLAG' (NON-UNIQUE) (Cost=1 Card=107)

  16   13                             TABLE ACCESS (FULL) OF 'FOLDER_D
          ATA' (Cost=128 Card=482316 Bytes=5787792)

  17   12                           INDEX (FAST FULL SCAN) OF 'DATA_I_
          ID_DATA_STATUS_ID_DELET' (NON-UNIQUE) (Cost=190 Card=481255
          Bytes=5293805)

  18   11                         TABLE ACCESS (FULL) OF 'DATA_STATUS'
           (Cost=2 Card=27 Bytes=297)

  19   10                       INDEX (FAST FULL SCAN) OF 'APGEOMETRY_
          I_STORY_ID_STORYLEN' (NON-UNIQUE) (Cost=2 Card=2341 Bytes=21
          069)

  20    9                     INDEX (RANGE SCAN) OF 'IDX_USER_READED_D
          ATA' (NON-UNIQUE) (Cost=2 Card=116 Bytes=1856)

  21    3         TABLE ACCESS (FULL) OF 'STORY_PROPERTY' (Cost=382 Ca
          rd=380900 Bytes=25139400)

  22    2       INDEX (FAST FULL SCAN) OF 'SUBJECT_ISSUE_I_ID_ISSUE_ID
          ' (NON-UNIQUE) (Cost=2 Card=2182 Bytes=21820)

  23    1     TABLE ACCESS (BY INDEX ROWID) OF 'STORY' (Cost=1 Card=1
          Bytes=2565)

  24   23       INDEX (UNIQUE SCAN) OF 'PK_STORY' (UNIQUE)

8.看来all_rows更偏重使用hash join?

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4096/viewspace-52460/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-12-26

  • 博文量
    134
  • 访问量
    99847