问题描述
昨天开发的让我从生产库中同步一个库到测试机，刚好手头有这周一的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可能会有多个执行计划，我认为统计数据变化后重新生成执行计划可能只是个时间的问题吧。
以上仅供参考
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
