HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

不均衡分区和绑定变量窥视导致的查询计划错误

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2009-09-18 02:40:39

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

    周一收到生成支持人员的报告,系统上一个作业启动后很长时间没有完成,其执行时间远远大于上周的正常执行时间。接到报告后,首先检查了系统,不存在锁队列的问题。然后查询V$SESSION_LONGOPS,立即发现下面的语句正在进行长操作:

SQL代码
  1. SELECT *   
  2.   FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT   
  3.  WHERE BKGSHMTRESULT.BKG_CFM_ID = :B1   
  4.    AND BKGSHMTRESULT.COMP_ID = :B2  

    从V$SESSION_LONGOPS看,它正在对表CR_BKG_INTMD_SHMT_PARTITION做FULL TABLE SCAN。而表CR_BKG_INTMD_SHMT_PARTITION是一张非常大的分区表,是我们之前做的优化建立的分区表(该案例我有在《11g新特性 ——更加灵活的分区策略》中提到,Partition Key是COMP_ID,分区策略是每个VIP用户一个分区,所有非VIP用户在DEFAULT分区)。

    这条语句的查询条件很简单,且在(BKG_CFM_ID,COMP_ID)上有建一个Global Index。通过直接对其解析查询计划,发现它能正确命中索引:

SQL代码
  1. SQL> EXPLAIN PLAN FOR  
  2.   2  SELECT *   
  3.   3    FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT   
  4.   4   WHERE BKGSHMTRESULT.BKG_CFM_ID = :B1   
  5.   5     AND BKGSHMTRESULT.COMP_ID = :B2;   
  6.   
  7. Explained.   
  8.   
  9. SQL> select * from table(dbms_xplan.display());   
  10.   
  11. PLAN_TABLE_OUTPUT   
  12. -----------------------------------------------------------------------------------------------------------------------------------   
  13. Plan hash value: 772272200   
  14.   
  15. -----------------------------------------------------------------------------------------------------------------------------------   
  16. | Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |   
  17. -----------------------------------------------------------------------------------------------------------------------------------   
  18. |   0 | SELECT STATEMENT                   |                              |     1 |   880 |     5   (0)| 00:00:01 |       |       |   
  19. |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| CR_BKG_INTMD_SHMT_PARTITION  |     1 |   880 |     5   (0)| 00:00:01 | ROWID | ROWID |   
  20. |*  2 |   INDEX RANGE SCAN                 | CR_BKG_INTMD_PARTITION_IDX03 |     1 |       |     4   (0)| 00:00:01 |       |       |   
  21. -----------------------------------------------------------------------------------------------------------------------------------   
  22.   
  23. Predicate Information (identified by operation id):   
  24. ---------------------------------------------------   
  25.   
  26.    2 - access("BKGSHMTRESULT"."BKG_CFM_ID"=TO_NUMBER(:B1) AND "BKGSHMTRESULT"."COMP_ID"=:B2)   

    但是,通过SQL_ID查询,实际的查询计划却是全表扫描:

SQL代码
  1. SQL> select lpad(' ', 2 * (level - 1)) || operation || ' ' ||   
  2.   2         decode(id, 0, 'Cost = ' || position) "OPERATION",   
  3.   3         options,   
  4.   4         object_name   
  5.   5    from v$sql_plan   
  6.   6   start with (sql_id = 'f0mwuqfxxmtmf' and hash_value = 3151619694 and id = 0)   
  7.   7  connect by prior id = parent_id   
  8.   8         and prior sql_id = sql_id   
  9.   9         and prior hash_value = hash_value   
  10.  10   order by id, position;   
  11.   
  12. OPERATION                    OPTIONS                               OBJECT_NAME   
  13. ---------------------------- ------------------------------------- ------------------------   
  14. SELECT STATEMENT Cost = 265   
  15.   PARTITION LIST             SINGLE   
  16.     TABLE ACCESS             FULL                                  CR_BKG_INTMD_SHMT_PARTITION   

    这一现象通常是由于绑定变量窥视(Bind Variable Peeking)造成的:Peeking的变量值比较特殊,造成计算出的全表扫描代价低于索引扫描代价。为了确认问题,我们找到解析查询计划所“窥视”到的数据:

SQL代码
  1. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f0mwuqfxxmtmf', 0, 'ADVANCED'));   
  2.   
  3. PLAN_TABLE_OUTPUT   
  4. --------------------------------------------------   
  5. SQL_ID  f0mwuqfxxmtmf, child number 0   
  6. -------------------------------------   
  7. SELECT *   FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT  WHERE BKGSHMTRESULT.BKG_CFM_ID = :V_BKG_CFM_ID   
  8.   AND BKGSHMTRESULT.COMP_ID = :V_COMP_ID   
  9.   
  10. Plan hash value: 3035855418   
  11.   
  12. ---------------------------------------------------------------------------------------------------------------------   
  13. | Id  | Operation             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |   
  14. ---------------------------------------------------------------------------------------------------------------------   
  15. |   0 | SELECT STATEMENT      |                             |       |       |   265 (100)|          |       |       |   
  16. |   1 |  PARTITION LIST SINGLE|                             |     1 |   756 |   265   (1)| 00:00:04 |   KEY |   KEY |   
  17. |*  2 |   TABLE ACCESS FULL   | CR_BKG_INTMD_SHMT_PARTITION |     1 |   756 |   265   (1)| 00:00:04 |   KEY |   KEY |   
  18. ---------------------------------------------------------------------------------------------------------------------   
  19.   
  20. Query Block Name / Object Alias (identified by operation id):   
  21. -------------------------------------------------------------   
  22.   
  23.    1 - SEL$1   
  24.    2 - SEL$1 / BKGSHMTRESULT@SEL$1   
  25.   
  26. Outline Data   
  27. -------------   
  28.   
  29.   /*+   
  30.       BEGIN_OUTLINE_DATA   
  31.       IGNORE_OPTIM_EMBEDDED_HINTS   
  32.       OPTIMIZER_FEATURES_ENABLE('10.2.0.3')   
  33.       OPT_PARAM('_complex_view_merging' 'false')   
  34.       ALL_ROWS   
  35.       OUTLINE_LEAF(@"SEL$1")   
  36.       FULL(@"SEL$1" "BKGSHMTRESULT"@"SEL$1")   
  37.       END_OUTLINE_DATA   
  38.   */   
  39.   
  40. Peeked Binds (identified by position):   
  41. --------------------------------------   
  42.   
  43.    1 - :V_BKG_CFM_ID (NUMBER): 592533   
  44.    2 - :V_COMP_ID (VARCHAR2(30), CSID=873): 'BANDHK270600815'  

    可以看到,两个变量的值分别为V_BKG_CFM_ID : 592533、V_COMP_ID : 'BANDHK270600815'。使用这2个值,再重新解析查询计划,果然是全表扫描:

