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

张维照 2019-05-31
32
0 0
摘要:同一个SQL在生产库上与测试库的执行时间差很多,测试库2秒,生产库要1分钟多

问题描述

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

专家解答

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可能会有多个执行计划,我认为统计数据变化后重新生成执行计划可能只是个时间的问题吧。

以上仅供参考


「喜欢文章,快来给作者赞赏墨值吧」

评论

0
0
Oracle
订阅
欢迎订阅Oracle频道,订阅之后可以获取最新资讯和更新通知。
墨值排行
今日本周综合
近期活动
全部
相关课程
全部