首页 » ORACLE » oracle 同样的sql生产库比测试库执行慢(案例)

oracle 同样的sql生产库比测试库执行慢(案例)

昨天开发的让我从生产库中同步一个库到测试机,刚好手头有这周一的exp备份,imp了一份,但是今天早上开发打电话找我说情况有点不对头,同一个SQL在生产库上与测试库的执行时间差很多,测试库2秒,生产库要1分钟多,于是QQ发给我SQL我看了一下执行计划,相差很大,查询的SQL很简单,就是几张表的关联,加了一个分页,最大的表有2000万多数据。

sql:

select *
  from (select ROW_.*, rownum ROWNUM_
          from (select t1.score_id,
                       t1.create_time,
                       t1.score_remark,
                       t2.subject_name,
                       t2.subject_class_score,
                       t2.subject_class_hour,
                       t3.verify_code,
                       t2.subject_id,
                       t9.score_type_id,
                       t9.score_type_name,
                       t4.ic_code,
                       t4.name,
                       t5.rank_name,
                       t1.source_flag,
                       t6.org_name as give_org_name,
                       t7.org_name as check_org_name
                  from icme_project_score  t1,
                       icme_subject        t2,
                       icme_project        t3,
                       icme_student        t4,
                       icme_student_rank   t5,
                       icme_org            t6,
                       icme_org            t7,
                       icme_score_type     t9,
                       icme_org_check_sign t10
                 where t1.subject_id = t2.subject_id
                   and t2.project_id = t3.project_id
                   and t3.score_type_id = t9.score_type_id
                   and t1.ic_code = t4.ic_code
                   and t4.rank_id = t5.rank_id
                   and t1.give_org_id = t6.org_id
                   and t10.check_org_id = 35000000
                   and t10.project_or_score in (2, 3)
                   and t10.score_type_id = t3.score_type_id
                   and t7.org_code like '0002001900190020%'
                   and t1.from_org_id = t7.org_id
                   and t1.to_org_id = 60000000
                   and t1.score_activity_date >= to_date('2011-01-01','yyyy-mm-dd')
                   and t1.score_activity_date < to_date('2011-12-31','yyyy-mm-dd') +1                    and t1.score_check_flag = 4) ROW_)  where ROWNUM_ > 0
   and ROWNUM_ <= 20

测试库的执行计划

Execution Plan
----------------------------------------------------------
Plan hash value: 3787871287                                                                                                                           

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                           |     1 |   547 |    13   (0)| 00:00:01 |
|*  1 |  VIEW                                 |                           |     1 |   547 |    13   (0)| 00:00:01 |
|   2 |   COUNT                               |                           |       |       |            |          |
|   3 |    NESTED LOOPS                       |                           |     1 |   269 |    13   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                           |     1 |   252 |    12   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                     |                           |     1 |   210 |    11   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                    |                           |     1 |   196 |    10   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                           |     1 |   173 |     8   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                           |     1 |   153 |     7   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                 |                           |     1 |   138 |     6   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                |                           |     1 |   115 |     5   (0)| 00:00:01 |
|* 11 |            TABLE ACCESS BY INDEX ROWID| ICME_PROJECT_SCORE        |     1 |    62 |     4   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | IDX_PROJECT_SCORE_TOORGID |     1 |       |     3   (0)| 00:00:01 |
|  13 |            TABLE ACCESS BY INDEX ROWID| ICME_SUBJECT              |     1 |    53 |     1   (0)| 00:00:01 |
|* 14 |             INDEX UNIQUE SCAN         | PK_ICME_SUBJECT           |     1 |       |     0   (0)| 00:00:01 |
|  15 |           TABLE ACCESS BY INDEX ROWID | ICME_PROJECT              |     1 |    23 |     1   (0)| 00:00:01 |
|* 16 |            INDEX UNIQUE SCAN          | PK_ICME_PROJECT           |     1 |       |     0   (0)| 00:00:01 |
|* 17 |          TABLE ACCESS BY INDEX ROWID  | ICME_ORG_CHECK_SIGN       |     1 |    15 |     1   (0)| 00:00:01 |
|* 18 |           INDEX UNIQUE SCAN           | PK_ICME_ORG_CHECK_SIGN    |     1 |       |     0   (0)| 00:00:01 |
|  19 |         TABLE ACCESS BY INDEX ROWID   | ICME_SCORE_TYPE           |     1 |    20 |     1   (0)| 00:00:01 |
|* 20 |          INDEX UNIQUE SCAN            | PK_ICME_SCORE_TYPE        |     1 |       |     0   (0)| 00:00:01 |
|  21 |        TABLE ACCESS BY INDEX ROWID    | ICME_STUDENT              |     1 |    23 |     2   (0)| 00:00:01 |
|* 22 |         INDEX RANGE SCAN              | PK_ICME_STUDENT           |     1 |       |     1   (0)| 00:00:01 |
|  23 |       TABLE ACCESS BY INDEX ROWID     | ICME_STUDENT_RANK         |     1 |    14 |     1   (0)| 00:00:01 |
|* 24 |        INDEX UNIQUE SCAN              | PK_ICME_STUDENT_RANK      |     1 |       |     0   (0)| 00:00:01 |
|* 25 |      TABLE ACCESS BY INDEX ROWID      | ICME_ORG                  |     1 |    42 |     1   (0)| 00:00:01 |
|* 26 |       INDEX UNIQUE SCAN               | PK_ICME_ORG               |     1 |       |     0   (0)| 00:00:01 |
|  27 |     TABLE ACCESS BY INDEX ROWID       | ICME_ORG                  |     1 |    17 |     1   (0)| 00:00:01 |
|* 28 |      INDEX UNIQUE SCAN                | PK_ICME_ORG               |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------                                   

Statistics
----------------------------------------------------------
 0  recursive calls  
 0  db block gets
 499513  consistent gets  
 0  physical reads
 0  redo size
 3375  bytes sent via SQL*Net to client
 766  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
 20  rows processed   

下面是生产库的执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 595245462

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                           |     1 |   547 |    53   (0)| 00:00:01 |
|*  1 |  VIEW                                 |                           |     1 |   547 |    53   (0)| 00:00:01 |
|   2 |   COUNT                               |                           |       |       |            |          |
|   3 |    NESTED LOOPS                       |                           |     1 |   269 |    53   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                           |     1 |   252 |    52   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                     |                           |     1 |   238 |    51   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                    |                           |     1 |   215 |    49   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                           |     1 |   195 |    48   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                           |     1 |   180 |    47   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                 |                           |     1 |   157 |    46   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                |                           |     1 |   104 |    45   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| ICME_ORG                  |     1 |    42 |     4   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | PK_ICME_ORG_CODE          |     1 |       |     3   (0)| 00:00:01 |
|* 13 |            TABLE ACCESS BY INDEX ROWID| ICME_PROJECT_SCORE        |     1 |    62 |    41   (0)| 00:00:01 |
|* 14 |             INDEX RANGE SCAN          | IDX_PROJECT_SCORE_TOORGID |  1952 |       |     6   (0)| 00:00:01 |
|  15 |           TABLE ACCESS BY INDEX ROWID | ICME_SUBJECT              |     1 |    53 |     1   (0)| 00:00:01 |
|* 16 |            INDEX UNIQUE SCAN          | PK_ICME_SUBJECT           |     1 |       |     0   (0)| 00:00:01 |
|  17 |          TABLE ACCESS BY INDEX ROWID  | ICME_PROJECT              |     1 |    23 |     1   (0)| 00:00:01 |
|* 18 |           INDEX UNIQUE SCAN           | PK_ICME_PROJECT           |     1 |       |     0   (0)| 00:00:01 |
|* 19 |         TABLE ACCESS BY INDEX ROWID   | ICME_ORG_CHECK_SIGN       |     1 |    15 |     1   (0)| 00:00:01 |
|* 20 |          INDEX UNIQUE SCAN            | PK_ICME_ORG_CHECK_SIGN    |     1 |       |     0   (0)| 00:00:01 |
|  21 |        TABLE ACCESS BY INDEX ROWID    | ICME_SCORE_TYPE           |     1 |    20 |     1   (0)| 00:00:01 |
|* 22 |         INDEX UNIQUE SCAN             | PK_ICME_SCORE_TYPE        |     1 |       |     0   (0)| 00:00:01 |
|  23 |       TABLE ACCESS BY INDEX ROWID     | ICME_STUDENT              |     1 |    23 |     2   (0)| 00:00:01 |
|* 24 |        INDEX RANGE SCAN               | PK_ICME_STUDENT           |     1 |       |     1   (0)| 00:00:01 |
|  25 |      TABLE ACCESS BY INDEX ROWID      | ICME_STUDENT_RANK         |     1 |    14 |     1   (0)| 00:00:01 |
|* 26 |       INDEX UNIQUE SCAN               | PK_ICME_STUDENT_RANK      |     1 |       |     0   (0)| 00:00:01 |
|  27 |     TABLE ACCESS BY INDEX ROWID       | ICME_ORG                  |     1 |    17 |     1   (0)| 00:00:01 |
|* 28 |      INDEX UNIQUE SCAN                | PK_ICME_ORG               |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
 10  recursive calls
 0  db block gets
 4673921  consistent gets
 0  physical reads
 0  redo size
 3515  bytes sent via SQL*Net to client
 811  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
 20  rows processed