SQL代码
  1. SQL> explain plan for  
  2.   2  SELECT *   
  3.   3    FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT   
  4.   4   WHERE BKGSHMTRESULT.BKG_CFM_ID = 592533   
  5.   5     AND BKGSHMTRESULT.COMP_ID = 'BANDHK270600815';   
  6.   
  7. Explained.   
  8.   
  9. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());   
  10.   
  11. PLAN_TABLE_OUTPUT   
  12. ---------------------------------------------------------------------------------------------------------------------   
  13. Plan hash value: 554208192   
  14.   
  15. ---------------------------------------------------------------------------------------------------------------------   
  16. | Id  | Operation             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |   
  17. ---------------------------------------------------------------------------------------------------------------------   
  18. |   0 | SELECT STATEMENT      |                             |     1 |   756 |   265   (1)| 00:00:04 |       |       |   
  19. |   1 |  PARTITION LIST SINGLE|                             |     1 |   756 |   265   (1)| 00:00:04 |   KEY |   KEY |   
  20. |*  2 |   TABLE ACCESS FULL   | CR_BKG_INTMD_SHMT_PARTITION |     1 |   756 |   265   (1)| 00:00:04 |    14 |    14 |   
  21. ---------------------------------------------------------------------------------------------------------------------   
  22.   
  23. Predicate Information (identified by operation id):   
  24. ---------------------------------------------------   
  25.   
  26.    2 - filter("BKGSHMTRESULT"."BKG_CFM_ID"=592536)   

    注意到在查询条件中存在Partition Key:COMP_ID,因此查询计划中存Partition List Single,仅对所在分区(14)查询。,这里的Full Table Scan实际上是对一个分区的Full Scan,而'BANDHK270600815'正是一个VIP用户。我们再看该分区上的statistics数据:

SQL代码
  1. SQL> select num_rows, blocks from dba_tab_statistics where table_name = 'CR_BKG_INTMD_SHMT_PARTITION' and owner = 'CS2_PARTY_OWNER' and partition_name = 'P_COMP_BANDHK270600815';   
  2.   
  3.   NUM_ROWS     BLOCKS   
  4. ---------- ----------   
  5.         18          8   

    分区上的数据非常少,因此这个Full Scan的Cost不高,解析出的查询计划为Full Table Scan,当数据落入DEFAULT分区(最大分区),其查询计划仍为Full Scan,从而导致了性能问题!继续查询,发现还存在少数几个分区的数据也非常少。这一结果和当初我们做POC时的有出入:在POC中,所有VIP用户的数据都在10K以上,但生产环境上却出现数据量极少的VIP用户。这一问题恐怕需要从开发那边BA/SA找答案了。作为DBA,我们的当前的任务就是如何避免再次发生由此引发的性能问题。

    由于我们的系统是工作日(周一到周五)运行,每周系统都会重启。因此,在周一很多语句都会被硬解析。这样的话,很难避免在硬解析时,窥视的数据再次落入这些小分区内。要避免再次造成性能问题,可以考虑以下方法:

  1. 相关语句上加HINT,强制使用索引。但是这样的修改涉及面太大,且如果将来Schema发生变化,代码维护更新困难;
  2. 用Stored Outline为语句固定查询计划。其缺点和第一点差不多;
  3. 禁用Bind Variable Peeking。因为我们的系统会每周重启,如果在db level禁用,风险较大,所以我们考虑在session level禁用。因为该模块的代码都是通过Package调用的,所以修改的代码量非常少:在入口函数上加上以下语句。
    SQL代码
    1. execute immediate 'alter session set "_OPTIM_PEEK_USER_BINDS" = false';  

后记

    此案例涉及两个问题值得注意:

  1. 分区的平衡问题。如果分区之间的数据量存在很大差异,在绑定变量窥视被启用(默认)的情况下,硬解析出来的查询计划在不同分区上的性能差异可能非常大;
  2. 绑定变量窥视其目的主要是帮助CBO下更加精确的计算出查询计划代价。但是,因为这依赖于被“窥视”的变量值,因而也为查询计划带来了不稳定性。数据的不平衡、分区的不平衡都可能会因为这种不稳定性而导致性能风险。   

    --- Fuyuncat Mark ---

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat