关于ROWID分页中,执行计划的错误选择与处理(二)

来源:互联网 发布:网络教育统考有作弊吗 编辑:程序博客网 时间:2024/05/15 17:12

在以上的现象中,是很难理解oracle为什么采用了全表扫描与hash join的连接方式,我们不妨做一个10053 event,看看oracle到底做了什么。

SQL> set autot trace
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

Session altered.

SQL> SELECT t.topic_id,t.topic_type,t.topic_distillate,t.topic_vote,t.topic_status,t.topic_moved_id,
  2    TO_CHAR(t.topic_time,'YYYY-MM-DD HH24:MI:SS')  topic_time,
  3    t.topic_last_post_id,t.topic_views,t.topic_title, t.topic_replies,
  4    t.topic_poster FROM forum_topics t
  5    where rowid in
  6    (select rid from
  7    (select a.rowid rid,row_number() over(order by a.topic_type DESC,a.topic_last_post_id DESC) rn
  8    from  forum_topics a
  9    WHERE a.forum_id=40
 10    AND a.topic_type < 2
 11    AND a.topic_status <> 3 
 12    ) WHERE rn < 50 and rn >= 1) ;

49 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=869 Card=1678 Bytes=194648)
   1    0   HASH JOIN (SEMI) (Cost=869 Card=1678 Bytes=194648)
   2    1     TABLE ACCESS (FULL) OF 'FORUM_TOPICS' (Cost=444 Card=221324 Bytes=24124316)
   3    1     VIEW OF 'VW_NSO_1' (Cost=17 Card=1678 Bytes=11746)
   4    3       VIEW (Cost=17 Card=1678 Bytes=33560)
   5    4         WINDOW (SORT PUSHED RANK) (Cost=17 Card=1678 Bytes=31882)
   6    5           INDEX (RANGE SCAN) OF 'IND_FORUM_TOP_FOR_TP_ST_ID'(NON-UNIQUE) (Cost=2 Card=1678 Bytes=31882)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4613  consistent gets
          0  physical reads
          0  redo size
       5576  bytes sent via SQL*Net to client
        537  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         49  rows processed

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

Session altered.

我们来分析跟踪文件。这里是跟踪文件的第二部分,这里决定了为什么采用了hash连接还是nested连接方式。

GENERAL PLANS
***********************

#可以看到,这里是第一种连接顺序的比较
Join order[1]: FORUM_TOPICS [ T] VW_NSO_1 [VW_NSO_1]
Now joining: VW_NSO_1 [VW_NSO_1] *******
NL Join    #NESTED LOOP连接方式
  Outer table: cost: 444  cdn: 221324  rcz: 109  resp:  444
  Inner table: VW_NSO_1        #内部表是VW_NSO_1,也就是子查询
    Access path: tsc  Resc: 16
    Join:  Resc:  3652290  Resp:  3652290
Column:        RID  Col#: 1      Table: from$_subquery$_002   Alias: from$_subquery$_002
    NO STATISTICS  (using defaults)     #因为是子查询,返回的又是rowid,所以没有统计信息
    NDV: 221324    NULLS: 0         DENS: 4.5183e-06  #这里采用了主表的统计信息,不重复的rowid数
    NO HISTOGRAM: #BKT: 0 #VAL: 0
Column: $nso_col_1  Col#: 1      Table: VW_NSO_1   Alias: VW_NSO_1
    NO STATISTICS  (using defaults)
    NDV: 221324    NULLS: 0         DENS: 4.5183e-06
    NO HISTOGRAM: #BKT: 0 #VAL: 0
Semi-join cardinality:  1678 = outer (221324) * sel (7.5816e-03) [flag=12]   #成本计算
  Best NL cost: 3652290  resp: 3652290         #可见,这样的连接顺序肯定不行,成本太大
SM Join     #SEMI  Merge join  连接方式
  Outer table:
    resc: 444  cdn: 221324  rcz: 109  deg: 1  resp: 444
  Inner table: VW_NSO_1
    resc: 16  cdn: 1678  rcz: 7  deg:  1  resp: 16
    using join:1 distribution:2 #groups:1
    SORT resource      Sort statistics
      Sort width:          152 Area size:     1048576 Max Area size:    53686272   Degree: 1
      Blocks to Sort:     3523 Row size:          130 Rows:     221324
      Initial runs:          2 Merge passes:        1 IO Cost / pass:       3883
      Total IO sort cost: 3703
      Total CPU sort cost: 0
      Total Temp space used: 62530000
    SORT resource      Sort statistics
      Sort width:          152 Area size:     1048576 Max Area size:    53686272   Degree: 1
      Blocks to Sort:        4 Row size:           18 Rows:       1678
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         19
      Total IO sort cost: 12
      Total CPU sort cost: 0
      Total Temp space used: 0
  Merge join  Cost:  4175  Resp:  4175    #最终成本
