Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › SQL 执行计划不走B-TREE索引而走Bitmap，帮忙分析分析 ...
发新帖
SKYLINE.LIU

0

积分

1

好友

6

主题

[RAC性能调优] SQL 执行计划不走B-TREE索引而走Bitmap，帮忙分析分析

1#
发表于 2013-8-30 19:23:32 | 查看: 3954| 回复: 4
本帖最后由 SKYLINE.LIU 于 2013-8-30 23:09 编辑

现场系统中以下语句执行效率很差，将现场数据库中的数据导入新的用户下进行分析：
1、只有时间条件时，JBSJ字段收集histogram和不收集histogram时，都走索引，但是JBSJ字段不收集histogram时，执行计划评估的行数与实际行数差距很大。
2、JBSJ字段收集histogram时，JBSJ条件+SFYCLID 索引走的是bitmap索引，而不是B-TREE索引
3、(j.jbdwid ='11010542000000' or j.cbdw = '11010542000000') 条件只满足之一这样写是否还有优化的余地？

数据库版本 11.1.0.6.0
操作系统 AIX 6.1

查询语句：
  1. SELECT count(1) from xcxt_wh.T_AJ_QQSJ_0828 j where j.SFYCLID = '1'
  2. and j.jbsj >= to_Date('2013-08-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
  3. and j.jbsj <= to_Date('2013-08-10 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
  4. and (j.jbdwid ='11010542000000' or j.cbdw = '11010542000000')
复制代码
表的统计信息：
  1. TABLE_NAME        NUM_ROWS        BLOCKS        AVG_ROW_LEN        SAMPLE_SIZE        LAST_ANALYZED
  2. T_AJ_QQSJ_0828        5164623        372997        484        5164623        2013/8/30 15:47:26
复制代码
JBSJ字段不收集histogram索引信息：
COLUMN_NAME        NUM_DISTINCT        NUM_NULLS        NUM_BUCKETS        DENSITY        INDEX_TYPE        LAST_ANALYZED        TABLE_NAME
SFYCLID        2        52453        2        9.7805824141216E-8        2013/8/29 16:57:08        NORMAL        T_AJ_JJDJ_0828
JBSJ        817934        0        1        1.22259253191578E-6        2013/8/29 16:57:08        NORMAL        T_AJ_JJDJ_0828
JBDWID        914        31        254        0.00333593455696059        2013/8/29 16:57:08        NORMAL        T_AJ_JJDJ_0828
CBDW        788        4701079        254        0.00255225356592582        2013/8/29 16:57:08        NORMAL        T_AJ_JJDJ_0828


JBSJ字段收集histogram索引信息：
  1. COLUMN_NAME        NUM_DISTINCT        NUM_NULLS        NUM_BUCKETS        DENSITY        LAST_ANALYZED        INDEX_TYPE        TABLE_NAME
  2. SFYCLID        2        52453        2        9.7805824141216E-8        2013/8/30 15:47:26        NORMAL        T_AJ_QQSJ_0828
  3. JBSJ        817934        0        254        1.70419305547192E-6        2013/8/30 15:47:26        NORMAL        T_AJ_QQSJ_0828
  4. JBDWID        914        31        254        0.00333593455696059        2013/8/30 15:47:26        NORMAL        T_AJ_QQSJ_0828
  5. CBDW        788        4701079        254        0.00255225356592582        2013/8/30 15:47:26        NORMAL        T_AJ_QQSJ_0828
复制代码
  1. SFYCLID 值的分布情况
  2. SFYCLID        COUNT(1)
  3. 1        5100157
  4.         52453
  5. 2        12013
复制代码
  1. JBSJ按月分布情况
  2. TRUNC(JJDJ.JBSJ,'MM')        COUNT(1)
  3. 0007/7/1        1
  4. 0007/10/1        1
  5. 1970/1/1        1
  6. 1994/6/1        1
  7. 1995/11/1        1
  8. 1996/3/1        1
  9. 1996/11/1        2
  10. 1997/4/1        1
  11. 1997/8/1        1
  12. 1997/11/1        1
  13. 1997/12/1        1
  14. 1998/6/1        1
  15. 1998/7/1        3
  16. 1998/9/1        1
  17. 1999/5/1        1
  18. 1999/10/1        1
  19. 2000/4/1        1
  20. 2000/5/1        1
  21. 2000/9/1        1
  22. 2000/12/1        1
  23. 2001/1/1        1
  24. 2001/2/1        2
  25. 2001/7/1        1
  26. 2002/2/1        1
  27. 2002/9/1        1
  28. 2002/12/1        1
  29. 2003/3/1        1
  30. 2003/12/1        1
  31. 2004/6/1        1
  32. 2004/7/1        1
  33. 2004/8/1        1
  34. 2004/9/1        2
  35. 2005/4/1        2
  36. 2005/6/1        1
  37. 2005/8/1        1
  38. 2005/9/1        1
  39. 2005/12/1        2
  40. 2006/1/1        1
  41. 2006/2/1        1
  42. 2006/4/1        3
  43. 2006/5/1        1
  44. 2006/6/1        3
  45. 2006/7/1        3
  46. 2006/9/1        1
  47. 2007/1/1        1
  48. 2007/2/1        2
  49. 2007/3/1        2
  50. 2007/5/1        1
  51. 2007/8/1        1
  52. 2007/9/1        6
  53. 2007/10/1        2
  54. 2007/11/1        2
  55. 2007/12/1        34
  56. 2008/1/1        2
  57. 2008/2/1        1
  58. 2008/3/1        16
  59. 2008/4/1        32
  60. 2008/5/1        5
  61. 2008/6/1        4
  62. 2008/7/1        4
  63. 2008/8/1        4
  64. 2008/9/1        3
  65. 2008/10/1        20
  66. 2008/11/1        13
  67. 2008/12/1        11
  68. 2009/1/1        7
  69. 2009/2/1        12
  70. 2009/3/1        24
  71. 2009/4/1        16
  72. 2009/5/1        20
  73. 2009/6/1        18
  74. 2009/7/1        8
  75. 2009/8/1        22
  76. 2009/9/1        30
  77. 2009/10/1        35
  78. 2009/11/1        37
  79. 2009/12/1        47
  80. 2010/1/1        19
  81. 2010/2/1        10
  82. 2010/3/1        31
  83. 2010/4/1        21
  84. 2010/5/1        31
  85. 2010/6/1        35
  86. 2010/7/1        36
  87. 2010/8/1        63
  88. 2010/9/1        205
  89. 2010/10/1        50
  90. 2010/11/1        70
  91. 2010/12/1        63
  92. 2011/1/1        143
  93. 2011/2/1        95
  94. 2011/3/1        235
  95. 2011/4/1        253
  96. 2011/5/1        234
  97. 2011/6/1        264
  98. 2011/7/1        298
  99. 2011/8/1        386
  100. 2011/9/1        605
  101. 2011/10/1        658
  102. 2011/11/1        889
  103. 2011/12/1        2211
  104. 2012/1/1        176434
  105. 2012/2/1        190879
  106. 2012/3/1        254203
  107. 2012/4/1        269479
  108. 2012/5/1        293446
  109. 2012/6/1        272146
  110. 2012/7/1        302918
  111. 2012/8/1        290801
  112. 2012/9/1        277589
  113. 2012/10/1        258315
  114. 2012/11/1        246647
  115. 2012/12/1        252392
  116. 2013/1/1        234720
  117. 2013/2/1        149716
  118. 2013/3/1        261894
  119. 2013/4/1        282105
  120. 2013/5/1        302420
  121. 2013/6/1        279323
  122. 2013/7/1        299978
  123. 2013/8/1        261813
  124. 9000/3/1        1
复制代码

有直方图执行计划.txt

8.39 KB, 下载次数: 958

无直方图执行计划.txt

6.37 KB, 下载次数: 1001
2#
发表于 2013-8-30 21:35:50
1、没有版本信息
2、 在 列倾斜的情况下 且无直方图情况下  基数评估错误属于正常现象

回复 只看该作者 道具 举报

3#
发表于 2013-8-30 21:42:08
SQL> SELECT count(1) from xcxt_wh.T_AJ_QQSJ_0828 j where j.SFYCLID = '1'
  2  and j.jbsj >= to_Date('2013-08-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
  3  and j.jbsj <= to_Date('2013-08-10 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
  4  /

  COUNT(1)
----------
     89309

Elapsed: 00:00:19.69

Execution Plan
----------------------------------------------------------
Plan hash value: 1331743931

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                           |     1 |    10 |       | 10193   (2)| 00:02:03 |
|   1 |  SORT AGGREGATE                  |                           |     1 |    10 |       |            |          |
|   2 |   BITMAP CONVERSION COUNT        |                           | 93509 |   913K|       | 10193   (2)| 00:02:03 |
|   3 |    BITMAP AND                    |                           |       |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                           |       |       |       |            |          |
|   5 |      SORT ORDER BY               |                           |       |       |  2984K|            |          |
|*  6 |       INDEX RANGE SCAN           | INDEX_AJ_JJDJ0828_JBSJ    | 94691 |       |       |   256   (1)| 00:00:04 |
|   7 |     BITMAP CONVERSION FROM ROWIDS|                           |       |       |       |            |          |
|*  8 |      INDEX RANGE SCAN            | INDEX_AJ_JJDJ0828_SFYCLID | 94691 |       |       |  9352   (2)| 00:01:53 |
----------------------------------------------------------------------------------------------------------------------

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

   6 - access("J"."JBSJ">=TO_DATE(' 2013-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "J"."JBSJ"<=TO_DATE('
              2013-08-10 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
   8 - access("J"."SFYCLID"='1')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      74793  consistent gets
      10708  physical reads
          0  redo size
        524  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



这个执行计划 本身没太多问题， 因为需要对比SFYCLID=1 但是 INDEX_AJ_JJDJ0828_JBSJ索引上应当没有改字段 ， 那么如果不用INDEX_AJ_JJDJ0828_SFYCLID ， 则也需要 access table by rowid ，  其执行较慢应当是因为INDEX_AJ_JJDJ0828_SFYCLID 未加载在buffer cache里。

你可以考虑针对该SQL 调整 对应索引 为组合索引

回复 只看该作者 道具 举报

4#
发表于 2013-8-30 23:28:47
本帖最后由 SKYLINE.LIU 于 2013-8-30 23:31 编辑

数据库版本已经补上了
我周一到公司建个组合索引 JBSJ,SFYCLID 试一下，然后反馈结果。

不过我有个疑问：
SFYCLID='1' 的记录占全表的98%，是高度倾斜的，根据直方图的统计信息，走全表扫描的代价更低一些。在这种情况下查询SFYCLID='1'的记录时SFYCLID字段的索引是不起作用的，那么建立组合索引的话代价真的会低吗？

回复 只看该作者 道具 举报

5#
发表于 2013-9-2 10:47:24
采用刘大的建议采用复合索引，成效很明显，逻辑读与物理读有明显降低。
  1. SQL>drop index xcxt_wh.INDEX_AJ_JJDJ0828_JBSJ
  2. SQL>drop index xcxt_wh.INDEX_AJ_JJDJ0828_SFYCLID

  4. SQL> create index xcxt_wh.INDEX_AJ_JJDJ0828_JBSJ on xcxt_wh.T_AJ_QQSJ_0828(JBSJ,SFYCLID)

  6. Index created.

  8. Elapsed: 00:00:39.22

  10. SQL> SELECT COUNT(1) as CT from xcxt_wh.T_AJ_QQSJ_0828 j where
  11.   2  j.jbsj >= to_Date('2013-08-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
  12.   3  and j.jbsj <= to_Date('2013-08-10 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
  13.   4  /

  15.         CT
  16. ----------
  17.      95845

  19. Elapsed: 00:00:00.25

  21. Execution Plan
  22. ----------------------------------------------------------
  23. Plan hash value: 738637433

  25. --------------------------------------------------------------------------------------------
  26. | Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
  27. --------------------------------------------------------------------------------------------
  28. |   0 | SELECT STATEMENT  |                        |     1 |     8 |   282   (1)| 00:00:04 |
  29. |   1 |  SORT AGGREGATE   |                        |     1 |     8 |            |          |
  30. |*  2 |   INDEX RANGE SCAN| INDEX_AJ_JJDJ0828_JBSJ | 94691 |   739K|   282   (1)| 00:00:04 |
  31. --------------------------------------------------------------------------------------------

  33. Predicate Information (identified by operation id):
  34. ---------------------------------------------------

  36.    2 - access("J"."JBSJ">=TO_DATE(' 2013-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
  37.               AND "J"."JBSJ"<=TO_DATE(' 2013-08-10 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))


  40. Statistics
  41. ----------------------------------------------------------
  42.           1  recursive calls
  43.           0  db block gets
  44.         283  consistent gets
  45.         282  physical reads
  46.           0  redo size
  47.         518  bytes sent via SQL*Net to client
  48.         524  bytes received via SQL*Net from client
  49.           2  SQL*Net roundtrips to/from client
  50.           0  sorts (memory)
  51.           0  sorts (disk)
  52.           1  rows processed
  53.                   
  54. SQL> SELECT count(1) from xcxt_wh.T_AJ_QQSJ_0828 j
  55.   2  where j.jbsj >= to_Date('2013-08-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
  56.   3  and j.jbsj <= to_Date('2013-08-10 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
  57.   4  and j.SFYCLID = '1'
  58.   5  /

  60.   COUNT(1)
  61. ----------
  62.      89309

  64. Elapsed: 00:00:00.08

  66. Execution Plan
  67. ----------------------------------------------------------
  68. Plan hash value: 738637433

  70. --------------------------------------------------------------------------------------------
  71. | Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
  72. --------------------------------------------------------------------------------------------
  73. |   0 | SELECT STATEMENT  |                        |     1 |    10 |   282   (1)| 00:00:04 |
  74. |   1 |  SORT AGGREGATE   |                        |     1 |    10 |            |          |
  75. |*  2 |   INDEX RANGE SCAN| INDEX_AJ_JJDJ0828_JBSJ | 93509 |   913K|   282   (1)| 00:00:04 |
  76. --------------------------------------------------------------------------------------------

  78. Predicate Information (identified by operation id):
  79. ---------------------------------------------------

  81.    2 - access("J"."JBSJ">=TO_DATE(' 2013-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
  82.               AND "J"."SFYCLID"='1' AND "J"."JBSJ"<=TO_DATE(' 2013-08-10 23:59:59', 'syyyy-mm-dd
  83.               hh24:mi:ss'))
  84.        filter("J"."SFYCLID"='1')


  87. Statistics
  88. ----------------------------------------------------------
  89.           1  recursive calls
  90.           0  db block gets
  91.         285  consistent gets
  92.          35  physical reads
  93.           0  redo size
  94.         524  bytes sent via SQL*Net to client
  95.         524  bytes received via SQL*Net from client
  96.           2  SQL*Net roundtrips to/from client
  97.           0  sorts (memory)
  98.           0  sorts (disk)
  99.           1  rows processed
  100.                   
  101. SQL> SELECT count(1) from xcxt_wh.T_AJ_QQSJ_0828 j
  102.   2  where j.jbsj >= to_Date('2013-08-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
  103.   3  and j.jbsj <= to_Date('2013-08-10 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
  104.   4  and j.SFYCLID = '1'
  105.   5  and (j.jbdwid ='11010542000000' or j.cbdw = '11010542000000')
  106.   6  /

  108.   COUNT(1)
  109. ----------
  110.        876

  112. Elapsed: 00:00:00.37

  114. Execution Plan
  115. ----------------------------------------------------------
  116. Plan hash value: 1223631081

  118. ----------------------------------------------------------------------------------------------------------------------
  119. | Id  | Operation                         | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  120. ----------------------------------------------------------------------------------------------------------------------
  121. |   0 | SELECT STATEMENT                  |                          |     1 |    28 |       |  1053   (2)| 00:00:13 |
  122. |   1 |  SORT AGGREGATE                   |                          |     1 |    28 |       |            |          |
  123. |   2 |   BITMAP CONVERSION COUNT         |                          |  1161 | 32508 |       |  1053   (2)| 00:00:13 |
  124. |   3 |    BITMAP AND                     |                          |       |       |       |            |          |
  125. |   4 |     BITMAP OR                     |                          |       |       |       |            |          |
  126. |   5 |      BITMAP CONVERSION FROM ROWIDS|                          |       |       |       |            |          |
  127. |*  6 |       INDEX RANGE SCAN            | INDEX_AJ_JJDJ0828_JBDWID | 93509 |       |       |   225   (1)| 00:00:03 |
  128. |   7 |      BITMAP CONVERSION FROM ROWIDS|                          |       |       |       |            |          |
  129. |*  8 |       INDEX RANGE SCAN            | INDEX_AJ_JJDJ0828_CBDW   | 93509 |       |       |     4   (0)| 00:00:01 |
  130. |   9 |     BITMAP CONVERSION FROM ROWIDS |                          |       |       |       |            |          |
  131. |  10 |      SORT ORDER BY                |                          |       |       |  2984K|            |          |
  132. |* 11 |       INDEX RANGE SCAN            | INDEX_AJ_JJDJ0828_JBSJ   | 93509 |       |       |   282   (1)| 00:00:04 |
  133. ----------------------------------------------------------------------------------------------------------------------

  135. Predicate Information (identified by operation id):
  136. ---------------------------------------------------

  138.    6 - access("J"."JBDWID"='11010542000000')
  139.    8 - access("J"."CBDW"='11010542000000')
  140.   11 - access("J"."JBSJ">=TO_DATE(' 2013-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "J"."SFYCLID"='1' AND
  141.               "J"."JBSJ"<=TO_DATE(' 2013-08-10 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
  142.        filter("J"."SFYCLID"='1' AND "J"."JBSJ"<=TO_DATE(' 2013-08-10 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
  143.               "J"."JBSJ">=TO_DATE(' 2013-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


  146. Statistics
  147. ----------------------------------------------------------
  148.           1  recursive calls
  149.           0  db block gets
  150.         471  consistent gets
  151.         188  physical reads
  152.           0  redo size
  153.         523  bytes sent via SQL*Net to client
  154.         524  bytes received via SQL*Net from client
  155.           2  SQL*Net roundtrips to/from client
  156.           1  sorts (memory)
  157.           0  sorts (disk)
  158.           1  rows processed

复制代码

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-6-14 17:41 , Processed in 0.054529 second(s), 23 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569