今天捕获到一个非常耗资源的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)