HA Join        #HASH JOIN连接方式
  Outer table:
    resc: 444  cdn: 221324  rcz: 109  deg: 1  resp: 444
  Inner table: VW_NSO_1
    resc: 16  cdn: 1678  rcz: 7  deg:  1  resp: 16
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 408   Deg: 1
      hash_area:  256 (max=13107)  buildfrag:  3270                probefrag:   4 ppasses:    1
  Hash join   Resc: 868   Resp: 868
Join result: cost: 869  cdn: 1678  rcz: 116        #可见以上三种连接中,hash的成本是最低的。
Best so far: TABLE#: 0  CST:        444  CDN:     221324  BYTES:   24124316
Best so far: TABLE#: 1  CST:        869  CDN:       1678  BYTES:     194648
***********************

#下面,是改变一种连接顺序后的结果
Join order[2]: VW_NSO_1 [VW_NSO_1] FORUM_TOPICS [ T]
    SORT resource      Sort statistics
      Sort width:          152 Area size:     1048576 Max Area size:    53686272   Degree: 1
      Blocks to Sort:        4 Row size:           18 Rows:       1678
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         19
      Total IO sort cost: 12
      Total CPU sort cost: 0
      Total Temp space used: 0
     
Now joining: FORUM_TOPICS [ T] *******
NL Join   #一样,先是NESTED LOOP连接
  Outer table: cost: 28  cdn: 1678  rcz: 7  resp:  28
  Inner table: FORUM_TOPICS
    Access path: tsc  Resc: 444
    Join:  Resc:  745060  Resp:  745060
  Inner table: FORUM_TOPICS
    Access path: rowid  Resc: 1
    Join:  Resc:  1706  Resp:  1706
Join cardinality:  1678 = outer (1678) * inner (221324) * sel (4.5183e-06)  [flag=0] #成本计算
  Best NL cost: 1706  resp: 1706          #这次的成本比上一个连接顺序的成本要小多了
SM Join
  Outer table:
    resc: 28  cdn: 1678  rcz: 7  deg: 1  resp: 28
  Inner table: FORUM_TOPICS
    resc: 444  cdn: 221324  rcz: 109  deg:  1  resp: 444
    using join:1 distribution:2 #groups:1
    SORT resource      Sort statistics
      Sort width:          152 Area size:     1048576 Max Area size:    53686272   Degree: 1
      Blocks to Sort:        4 Row size:           18 Rows:       1678
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         19
      Total IO sort cost: 12
      Total CPU sort cost: 0
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:          152 Area size:     1048576 Max Area size:    53686272   Degree: 1
      Blocks to Sort:     3523 Row size:          130 Rows:     221324
      Initial runs:          2 Merge passes:        1 IO Cost / pass:       3883
      Total IO sort cost: 3703
      Total CPU sort cost: 0
      Total Temp space used: 62530000
  Merge join  Cost:  4186  Resp:  4186
HA Join
  Outer table:
    resc: 28  cdn: 1678  rcz: 7  deg: 1  resp: 28
  Inner table: FORUM_TOPICS
    resc: 444  cdn: 221324  rcz: 109  deg:  1  resp: 444
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 7   Deg: 1
      hash_area:  256 (max=13107)  buildfrag:  4                probefrag:   3270 ppasses:    1
  Hash join   Resc: 479   Resp: 479    #同时看到,hash连接的成本更低      
Final:   #最终,根据以上计算,oracle选择一个认为最优的成本
  CST: 869  CDN: 1678  RSC: 868  RSP: 868  BYTES: 194648
  IO-RSC: 868  IO-RSP: 868  CPU-RSC: 0  CPU-RSP: 0

#所以,最终的执行计划为
PLAN
Cost of plan:  869
Operation...........Object name.....Options.........Id...Pid..
SELECT STATEMENT                                        0
HASH JOIN                           SEMI                1
TABLE ACCESS        FORUM_TOPICS    FULL                2    1
VIEW                VW_NSO_1                            3    1
VIEW                                                    4    3
WINDOW                              SORT PUSHED RANK    5    4
INDEX               IND_FORUM_TOP_FORANGE SCAN          6    5

在以上的步骤中,可以看到,不管怎么连接,NESTED LOOP的连接成本都高于hash的成本,我们选择一个低的NL成本计算来分析一下:

1678 = outer (1678) * inner (221324) * sel (4.5183e-06)  [flag=0]

在这里外部表是子查询,inner (221324) * sel (4.5183e-06) 是=1的,决定成本的就是外部表的值了,居然是1678,通过分析获得,几乎等于子查询不加rn限制的记录数,也就是整个子查询的记录数,Oracle在这里出了问题,没有获得rn分页条件的限制信息,而直接使用了子查询的记录作为成本计算,当它认为1678行与221324 行的表做关联时,hash是最优的。

这么说来,只要改变子查询的记录,执行计划就可能改变,通过实验发现,如果子查询的记录数少到一定程度(如600条以下),果然就采用了NESTED LOOP连接方式。

以下是Oracle官方的解释:

The database is CBO based which means Oracle will choose the execution plan with less cost.
In oracle 9i,HJ has lower cost comparing to NL (normally),so oracle will always choose HJ instead of NL.
If you really need less logical read in the query,you should force the database choose NL inste
ad of HJ.

Thanks and regards
Paulo