可以看到执行计划 里生产库比测试库recursive calls 递归调用10倍,consistent gets 一致性读块差不多10倍
考虑应该是统计信息不准确的问题影响了执行计划,现对最大的表做一个全表分析用dbms_stats.GATHER_TABLE_STATS包,现在推荐用这个包,因为有的说analyze分析不准,不过GATHER_TABLE_STATS默认是不分析索引的,分析2000w数据用了10多分钟,不过提醒一下,这个进程点用cpu高达1个U的100%,再执行,哇 瞬间!

分析后生产库执行计划

Statistics
———————————————————-
0  recursive calls
0  db block gets

3931  consistent gets
0  physical reads
0  redo size
3515  bytes sent via SQL*Net to client
674  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
20  rows processed

问题还没完,通知开发的try again,说还是很慢,我再把qq聊天记录的sql 执行,果然还是1分30秒,为什么会这样?后来一想不会是这样吧,把sql 稍微做了点小改动只是把where 的顺序先后改了一下,再执行,哇,瞬间再现!到这里我要以肯定了

为什么我一再强调copy qq 聊天记录的sql,因为那个sql绝对和测试运行的一样,是因为这个sql 以前运行过已经解析过存在于shared_pool中,再次调用只会软解析,发现还是走原来的执行计划,但是你把sql稍为改动再执行就会硬解析,重新分配执行计划,CBO运用最新的统计信息,so, you know…… 我只能这样解释的通

但老白说是统计数据变了,CURSOR的一致性就破坏了,下次执行前需要重新生成执行计划,一个sql的subcursor可能有不同的执行计划,另一位网友说一个sql可能会有多个执行计划,我认为统计数据变化后重新生成执行计划可能只是个时间的问题吧。

以上仅供参考

打赏

目前这篇文章有8条评论(Rss)评论关闭。

  1. Shawanna Gottsch | #1
    2011-12-21 at 06:26

    One more thing I would like to convey is that in lieu of trying to match all your online degree programs on days of the week that you finish work (because most people are exhausted when they get home), try to receive most of your classes on the saturdays and sundays and only one or two courses in weekdays, even if it means a little time off your weekend. This pays off because on the weekends, you will be much more rested plus concentrated upon school work. Many thanks for the different tips I have realized from your blog site.

  2. Susannah | #2
    2011-06-23 at 11:05

    Oh yeah, fabuulos stuff there you!

    • Sailor | #3
      2011-11-12 at 06:39

      Super jazzed about gtteing that know-how.

  3. Brandie | #4
    2011-06-16 at 10:00

    Never seen a beettr post! ICOCBW

    • Mimosa | #5
      2011-06-22 at 15:18

      Very true! Makes a cahgne to see someone spell it out like that. 🙂

      • Early | #6
        2011-11-11 at 14:57

        Not bad at all fellas and glaals. Thanks.

    • Turk | #7
      2011-06-23 at 02:26

      That’s a mold-beraekr. Great thinking!

      • Dell | #8
        2011-11-11 at 21:23

        Wow, this is in every respect what I ndeeed to know.